LIBRARY CACHE PIN 等待事件

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 calling

session 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 -