CPU and memory metrics for databases in Oracle RAC
in [Oracle]
|
From: vsevolod afanassiev on 25 Mar 2010 23:33 May be I misunderstood the questions, but it seems that Statspack reports for each instance will give you good idea of the impact of 3- rd node. Let's say you produce Statspack reports for Node#1 and Node#2 prior to creation of Node#3 and they show for CPU usage per day Monday: Node#1: 35000 sec, Node#2: 37000 sec Tuesday: Node#1: 36000 sec, Node#2: 38000 sec Wednesday: Node#1: 34000 sec, Node#2: 33000 sec Monday: Node#1: 36000 sec, Node#2: 36000 sec Monday: Node#1: 39000 sec, Node#2: 38000 sec From this you can conclude that: 1. Each node consumes approx 10 hours of CPU time per day 2. The workload is evenly distributed between nodes 3. The workload doesn't change much between weekdays Let's say you installed 3-rd node and got something like that: Monday: Node#1: 22000 sec, Node#2: 23000 sec, Node#3: 25000 sec (and similar for other days) From this you can conclude that workload is evenly distributed between 3 nodes, total CPU usage by all nodes is still 20 hours per day. Similar observations can be made for I/O and for number of executions for top SQL statements (assuming you are using bind variables). Let's say you have SQL statement with hash value 12345. Previously it was executed 1000 times per hour on both nodes, after addition of node#3 it gets executed 660 times per hour on each node.
From: trub3101 on 26 Mar 2010 08:18
On 26 Mar, 03:33, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> wrote: > May be I misunderstood the questions, but it seems that Statspack > reports for each instance will give you good idea of the impact of 3- > rd node. > > Let's say you produce Statspack reports for Node#1 and Node#2 prior to > creation of Node#3 and they show for CPU usage per day > > Monday: Node#1: 35000 sec, Node#2: 37000 sec > Tuesday: Node#1: 36000 sec, Node#2: 38000 sec > Wednesday: Node#1: 34000 sec, Node#2: 33000 sec > Monday: Node#1: 36000 sec, Node#2: 36000 sec > Monday: Node#1: 39000 sec, Node#2: 38000 sec > > From this you can conclude that: > 1. Each node consumes approx 10 hours of CPU time per day > 2. The workload is evenly distributed between nodes > 3. The workload doesn't change much between weekdays > > Let's say you installed 3-rd node and got something like that: > > Monday: Node#1: 22000 sec, Node#2: 23000 sec, Node#3: 25000 sec > (and similar for other days) > > From this you can conclude that workload is evenly distributed between > 3 nodes, total CPU usage by all nodes is still 20 hours per day. > > Similar observations can be made for I/O and for number of executions > for top SQL statements (assuming you are using bind variables). Let's > say you have SQL statement with hash value 12345. > Previously it was executed 1000 times per hour on both nodes, after > addition of node#3 it gets executed 660 times per hour on each node. Thank you all for your replies, Mark/Vsevolod I will more than likely be adopting the comparsion approach as lets face it it is the most logic thing to do in this circumstance. I also have at look STATS$FILESTATXS table to see what I can pull out from there. Madhu, thanks for the tip. I will have a look at tool you suggested and hopefully get the 10g OEM up and running at some point. Thanks again everyone! tb3101 |