Library Cache Pinに対するSelf Deadlockを作ってみよう。

Library Cache Pinに対するSelf Deadlockをとても易く作ってみます。まず次のように空いたプロシージャであるTEST_PROC1を作ります。

create or replace procedure test_proc1 
is
begin
 null;
end;
/

次のPL/SQLブロックでTEST_PROC1を実行し、まのなくTEST_PROC1をコンパイルします。そうすると、セッションはハング状態に落ちってしまいます。

TPACK@ukja1120> begin
  2     test_proc1;
  3
  4     execute immediate 'alter procedure test_proc1 compile';
  5
  6  end;
  7  /

...
(Hang)

ASH(Active Session History)を通じて該当セッションの状態を分析してみると、WaiterとBlockerが一致していることが分かります。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)

理由は何と考えますか？