Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: CPU load per db instance

From: Dennis Williams <oracledba.williams_at_gmail.com>
Date: Thu, 22 Nov 2007 09:29:52 -0600
Message-ID: <de807caa0711220729j75d296f1lfffa70a3ce6fa4f1@mail.gmail.com>


Raj,

For this situation, what I've done is:

Have a Happy Thanksgiving (assuming you are in the U.S.). Dennis Williams

On 11/21/07, rjamya <rjamya_at_gmail.com> 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 < 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
Received on Thu Nov 22 2007 - 09:29:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US