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 <rjamya@xxxxxxxxx> 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@xxxxxxxxx> > 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 -- Regards Zhu Chao www.cnoug.org