-- (c) Kyle Hailey 2007

clear breaks
clear computes
column wait format a20
--break on sql_id skip 1

select sql_id, 
       substr(wait,0,25) event,  
       count(wait) cnt
       ,sum(count(wait)) over ( partition by sql_id order by count(wait) rows unbounded preceding ) rsum

column first format a15
column second format a15
column third  format a15
column tot  format 99999
column fpct format 999.99
column spct format 999.99
column tpct format 999.99

select sql_id 
     , sum(cnt) tot
     , round(max(decode(seq,1,pct,null)),2) fpct
     , max(decode(seq,1,wait,null)) first
     , round(max(decode(seq,2,pct,null)),2) spct
     , max(decode(seq,2,wait,null)) second
     , round(max(decode(seq,3,pct,null)),2) tpct
     , max(decode(seq,3,wait,null)) third
  from
  (
     select 
            sql_id
          , wait
          , cnt
          , row_number() over ( partition by sql_id order by cnt desc ) seq
          , ratio_to_report( sum(cnt)) over ( partition by sql_id ) pct
     from (
            select
               ash.SQL_ID sql_id
             , count(*) cnt
             , substr(decode(ash.session_state,'ON CPU','ON CPU',n.name),0,15) wait
            from sash ash,
                 sash_event_names n
            where SQL_ID is not NULL  
              and n.event# = ash.event#
              and SQL_ID > 0
              and sample_time >= sysdate - (&minutes/(24*60))
            group by sql_id, decode(ash.session_state,'ON CPU','ON CPU',n.name) 
           ) 
      group by sql_id, wait,cnt
  )
  where seq <= 3
  group by sql_id
  order by sum(cnt)
/