Row Cache Lock 관련 대기 이벤트

Row Cache Lock 대기 이벤트 소개

• Row Cache Lock 은 딕셔너리 오브젝트(Dictionary Object)를 보호하는 시스템 락
• 딕셔너리 오브젝트란 딕셔너리 캐시 (Dictionary Cache) 영역에 저장되는 모든 종류의 공유 오브젝트를 의미함
• 유저, 테이블, 인텍스, 시권스, 컬럼, 함수, 프로시저 등 SQL을 실행하기 위해 펼요한 모든 딕셔너리 오브젝트들은 반드시 딕셔너리 캐시를 통해 액세스하게 됨
• Row Cache Lock 은 여러 세션이 동시에 동일한 딕셔너리 오브젝트를 변경하는 것을 방지하기 위해 사용되며, 획득하지 못한 경우 이 이벤트 대기함
• 이 이벤트는 V$SESSION에서 통합적인 정보 취득 가능하며, 상세 정보는 V$ROWCACHE_PARENT 뷰에서 확인 가능

DESC V$ROWCACHE_PARENT

Name               Null?    Type
------------------ -------- -----------------
INDX                        NUMBER
HASH                        NUMBER
ADDRESS                     RAW(8)
CACHE#                      NUMBER
CACHE_NAME                  VARCHAR2(64)
EXISTENT                    VARCHAR2(1)
LOCK_MODE                   NUMBER
LOCK_REQUEST                NUMBER
TXN                         RAW(8)
SADDR                       RAW(8)
INST_LOCK_REQUEST           NUMBER
INST_LOCK_RELEASE           NUMBER
INST_LOCK_TYPE              VARCHAR2(2)
INST_LOCK_ID1               RAW(4)
INST_LOCK_ID2               RAW(4)
KEY                         RAW(100)
;

SELECT CACHE#,
       CACHE_NAME,
       LOCK_MODE,
       LOCK_REQUEST,
       SADDR
FROM   V$ROWCACHE_PARENT
WHERE  ROWNUM <= 10
;

CACHE# CACHE_NAME           LOCK_MODE LOCK_REQUEST SADDR
------ ------------------- ---------- ------------ ----------------
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00
     0 dc_tablespaces               0            0 00

Row Cache Lock 테스트

• 테스트 시나리오는 다음과 같다.
• NOCACHE 속성의 시권스에 대해 NEXTVAL 을 호출할 때는 Row Cache Lock 을 획득해야 하므로, 동시에 여러 세션이 시퀀스를 사용하여 이벤트 유도함

Session #1

• 총 100,000번 LOOPING 돌면서 SEQ1.NEXTVAL 호출함

소스코드

-- 1. Sequence 생성
CREATE SEQUENCE SEQ1 NOCACHE ;

-- 2. 수행
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');

BEGIN
    FOR IDX IN 1..100000 LOOP
        FOR R IN (SELECT SEQ1.NEXTVAL
                  FROM   DUAL) LOOP
            NULL;
        END LOOP;
    END LOOP;
END;
/

수행내역

SQL >
  1  CREATE SEQUENCE SEQ1 NOCACHE ;

Sequence created.

SQL >
  1  EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');

PL/SQL procedure successfully completed.

SQL >
  1  BEGIN
  2      FOR IDX IN 1..100000 LOOP
  3          FOR R IN (SELECT SEQ1.NEXTVAL
  4                    FROM   DUAL) LOOP
  5              NULL;
  6          END LOOP;
  7      END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

Session #2

• 총 100,000번 LOOPING 돌면서 SEQ1.NEXTVAL 호출함

소스코드

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');

BEGIN
    FOR IDX IN 1..100000 LOOP
        FOR R IN (SELECT SEQ1.NEXTVAL
                  FROM   DUAL) LOOP
            NULL;
        END LOOP;
    END LOOP;
END;
/

수행내역

SQL >
  1  CREATE SEQUENCE SEQ1 NOCACHE ;

Sequence created.

SQL >
  1  EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');

PL/SQL procedure successfully completed.

SQL >
  1  BEGIN
  2      FOR IDX IN 1..100000 LOOP
  3          FOR R IN (SELECT SEQ1.NEXTVAL
  4                    FROM   DUAL) LOOP
  5              NULL;
  6          END LOOP;
  7      END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

Row Cache Lock 모니터링

소스코드

-- 1. Row Cache Lock 세션 모니터링
set serveroutput on
col sid new_value sid

SELECT SID
FROM   V$SESSION
WHERE  CLIENT_INFO = 'SESSION_2';

BEGIN
    PRINT_TABLE('SELECT *
                 FROM   V$SESSION_WAIT
                 WHERE  SID = &SID');
END;
/

-- 2. 상세 모니터링
col cache_name format a15

SELECT (SELECT SID
        FROM   V$SESSION
        WHERE  SADDR = H.SADDR) AS HOLDER_SID,
       H.CACHE_NAME,
       H.LOCK_MODE
FROM   V$ROWCACHE_PARENT H,
       V$ROWCACHE_PARENT W,
       V$SESSION         S
WHERE  H.ADDRESS   = W.ADDRESS
AND    W.SADDR     = S.SADDR
AND    S.SID       = &SID
AND    H.LOCK_MODE > 0
;

수행내역

SQL >
  1  SELECT SID
  2  FROM   V$SESSION
  3  WHERE  CLIENT_INFO = 'SESSION_2';

       SID
----------
      9623

1 row selected.

SQL >
  1  BEGIN
  2      PRINT_TABLE('SELECT *
  3                   FROM   V$SESSION_WAIT
  4                   WHERE  SID = &SID');
  5  END;
  6  /

SID                           : 9623
SEQ#                          : 105
EVENT                         : row cache lock
P1TEXT                        : cache id
P1                            : 13
P1RAW                         : 000000000000000D
P2TEXT                        : mode
P2                            : 0
P2RAW                         : 00
P3TEXT                        : request
P3                            : 5
P3RAW                         : 0000000000000005
WAIT_CLASS_ID                 : 3875070507
WAIT_CLASS#                   : 4
WAIT_CLASS                    : Concurrency
WAIT_TIME                     : 0
SECONDS_IN_WAIT               : 19
STATE                         : WAITING
---------------------------------------------------------------------------

PL/SQL procedure successfully completed.
SQL >
  1  col cache_name format a15
SQL >
  1  SELECT (SELECT SID
  2          FROM   V$SESSION
  3          WHERE  SADDR = H.SADDR) AS HOLDER_SID,
  4         H.CACHE_NAME,
  5         H.LOCK_MODE
  6  FROM   V$ROWCACHE_PARENT H,
  7         V$ROWCACHE_PARENT W,
  8         V$SESSION         S
  9  WHERE  H.ADDRESS   = W.ADDRESS
 10  AND    W.SADDR     = S.SADDR
 11  AND    S.SID       = &SID
 12  AND    H.LOCK_MODE > 0
 13  ;

HOLDER_SID CACHE_NAME       LOCK_MODE
---------- --------------- ----------
      9889 dc_sequences             5

1 row selected.