Re: CPU load per db instance

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 <oracledba.williams_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l