-- 
-- Show User session orderer by wait class 
-- Examples of wait class:  
-- Normal I/O, Other CPU and so one
-- (C) 2004 Dsvolk (http://dsvolk.msk.ru/oracle)
-- for Oracle 9i 
-- 
select a.sid, s.serial#, s.program, s.username,  
    to_char(100*ratio_to_report (time_spend) over (), '999.00') || ' %' pct
 from 
(
select e.sid, apt_sesstat_value ('parse time cpu', e.sid) time_spend, 
'n/a' wait_event  from  v$session_event e
where '&minor' = 'parse time cpu'
union 
select e.sid, apt_sesstat_value ('recursive cpu usage', e.sid) time_spend, 
'n/a' wait_event  from  v$session_event e
where '&minor' = 'recursive cpu usage'

union 

select  sid, time_spend, 'n/a' wait_event from
(
 select e.sid, apt_sesstat_value ('CPU used when call started', e.sid) time_spend, 
 'n/a' wait_event  from  v$session_event e
 where '&minor' = 'Other CPU'
minus
 select e.sid, apt_sesstat_value ('parse time cpu', e.sid) time_spend, 
 'n/a' wait_event  from  v$session_event e
 where '&minor' = 'Other CPU'
minus
 select e.sid, apt_sesstat_value ('recursive cpu usage', e.sid) time_spend, 
 'n/a' wait_event  from  v$session_event e
 where '&minor' = 'Other CPU'
)


union


select  e.sid, sum(total_waits) time_spend, e.event wait_event
from v$session_event e 
where substr(apt_event_class (e.event), 3) = '&minor'
group by e.sid,  e.event
order by time_spend desc
 ) a, v$session s where a.sid = s.sid and s.type = 'USER'
 order by time_spend desc