Re: CPU and memory metrics for databases in Oracle RAC

From: trub3101 <trub3101_at_sky.com>
Date: Thu, 25 Mar 2010 09:01:13 -0700 (PDT)
Message-ID: <19783dc5-ff10-412b-9b44-b6b32c9d7c31_at_k19g2000yqn.googlegroups.com>



On 25 Mar, 15:06, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Mar 25, 9:53 am, trub3101 <trub3..._at_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 Received on Thu Mar 25 2010 - 11:01:13 CDT

Original text of this message