各スナップ間における上位5つの待機イベントをcsv形式で出力するスクリプトです。
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に取り込み、ピボットグラフでグラフ化すると以下のようなイメージになります。
（SQLはDB12.1.0.2にて試行）