From: trub3101 on
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
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
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
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
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