记一次shared pool中的等待事件——LIBRARY CACHE PIN

周末在公司打游戏，同事突然问我，为什么我的procedure删除不了？我觉得很纳闷，过去一看，drop procedure之后处于吊死状态，凭借经验，很有可能是library cache pin或者library cache lock，通过一番检查发现确实是library cache pin

虽然现在又开始从事Java的设计和开发工作，但是对于Oracle方面还是比较热衷的，类似等待事件都必须非常熟悉，接下去就要结合多次文章来把常见的等待事件使用模拟的方式来进行分析讲解

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.

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

接下来我来通过实际模拟来解释LIBRARY CACHE PIN等待事件：

首先，我们先创建需要使用到的procedure：

C:Documents and Settingskidd>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on 星期五 1月 29 14:18:00 2010

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn /as sysdba

已连接。

SQL> create or replace PROCEDURE pining

2 IS

3 BEGIN

4 NULL;

5 END;

6 /

过程已创建。

SQL> create or replace procedure calling

2 is

3 begin

4 pining;

5 dbms_lock.sleep(3000);

6 end;

7 /

过程已创建。

然后在另一个会话中执行calling，使calling进入等待阶段：

C:Documents and Settingskidd>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on 星期五 1月 29 14:21:59 2010

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn /as sysdba

已连接。

SQL> exec calling

[@more@]

接下去就是要在其它的终端对calling进行DDL干涉了，比如说：

C:Documents and Settingskidd>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on 星期五 1月 29 14:26:17 2010

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn /as sysdba

已连接。

SQL> drop procedure calling;

结果就是会话被挂起

接下去我们就要开始揭抛了

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

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait

where event like 'library%';

SID SEQ#

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

EVENT P1

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

P1RAW P2 P2RAW P3 P3RAW STATE

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

10 40

library cache pin 1712146160

660D46F0 1719361780 667B60F4 301 0000012D WAITING

在这里简单的解释一下v$session_wait的几个列，P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示，其它的可以通过相关资料获得：）

首先获得library cache pin等待的对象的handle地址为: 660D46F0

有了handle之后，通过查询X$KGLOB视图就可以得到对象的具体信息:

SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ

2 from X$KGLOB

3 where KGLHDADR ='660D46F0';

ADDR KGLHDADR KGLHDPAR

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

KGLNAOWN

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

KGLNAOBJ

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

KGLNAHSH KGLHDOBJ

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

029C1034 660D46F0 660D46F0

SYS

CALLING

1069625444 660D5638

从这个信息反馈上，我们了解到在PINING对象上正经历library cache pin的等待

SQL> 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 = '660D46F0' and b.KGLPNMOD<>0;

SID USERNAME

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

PROGRAM ADDR

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

KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ

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

8 SYS

sqlplus.exe 029BBB10

667B62EC 66DB7758 66DB7758 660D46F0 667B51E8 2 0

OK，我们引入了x$kglpn视图，可以获得当前持有该SID用户信息

SQL> select EVENT from v$session_wait where sid=8;

EVENT

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

PL/SQL lock timer

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

SQL> select v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS from v$session where sid = '8';

SQL_HASH_VALUE SQL_ADDR

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

1315522757 660C9EE8

我们通过SID得到了v$session视图的SQL_HASH_VALUE，这样我们就可以得到v$sqlarea .sql_text

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

SQL_TEXT

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

BEGIN calling; END;

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

当然了，那么shared pool中的lock机制是如何的呢？当然我们可以通过存储shared pool到trace，在之前做了些什么，之后又做了些什么呢？

我们将在下一次篇幅中进行介绍

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/111631/viewspace-1030986/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/111631/viewspace-1030986/