Re: How do I find top CPU consumers on a windows machine running Oracle 9i (9.2.0.4 Enterprise Edition)?

> Oracle tracks the CPU usage of its own sessions and presents it in
> gv$sesstat. It may not be accurate, but the inaccurate data of one session
> is somewhat accurately relative to that of others. So a serious offender
> should stand out. Gv$sesstat joins to gv$session by SID and INST_ID for
> the session info and to gv$statname by STATISTIC# and INST_ID to restrict by
> statistic. You can start with 'CPU used by this session'.

Just wanted to point out that

(a) "CPU used by this session" does not get updated until the call ends, so for a long running single SQL statement, it would be a better idea to look for large values of 'session logical reads'. (b) Jonathan actually inspired this long ago, but I wrote a little package that snapshots the V$SESSTAT/V$SESSION_EVENT when it starts, waits for a specific number of seconds, snapshots this again and then compares and prints the difference, so this is like a STATSPACK snapshot for a session. You can find this on last years's Oracle Open World site. Combine this with a query against V$SESSION for all sessions that have ACTIVE in the STATUS column and you may have another way of getting the most active sessions... I believe Tanel has a similar utility.

-- 
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l