v$active_session_history뷰를 통해 Top 5 wait events 확인

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : v$active_session_history뷰를 통해 Top 5 wait events 확인

이 쿼리는 v$active_session_history 뷰에서 지난 1시간 동안 상위 5개의 대기 이벤트를 보여줌

#주의사항 diagnostic pack의 일부이기 때문에, 라이센스가 없는 경우 이 뷰를 조회해서는 안됨

col wait_class for a10
col event for a50
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;

결과값 : 

WAIT_CLASS EVENT                          EST_SECS_IN_WAIT
---------- -------------------------------------------------- ----------------
(null)       (null)                                62
Other       DFS lock handle                            10
System I/O control file sequential read                      4
System I/O db file parallel write                         2
Other       CGS wait for IPC msg                          1
 
5 rows selected.

참조 : https://www.dba-scripts.com/scripts/diagnostic-and-tuning/oracle-active-session-history-ash/top-5-wait-events-vactive_session_history/