library cache pin/lock

library cache pin/lock

 

近日数据库出现library cache pin的问题，网上转过来一个简单的查询办法，以备日后使用。

 

Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin.
Lock比pin具有更高的级别.

Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.
锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.

在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.

通常我们访问、执行过程、Package时获得的都是共享pin,如果排他pin被持有,那么数据库此时就要产生等待.
在很多statspack的report中,我们可能看到此等待事件。

                                         

这里的library cache lock和library cache pin都是我们关心的.接下来我们就研究一下这几个等待事件.

(一).LIBRARY CACHE PIN等待事件

Oracle文档上这样介绍这个等待事件:
"library cache pin" 是用来管理library cache的并发访问的,pin一个object会引起相应的heap被
载入内存中(如果此前没有被加载),Pins可以在三个模式下获得:NULL,SHARE,EXCLUSIVE,可以认为pin是一种特定
形式的锁.
当Library Cache Pin等待事件出现时,通常说明该Pin被其他用户已非兼容模式持有.

"library cache pin"的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.
"library cache pin"的参数如下,有用的主要是P1和P2:
                               P1 - KGL Handle address.
                               P2 - Pin address
                               P3 - Encoded Mode & Namespace

"LIBRARY CACHE PIN"通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,
如安装应用程序,升级,安装补丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效,
可以通过object的"LAST_DDL"观察这些变化.
当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin
到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新
编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.

下面让我们通过一个例子来模拟及解释这个等待:

1.创建测试用存储过程
SQL> create or replace PROCEDURE pining
   2   IS
   3   BEGIN
   4                   NULL;
   5   END;
   6   /

Procedure created.

SQL> create or replace procedure calling
   2   is
   3   begin
   4                   pining;
   5                   dbms_lock.sleep(3000);
   6   end;
   7   /

Procedure created.         
           

2.模拟
首先执行calling过程,在calling过程中调用pining过程
此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待
直到calling执行完毕.

session 1:

SQL> exec calling

 

此时calling开始执行

session 2:

 

SQL> grant execute on pining to eygle;

 

此时session 2挂起

ok,我们开始我们的研究:

从v$session_wait入手,我们可以得到哪些session正在经历library cache pin的等待

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
   2   from v$session_wait where event like 'library%';

  SID             SEQ# EVENT                                             P1 P1RAW                       P2 P2RAW                       P3   WAIT_TIME SECONDS_IN_WAIT STATE
---- ---------- ------------------- ---------- -------- ---------- 
     8               268 library cache pin     1389785868 52D6730C 1387439312 52B2A4D0               301                   0                             2 WAITING

等待3秒就超时,seq#会发生变化

SQL>

  SID             SEQ# EVENT                                             P1 P1RAW                       P2 P2RAW                       P3   WAIT_TIME SECONDS_IN_WAIT STATE
---- ---------- ------------------- ---------- -------- ---------- 
     8               269 library cache pin     1389785868 52D6730C 1387439312 52B2A4D0               301                   0                             2 WAITING

SQL>

  SID             SEQ# EVENT                                             P1 P1RAW                       P2 P2RAW                       P3   WAIT_TIME SECONDS_IN_WAIT STATE
---- ---------- ------------------- ---------- -------- ---------- 
     8               270 library cache pin     1389785868 52D6730C 1387439312 52B2A4D0               301                   0                             0 WAITING

           

在这个输出中,P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示

我们看到,library cache pin等待的对象的handle地址为:52D6730C
通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:

select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='52D6730C'

ADDR         KGLHDADR KGLHDPAR KGLNAOWN     KGLNAOBJ                             KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ----------
404F9FF0 52D6730C 52D6730C SYS               PINING                             2300250318 52D65BA4           
           

这里KGLNAHSH代表该对象的Hash Value

由此我们知道,在PINING对象上正经历library cache pin的等待.

然后我们引入另外一个内部视图X$KGLPN:

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0
/

   SID USERNAME       PROGRAM                                                                   ADDR         KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK     KGLPNMOD     KGLPNREQ
----- ----------- ---------------------------------------- -------- 
     13 SYS      (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8                   2                  0
           

通过联合v$session,可以获得当前持有该handle的用户信息.
对于我们的测试sid=13的用户正持有该handle

那么这个用户正在等什么呢?

SQL> select * from v$session_wait where sid=13;

             SID             SEQ# EVENT                             P1TEXT                       P1 P1RAW       P2TEXT                   P2 P2RAW       P3TEXT                   P3 P3RAW         WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ------------------- --------- ---------- 
               13                 25 PL/SQL lock timer     duration           120000 0001D4C0                                   0 00                                               0 00                               0                       1200 WAITING
           

Ok,这个用户正在等待一次PL/SQL lock timer计时.

得到了sid,我们就可以通过v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段关联v$sqltext,v$sqlarea等视图获得当前session正在执行的操作.

SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777';

SQL_TEXT
--------------------------------------------------------------------
BEGIN calling; END;       
 

这里我们得到这个用户正在执行calling这个存储过程,接下来的工作就应该去检查calling在作什么了.

我们这个calling作的工作是dbms_lock.sleep(3000)
也就是PL/SQL lock timer正在等待的原因

至此就找到了Library Cache Pin的原因.

简化一下以上查询:

1.获得Library Cache Pin等待的对象

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
   FROM x$kglob
  WHERE kglhdadr IN (SELECT p1raw
                                           FROM v$session_wait
                                         WHERE event LIKE 'library%')

ADDR         KGLHDADR KGLHDPAR KGLNAOWN     KGLNAOBJ                             KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------
404F2178 52D6730C 52D6730C SYS               PINING                             2300250318 52D65BA4       
 

2.获得持有等待对象的session信息
SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
             b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
   FROM v$session a, x$kglpn b
  WHERE a.saddr = b.kglpnuse
     AND b.kglpnmod <> 0
     AND b.kglpnhdl IN (SELECT p1raw
                                               FROM v$session_wait
                                             WHERE event LIKE 'library%')

             SID USERNAME     PROGRAM                                                                                   ADDR         KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK     KGLPNMOD     KGLPNREQ
---------- ---------- ----------------------------------------------13 SYS            (TNS V1-V3)                 404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8                   2                   0
           

3.获得持有对象用户执行的代码
SELECT sql_text
   FROM v$sqlarea
  WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
                   SELECT sql_address, sql_hash_value
                       FROM v$session
                     WHERE SID IN (
                                       SELECT SID
                                           FROM v$session a, x$kglpn b
                                         WHERE a.saddr = b.kglpnuse
                                             AND b.kglpnmod <> 0
                                             AND b.kglpnhdl IN (SELECT p1raw
                                                                                       FROM v$session_wait
                                                                                     WHERE event LIKE 'library%')))

SQL_TEXT
---------------------------------------------------------------------
BEGIN calling; END;
           

实际上recompile过程包含以下步骤,我们看一下lock和pin是如何交替发挥作用的:
1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的
exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象.
2.以shared模式pin该对象,以执行安全和错误检查.
3.共享pin被释放,重新以排他模式pin该对象,执行重编译.
4.使所有依赖该过程的对象失效
5.释放exclusive lock和exclusive pin

(二).LIBRARY CACHE LOCK等待事件

如果此时我们再发出一条grant或compile的命令,那么library cache lock等待事件将会出现:

session 3:

SQL> alter procedure pining compile;

           

此进程挂起,我们查询v$session_wait视图可以获得以下信息:

SQL> select * from v$session_wait;

  SID SEQ# EVENT                             P1TEXT                                   P1 P1RAW       P2TEXT                             P2 P2RAW      P3TEXT                           P3 P3RAW         WAIT_TIME SECONDS STATE 
---- ---- ------------------- --------------- ---------- -------- 
   11   143 library cache pin     handle address   1390239716 52DD5FE4 pin address   1387617456 52B55CB0 100*mode+namespace 301 0000012D                   0             6 WAITING
   13     18 library cache lock   handle address   1390239716 52DD5FE4 lock address 1387433984 52B29000 100*mode+namespace 301 0000012D                   0             3 WAITING
     8   415 PL/SQL lock timer     duration               120000         0001D4C0                                             0 00                                                       0 00                               0           63 WAITING
....

13 rows selected
         

由于handle上的lock已经被session 2以exclusive模式持有,所以session 3产生了等待.

我们可以看到,在生产数据库中权限的授予、对象的重新编译都可能会导致library cache pin等待的出现.
所以应该尽量避免在高峰期进行以上操作.

另外我们测试的案例本身就说明:如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现.
所以在应用开发的过程中,我们也应该注意这方面的内容.

 

文章引用自：http://blog.chinaunix.net/u/12960/showart.php?id=100227