ASH를 이용한 wait event 및 session 찾기

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

DB 환경 : Oracle Database 11.2.0.4

방법 : ASH를 이용한 wait event 및 session 찾기

추적 및 탐색

1. dba_hist_system_event 조회, 다량으로 발생된 이벤트를 확인.

2. dba_hist_active_sess_history 조회, 해당 이벤트를 많이 대기한 세션을 확인.

3. 블로킹 세션 정보로 dba_hist_active_sess_history 재조회.

4. 블로킹 세션이 찾아지면 해당 세션이 그 시점에 어떤 작업을 수행 중이었는지 확인.

5. sql_id를 이용해 그 당시 SQL과 실행계획까지 확인. v$sql 및 v$sql_plan이 AWR에 저장되기 때문.

1. dba_hist_system_event 조회, 다량으로 발생된 이벤트를 확인.

SQL> 
SELECT EVENT_NAME, COUNT(EVENT_NAME)
FROM DBA_HIST_SYSTEM_EVENT
GROUP BY EVENT_NAME
ORDER BY 2 DESC;
 
EVENT_NAME                             COUNT(EVENT_NAME)
---------------------------------------------------------------- -----------------
cursor: pin S wait on X                               32000
log file parallel write                                360
KJC: Wait for msg sends to complete                           360
PX Deq: Join ACK                                   360
gc remaster                                       360

또는 아래 쿼리로도 top 5 이벤트 확인가능

col wait_class for a12
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
---------- -------------------------------------------------- ----------------
Concurrency cursor: pin S wait on X                                       79654
Concurrency library cache lock                                             3990
(Null)                                                                     653
Concurrency latch: cache buffers chains                                      32
User I/O   db file sequential read                                          28

2. dba_hist_active_sess_history 조회, 해당 이벤트를 많이 대기한 세션을 확인.

set pages 1000
set lines 200
col evnet for a30
select EVENT, sql_id, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#
from dba_hist_active_sess_history
where event = 'cursor: pin S wait on X'
and rownum <= 10;
 
EVENT                     SQL_ID        BLOCKING_SESSION BLOCKING_SESSION_SERIAL#
------------------------- ------------- ---------------- ------------------------
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   bvrysy42hhc4s
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   g2f8p5vd71r6g
cursor: pin S wait on X   bvrysy42hhc4s
cursor: pin S wait on X   g2f8p5vd71r6g

많아보이는 sql_id로 count 조회

select count(*) from dba_hist_active_sess_history
where event = 'cursor: pin S wait on X'
and sql_id='g2f8p5vd71r6g';
 
  COUNT(*)
----------
     16039

 

많아보이는 sql_id로 count 조회2

SQL> select count(*) from dba_hist_active_sess_history
where event = 'cursor: pin S wait on X'
  and sql_id='bvrysy42hhc4s';
 
  COUNT(*)
----------
      3478

3, 4 건너뛰고..

5. sql_id를 이용해 그 당시 SQL과 실행계획까지 확인. v$sql 및 v$sql_plan이 AWR에 저장되기 때문.

set pages 1000
set lines 200
select sql_text
from v$SQLTEXT_WITH_NEWLINES
where sql_id ='g2f8p5vd71r6g'
order by piece
/
 
select ~~~~~

실행계획 확인

sql_id 입력

select * from table(dbms_xplan.display_awr('g2f8p5vd71r6g'));

참조 : http://wiki.gurubee.net/display/ONSTUDY/ASH%28Active+Session+History%29