[ORACLE] Wait Event, Top5 Wait Event 성능 분석 쿼리

※
:DBID
:INSTANCE_NUMBER
:BEGIN_SNAP_ID
:END_SNAP_ID
값 설정후 스크립트 실행

 snap_id 찾기


select snap_id, to_char(min(begin_time),'dd hh24:mi') DTIME, to_char(min(end_time),'dd hh24:mi') ETIME
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE begin_time > sysdate-2
and instance_number=1
group by snap_id
order by snap_id
/

 Wait Event 



select EVENT_NAME "Event", TOTAL_WAITS "Wait", round(TIME_WAITED_MICRO/1000000) "Time(s)",
       round(TIME_WAITED_MICRO/TOTAL_WAITS/1000) "Avg Wait(ms)",
       case when TOT_DBTIME = 0 then 0 else round(100*TIME_WAITED_MICRO/TOT_DBTIME,2) end "%DB Time",
       WAIT_CLASS "Wait Class"
  from (select EVENT_NAME, WAIT_CLASS,
               sum(case when SNAP_ID = :END_SNAP_ID   then TOTAL_WAITS_FG else 0 end) -
               sum(case when SNAP_ID = :BEGIN_SNAP_ID then TOTAL_WAITS_FG else 0 end) TOTAL_WAITS,
               sum(case when SNAP_ID = :END_SNAP_ID   then TOTAL_TIMEOUTS_FG else 0 end) -
               sum(case when SNAP_ID = :BEGIN_SNAP_ID then TOTAL_TIMEOUTS_FG else 0 end) TOTAL_TIMEOUTS,
               sum(case when SNAP_ID = :END_SNAP_ID   then TIME_WAITED_MICRO_FG else 0 end) -
               sum(case when SNAP_ID = :BEGIN_SNAP_ID then TIME_WAITED_MICRO_FG else 0 end) TIME_WAITED_MICRO
          from DBA_HIST_SYSTEM_EVENT
         where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID in (:BEGIN_SNAP_ID, :END_SNAP_ID)
           and WAIT_CLASS <> 'Idle'
         group by EVENT_NAME, WAIT_CLASS
        union all
        select 'DB CPU', null, null, null,
               (sum(case when SNAP_ID = :END_SNAP_ID   then VALUE else 0 end) -
                sum(case when SNAP_ID = :BEGIN_SNAP_ID then VALUE else 0 end))
          from DBA_HIST_SYS_TIME_MODEL
         where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID in (:BEGIN_SNAP_ID, :END_SNAP_ID)
           and STAT_NAME = 'DB CPU'
         order by 5 desc, 3 desc) e,
       (select sum(case when SNAP_ID = :END_SNAP_ID   then VALUE else 0 end) -
               sum(case when SNAP_ID = :BEGIN_SNAP_ID then VALUE else 0 end) TOT_DBTIME
          from DBA_HIST_SYS_TIME_MODEL
         where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID in (:BEGIN_SNAP_ID, :END_SNAP_ID)
           and STAT_NAME = 'DB time') s
 where TIME_WAITED_MICRO > 0 and rownum <= :T_EVT_N
/




Top 5 Wait Event 



select to_char(END_INTERVAL_TIME, 'DD HH24:MI') TIME,
       round(T1/1000/EL,2) T1, round(T2/1000/EL,2) T2, round(T3/1000/EL,2) T3, round(T4/1000/EL,2) T4,
       round(T5/1000/EL,2) T5, N1, N2, N3, N4, N5
  from (select SNAP_ID,
               T1 - LAG(T1, 1, 0) OVER (ORDER BY SNAP_ID) T1, T2 - LAG(T2, 1, 0) OVER (ORDER BY SNAP_ID) T2,
               T3 - LAG(T3, 1, 0) OVER (ORDER BY SNAP_ID) T3, T4 - LAG(T4, 1, 0) OVER (ORDER BY SNAP_ID) T4,
               T5 - LAG(T5, 1, 0) OVER (ORDER BY SNAP_ID) T5, N1, N2, N3, N4, N5
          from (select SNAP_ID,
                       sum(case when T = 1 then TIME_WAITED_MICRO else 0 end) T1,
                       sum(case when T = 2 then TIME_WAITED_MICRO else 0 end) T2,
                       sum(case when T = 3 then TIME_WAITED_MICRO else 0 end) T3,
                       sum(case when T = 4 then TIME_WAITED_MICRO else 0 end) T4,
                       sum(case when T = 5 then TIME_WAITED_MICRO else 0 end) T5,
                       max(case when T = 1 then e.EVENT_NAME else null end) N1,
                       max(case when T = 2 then e.EVENT_NAME else null end) N2,
                       max(case when T = 3 then e.EVENT_NAME else null end) N3,
                       max(case when T = 4 then e.EVENT_NAME else null end) N4,
                       max(case when T = 5 then e.EVENT_NAME else null end) N5
                  from DBA_HIST_SYSTEM_EVENT e,
                       (select EVENT_NAME, ROWNUM T 
                          from (select case when WAIT_CLASS = 'Idle' then 0 else 1 end WC, EVENT_NAME,
                                       sum(case when SNAP_ID = :END_SNAP_ID   then TOTAL_WAITS_FG else 0 end) -
                                       sum(case when SNAP_ID = :BEGIN_SNAP_ID then TOTAL_WAITS_FG else 0 end) TOTAL_WAITS,
                                       sum(case when SNAP_ID = :END_SNAP_ID   then TOTAL_TIMEOUTS_FG else 0 end) -
                                       sum(case when SNAP_ID = :BEGIN_SNAP_ID then TOTAL_TIMEOUTS_FG else 0 end) TOTAL_TIMEOUTS,
                                       sum(case when SNAP_ID = :END_SNAP_ID   then TIME_WAITED_MICRO_FG else 0 end) -
                                       sum(case when SNAP_ID = :BEGIN_SNAP_ID then TIME_WAITED_MICRO_FG else 0 end) TIME_WAITED_MICRO,
                                       max(case when WAIT_CLASS = 'Idle' then 1 else 0 end) WAIT_CLASS
                                  from DBA_HIST_SYSTEM_EVENT
                                 where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID in (:BEGIN_SNAP_ID, :END_SNAP_ID)
                                 group by WAIT_CLASS, EVENT_NAME
                                 order by WAIT_CLASS, TIME_WAITED_MICRO desc, TOTAL_WAITS desc, EVENT_NAME)
                         where rownum <= :T_EVT_N) t
                 where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID between :BEGIN_SNAP_ID and :END_SNAP_ID
                   and e.EVENT_NAME = t.EVENT_NAME
                 group by SNAP_ID)) e,
       (select SNAP_ID, END_INTERVAL_TIME, extract(day from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*86400 + 
               extract(hour from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*3600 + 
               extract(minute from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))*60 + 
               round(extract(second from (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME))) EL
          from DBA_HIST_SNAPSHOT
         where INSTANCE_NUMBER = :INSTANCE_NUMBER and DBID = :DBID and SNAP_ID between :BEGIN_SNAP_ID and :END_SNAP_ID) s
 where s.SNAP_ID = e.SNAP_ID
   and s.SNAP_ID > :BEGIN_SNAP_ID
 order by s.SNAP_ID
/