DanBrother的部落格

--  Display Session CPU Usage

select s.username as "Oracle User",
       s.osuser as "OS User",
       i.consistent_gets as "Consistent Gets",
       i.physical_reads as "Physical Reads",
       s.status as "Status",
       s.sid "SID",s.serial# as "Serial#",
       s.machine as "Machine",
       s.program as "Program",
       to_char(logon_time, 'YYYY/MM/DD HH24:MI:SS') as "Logon Time",
       w.seconds_in_wait as "Idle Time",
       P.SPID as "SPID",
       name as "Stat CPU",
       value
from v$session s,v$sess_io i,v$session_wait w,v$process p,v$statname n,v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and s.username is not null
and 'SQL*Net message from client' = w.event(+)
and s.paddr = p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.sid = s.sid
order by i.consistent_gets desc,i.physical_reads desc;

[Reference]:
http://www.oracle.com/technology/oramag/code/tips2005/112805.html
 Jony Safi, DBA at Videotron Ltee, in Montreal, Quebec,Canada