转载

深入理解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

正文到此结束