V$WAIT_CHAINS 뷰를 이용한 블로킹 세션과 대기 세션 모니터링 방법

운영 시스템을 모니터링할때 중요한 부분 중의 하나는 락 (enqueue) 또는 Library cache pin/lock등의 마지막 홀더 세션을 빠르게 추적함으로써 Root Cause를 정확히 파악하는 것입니다. 이를 위해 전통적으로 사용되었던 방법은 락 (enqueue)의 경우에는 V$LOCK 뷰를 이용하고, library cache pin과 library cache lock은 각각 X$KGLPN와 X$KGLLK fixed 테이블을 이용해서 홀더 세션을 추적하는 것이었습니다. 오라클은 11g 부터는 이와 더불어 V$WAIT_CHAINS 뷰를 제공합니다.

1. V$WAIT_CHAINS 뷰의 특징


V$WAIT_CHAINS 뷰는 이름에서도 알 수 있듯이 대기 체인들의 목록을 제공합니다. 해당 뷰의 몇 가지 특성은 다음과 같습니다.

  • RAC 환경에서 별도로 GV$WAIT_CHAINS 뷰를 제공하지 않습니다. 즉, V$WAIT_CHAINS에 모든 인스턴스의 체인 목록을 저장합니다.
  • 이러한 이유로 인해, RAC 환경에서 해당 뷰를 조회하면 GV$를 조회하는것과 동일하게 각 노드의 PQ 슬레이스 프로세스가 쿼리를 수행합니다.
  • 현재 오픈된 대기 체인들의 관계를 아주 쉽게 파악할 수 있습니다. (장점)
  • 적시성이 떨어집니다 (단점) 환경마다 차이가 있겠지만, 제 테스트 환경에서는 대기 세션이 발생한지 10~15초 이후에 해당 뷰에 등록됩니다.
  • 이것을 통해 미루어 짐작할 수 있는 부분은 대기 세션이 직접 해당 뷰 (X$KSDHNG_CHAINS Fiexed 테이블)에 등록하는 방식이 아니라, 특정 백그라운드 프로세스가 주기적으로 대기 체인을 확인한 후에 해당 뷰에 등록하는 방식을 사용하는 것 같습니다. 참고로 V$SESSION 뷰의 BLOCKING_SESSION 칼럼도 이와 동일하게 동작합니다.
  • 이러한 적시성의 한계로 인해, 일부 환경에서는 해당 뷰를 사용하기 힘들 수도 있습니다. 다만, 이러한 delay는 환경마다 차이가 있을수 있으므로 각자 환경에서 테스트해보면 좋을것 같습니다.

Note
‘ORADEBUG -G ALL HANGANALYZE 10’ 명령어를 사용해본 분들을 ‘CHAINS’란 단어가 익숙할것입니다. V$WAIT_CHAINS 뷰는 HANGANALYZE와 동일한 정보를 아주 빠르게 제공함과 동시에 트레이스 파일 대비 높은 가독성을 제공합니다.

2. V$WAIT_CHAINS 뷰를 이용한 스크립트


V$WAIT_CHAINS 뷰 자체로도 충분한 정보를 제공하고 있습니다만, 프로그램/모듈 및 SQL 정보를 확인하려면 V$SESSION 및 V$SQL 뷰와 조인을 해야합니다. 아래의 쿼리는 SQL*Plus 환경에서 180 라인에 맞춰서 작성하기 위해 일부 칼럼을 제외하거나 (P2, P3 제외) 칼럼의 길이를 짧게 조정한것이므로, 그리드가 제공되는 툴을 사용하는 경우에는 적절히 편집해서 사용하시면 됩니다.

set linesize 180
set pages 100

col inst_id  for  999
col sid      for  a8
col serial#  for  999999
col wait_secs  heading 'WAIT|_SECS' for 999999
col object_id  heading 'OBJECT|_ID' for 999999
col spid     for a7
col sql_text for a21
col event    for a30
col prog_mod for a10
col p1       for a28

WITH v_wait_chains AS (
                      SELECT
                             instance ,
                             LPAD( ' ' , 2*( lv-1 ) ) ||sid sid,
                             sess_serial# ,
                             osid spid,
                             wait_event_text event,
                             p1_text,
                             p1,
                             p2_text,
                             p2,
                             p3_text,
                             p3,
                             in_wait_secs ,
                             row_wait_obj#
                      FROM   (
                              SELECT a.* ,
                                     LEVEL lv
                              FROM   v$wait_chains a
                              START WITH blocker_is_valid = 'FALSE'
                              CONNECT BY PRIOR sid          = blocker_sid
                              AND        PRIOR sess_serial# = blocker_sess_serial#
                              AND        PRIOR instance     = blocker_instance

                             )
                      WHERE  num_waiters > 0
                      OR     blocker_sid IS NOT NULL
     )
SELECT /*+ leading(a b c) use_nl(b c) */
     a.instance inst_id ,
     a.sid ,
     a.sess_serial# serial# ,
     a.in_wait_secs wait_secs ,
     nvl(b.module, b.program) prog_mod,
     a.spid ,
     SUBSTR( c.sql_text , 1 , 20 ) sql_text ,
     a.row_wait_obj# object_id,
     a.event ,
     a.p1_text||'=>'||a.p1 p1
FROM v_wait_chains a ,
     gv$session    b ,
     gv$sql        c
WHERE a.instance    = b.inst_id
AND  a.sid          = b.sid
AND  a.sess_serial# = b.serial#
AND  b.inst_id      = c.inst_id
AND  nvl(b.sql_id, b.prev_sql_id) = c.sql_id;

INST_ID SID      SERIAL#   _SECS PROG_MOD   SPID    SQL_TEXT                  _ID EVENT                          P1
------- -------- ------- ------- ---------- ------- --------------------- ------- ------------------------------ ----------------------------
      2 40          1837     707 SQL*Plus   20707   BEGIN pin_test; END;       -1 PL/SQL lock timer              duration=>0
      2   82        1903     705 SQL*Plus   20671   alter procedure pin_    20366 library cache pin              handle address=>1815917400
      1     45     58257     676 SQL*Plus   24571   BEGIN pin_test; END;       -1 library cache lock             handle address=>1738926912
      2 42         46140   15058 SQL*Plus   18716   update lock_test set    94662 SQL*Net message from client    driver id=>1650815232
      1   57       16358   15049 SQL*Plus   18648   update lock_test set    94662 enq: TX - row lock contention  name|mode=>1415053318
      3     49     52493   15046 SQL*Plus   25700   update lock_test set    94662 enq: TX - row lock contention  name|mode=>1415053318
      1 73         31456   15139 SQL*Plus   18488   update lock_test set    94662 SQL*Net message from client    driver id=>1650815232
      2   64       42726   15134 SQL*Plus   18652   update lock_test set    94662 enq: TX - row lock contention  name|mode=>1415053318
      3     80     49316   15129 SQL*Plus   25613   update lock_test set    94662 enq: TX - row lock contention
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s