CPU and memory metrics for databases in Oracle RAC
in [Oracle]
|
From: trub3101 on 25 Mar 2010 09:53 Hi, I have been tasked to provide some cpu and memory metrics for the individual databases in our Oracle RAC prior to and after the installation of the third database node installed recently. Would it be possible to extract this information from the tables in the PERFSTAT schema for each database where we have snapshots which pre-dates the install (for the first two nodes)? If so would someone be able to tell which tables I should be using to extract this information? Many thanks, tb3101
From: Mark D Powell on 25 Mar 2010 11:06 On Mar 25, 9:53 am, trub3101 <trub3...(a)sky.com> wrote: > Hi, > > I have been tasked to provide some cpu and memory metrics for the > individual databases in our Oracle RAC prior to and after the > installation of the third database node installed recently. > > Would it be possible to extract this information from the tables in > the PERFSTAT schema for each database where we have snapshots which > pre-dates the install (for the first two nodes)? > > If so would someone be able to tell which tables I should be using to > extract this information? > > Many thanks, > tb3101 You should be a little more specific on exactly what information you want. Does any of the values you want currently appear on a statspack report? You should specify which version of Oracle is in use since there have been a few changes to the base statspack tables over releases. HTH -- Mark D Powell --
From: trub3101 on 25 Mar 2010 12:01 On 25 Mar, 15:06, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Mar 25, 9:53 am, trub3101 <trub3...(a)sky.com> wrote: > > > Hi, > > > I have been tasked to provide some cpu and memory metrics for the > > individual databases in our Oracle RAC prior to and after the > > installation of the third database node installed recently. > > > Would it be possible to extract this information from the tables in > > the PERFSTAT schema for each database where we have snapshots which > > pre-dates the install (for the first two nodes)? > > > If so would someone be able to tell which tables I should be using to > > extract this information? > > > Many thanks, > > tb3101 > > You should be a little more specific on exactly what information you > want. Does any of the values you want currently appear on a statspack > report? > > You should specify which version of Oracle is in use since there have > been a few changes to the base statspack tables over releases. > > HTH -- Mark D Powell -- Thanks for your reply Mark, You are right of course! Here are the DB and OS details: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production (3 node RAC) Redhat 4 Advanced Server As for being more specific ...hmmmm.. Ideally I would like to be able to capture the cpu and memory usage as produced by the sar commands: sar -r (memory) and sar -u (cpu) which captures usage for the server as whole. e.g. sar -r %memused value:- 03:08:01 PM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad 03:10:01 PM 2015404 14398716 87.72 656516 12537660 1052216 0 0.00 0 and sar -u typically the %user value:- 03:08:01 PM CPU %user %nice %system %iowait %idle 03:10:01 PM all 72.35 0.00 9.66 1.59 16.40 I would like to capture this information (or any metrics relevant to this!) for each database running on the server. The business is trying to ascertain whether or not they can afford to re-instate a functionality that had to be removed from a particular application because of the high CPU usage and server load which would eventually cause the app to crash. We have since installed an additional node which has reduced the CPU and Server Load spikes to a negiligble count. Indeed, I would be very much interested if someone were to point me to any values to compare between statspack reports pre and post third node installation that would give credence as to if we could or why we should not be re-introducing said functionality. I hope I have not meandered too much. Thanks tb3101
From: Mark D Powell on 25 Mar 2010 13:53 On Mar 25, 12:01 pm, trub3101 <trub3...(a)sky.com> wrote: > On 25 Mar, 15:06, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > > > > On Mar 25, 9:53 am, trub3101 <trub3...(a)sky.com> wrote: > > > > Hi, > > > > I have been tasked to provide some cpu and memory metrics for the > > > individual databases in our Oracle RAC prior to and after the > > > installation of the third database node installed recently. > > > > Would it be possible to extract this information from the tables in > > > the PERFSTAT schema for each database where we have snapshots which > > > pre-dates the install (for the first two nodes)? > > > > If so would someone be able to tell which tables I should be using to > > > extract this information? > > > > Many thanks, > > > tb3101 > > > You should be a little more specific on exactly what information you > > want. Does any of the values you want currently appear on a statspack > > report? > > > You should specify which version of Oracle is in use since there have > > been a few changes to the base statspack tables over releases. > > > HTH -- Mark D Powell -- > > Thanks for your reply Mark, > > You are right of course! Here are the DB and OS details: > > Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production (3 > node RAC) > Redhat 4 Advanced Server > > As for being more specific ...hmmmm.. > > Ideally I would like to be able to capture the cpu and memory usage as > produced by the sar commands: sar -r (memory) and sar -u (cpu) which > captures usage for the server as whole. e.g. sar -r %memused value:- > > 03:08:01 PM kbmemfree kbmemused %memused kbbuffers kbcached > kbswpfree kbswpused %swpused kbswpcad > 03:10:01 PM 2015404 14398716 87.72 656516 > 12537660 1052216 0 0.00 0 > > and sar -u typically the %user value:- > > 03:08:01 PM CPU %user %nice %system %iowait > %idle > 03:10:01 PM all 72.35 0.00 9.66 > 1.59 16.40 > > I would like to capture this information (or any metrics relevant to > this!) for each database running on the server. > > The business is trying to ascertain whether or not they can afford to > re-instate a functionality that had to be removed from a particular > application because of the high CPU usage and server load which would > eventually cause the app to crash. We have since installed an > additional node which has reduced the CPU and Server Load spikes to a > negiligble count. > > Indeed, I would be very much interested if someone were to point me to > any values to compare between statspack reports pre and post third > node installation that would give credence as to if we could or why we > should not > be re-introducing said functionality. > > I hope I have not meandered too much. > > Thanks > tb3101- Hide quoted text - > > - Show quoted text - If you want sar information I suggest you write a shell script to capture out what you want. I cannot think of where the statspack tables have system memory and cpu usage statistics recorded in it. It does have Oracle shared pool utilization and cpu time used information but I am not sure if this information really serves any useful information for comparing the performance of Oracle when there were two instance to when ther are three instances on the box. The average IO time statistics recorded however might be useful. See table STATS$FILESTATXS. I would just capture a prime time report for each instance taken over the same time period and then I would manually compare this to new reports made during the same time period after the new database has been added. HTH -- Mark D Powell --
From: madhu on 25 Mar 2010 19:52
The stats you are looking at is not available in the database (cpu, memory..). I recommend the freely available tool - "cacti" ( http://www.cacti.net) . It's OS level tool, not DB. Alternatively you could also consider Enterprise Manager (the >=10g version). -Madhu Sreeram |