1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 3차 코어 오라클 데이터베이스 스터디
  4. ASH(Active Session History)

ASH(Active Session History) (by jongmali) [2009.11.18]

09. ASH(Active Session History)

SQL> select * from v$sgastat where name = 'ASH buffers';



POOL NAME BYTES

--
---

shared pool ASH buffers 16252928
  • 오라클은 현재 접속해서 활동 중인 Active 세션 정보를 1초에 한번씩 샘플링해서 ASH 버퍼에 저장한다.
  • SGA Shared Pool에서 CPU당 2MB의 버퍼를 할당 받아 세션 정보를 기록하며, 1시간 혹은 버퍼의 2/3가 찰 때마다 디스크로 기록한다. 즉, AWR에 저장하는 것이다.
  • v$active_session_history 뷰를 이용해 ASH 버퍼에 저장된 세션 히스토리 정보를 조회할 수 있다.
select

sample_id, sample_time
---①

, session_id, session_serial#, user_id, xid
-②

, sql_id, sql_child_number, sql_plan_hash_value
--③

, session_state
-④

, qc_instance_id, gc_session_id
---⑤

, blocking_session, blocking_session_serial#, blocking_session_status
--⑥

, event, event#, seq#, wait_class, wait_time, time_waited
---⑦

, p1text, p1, p2text, p2, p3text, p3
---⑧

, current_obj#, current_file#, current_block#
-⑨

, program, module, action, client_id
-⑩

from V$ACTIVE_SESSION_HISTORY

① 샘플링이 일어난 시간과 샘플 ID
② 세션정보, User명, 트랜잭션ID
③ 수행 중 SQL 정보
④ 현재 세션의 상태 정보. 'ON CPU' 또는 'WAITING'
⑤ 병렬 Slave 세션일 때, 쿼리 코디네이터(QC) 정보를 찾을 수 있게 함
⑥ 현재 세션의 진행을 막고 있는(=블로킹) 세션 정보
⑦ 현재 발생 중인 대기 이벤트 정보
⑧ 현재 발생 중인 대기 이벤트의 파라미터 정보
⑨ 해당 세션이 현재 참조하고 있는 오브젝트 정보. V$session 뷰에 있는 row_wait_obj#, row_wait_file#,
row_wait_block# 컬럼을 가져온 것임
⑩ 애플리케이션 정보

  • ⑦과 ⑧번 '대기 이벤트' 정보는 두말할 것도 없고, ⑥번 '블로킹 세션' 정보와 ⑨번 '현재 액세스 중인 오브젝트' 정보도 매우 유용하다. 블로킹 세션 정보를 통해 현재 Lock을 발생시킨 세션을 빨리 찾아 해소 할 수 있게 도와준다
  • 오브젝트 정보도 더할 나위 없이 유용하지만 현재 발생 중인 대기 이벤트의 Wait Class가 Application, Concurrency, Cluster, User I/O 일 때만 의미 있는 값임을 알아야 한다.
  • 예를 들어, ITL 슬롯 부족 때문에 발생하는 enq:TX - allocate ITL entry 대기 이벤트는 Configuration에 속하므로, v$active_session_history 뷰를 조회할 때 함께 출력되는 오브젝트에 Lock이 걸렸다고 판단해서는 안된다. 대개 그럴 때는 오브젝트번호가 -1로 출력되지만 직전에 발생한 이벤트의 오브젝트 정보가 계속 남아서 보이는 경우가 있으므로 잘못 해석하지 않도록 주의해야 한다.
  • 아래 예시에도 파일 번호와 블록 번호가 그대로 남아있는 것을 알 수 있다.
column current_obj# format 99999 heading 'CUR_OBJ#'
column current_file# format 999 heading 'CUR_FIL#'
column current_block# format 999 heading 'CUR_BLK#'
select to_char(sample_time, 'hh24:mi:ss') sample_tm, session_state
, event, wait_class, current_obj#, current_file#, current_block#
from v$active_session_history
where session_id = 143
and session_serial# = 9
order by sample_time;
SAMPLE_TSESSIONEVENTWAIT_CLASSCUR_OBJ#EUR_FIL#CUR_CLK#
15:00:44WATINGEnq: TX - row lock contentionApplication557654476
15:00:45WATINGEnq: TX - row lock contentionApplication557654476
15:00:46WATINGEnq: TX - row lock contentionApplication557654476
15:00:47WATINGEnq: TX - row lock contentionApplication557654476
15:01:36WATINGEnq: TX - allocate ITL entryConficuration-14476
15:01:37WATINGEnq: TX - allocate ITL entryConficuration-14476
15:01:38WATINGEnq: TX - allocate ITL entryConficuration-14476
15:01:39WATINGEnq: TX - allocate ITL entryConficuration-14476
  • 초단위로 쓰기가 발생하는 ASH 버퍼를 읽을 때 래치를 사용한다면 경합이 생길 수 있다. 따라서 오라클은 ASH 버퍼를 읽는 세션에 대해서는 래치를 요구하지 않으며 그 때문에 간혹 일관성 없는 잘못된 정보가 나타날 수도 있다.
  • ASH 기능을 이용하면 현재뿐 아니라 과거시점에 발생한 장애 및 성능 저하 원인까지 세션 레벨로 분석할 수 있게 도와준다. 오라클 10g부터는 v$active_session_history 정보를 AWR 내에 보관하므로 과거치에 대한 세션 레벨 분석이 가능해 졌다.(SGA를 DMA방식으로 억세스)
  • 1/10만 샘플링해서 저장.(문제가 되는 대기 이벤트는 일정간격을 두고 지속적으로 발생하기 때문에 샘플링 된 자료만으로도 원인을 찾는데 큰 지장이 없다)
  • v$active_session_history를 조회했을 때 정보가 찾아지지 않는다면 이미 AWR에 쓰여진 것으로 dba_hist_active_sess_history뷰를 조회하면 된다.

    1. AWR 뷰를 이용해 하루 동안의 이벤트 발생현황을 조회해 본다.
    그래프는 dba_hist_system_event를 이용해 그린 것인데, 08:15~09:15 구간에서 enq: TM - contention 이벤트가 다량 발생한 것이 확인 되었다.
    2. dba_hist_active_sess_history를 조회해서 해당 이벤트를 많이 대기한 세션을 확인 한다.
    3. 블로킹 세션 정보를 통해 dba_hist_active_sess_history를 다시 조회한다. 블로킹 세션이 찾아지면 해당 세션이 그 시점에 어떤 작업을 수행 중이었는지 확인한다. sql_id를 이용해 그 당시 SQL과 실행계획까지 확인 할 수 있다. v$sql과 v$sql_plan까지 AWR에 저장되기 때문이다. 위 사례에서는 브로킹 세션이 Append Mode Insert를 수행하면서 Exclusive 모드 TM Lock에 의한 경합이 발생하고 있었다.
    4. program, module, action, client_id 등 애플리케이션 정보를 이용해 관련 프로그램을 찾아 Append 힌트를 제거한다. 그러고 나서, 다른 트랜잭션과 동시 DML이 발생할 수 있는 상황에서는 insert문에 Append 힌트를 사용해서는 안 된다는 사실을 개발팀 전체에 공지한다.

문서에 대하여

  1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 3차 코어 오라클 데이터베이스 스터디
  4. ASH(Active Session History)