active session history 조회

==================================== 

- 해당기간 모두 조회

 

set markup html on spool on 

 spool session_diag2.html 

 SET echo on 

 select * from gv$active_session_history 

 where SAMPLE_TIME between to_timestamp('27-Apr-15 16:30:00.000000', 'DD-Mon-RR HH24:MI:SS.FF') 

 and to_timestamp('27-Apr-15 18:10:00.000000', 'DD-Mon-RR HH24:MI:SS.FF') 

 order by SAMPLE_TIME ; 

 spool off 

 set markup html off spool on 

====================================

- 시간대내 발생 이벤트

SELECT SAMPLE_TIME,

       SESSION_ID,

       SESSION_SERIAL#,

       USER_ID,

       SQL_ID,

       SQL_PLAN_OPERATION, - 11g 이상

       SQL_PLAN_OPTIONS,   - 11g 이상

       EVENT,

       P1,

       P2,

       P3,

       WAIT_TIME,

       TIME_WAITED

  FROM gv$active_session_history

 WHERE sample_time BETWEEN TO_DATE ('20131217 170000', 'YYYYMMDD HH24MISS')

                       AND TO_DATE ('20131217 171000', 'YYYYMMDD HH24MISS') ;

====================================

- 시간대내 이벤트 빈도 많은 순서로

SELECT USER_ID, SQL_ID, EVENT, COUNT*), SUM(WAIT_TIME), SUM(TIME_WAITED)

  FROM gv$active_session_history

 WHERE sample_time BETWEEN TO_DATE ('20131217 170000', 'YYYYMMDD HH24MISS')

                       AND TO_DATE ('20131217 171000', 'YYYYMMDD HH24MISS') ;

AND SESSION_TYPE < > 'BACKUPGROUD'

GROUP BY USER_ID, SQL_ID, EVENT

ORDER BY COUNT(*) DESC;

====================================

- TOP 5 wait event from v$active_session_history

col WAIT_CLASS FOR A40

col EVENT FOR A40

select * from (

select

WAIT_CLASS ,

EVENT,

count(sample_time) as EST_SECS_IN_WAIT

from v$active_session_history

where sample_time between sysdate - interval '1' hour and sysdate

group by WAIT_CLASS,EVENT

order by count(sample_time) desc

)

where rownum <6;

====================================

- 5분 동안 가장 많은 DB time을 차지한 query

SELECT activity_pct,

  db_time,

  sql_id

  FROM (

  SELECT round(100 * ratio_to_report(count(*)) OVER (), 1) AS activity_pct,

  count(*) AS db_time,

  sql_id

  FROM v$active_session_history

  WHERE sample_time BETWEEN to_timestamp('2016-04-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS')

  AND to_timestamp('2014-04-15 10:05:00', 'YYYY-MM-DD HH24:MI:SS')

  AND sql_id IS NOT NULL

  GROUP BY sql_id

  ORDER BY count(*) DESC

  )

  WHERE rownum <= 10;

====================================

- Top 10 SQL Activity

SELECT trunc(sample_time,'MI'),sql_id,count(sql_id) as TOTAL FROM v$active_session_history WHERE sample_time between sysdate - interval '1' hour and sysdate AND sql_id in (select sql_id from (select SQL_ID, sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY

from v$active_session_history WHERE sample_time between sysdate - interval '1' hour and sysdate group by sql_id order by sum(decode(session_state,'WAITING',1,1))   desc) where rownum < 11) group by trunc(sample_time,'MI'),sql_id order by trunc(sample_time,'MI') desc;