深入理解shared pool共享池之library cache的library cache lock系列四

背景

本文了解下等待事件library cache lock，進一步理解library cache，之前的文章請見：
深入理解shared pool共享池之library cache的library cache pin系列三
http://blog.itpub.net/9240380/viewspace-1845911/

結論

1，道理同library cache pin一樣，診斷library cache lock也要使用v$session及dba_kgllock
2, library cache lock是由於library cache pin所引發的，即後者是原因
因為如果會話想修改或檢視物件，必須對物件持鎖後獲取一個PIN，即先要獲取library cache pin,然後才是library cache lock
3，從DUMP LIBRARY CACHE的結構來看，
可見基於前文：多了lock waiters結構,從LIBRARY CACHE LOCK的含義可見，必須先要獲取LIBRARY CACHE PIN,方可獲取到LIBRARY CACHE LOCK，現在明顯LIBRARY CACHE PIN被其它會話佔用，所以獲取不到，
只有等待，這樣就產生了LIBRARY CACHE LOCK，也就是說只要產生LIBRARY CACHE LOCK,必然存在LIBRARY CACHE PIN,而如果出現LIBRARY CACHE PIN,不一定會出現LIBRARY CACHE LOCK

4，所以在產生LIBRARY CACHE PIN要馬上處理，以防在業務高期連鎖出現library cache lock，最終把業務拖死
5,當然上述我們只是從原理來分析下library cache pin及library cache lock,可以直接採用如下指令碼，直接找到原因（不過你明白了LIBRARY CACHE的原因，會更有助於解決與此相關的故障）
當然你直接通過blocking_session也可以更為真觀直接找到引發library cache lock的元凶，直觀原因是library cache pin,最後原因是執行儲存過程
也可以說明library cache lock不是latch,而是鎖lock
SQL> select sid,serial#,saddr,event,blocking_session from v$session where sid in (111,116,121);

SID SERIAL# SADDR EVENT BLOCKING_SESSION
---------- ---------- ---------------- ------------------------------ ----------------
111 101 00000000A4720168 library cache lock 121
116 153 00000000A4726CC0 SQL*Net message from client
121 215 00000000A472D818 library cache pin 116
6,library cache lock和library cache pin一樣，不是LATCH，它是LOCK鎖

測試

library cache lock的含義：
1,用於管理併發訪問library cache
2,pinning一個物件，即會把HEAP載入到記憶體中,也就是說獲取到PIN後，方可把HEAP載入到記憶體中
3,如果會話想修改或檢視物件，必須對物件持鎖後獲取一個PIN，即先要獲取library cache pin,然後才是library cache lock

在上述前文的繼續，如果新開一個會話編譯儲存過程，則會產生library cache lock

SQL> select sid from v$mystat where rownum=1;

SID
----------
111

SQL> alter procedure proc_hard_parse compile;

SQL> select sid,event,p1,p2,p3 from v$session_wait where sid in (116,121,111);

SID EVENT P1 P2 P3
---------- ---------------------------------------------------------------- ---------- ---------- ----------
111 library cache lock 2739958280 2664431712 301
116 SQL*Net message from client 1650815232 1 0
121 library cache pin 2739958280 2663017352 301

--查詢與library cache lock相關的LATCH
SQL> select latch#,name from v$latch where lower(name) like '%library cache lock%';

LATCH# NAME
---------- --------------------------------------------------
218 library cache lock
221 library cache lock allocation

--library cache lock LATCH的資訊
SQL> select latch#,name from v$latch_children where lower(name) like '%library cache lock%';

LATCH# NAME
---------- ------------------------------
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock

SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where lower(name) like '%library cache lock%';

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
library cache lock handle address lock address 100*mode+namespace Concurrency

---解決LIBRARY CACHE LOCK的指令碼

---第1步
可見LIBRARY CACHE LOCK及LIBRARY CACHE PIN皆是基於LIBRARY CACHE HANDLE
SQL> select sid,event,p1,p2,p3 from v$session_wait where sid in (116,121,111);

SID EVENT P1 P2 P3
---------- ---------------------------------------------------------------- ---------- ---------- ----------
111 library cache lock 2739958280 2664431712 301 ---library cache lock
116 SQL*Net message from client 1650815232 1 0
121 library cache pin 2739958280 2663017080 301

---第2步，轉換上述的P1為16進位制
SQL> select to_char('2739958280','xxxxxxxx') from dual;

TO_CHAR('
---------
a3507208

--第3步，傳入上述的P1到如下字典，找到等待及持有LIBRARY CACHE LOCK的資訊（請注意列：KGLLKMOD及KGLKREQ的2個列，道理同於V$LOCK的REQUEST及LMODE）

SQL> select kgllkuse,kgllkhdl,kgllkmod,kgllkreq,kgllktype from dba_kgllock where kgllkhdl='00000000A3507208' and kgllktype='Lock';

KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL
---------------- ---------------- ---------- ---------- ----
00000000A4720168 00000000A3507208 0 3 Lock --等待library cache lock者
00000000A4726CC0 00000000A3507208 1 0 Lock
00000000A472D818 00000000A3507208 3 0 Lock ---持有library cache lock者

--第4步找到library cache lock的引發者，即library cache pin(關於library cache pin,請見我本文開始的之前的前文)
SQL> select sid,serial#,event from v$session where saddr='00000000A472D818';

SID SERIAL# EVENT
---------- ---------- ------------------------------
121 215 library cache pin

再整合下上述的SQL指令碼

當然你直接通過blocking_session也可以更為真觀直接找到引發library cache lock的元凶，直觀原因是library cache pin,最後原因是執行儲存過程
也可以說明library cache lock不是latch,而是鎖lock
SQL> select sid,serial#,saddr,event,blocking_session from v$session where sid in (111,116,121);

SID SERIAL# SADDR EVENT BLOCKING_SESSION
---------- ---------- ---------------- ------------------------------ ----------------
111 101 00000000A4720168 library cache lock 121
116 153 00000000A4726CC0 SQL*Net message from client
121 215 00000000A472D818 library cache pin 116

再從library cache bucket這個維度分析下，可見基於前文：多了lock waiters結構,從LIBRARY CACHE LOCK的含義可見，必須先要獲取LIBRARY CACHE PIN,方可獲取到LIBRARY CACHE LOCK，現在明顯LIBRARY CACHE PIN被其它會話佔用，所以獲取不到，
只有等待，這樣就生了LIBRARY CACHE LOCK，也就是說只要產生LIBRARY CACHE LOCK,必然存在LIBRARY CACHE PIN,而如果出現LIBRARY CACHE PIN,不一定會出現LIBRARY CACHE LOCK
BUCKET 59215:
LIBRARY OBJECT HANDLE: handle=a3507208 mtx=0xa3507338(0) lct=33 pct=32 cdp=0
name=SCOTT.PROC_HARD_PARSE
hash=84bc90d61f9452e79e7fe2d54c82e74f timestamp=11-24-2015 02:02:36
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-001d-20bf lock=X pin=S latch#=9 hpc=000c hlc=000c
lwt=0xa35072b0[0x9ed00090,0x9ed00090] ltm=0xa35072c0[0xa35072c0,0xa35072c0]
pwt=0xa3507278[0x9eba6d50,0x9eba6d50] ptm=0xa3507288[0xa3507288,0xa3507288]
ref=0xa35072e0[0xa35072e0,0xa35072e0] lnd=0xa35072f8[0xa34de600,0xa34fed28]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
98cf88c8 2 DEP[01] whr=0 timestamp=11-24-2015 02:02:36
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9ebc9b28 a472d818 a472d818 1 X [00] --可以關聯DBA_KGLLOCK及V$SESSION找到引發LIBRARY CACHE LOCK的直接原凶，即上述的121會話，可見持有排它鎖
9eb3d748 a4726cc0 a4726cc0 1 N PNC/[400] --這是個是最終的原凶，因為最初這個會話是持有NULL模式的LOCK鎖，即上述的116會話，可見持有NULL鎖
9eb3d3b0 a4726cc0 a4726cc0 0 N [4044]
LOCK WAITERS:
lock user session count mode
-------- -------- -------- ----- ----
9ed00060 a4720168 a4720168 0 X
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
9eae0b98 a4726cc0 a4726cc0 9eb3d748 3 S 0011
PIN WAITERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
9eba6d20 a472d818 a472d818 0 0 X 0000
LIBRARY OBJECT: object=9d949488
type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
DEPENDENCIES: count=3 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9d94b210 9d94af50 a3a1b6f0 0 DEP[01]
1 9d94b210 9d94afa8 a351ce38 33 DEP[01]
2 9d94b210 9d94b000 a3929db0 0 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency# table reference handle flags
----------- -------- --------- -------- -------------------
0 9d9288d0 9d9285e8 a34fa2c8 /ROD[20]
ACCESSES: count=1 size=16
dependency# types
----------- -----
1 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
a351ce38 a351ce38
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3507148 9d9495a0 I/P/A/-/- 0 NONE 00 0.37 1.09
2 9d9497a0 977456b0 I/-/A/-/- 0 NONE 00 13.27 16.00
4 9d928540 97b67850 I/P/A/-/- 1 NONE 00 2.26 4.00
BUCKET 59215 total object count=1

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29371470/viewspace-1868480/，如需轉載，請註明出處，否則將追究法律責任。

深入理解shared pool共享池之library cache的library cache lock系列四

背景

相關圖例

結論

測試

相關文章