Re: CPU load per db instance

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

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