Re: CPU and memory metrics for databases in Oracle RAC
From: trub3101 <trub3101_at_sky.com>
Date: Fri, 26 Mar 2010 05:18:15 -0700 (PDT)
Message-ID: <4e534dc9-cccb-4a43-805e-0b24c076c1ed_at_q23g2000yqd.googlegroups.com>
On 26 Mar, 03:33, vsevolod afanassiev <vsevolod.afanass..._at_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.
Date: Fri, 26 Mar 2010 05:18:15 -0700 (PDT)
Message-ID: <4e534dc9-cccb-4a43-805e-0b24c076c1ed_at_q23g2000yqd.googlegroups.com>
On 26 Mar, 03:33, vsevolod afanassiev <vsevolod.afanass..._at_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 Received on Fri Mar 26 2010 - 07:18:15 CDT