Library Cache Pin에 대한 Self Deadlock을 아주 간단한게 만들어보겠습니다. 우선 다음과 같이 텅빈 프로시저 TEST_PROC1을 만듭니다.
create or replace procedure test_proc1
is
begin
null;
end;
/
그리고 아래의 PL/SQL 블록에서 TEST_PROC1을 실행하고, 곧이어 TEST_PROC1 프로시저를 컴파일합니다. 그러면 행(Hang) 상태에 빠져버립니다.
TPACK@ukja1120> begin
2 test_proc1;
3
4 execute immediate 'alter procedure test_proc1 compile';
5
6 end;
7 /
...
(Hang)
Active Session History를 통해 행 상태에 빠진 세션이 어떤 대기 상태인지 확인해보면 아래와 같이 library cache pin 이벤트를 무한 대기(는 아니고 타임아웃이 발생할 때까지)하고 있는 것을 알 수 있습니다. 그리고 Blocker와 SID가 동일한 것으로부터 Self Deadlock 상태에 빠진 것을 알 수 있습니다.
select *
from (
select
h.session_id as sid,
to_char(h.sample_time,'mi:ss') as sample_time,
h.sql_id,
(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
event,
blocking_session as blocker
from
v$active_session_history h
where
h.session_id = &sid
order by h.sample_time desc
) where rownum <= 20
;
SID SAMPL SQL_ID SQL_TEXT EVENT BLOCKER
---- ----- ------------- -------------------- ---------- ----------
136 49:10 library ca 136
che pin
136 49:09 library ca 136
che pin
136 49:08 library ca 136
che pin
136 49:07 library ca 136
che pin
136 49:06 library ca 136
che pin
136 49:05 library ca 136
che pin
136 49:04 library ca 136
che pin
136 49:03 library ca 136
che pin
136 49:02 library ca 136
che pin
136 49:01 library ca 136
che pin
136 49:00 library ca 136
che pin
136 48:59 library ca 136
che pin
136 48:58 library ca 136
che pin
136 48:57 library ca 136
che pin
136 48:56 library ca 136
che pin
136 48:55 library ca 136
che pin
136 48:54 library ca 136
che pin
136 48:53 library ca 136
che pin
136 48:52 library ca 136
che pin
136 48:51 library ca 136
che pin
20 rows selected.
티팩을 통해서 해당 세션 정보를 확인해보면 더욱 명확한 정보를 얻을 수 있습니다.
TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))
NAME VALUE
------------------------------ --------------------
SID 136
Serial# 2797
SPID 5148
Program sqlplus.exe
Process 5404:672
Module SQL*Plus
SQL ID 9pbva4bn2m25b
Child No 0
SQL Text alter procedure test
_proc1 compile
Status ACTIVE
Blocking Instance 1
Blocking Session 136
SQL Exec Start 2010/09/15 13:45:34
Event library cache pin
Seq# 130
P1(P1raw) 384372376(0000000016
E90E98)
P2(P2raw) 384372376(0000000016
DAB608)
P3(P3raw) 384372376(00014F8500
010003)
Seconds in wait 40
State WAITING
Wait Event library cache pin
Holder SID 136
Namespace TABLE/PROCEDURE
Object TEST_PROC1
Holding Mode 2(S)
위의 결과를 잘 해석해보면 TEST_RPOC1 프로시저를 실행한 후 Library Cache Pin을 해제하지 않고 Shared Mode로 계속해서 점유하고 있는 것이 이유라는 것을 알 수 있습니다. TEST_PROC1을 컴파일하려면 Library Cache Pin을 Exclusive Mode로 획득해야하는데, Shared Mode의 Library Cache Pin이 아직 해제되지 않았기 때문에 대기하게 되는 것입니다.
왜 Shared Mode의 Library Cache Pin을 곧바로 해제하지 않는가가? 그 이유는 MOS 노트 264476.1에서 설명되어 있습니다.
When a stored procedure is called from another stored procedure/function/PLSQL block, 'LIBRARY CACHE PIN' held on called stored procedures is released only at the end of the calling stored procedure or PL/SQL block (not immediately after execution of called procedure ).
So, any DDL operation on the called stored procedures will wait on 'LIBRARY CACHE PIN' until full pl/sql block is completed. However, you can execute called the procedure.
이 현상을 좀 더 확장해보면 최악의 경우 아래와 같이 불필요한 Library Cache Pin 대기를 유발할 수 있습니다.
-- Session #1은 실행 중
TPACK@ukja1120> begin
2 test_proc1;
3
4 dbms_lock.sleep(120);
5
6 end;
7 /
... (실행 중)
-- Session #2는 Session #1 때문에 컴파일을 못해서 대기
TPACK@ukja1120> alter procedure test_proc1 compile;
... (대기 중)
-- Session #3은 Session #2 때문에 실행을 못하고 대기
TPACK@ukja1120> exec test_proc1;
... (대기 중)