> 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