原文链接:
http://www.eygle.com/internal/shared_pool-5.htm

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中,我们可能看到以下等待事件:

PHP code:



Top 5 Wait Events


~~~~~~~~~~~~~~~~~                                             Wait     % Total


Event                                               Waits  Time (cs)   Wt Time


-------------------------------------------- ------------ ------------ -------


library cache lock                                 75,884    1,409,500   48.44


latch free                                     34,297,906    1,205,636   41.43


library cache pin                                     563      142,491    4.90


db file scattered read                            146,283       75,871    2.61


enqueue                                             2,211       13,003     .45


          -------------------------------------------------------------

这里的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.创建测试用存储过程

PHP code:



[oracle@jumper udump]$ sqlplus "/ as sysdba"





SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 14:16:57 2004





Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.





Connected to an idle instance.





SQL> startup


ORACLE instance started.





Total System Global Area   47256168 bytes


Fixed Size                   451176 bytes


Variable Size              29360128 bytes


Database Buffers           16777216 bytes


Redo Buffers                 667648 bytes


Database mounted.


Database opened.


SQL> create or replace PROCEDURE pining


  2  IS


  3  BEGIN


  4          NULL;


  5  END;


  6  /





Procedure created.





SQL> 


SQL> create or replace procedure calling


  2  is


  3  begin


  4          pining;


  5          dbms_lock.sleep(3000);


  6  end;


  7  /





Procedure created.





SQL>

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

session 1:

PHP code:



[oracle@jumper oracle]$ sqlplus "/ as sysdba"





SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:13:43 2004





Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.








Connected to:


Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production


With the Partitioning, OLAP and Oracle Data Mining options


JServer Release 9.2.0.3.0 - Production





SQL> exec calling


.

此时calling开始执行

session 2:

PHP code:



[oracle@jumper udump]$ sqlplus "/ as sysdba"





SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:14:16 2004





Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.








Connected to:


Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production


With the Partitioning, OLAP and Oracle Data Mining options


JServer Release 9.2.0.3.0 - Production





SQL> grant execute on pining to eygle;


.

此时session 2挂起

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

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

PHP code:



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#会发生变化

PHP code:



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视图就可以得到对象的具体信息:

Note: X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject

PHP code:



col KGLNAOWN for a10


col KGLNAOBJ for a20


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:

Note:X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s

PHP code:



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         <a href="mailto:sqlplus@jumper.hurray.com.cn">sqlplus@jumper.hurray.com.cn</a> (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8          2          0


.

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

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

PHP code:



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            1

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

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

PHP code:



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等待的对象

PHP code:



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信息

PHP code:



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%')


/


SQL> 





       SID USERNAME   PROGRAM                                          ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ


---------- ---------- ------------------------------------------------ -------- -------- -------- -------- -------- -------- ---------- ----------


        13 SYS        <a href="mailto:sqlplus@jumper.hurray.com.cn">sqlplus@jumper.hurray.com.cn</a> (TNS V1-V3)         404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8          2          0


.

3.获得持有对象用户执行的代码

PHP code:



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;

在grant之前和之后我们可以转储一下shared pool的内容观察比较一下:

PHP code:



SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';





Session altered.

在grant之前:

从前面的查询获得pining的Handle是52D6730C:

PHP code:



******************************************************


BUCKET 67790:


  LIBRARY OBJECT HANDLE: handle=52d6730c


  name=SYS.PINING 


  hash=891b08ce timestamp=09-06-2004 16:43:51


  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]


  kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1


--在Object上存在共享pin


--在handle上存在Null模式锁定,此模式允许其他用户继续以Null/shared模式锁定该对象


  lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]


  pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]


  ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]


    LIBRARY OBJECT: object=52d65ba4


    type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0


    DATA BLOCKS:


    data#     heap  pointer status pins change    alloc(K)  size(K)


    ----- -------- -------- ------ ---- ------     -------- --------


        0 52d65dac 52d65c90 I/P/A     0 NONE       0.30     0.55


        4 52d65c40 52d67c08 I/P/A     1 NONE       0.44     0.48





.

在发出grant命令后:

PHP code:



******************************************************


BUCKET 67790:


  LIBRARY OBJECT HANDLE: handle=52d6730c


  name=SYS.PINING 


  hash=891b08ce timestamp=09-06-2004 16:43:51


  namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]


  kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1


--由于calling执行未完成,在object上仍让保持共享pin


--由于grant会导致重新编译该对象,所以在handle上的排他锁已经被持有


--进一步的需要获得object上的Exclusive pin,由于shared pin被calling持有,所以library cache pin等待出现.


  lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]


  pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]


  ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]


    LIBRARY OBJECT: object=52d65ba4


    type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0


    DATA BLOCKS:


    data#     heap  pointer status pins change    alloc(K)  size(K)


    ----- -------- -------- ------ ---- ------     -------- --------


        0 52d65dac 52d65c90 I/P/A     0 NONE       0.30     0.55


        4 52d65c40 52d67c08 I/P/A     1 NONE       0.44     0.48


.

实际上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:

PHP code:



[oracle@jumper oracle]$ sqlplus "/ as sysdba"





SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 7 17:05:25 2004





Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.








Connected to:


Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production


With the Partitioning, OLAP and Oracle Data Mining options


JServer Release 9.2.0.3.0 - Production





SQL> alter procedure pining compile;

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

PHP code:



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 ro

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

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

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

That's all for this topic.

前几天一个不小心做了一个开发人员上项目提出的表的结构修改，导致了我所不知道也没有仔细核查的 对象依赖关系
造成recompile在数据库繁忙情况下包不能编译成功。

使得偶几个月来终于出现故障，更惨的是刚开始发起半个月多的0故障比赛偶终于倒下了，海南之行的奖励看起来是困难了  

QUOTE:

---

最初由 biti_rainy 发布
前几天一个不小心做了一个开发人员上项目提出的表的结构修改，导致了我所不知道也没有仔细核查的 对象依赖关系
造成recompile在数据库繁忙情况下包不能编译成功。

使得偶几个月来终于出现故障，更惨的是刚开始发起半个月多的0故障比赛偶终于倒下了，海南之行的奖励看起来是困难了  

---

那就来个北京之行吧