Re: CPU usage per schema

From: <schimme_at_gmail.com>
Date: 5 Feb 2007 00:13:14 -0800
Message-ID: <1170663194.657544.181390@m58g2000cwm.googlegroups.com>

I'm not try to resolve my db performanse problems. I just try to identify which schema is significanr user od CPU and i run this script

select 	ses.schemaname,
	sum(sest.VALUE) "CPU used by schema"
from 	v$session ses,
	v$statname sn,
	v$sesstat sest
where 	ses.SID = sest.SID
and 	sn.STATISTIC# = sest.STATISTIC#
and 	sest.VALUE is not null
and 	sest.VALUE != 0
and	ses.status='ACTIVE'
and     sest.statistic#='12'

group by ses.schemaname

bye

MS
yong321_at_yahoo.com je napisao/la:
> On Feb 3, 9:56 pm, schi..._at_gmail.com wrote:
> > On my Oracle RDBMS server 9.2.0.6 RAC on one node I have 0 % idle
> > time. I try ro find out top session and I indetify that. Now I neen
> > some script to find out percentage CPU usage per schema ...
> >
> > TIA
> > Me
>
> Interesting question. How about periodically check v$sesstat for 'CPU
> used by this session' and group all sessions with the same username
> together? That gives you CPU usage per schema at the moment. If the
> sessions usually log in for a long time, that's close to the aggregate
> CPU usage per schema. If they log in and out quickly, how about
> creating a logoff trigger to record that statistic somewhere?
>
> Yong Huang
Received on Mon Feb 05 2007 - 02:13:14 CST