SGA - library cache lock/pin II

library cache lock/pin은 library cache내의 object에 대한 매우 빠른 시간내에 처리되는 lock 메카니즘입니다.

따라서 일반적인 운영상황에서는 library cache lock/pin 등의 contention을 dump 등으로 정보를 수집하기는 엄청어렵죠.
뭐 운영중 DDL을 수행하면 library cache lock/pin을 유발할 순 있겠지만..

다음의 시나리오는 2006년 가을호 오라클 매거진에 있는 hang을 유발하는 시나리오 입니다.
(보고 싶은신 분들은 www.oracle.com에 가면 아마~ 있을 겁니다.)

오라클 매거진에서는 system state dump를 설명하고 있지만, 
요 시나리오를 이용해서 library cache dump를 떠보겠습니다. 

욱짜님의 오라클 블로그에 보면 "library cache pin self deadlock 만들기" 방법도 있습니다..

* 준비 과정

create table employee (empno number,ename varchar2(200), deptno number);
insert into employee values (1,'Alice',10);
commit;
create or replace procedure deleteEmployee(eno number)
is
begin
delete employee where empno = eno;
end;
/

* 시나리오

T1 (SID 110) : delete employee where empno = 1;
T2 (SID 92)  : exec deleteEmployee(1);
T3 (SID 67)  : alter procedure deleteEmployee compile;
T4 (SID 36)  : exec deleteEmployee(2);

alter session set events 'immediate trace name LIBRARY_CACHE level 11';

위와 같이 수행하게 되면 SID 92, 7, 36은 sql prompt가 떨어지지 않고 기다리게 됩니다.
각각 어떤 resource를 기다리고 있을까요?

* wait event

       SID EVENT                                  P1 P1RAW                    P2 P2RAW                    P3
---------- ------------------------------ ---------- ---------------- ---------- ---------------- ----------
        36 library cache lock             4.4044E+12 000004017D44D868 4.4040E+12 0000040163A70D88        201
        67 library cache pin              4.4044E+12 000004017D44D868 4.4040E+12 0000040163D23718        301
        92 enq: TX - row lock contention  1415053318 0000000054580006     131076 0000000000020004      31074
       110 SQL*Net message from client    1650815232 0000000062657100          1 0000000000000001          0

* lock info

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000004017E512348 000004017E512368         92 TX     131076      31074          0          6        144          0
000004017BBA0DC0 000004017BBA0DE8        110 TM     107693          0          3          0        150          0
000004017BBA0EC0 000004017BBA0EE8         92 TM     107693          0          3          0        144          0
000004017BC45998 000004017BC459D0        110 TX     131076      31074          6          0        150          1

wait event를 보면

SID 110이 "delete employee where empno = 1"을 수행해서 employee table에 대한 TM lock과 empno가 1인 row에 대한 TX lock을 획득하고 있습니다. 물론 commit을 하지 않아 그 상태가 계속 유지가 되죠.

SID 92는 "deleteEmployee(1)"을 수행해 empno가 1인 row를 지우려 하지만 이미 해당 row에 대한 lock을 SID 110이 갖고 있어 이를 기다리게 됩니다. 이 상태에서 해당 procedure는 현재 수행 중이기 때문에 library cache pin을 갖고 있게 됩니다.

SID 67은 deleteEmployee procedure를 수정하려 하기 때문에 관련 library cache lock과 pin이 필요하게 됩니다. 그러나 library cache pin은 현재 SID 92에서 갖고 있기 때문에 이를 기다려야 합니다.

SID 36은 "deleteEmployee(2)"를 수행하려 합니다. 그러나 deleteEmployee procedure를 수행하기 위해서는 library cache lock에 대한sahred lock을 할당 받아야 합니다.

우선 wait event에 있는 library cache handle 먼저 찾아 보겠습니다. (library cache lock/pin event는 p1 값이 library cache handle을, p2 값이 각각 library cache lock/pin address를 가리킵니다.)

아래 그림을 보시면 SCOTT.DELETEEMPLOYEE library cache object의 lock과 pin의 holder, waiter 정보를 확인할 수 있습니다. 그리고 DEPENDENCIES 항목을 보면 library cache object의 관련 object들도 list-up 된 것을 확인 할 수 있습니다.

library cache dump 등을 장애때 직접 수행해서 관련 정보를 분석할 일은 별로 없긴 하지만,
이를 통해 조금 더 오라클 내부 구조나 hang/lock 현상 등을 좀더 잘 이해 할 수 있는 과정이 될 수 있지 않을까 생각합니다.