LIBRARY CACHE PIN 等待事件：
Oracle文档上这样介绍这个等待事件：library cache pin是用来管理library cache的并发访问的，pin一个Object会引起相应的heap被载入内存中（如果此前没有被加载），pins可以在Null、Share、Exclusive这3个模式下获得，可以认为pin是一种特定形式的锁。
当library cache pin等待事件出现时，通常说明该pin被其他用户已非兼容模式持有。library cache pin的等待时间为3秒钟，其中有1秒钟用于PMON后台进程，即在取得pin之前最多等待3秒钟，否则就超时。library cache pin的参数有P1（KGL Handle Address）、P2（Pin Address）和P3（Encoded Mode & Namespace），常用的主要是P1和P2。
library cache 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的过程。
下面通过一个例子来模拟及解释这个等待，以下测试来自Oracle 10g数据库：
⑴ 创建测试用存储过程：
sys@NEI> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.sys@NEI> create or replace PROCEDURE calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(3000);
6 end;
7 /
Procedure created.sys@NEI> grant execute on pining to tq;
Grant succeeded.sys@NEI> grant execute on calling to tq;
Grant succeeded.
⑵ 模拟竞争。
首先执行执行calling过程，在calling过程中调用pining过程。此时pining过程上获得共享pin，如果此时尝试对pining进行授权或重新编译，将产生library cache pin等待，直到calling执行完毕。
session 1：
sys@NEI> exec callingsession 2：
sys@NEI> revoke execute on pining from tq;
此时Session 2挂起，直到出现：
revoke execute on pining from tq
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.PINING
现在开始研究，从v$session_wait入手可以得到哪些session正在library cache pin的等待：
sys@NEI> select sid,seq#,event,p1,p1raw,p2,p2raw,p3raw,wait_time wt,seconds_in_wait sw,state
2 from v$session_wait where event like 'library%';
SID SEQ# EVENT P1 P1RAW P2 P2RAW P3RAW WT SW STATE
---- ----- -------------------- ---------- -------- ---------- -------- -------- --- ---- -------------------
142 33 library cache pin 1042613708 3E2505CC 1093213516 41291D4C 0000012D 0 222 WAITING
在这个输出中，P1列是Library Cache Handle Address，Pn字段是十进制表示，PnRaw字段是十六进制表示。可以看到，library cache pin等待的对象的handle地址为3E2505CC。通过这个地址，查询X$KGLOB（[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject）视图就可以得到对象的具体信息。
sys@NEI> col KGLNAOWN for a10
sys@NEI> col KGLNAOBJ for a20
sys@NEI> select addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj
2 from x$kglob
3 where kglhdadr = '3E2505CC';
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
B71FCCF4 3E2505CC 3E2505CC SYS PINING 2030642293 00
这里KGLNAHSH代表该对象的Hash Value，由此知道，在PINING对象上正经历library cache pin的等待。然后引入另外一个内部视图X$KGLPN（[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s）。
sys@NEI> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
2 b.KGLPNLCK,b.KGLPNMOD,b.KGLPNREQ
3 from v$session a ,x$kglpn b
4 where a.saddr=b.kglpnuse and b.kglpnhdl='3E2505CC' and b.KGLPNMOD<>0 ;SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
---- ---------- ------------------------------ -------- -------- -------- -------- ---------- -------- ---------- ----------
140 SYS sqlplus@commserver (TNS V1-V3) B71FB020 40FBC190 43580A28 43580A28 3E2505CC 41249670 2 0
通过联合v$session，可以获得当前持有该handle的用户信息，对于测试sid=140的用户正持有该handle。那么这个用户正在等什么呢？
sys@NEI> select * from v$session_wait where sid=140;
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXTP3
---- ----- -------------------- --------------- ---------- -------- --------------- ---------- -------- --------------- ----------
P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
-------- ------------- ----------- --------------- ---------- --------------- -------------------
140 67 PL/SQL lock timer duration 300000 000493E0 0 00 0
00 2723168908 6 Idle 0 1608 WAITING
现在可以看到，这个用户正在等待一个PL/SQL lock timer计时。
得到了sid，就可以通过v$session.SQL_HASH_VALUE、v$session.SQL_ADDRESS等字段关联v$sqltext、v$sqlarea等视图获得当前Session正在执行的操作。
sys@NEI> select sql_text
2 from v$sqlarea
3 where hash_value=(select SQL_HASH_VALUE from v$session where sid=140);
SQL_TEXT
------------------------------
BEGIN calling; END;
这里得到这个用户正在执行calling这个存储过程，接下来的工作就应该去检查calling在做什么了。这个calling做的工作是dbms_lock.sleep(3000)，也就是PL/SQL lock timer正在等待的原因。至此就找到了library cache pin的原因。
简化一下以上查询过程。
⑴ 获得library cache pin等待对对象：
sys@NEI> select addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj
2 from x$kglob
3 where kglhdadr IN (select p1raw
4 from v$session_wait
5 where event like 'library%')
6 /
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
-------- -------- -------- --------------- --------------- ---------- --------
B730F11C 3E2505CC 3E2505CC SYS PINING 2030642293 40581714
⑵ 获得持有等待对象的Session信息。
sys@NEI> select a.sid,a.username,a.program,b.addr,b.kglpnadr,b.kglpnuse,
2 b.kglpnses,b.kglpnhdl,b.kglpnlck,b.kglpnmod,b.kglpnreq
3 from v$session a ,x$kglpn b
4 where a.saddr=b.kglpnuse
5 and b.kglpnmod <> 0
6 and b.kglpnhdl IN ( select p1raw
7 from v$session_wait
8 where event like 'library%')
9 /
SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
---------- ---------- ------------------------------ -------- -------- -------- -------- -------- -------- ---------- ----------
140 SYS sqlplus@commserver (TNS V1-V3) B730F7FC 40FBC190 43580A28 43580A28 3E2505CC 41249670 2 0
⑶ 获得持有对象用户执行的代码：
sys@NEI> select sql_text
2 from v$sqlarea
3 where (v$sqlarea.address,v$sqlarea.hash_value) in
4 (select sql_address,sql_hash_value
5 from v$session
6 where sid in
7 (select sid
8 from v$session a,x$kglpn b
9 where a.saddr =b.kglpnuse
10 and b.kglpnmod <> 0
11 and b.kglpnhdl in
12 (select p1raw from v$session_wait where event like 'library%'
13 )
14 )
15 );SQL_TEXT
------------------------------
BEGIN calling; END;
在revoke之前和之后，可以转储一下Library Cache的内容观察比较一下：
sys@NEI> alter session set events 'immediate trace name LIBRARY_CACHE level 32';
Session altered.
在revoke之前，从前面的查询获得pining的Handle是3E2505CC：
******************************************************
BUCKET 74854 total object count=1
BUCKET 74869:
LIBRARY OBJECT HANDLE: handle=3e2505cc mutex=0x3e250680(0)
name=SYS.PINING
hash=ad44abd16bc3d6fc2d6ba6a979092475 timestamp=12-04-2009 14:44:42
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1 hpc=0004 hlc=0004
--在Object上存在共享pin
--在handle上存在Null模式锁定，此模式允许其他用户以Null/Shared模式锁定该对象
lwt=0x3e250628[0x3e250628,0x3e250628] ltm=0x3e250630[0x3e250630,0x3e250630]
pwt=0x3e25060c[0x3e25060c,0x3e25060c] ptm=0x3e250614[0x3e250614,0x3e250614]
ref=0x3e250648[0x3e250648,0x3e250648] lnd=0x3e250654[0x43b4c694,0x3e24efa8]
LIBRARY OBJECT: object=405d22d0
type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 43aacc20 405d2368 I/P/A/-/- 0 NONE 00 0.22 0.00
4 405d23c8 402d9840 I/P/A/-/- 1 NONE 00 0.52 4.00
HEAP DUMP OF DATA BLOCK 0:
******************************************************
在发出revoke命令后：
******************************************************
BUCKET 74854 total object count=1
BUCKET 74869:
LIBRARY OBJECT HANDLE: handle=3e2505cc mutex=0x3e250680(0)
name=SYS.PINING
hash=ad44abd16bc3d6fc2d6ba6a979092475 timestamp=12-04-2009 14:44:42
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1 hpc=0006 hlc=0006
--由于calling执行未完成，在object上仍让保持共享pin
--由于revoke会导致重新编译该对象，所以在handle上的排他锁已经被持有
--进一步地需要获得object上Exclusive pin，由于shared pin被calling持有，所以library cache pin等待出现
lwt=0x3e250628[0x3e250628,0x3e250628] ltm=0x3e250630[0x3e250630,0x3e250630]
pwt=0x3e25060c[0x41274c38,0x41274c38] ptm=0x3e250614[0x3e250614,0x3e250614]
ref=0x3e250648[0x3e250648,0x3e250648] lnd=0x3e250654[0x43b4c694,0x3e24efa8]
LIBRARY OBJECT: object=405d22d0
type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 43aacc20 405d2368 I/P/A/-/- 0 NONE 00 0.22 0.00
4 405d23c8 402d9840 I/P/A/-/- 1 NONE 00 0.52 4.00
HEAP DUMP OF DATA BLOCK 0:
******************************************************
实际上recompile过程包含以下步骤，同时来看一下lock和pin是如何交替发挥作用的。
·存储过程的library cache object以排他模式被锁定，这个锁定是在handle上获得的。Exclusive锁定可以防止其他用户执行同样的操作，同时防止其他用户创建新的引用此过程的对象。
·以Shared模式pin该对象，以执行安全和错误检查。
·共享pin被释放，重新以排他模式pin该对象，执行重编译。
·使所有依赖该过程的对象失效。
·释放Exclusive Lock和Exclusive Pin。
- The End -