AWRから指定した期間の待機イベントTOP5をcsv形式で取得する方法

各スナップ間における上位5つの待機イベントをcsv形式で出力するスクリプトです。

```sql:get_top5_wait_event.sql
set serveroutput on
set linesize 10000
set pagesize 0
set trimspool on
set termout off

spool top5_wait_event.log
prompt begin_snapid,yyyy,mm,dd,hh,end_snapid,instance_num,event,waits,time

declare
  event varchar2(100);
  waits number;
  time  number;
begin
  for snlp in
  (
    select
            bsn.SNAP_ID bsid,
            esn.SNAP_ID esid,
            to_char(bsn.END_INTERVAL_TIME,'yyyy') yyyy,
            to_char(bsn.END_INTERVAL_TIME,'mm')   mm,
            to_char(bsn.END_INTERVAL_TIME,'dd')   dd,
            to_char(bsn.END_INTERVAL_TIME,'hh24') hh,
            bsn.INSTANCE_NUMBER inum
    from 
            DBA_HIST_SNAPSHOT bsn,
            DBA_HIST_SNAPSHOT esn
    where
            bsn.DBID = esn.DBID
        and bsn.INSTANCE_NUMBER = esn.INSTANCE_NUMBER
        and bsn.END_INTERVAL_TIME = esn.BEGIN_INTERVAL_TIME
        and bsn.STARTUP_TIME = esn.STARTUP_TIME
        and bsn.END_INTERVAL_TIME >= to_date('2016/04/05 09:50','yyyy/mm/dd hh24:mi')
        and esn.END_INTERVAL_TIME <= to_date('2016/04/08 12:10','yyyy/mm/dd hh24:mi')
  ) loop

      for evlst in
      (
        select
                event, waits, time
        from
        (   
            select
                event,
                wtfg waits ,
                tmfg/1000000 time
            from
            (
                select 
                     e.EVENT_NAME event,
                     case  when e.TOTAL_WAITS_FG is not null
                           then e.TOTAL_WAITS_FG - nvl(b.total_waits_fg,0)
                           else (e.TOTAL_WAITS - nvl(b.TOTAL_WAITS,0))
                                 - greatest(0, (nvl(ebg.TOTAL_WAITS,0)
                                 - nvl(bbg.TOTAL_WAITS,0)))
                     end  wtfg,
                     case when e.TIME_WAITED_MICRO_FG is not null
                          then e.TIME_WAITED_MICRO_FG - nvl(b.TIME_WAITED_MICRO_FG,0)
                          else (e.TIME_WAITED_MICRO - nvl(b.TIME_WAITED_MICRO,0))
                                - greatest(0,(nvl(ebg.TIME_WAITED_MICRO,0)
                                - nvl(bbg.TIME_WAITED_MICRO,0)))
                     end tmfg
                from
                     DBA_HIST_SYSTEM_EVENT b,
                     DBA_HIST_SYSTEM_EVENT e,
                     DBA_HIST_BG_EVENT_SUMMARY bbg,
                     DBA_HIST_BG_EVENT_SUMMARY ebg
                where
                       b.SNAP_ID  (+)  = snlp.bsid
                   and e.SNAP_ID       = snlp.esid
                   and bbg.SNAP_ID (+) = snlp.bsid
                   and ebg.SNAP_ID (+) = snlp.esid
                   and e.INSTANCE_NUMBER = snlp.inum
                   and e.DBID = b.DBID (+)
                   and e.INSTANCE_NUMBER = b.INSTANCE_NUMBER (+)
                   and e.EVENT_ID        = b.EVENT_ID (+)
                   and e.DBID            = ebg.DBID (+)
                   and e.INSTANCE_NUMBER = ebg.INSTANCE_NUMBER (+)
                   and e.EVENT_ID        = ebg.EVENT_ID (+)
                   and e.DBID            = bbg.DBID (+)
                   and e.INSTANCE_NUMBER = bbg.INSTANCE_NUMBER (+)
                   and e.EVENT_ID        = bbg.EVENT_ID (+)
                   and e.TOTAL_WAITS     > nvl(b.TOTAL_WAITS,0)
                   and e.WAIT_CLASS     <> 'Idle'
                union all
                select
                    'DB CPU' event, 
                     to_number(null)  wtfg,
                     (esy.VALUE - bsy.VALUE) tmfg
                from
                     DBA_HIST_SYS_TIME_MODEL  bsy,
                     DBA_HIST_SYS_TIME_MODEL  esy
                where
                       bsy.SNAP_ID  (+)  = snlp.bsid
                   and esy.SNAP_ID       = snlp.esid
                   and bsy.INSTANCE_NUMBER = snlp.inum
                   and esy.INSTANCE_NUMBER = snlp.inum
                   and bsy.STAT_NAME = esy.STAT_NAME
                   and bsy.STAT_NAME = 'DB CPU'
            ) 
            order by tmfg desc,
                     wtfg desc
        )
        where rownum <= 5
      ) loop
           dbms_output.put_line(
                              '"'|| snlp.bsid
                           || '","'|| snlp.yyyy
                           || '","'|| snlp.mm
                           || '","'|| snlp.dd
                           || '","'|| snlp.hh
                           || '","'|| snlp.esid
                           || '","'|| snlp.inum
                           || '","'|| evlst.event
                           || '","'|| evlst.waits
                           || '","'|| evlst.time ||'"');

      end loop;

  end loop;
end;
/
spool off
/

```

Excelに取り込み、ピボットグラフでグラフ化すると以下のようなイメージになります。
![wait_event.png](https://qiita-image-store.s3.amazonaws.com/0/120705/05aa1c8a-9f99-71fd-fcb7-c63b79786a15.png)

（SQLはDB12.1.0.2にて試行）