"A Tour of the AWR Tables" [NOCOUG Summer Conference]

AWR table map을 찾던 중 "A Tour of the AWR Tables"라는 NOCOUG Summer Conference에서 소개된 자료를 확인할 수 있었습니다. 제목을 보니 AWR관련 table에 대한 여러 힌트를 얻을 수 있을 듯해서 자료를 보니 "load spike"를 찾기 위한 좀 다른 방법을 설명하고 있네요.

이전 " oracle awr 설정 시 고려해야 할 점" post에서 강호동, 이건희 회장까지 등장시키며 AWR 자료 수집 주기를 15 ~20분으로 줄여야한다고 글을 썼는데, 이 글의 저자는 "In my experience, the hourly interval is appropriate." 라고 하더군요. 저자는 DBA_HIST_SYSSTAT의 DB time, elapsed time 등의 load profile 자료를 사용하지 않고 1초마다 수집되는 ASH의 자료를 이용해 "load spike"를 찾는 방법을 제시하고 있습니다.

오옷 .. 그런 방법이.. 근데, 그 외의 성능관련 정보는 어떻게... --;
추가적인 AWR 관련 table을 이용한 문제가 될만한 SQL 찾는 방법 등도 같이 소개합니다.

Average Active Session이 튀는 시간 찾기.

column sample_hour format a16

select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24')
;

SAMPLE_HOUR          CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
----------------- ---------- ---------- ---------- ------------
2008-04-16 07:00         1.4         .4        1.8           .6
2008-04-16 08:00         1.8         .5        2.3            1
2008-04-16 09:00         2.3         .5        2.8          1.3
2008-04-16 10:00         2.6         .6        3.2          2.3
2008-04-16 11:00         3.5         .6        4.1          2.3
2008-04-16 12:00         2.4         .6          3          1.1
2008-04-16 13:00         2.3         .6        2.9            1
2008-04-16 14:00         3.7        2.7        6.4         95.4   <== spike in variance
2008-04-16 15:00         3.1         .7        3.8          1.9
2008-04-16 16:00         2.9         .7        3.6          1.6
2008-04-16 17:00         2.3         .4        2.7           .9
2008-04-16 18:00         2.1         .6        2.7          2.6

Average Active Session이 튀는 시간대의 특정 분단위 찾기.

column sample_minute format a16

select
   to_char(round(sub1.sample_time, 'MI'), 'YYYY-MM-DD HH24:MI') as sample_minute,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        v$active_session_history
     where
        sample_time > sysdate - (&minutes/1440)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'MI')
order by
   round(sub1.sample_time, 'MI')
;

SAMPLE_MINUTE           CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
-------------------- ---------- ---------- ---------- ------------
2008-04-16 13:54:00           3          1          4            0
2008-04-16 13:55:00         3.2         .3        3.5           .7
2008-04-16 13:56:00         4.2         .3        4.5          3.4
2008-04-16 13:57:00         3.8         .8        4.7           .7
2008-04-16 13:58:00         6.3          1        7.3          1.6
2008-04-16 13:59:00         3.4         .4        3.8           .2
2008-04-16 14:00:00         8.3         .5        8.8          1.8
2008-04-16 14:01:00        10.7        2.2       12.8           .5
2008-04-16 14:02:00         3.5         .7        4.2           .5
2008-04-16 14:03:00         2.6        1.2        3.8          1.5
2008-04-16 14:04:00         3.3        1.2        4.5          1.3
2008-04-16 14:05:00         8.2         .7        8.8          2.1
2008-04-16 14:06:00         6.7        1.3          8          1.1
2008-04-16 14:07:00         4.7        3.2        7.8          3.7
2008-04-16 14:08:00        20.5      109.8      130.3          170  <== spike in AAS
2008-04-16 14:09:00           6        1.3        7.3         10.3
2008-04-16 14:10:00         2.6         .4          3           .8
2008-04-16 14:11:00           4         .3        4.3          1.1
2008-04-16 14:12:00         5.7         .8        6.5          1.6
2008-04-16 14:13:00           3         .3        3.3           .7
2008-04-16 14:14:00         1.8         .7        2.5           .6
2008-04-16 14:15:00         3.3          2        5.3          2.2

 

참고:  A Tour of the AWR Tables (NoCOUG Summer Conference 2008, August 21 2008)