Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CPU load per db instance

Re: CPU load per db instance

From: Zhu,Chao <>
Date: Thu, 22 Nov 2007 22:01:28 +0800
Message-ID: <>

A rough estimate is:
CPU used by each instance =
(CPU_USED_BY_THIS_SESSION_FOR_INSTANCE_1/CPU_USED_BY_THIS_SESSION_TOTAL_INSTNANCES) * CPU_USAGE% CPU_USED_BY_THIS_SESSION could jsut be stats$sysstat snaptime1-snaptime2; --this requires you have regular statspack snapshot running.

or, if your oracle is running with different OSUSER, just prstat -a, it will report to you.

For a quick look of the CPU usage (the top cpu consumer), just /usr/ucb/ps -aux|sort, and you can see which instnace is running on CPU from the oracle_sid.

On Nov 22, 2007 11:28 AM, rjamya <> wrote:

> Thanks Dennis ...
> No, the aim is not for capacity planning ... when i get called to look at
> a server that run at 100% CPU for more than an hour, first thing i want to
> see which of the instances _could_ be culprit. (it happened and i managed to
> bring down CPU usage after tuning a couple of queries and adding an index on
> one of the instances, so server is about 60-80% loaded now, still working on
> other parts).
> But just wanted to see if something like this existed. I was planning to
> install LTOM, but unable to do so until java on server is upgraded. To get
> that java upgrade quickly has less chances than Congress passing the AMT
> concession bill in the next week. :)
> Yes, statspack is taking snapshots, and will probably hack a script to
> collect the information that puts together info from ps/vmstat/v$session
> etc, eventually. Zones isn't a possibility as it is sol8, 4dual core cpus,
> runs 3 (normally 6) 9206 instances.
> So, yes will eventually write the script, but wanted to see if anyone had
> already invented the wheel.
> Thanks and regards
> Raj
> On Nov 21, 2007 4:27 PM, Dennis Williams <>
> wrote:
> > Raj,
> >
> > A couple of thoughts for you and hopefully someone else has better
> > suggestions. One question is what is your goal. Are you just wanting a rough
> > idea for capacity planning or if you are going to bill customers based on
> > this. If the latter, you'll want a more bulletproof solution whose integrity
> > you can defend.
> > I'm not sure if STATSPACK will help much because it runs within the
> > Oracle environment. I think you need something at the Solaris level.
> > O.S. tools often track usage by O.S. user, so you could install each
> > Oracle instance in different Unix username, but most of us DBAs consider
> > that nonstandard. In Solaris 10, you could investigate Zones, but I'm not
> > sure if Oracle 10g supports Zones yet.
> > Solaris 10 includes DTrace and I'm guessing that might be able to do
> > what you want. It is a very powerful tool for this type of work. But you'll
> > probably have to ask a forum for Solaris like the Usenet Newsgroup
> > comp.unix.solaris.
> > A simple-minded approach would be to look at your processes
> > associated with each instance and see if there is anything unique about
> > them, using the "ps" command. Then you could write a Unix script that would
> > execute ps and categorize the processes by Oracle instance and sum the CPU
> > load for that instance. Run that every few minutes as a cron job and
> > accumulate an estimate of usage over time. Crude but I think doable.
> >
> > Dennis Williams
> >
> --
> -----
> Best regards
> RJamya

Zhu Chao

Received on Thu Nov 22 2007 - 08:01:28 CST

Original text of this message