Avewaits.sql

-- (c) Kyle Hailey 2007

column f_days new_value v_days
select &days f_days from dual;
column f_secs new_value v_secs
select 900 f_secs from dual;
--select &seconds f_secs from dual;
column f_bars new_value v_bars
select 5 f_bars from dual;
column aveact format 999.99
column graph format a30
column wait format a20
column address format a9
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 start_time
    , sum(top.cnt) tot
    , round(max(decode(top.seq,1,pct,null)),2) fpct
    , max(decode(top.seq,1,wait,null)) first
    , round(max(decode(top.seq,2,pct,null)),2) spct
    , max(decode(top.seq,2,wait,null)) second
    , round(max(decode(top.seq,3,pct,null)),2) tpct
    , max(decode(top.seq,3,wait,null)) third
 from
 (
    select
           start_time
         , wait
         , cnt
         , row_number() over ( partition by start_time order by cnt desc ) seq
         , ratio_to_report( sum(cnt)) over ( partition by start_time ) pct
    from (
           select
              to_char(min(sample_time),'MON DD HH24:MI') start_time
            , count(*) cnt
            , substr(decode(ash.session_state,'ON CPU','ON CPU',n.name),0,15) wait
           from v$ash ash,
                v$ash_event_names n
           where
                 n.event# (+) = ash.event#
             and sample_time >= sysdate - (&minutes/(24*60))
            group by round(to_char(sample_time,'DDDSSSSS')/&v_secs),
                     decode(ash.session_state,'ON CPU','ON CPU',n.name)
            order by to_char(min(sample_time),'MON DD HH24:MI')
          )
     group by start_time, wait,cnt
 ) top
where seq <= 3
 group by start_time

/