library cache pin和library cache lock的診斷分析

最近執行一個分批刪除資料的procedure，感覺執行比較慢，臨時取消了該job，然後再次重建procedure時，session hang住了，應該是library cache pin的等待事件了，oracle用兩種結構pin和lock來控制shared pool的併發訪問控制。

訪問一個物件將其pin到記憶體中，在pin之前需要先獲取該物件的handle的鎖定，lock的索引主要有null share exclusive，當然pin該物件也有上述三種狀態。

Session a ：execute delete_data

臨時取消了該procedure的執行，實際程式並沒有馬上釋放該物件的pin

Session b：

SQL> create or replace procedure delete_data

2 as

3 begin

4 loop

Delete from TEXTAUTO_FOLDERARTICLE where LASTPOSTDATE

Exit when sql%notfound;--dml語句中隱式遊標屬性進行控制

Commit;

End loop;

9 Commit;

10 end;

11 /

出現了library cache pin等待事件：

SQL> select event,count(*) from v$session group by event;

EVENT COUNT(*)

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

SQL*Net message from client 92

library cache pin 1

jobq slave wait 1

rdbms ipc message 10

smon timer 1

pmon timer 1

db file scattered read 1

Streams AQ: qmn slave idle wait 1

SQL*Net message to client 1

Streams AQ: qmn coordinator idle wait 1

Streams AQ: waiting for time management or cleanup tasks 1

11 rows selected.

分析一下上述的library cache pin是如何造成的：首先系統執行了該procedure，此時該物件已經被pin到記憶體，佔用了一個share pin和一個null lock，而此時在程式還沒有釋放此pin之前再次create procedure，此時要獲取一個exclusive pin和exclusive lock，由於之前的share pin和exclusive pin不共存，此時就會產生一個library cache pin等待。

這裡注意一下兩個基表x$kglpn和x$kgllk，對於解決library cache pin和lock太輕鬆了。

SQL> desc x$kglpn;

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

KGLPNADR RAW(8)

KGLPNUSE RAW(8)

KGLPNSES RAW(8)

KGLPNHDL RAW(8)

KGLPNLCK RAW(8)

KGLPNCNT NUMBER

KGLPNMOD NUMBER

KGLPNREQ NUMBER

KGLPNDMK NUMBER

KGLPNSPN NUMBER

透過等待事件的p1raw引數聯合kglpnhdl來獲取kglpnuse，這裡的kglpnmod和kglpnreq和v$lock的lmode和request基本相同，kglpnmod=2表示此時這個session佔有了library cache pin而造成了kglpnreq=3這個session的等待。

SQL> select kglpnuse,kglpnhdl,kglpnmod,kglpnreq from x$kglpn where kglpnhdl in (select p1raw from v$session where event='library cache pin');

KGLPNUSE KGLPNHDL KGLPNMOD KGLPNREQ

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

00000000D8227590 00000000DD876D98 0 3

00000000DA225340 00000000DD876D98 2 0

聯合v$session的saddr來獲取sid然後可以透過v$process來kill掉沒有釋放的程式。

SQL> select b.spid,a.sid from v$session a,v$process b where a.saddr in ('00000000DA225340') and a.paddr=b.addr;

SPID SID

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

11232 195

而如果此時再重新編譯一下該procedure，同樣需要獲取一個該物件的lock鎖定，由於上述重建procedure還在檢視獲取該物件的library cache lock的exclusive lock，那麼當然此時出現library cache lock是當然的。

SQL> alter procedure delete_data compile;

SQL> select event,count(*) from v$session group by event;

EVENT COUNT(*)

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

SQL*Net message from client 91

library cache pin 1

jobq slave wait 1

rdbms ipc message 10

smon timer 1

pmon timer 1

db file scattered read 1

library cache lock 1

Streams AQ: qmn slave idle wait 1

SQL*Net message to client 1

Streams AQ: qmn coordinator idle wait 1

SQL> desc x$kgllk;

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

KGLLKADR RAW(8)

KGLLKUSE RAW(8)

KGLLKSES RAW(8)

KGLLKSNM NUMBER

KGLLKHDL RAW(8)

KGLLKPNC RAW(8)

KGLLKPNS RAW(8)

KGLLKCNT NUMBER

KGLLKMOD NUMBER

KGLLKREQ NUMBER

KGLLKFLG NUMBER

KGLLKSPN NUMBER

KGLLKHTB RAW(8)

KGLNAHSH NUMBER

KGLLKSQLID VARCHAR2(13)

KGLHDPAR RAW(8)

KGLHDNSP NUMBER

USER_NAME VARCHAR2(30)

KGLNAOBJ VARCHAR2(60)

獲取blocking session的方法基本相同，也是透過p1raw引數聯合kgllkhdl來獲取kgllkuse，最後聯合v$session來獲取sid，進而透過v$process獲取spid，殺掉程式後即可

SQL> select kgllkhdl,kgllkuse,kgllkreq,kgllkmod from x$kgllk where kgllkhdl in (Select p1raw from v$session where event='library cache lock');

KGLLKHDL KGLLKUSE KGLLKREQ KGLLKMOD

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

00000000DD876D98 00000000DA225340 0 1

00000000DD876D98 00000000D8227590 0 3

00000000DD876D98 00000000D821FC40 3 0

透過上述的library cache pin和library cache lock等待事件的分析和診斷，可以看出資料字典基表可以提供我們非常的資訊，而幫助解決一些比較棘手的案例，當然上述我們也可以利用oradebug然後透過trace檔案來診斷，不過相對trace檔案較難以閱讀，需要一定的功底，後續如果有對該等待事件trace的解決案例再拿出來與大家分享。

[@more@]