library cache pin等待事件的模擬

library cache pin是用來管理library cache的併發訪問的。pin一個object會引起相應的heap被載入記憶體（如果此前沒有被載入），pins可以在Null、Share、Exlusive這三種模式下獲得，可以認為pin是一種特定形式的鎖。當library cache pin等待事件出現時，通常說明該pin被其他使用者以非相容模式持有，它一般發生在編譯或重新編譯PL/SQL、VIEW、TYPES等object時。編譯通常都是顯性的，但alter、grant、revoke等操作也會使object變得無效，可以透過object的last_ddl_time觀察到這些變化。當object變得無效時，oracle會在第一次訪問此object時試圖去重新編譯它，如果此時其他session已經把此object pin到library cache中，就會出現問題，特別是當有大量的活動session並且存在較複雜的dependence時。某些情況下，重新編譯object可能花費很長時間，從而阻塞其他試圖訪問該object的程式。以下透過一個例子來模擬和解釋這個等待。

 

1、建立測試用儲存過程

 

create or replace procedure pining is

begin

    null;

end;

/

create or replace procedure calling is

begin

    pining;

    dbms_lock.sleep(3000);

end;

/

 

2、模擬競爭

 

session 1：

exec calling;

 

calling中呼叫pining，此時pining過程上獲得共享pin，如果此時嘗試對pining進行授權或重新編譯，將產生library cache pin等待，直到calling執行完畢。

 

session 2：

grant execute on pining to scott;

revoke execute on pining from scott;

 

此時session2被掛起。

 

3、原因分析

 

從v$session_wait入手，得到哪些會話正在經歷library cache pin的等待

col event for a30

col p1text for a20

col p2text for a20

col p3text for a20

col wait_class for a20

select t.sid, seq#, t.event, t.p1text, t.p1, t.p1raw, t.p2text, t.p2, t.p2raw, t.p3text, t.p3, t.p3raw, t.wait_class#, t.wait_class, t.wait_time, t.seconds_in_wait, t.state from v$session_wait t where event like 'library%';

 

       SID       SEQ# EVENT                          P1TEXT                       P1 P1RAW            P2TEXT                       P2 P2RAW            P3TEXT                       P3 P3RAW            WAIT_CLASS# WAIT_CLASS            WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------- ------------------------------ -------------------- ---------- ---------------- -------------------- ---------- ---------------- -------------------- ---------- ---------------- ----------- -------------------- ---------- --------------- -------------------

       142         42 library cache pin              handle address       8791880329 000007FF04E76AD8 pin address          8791880412 000007FF04E8AED8 100*mode+namespace   1061758865 0000609100010003           4 Concurrency                   0              52 WAITING

 

對於library cache pin等待事件，引數p1raw表示等待物件的handle，查詢x$kglob，可以得到物件的具體資訊

col kglnaown for a10

col kglnaobj for a20

select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj from x$kglob where kglhdadr = '000007FF04E76AD8';

 

ADDR             KGLHDADR         KGLHDPAR         KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ

---------------- ---------------- ---------------- ---------- -------------------- ---------- ----------------

000000001F6523A8 000007FF04E76AD8 000007FF04E76AD8 SYS        PINING               1587262593 000007FF04E6ED60

 

上面兩個查詢可以結合一下，直接獲得library cache pin等待的物件

col kglnaown for a10

col kglnaobj for a20

select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj from x$kglob where kglhdadr in

  (select p1raw from v$session_wait where event like 'library%');

 

ADDR             KGLHDADR         KGLHDPAR         KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ

---------------- ---------------- ---------------- ---------- -------------------- ---------- ----------------

000000001F651390 000007FF04E76AD8 000007FF04E76AD8 SYS        PINING               1587262593 000007FF04E6ED60

 

獲得持有等待物件的session資訊

col username for a10

col program for a20

select a.sid,

       a.username,

       a.program,

       b.addr,

       b.kglpnadr,

       b.kglpnuse,

       b.kglpnses,

       b.kglpnhdl,

       b.kglpnlck,

       b.kglpnmod,

       b.kglpnreq

  from v$session a, x$kglpn b

 where a.saddr = b.kglpnuse

   and b.kglpnmod <> 0

   and b.kglpnhdl in

       (select p1raw from v$session_wait where event like 'library%');

 

       SID USERNAME   PROGRAM              ADDR             KGLPNADR         KGLPNUSE         KGLPNSES         KGLPNHDL         KGLPNLCK           KGLPNMOD   KGLPNREQ

---------- ---------- -------------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ----------

       141 SYS        sqlplus.exe          000000001F652648 000007FF04F15960 000007FF0BE2D6F8 000007FF0BE2D6F8 000007FF04E76AD8 000007FF04F15A60          2          0

 

該會話正在等待一次PL/SQL lock timer計時

select event from v$session_wait where sid = 141;

 

EVENT

------------------------------

PL/SQL lock timer

 

獲得持有物件使用者執行的程式碼

select sql_text

  from v$sqlarea

 where (v$sqlarea.address, v$sqlarea.hash_value) in

       (select sql_address, sql_hash_value

          from v$session

         where sid in (select sid

                         from v$session a, x$kglpn b

                        where a.saddr = b.kglpnuse

                          and b.kglpnmod <> 0

                          and b.kglpnhdl in

                              (select p1raw

                                 from v$session_wait

                                where event like 'library%')));

 

SQL_TEXT

--------------------------------------------------------------------------------

BEGIN calling; END;

 

於是檢查calling， 發現dbms_lock.sleep(3000)，也就是141會話出現的PL/SQL lock timer等待，從而找到了142會話發生library cache pin等待的原因。