--
-- Show Responce time breakdown for current user session
-- Based on idea of response_time_breakdown.sql by Steve Adams
-- (C) 2004 Dsvolk (http://dsvolk.msk.ru/oracle)
-- for Oracle 9i
-- 
select 
    substr(major, 3) "Major", substr(minor, 3) "Event class",  wait_event "Event name",
    to_char(100*ratio_to_report (time_spend) over (), '999.00') || ' %' pct
from 
(
select apt_sesstat_value ('parse time cpu') time_spend, 
'n/a' wait_event, '2 parsing' minor, '1 CPU time' major  from dual
union 

select apt_sesstat_value('recursive cpu usage') time_spend, 
'n/a' wait_event, '3 recursive call' minor, '1 CPU time' major  from dual
union 
--Other CPU stats
select  time_spend, 'n/a' wait_event, '1 Other CPU' minor, '1 CPU time' major  from 
(
 select  apt_sesstat_value('CPU used when call started') time_spend  from dual
  minus
 select apt_sesstat_value ('parse time cpu') time_spend from dual
  minus
 select apt_sesstat_value ('recursive cpu usage') time_spend from dual
)

union 
-- Waits Need select only foreground session 
select  sum(total_waits) time_spend, e.event wait_event, 
apt_event_class (e.event ) minor, 
'2 Waits ' major from v$session_event e, v$session s
where s.type = 'USER' and s.sid = e.sid
and e.event not in (select a.event_name from apt_idle_events a)
group by e.event
order by time_spend desc
)
 order by  major asc,  minor asc, time_spend desc