[SGA] library cache lock&pin 발생원인과 재현하기

어제 테이블의 ddl을 수행하려고 보니 (단순 add column) 수행이 끝나지 않고 계속 대기하는 현상이 발생하였습니다.

 

다른 세션을 열어 세션의 상태를 조회해보니 library cache lock 상태로 wait중이었습니다.

 

급하게 holder session을 찾아 kill을 하고 처리를 했지만, 정확한 처리방법이 맞는것인지 다시 재현하여 확인할 수 있는 다른 테이블이나 방법은 없는지 정리를 하기로 하였습니다.

 

 

library cache 란?

라이브러리 캐시는 Shared Pool 내에 위치하며, SQL 공유 커서 및 데이터베이스 오브젝트(테이블,인덱스)에 대한 정보를 관리합니다. 그리고 여기에 저장되는 정보의 단위를 라이브러리 캐시 오브젝트(LCO)라고 부릅니다.

 

SQL 커서 뿐만 아니라 컴파일을 거친 프로시저, 함수, 패키지, 트리거 등 PL/SQL프로그램을 담는 PL/SQL Area도 라이브러리 캐시에 저장합니다. (실행가능 LCO)

뿐만 아니라 거기서 참조하는 테이블,인덱스, 클러스터 같은 데이터베이스 오브젝트 정보들도 동등하게 하나의 오브젝트로써 관리됩니다.(오브젝트 LCO)

스키마 오브젝트 정보는 데이터 딕셔너리 캐시에도 캐싱돼 있는데 라이브러리 캐시에 중복 저장하는 이유가 무엇일까요.

라이브러리 캐시에 스키마 오브젝트 정보를 캐싱하는 것은 LCO간 의존성을 관리하려는 데 목적이 있습니다. LCO 각각에는 자신을 참조하는 다른 실행가능 LCO(커서,함수,프로시저,패키지 등)목록을 갖습니다.

 

 

 

발생원인?

라이브러리 캐시도 DB 버퍼 캐시처럼 해시구조로 관리됩니다. 즉 해시 버킷에 LCO핸들(LCO를 식별하고 힙을 포인팅하는 데 사용)이 체인으로 연결돼 있고, 핸들을 통해 LCO힙을 찾아가는 구조입니다. DB 버퍼 캐시와 마찬가지로 해시 함수를 통해 리턴된 해시값을 가지고 해시 버킷을 할당합니다.

 

라이브러리 캐시 체인을 탐색하고 변경하려면 library cache래치를 획득해야 합니다. 이에 대한 경합이 발생할 때 latch : library cache 대기 이벤트가 발생합니다. 

LCO를 보호하려고 오라클은 라이브러리 캐시 Lock과 라이브러리 캐시 Pin을 사용합니다.

LCO에 접근할 때는 먼저 핸들에 대한 Lock을 획득해야 합니다. 그러고 나서 LCO의 실제 내용이 담긴 heap에서 정보를 읽거나 변경할 때는 pin을 걸어두어야 합니다.그럼으로써 실행하는 동안 다른 프로세스에 의해 정보가 변경되거나 캐시에서 밀려나는 것을 방지합니다.

shared pool 래치와 library cache 래치 경합은 1) 소프트/하드 파싱을 동시에 심하게 일으킬 때 발생하고, library cache lock과 library cache pin 대기 이벤트는 주로 2)SQL 수행 도중 DDL을 날릴 때 발생합니다. 트랜잭션이 활발한 주간에 DDL문을 날려 데이터베이스 오브젝트 정의를 변경하려면 라이브러리 캐시에 심한 부하를 유발하므로 주의해야 합니다.

 

출처 : 오라클 성능고도화 원리와 해법1  03.라이브러리 캐시 구조

 

즉,원인은 SQL 수행 도중 alter table (DDL)을 수행하여 해당 wait이 발생하였던 것이었습니다.

 

재현방법?

sql 수행도중 ddl을 수행하여 재현할 수 있습니다. 

총 5개의 세션이 필요하고 4개의 세션은 쿼리를 수행하여 library cache lock/pin을 유발하고 나머지 한개는 모니터링합니다.

 

test db :23ai

 

테스트 테이블 및 데이터 삽입

CREATE TABLE test_emp (empno NUMBER,ename varchar2(200),deptno NUMBER);

INSERT INTO test_emp values(1,'test text',10);

COMMIT;

테스트 프로시저 생성

CREATE OR REPLACE PROCEDURE deleteemp(eno number)
IS 
BEGIN
	DELETE test_emp WHERE empno = eno;
END;
/

 

각 세션의 SID와 명령어 수행

 

세션1

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        62

SQL> DELETE test_emp WHERE empno = 1;

1 행이 삭제되었습니다.

 

세션2

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        64

SQL> EXEC deleteemp(1);

...waiting 중

 

세션3

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        66

SQL> EXEC deleteemp(2);

...waiting 중

 

세션4

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        63

SQL> ALTER PROCEDURE deleteemp compile;

..waiting 중

 

환경은 구성되었습니다.

 

세션5를 열어 모니터해보도록 하겠습니다.

63번(ddl 세션)이 library cache pin을 잡고있고 66번(exec deleteemp(2);)이 library cache lock으로 대기하고있는것으로 확인됩니다. 

SELECT * FROM v$session_wait
WHERE sid IN (62,63,64,66);
SID|SEQ#|EVENT                        |P1TEXT        |P1        |P1RAW   |P2TEXT        |P2        |P2RAW   |P3TEXT                               |P3             |P3RAW   |WAIT_CLASS_ID|WAIT_CLASS#|WAIT_CLASS |WAIT_TIME|SECONDS_IN_WAIT|STATE  |WAIT_TIME_MICRO|TIME_REMAINING_MICRO|TOTAL_TIME_WAITED_MICRO|HEUR_TIME_WAITED_MICRO|TIME_SINCE_LAST_WAIT_MICRO|CON_ID|
---+----+-----------------------------+--------------+----------+--------+--------------+----------+--------+-------------------------------------+---------------+--------+-------------+-----------+-----------+---------+---------------+-------+---------------+--------------------+-----------------------+----------------------+--------------------------+------+
 62| 181|SQL*Net message from client  |driver id     |1952673792|    tcp |#bytes        |         1|        |                                     |              0|        |   2723168908|          6|Idle       |        0|             24|WAITING|       24095570|                  -1|               24095570|              24095570|                         0|     3|
 63|  50|library cache pin            |handle address|1939264512|    s Ô |pin address   |1792210440|    jÒö |(identifier<<32)+(namespace<<16)+mode|346449242030083|  ;     |   3875070507|          4|Concurrency|        0|            443|WAITING|      442808755|           330909858|              569090142|             569090142|                         0|     3|
 64|  82|enq: TX - row lock contention|name|mode     |1415053318|    TX  |usn<<16 | slot|    262177|       !|sequence                             |           2651|       [|   4217450380|          1|Application|        0|            578|WAITING|      578201179|                  -1|              578201179|             578201179|                         0|     3|
 66|  39|library cache lock           |handle address|1939264512|    s Ô |lock address  |1786769888|    j ñà|(identifier<<32)+(namespace<<16)+mode|346449242030082|  ;     |   3875070507|          4|Concurrency|        0|             43|WAITING|       42900949|           857099051|               42900949|              42900949|                         0|     3|

 

v$libcache_locks 딕셔너리뷰에서도 lock과 pin의 정보를 확인할 수 있지만 어느세션인지 자세한 정보는 알기 힘듭니다.

SELECT * FROM "V$LIBCACHE_LOCKS" WHERE MODE_REQUESTED > 0;

TYPE         ADDR             HOLDING_USER_SES HOLDING_SESSION  OBJECT_HANDLE    LOCK_HELD          REFCOUNT  MODE_HELD MODE_REQUESTED SAVEPOINT_NUMBER       HASH     CON_ID
------------ ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- -------------- ---------------- ---------- ----------
LOCK         000000006A7FF1E0 00000000800FFD70 00000000800FFD70 000000007396D400 00                        0          0           2              154 3151630123          3
PIN          000000006AD2F608 00000000800F75B0 00000000800F75B0 000000007396D400 00                        0          0           3              161 3151630123          3

 

아래 쿼리는 한번에 library cache lock을 잡고있는 세션의 정보와 kill 명령어까지 나오는 스크립트입니다.

결과값을 보면 66번세션이 대기중이고 holder는 63번으로 나옵니다.

 

SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
       S.SID || ',' || S.SERIAL# AS SID,
       S.MODULE AS MODULE,
       S.SECONDS_IN_WAIT W_TIME,
       TO_CHAR(S.BLOCKING_INSTANCE) NODE,
       TO_CHAR(S.BLOCKING_SESSION) HOLDER,
       (SELECT SUBSTR(SQL_TEXT, 1, 65)
        FROM   V$SQL SQ
        WHERE  SQ.ADDRESS = S.SQL_ADDRESS
        AND    SQ.HASH_VALUE = S.SQL_HASH_VALUE
        AND    ROWNUM = 1) AS SQL_TEXT,
       NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
       NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
       NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
       'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
       ' ; ' KILL_SCRIPT,
       'kill -9  ' || P.SPID KILL_SCRIPT2
FROM   V$SESSION    S,
       V$PROCESS    P       
WHERE  S.PADDR       = P.ADDR
AND    S.EVENT       = 'library cache lock'
ORDER  BY S.SECONDS_IN_WAIT;


SID     |MODULE  |W_TIME|NODE|HOLDER|SQL_TEXT                 |P1                       |P2                     |P3                                                   |KILL_SCRIPT                            |KILL_SCRIPT2 |
--------+--------+------+----+------+-------------------------+-------------------------+-----------------------+-----------------------------------------------------+---------------------------------------+-------------+
66,63556|SQL*Plus|   283|1   |63    |BEGIN deleteemp(2); END; |handle address¶1939264512|lock address¶1775071528|(identifier<<32)+(namespace<<16)+mode¶346449242030082|alter system kill session '66,63556' ; |kill -9  2176|

 

select도 마찬가지로 발생할 수 있는 이벤트이기 때문에 ddl 수행시에는 사용하지 않을 때 반영해야함을 다시한번 확인했네요.

 

출처 : https://energ.tistory.com/entry/SGA-library-cache-lockpin-II

출처 : http://www.gurubee.net/lecture/4302

출처 : https://soohi0309.tistory.com/298