本人最后一个9i的客户前两天出了性能问题，一线生产数据库，十万火急，结果发现9i数据库的一个节点连接数为1300左右，尽然有240个异常等待事件，绝大部分是library cache pin和library cache lock，翻出05年写的一遍手记，感觉真的老，解决此问题，如下：

造成数据库性能下降或挂起的原因很多，“library cache pin”等待是其中之一。当数据库性能严重下降或挂起时，我们通过查询v＄session_wait,发现大量的“library cache pin”等待,查询的SQL语句如下:

#su_oracle

$svrmgrl

svrmgr>connect internal

svrmgr>select sid,event,p1,p2,p3from v$session_wait where wait_time=0;

SID

EVENT

P1

P2

P3

9

Library cache pin

15417016

10090832

20

154

Library cache pin

15417016

11224168

20

341

Library cache pin

15417016

11449936

20

349

Library cache pin

15417016

16489792

20

390

Library cache pin

15417016

11992536

20

160

Library cache pin

15417016

6166600

20

20

Library cache pin

15417016

10868760

20

因此,我们断定数据库性能下降或挂起是由“library cache pin”引起的.这种情况通常发生在大量使用数据库存储PL/SQL块的并发应用程序中,而在测试环境中则很难再现.

LIBRARY CACHE PIN等待事件

那么什么是“library cache pin”等待呢? “library cache pin”事件是用来管理library cache的并发访问的,pin一个object会引起相应的heap被载入内存中,如果客户端需要修改或检测这个object它就必须在锁住后取得一个pin.

“library cache pin”的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.

“library cache pin”的参数如下,有用的主要是P1和P2:

P1-KGL Handle address.

P2-Pin address

P3-10*Mode+Namespace

其中,P1,P2可与x$kglpn和x$kglob表相关. x$kglpn和x$kglob是ORACLE数据库的内部数据字典.

等待一个pin意味着另有一个阻塞者(blocker)，它处于更高级别或不兼容状态，我通过以下matrix来判断是否兼容，“X”表示不兼容：

Blocker

Waiter

KGLMO  KGLMN  KGLMS   KGLMX

KGLMO-     X      X         X

KGLMN-     -       X         X

KGLMS-      -       -       X

KGLMX-     ？      X         X

其中：KGLMO，KGLMN，KGLMS和KGLMX是X$KGLPN和X$KGLHDLMD字段的几种状态。

KGLMO   0   no lock/pin held

KGLMN   1   null　mode

KGLMS   2   share mode

KGLMX   3  exclusive mode

常见的原因及解决方法

“LIBRARY CACHE PIN”通常是发生在编译或重新编译PL/SQL，VIEW，TYPES等object时，编译通常都是显性的，如安装应用程序，升级，安装补丁程序等，但object的重新编译也可能发生在object变得无效时。

我们在处理因“LIBRARY CACHE PIN”引起的性能变慢或挂起时，应检查object无效方面的原因。当我们对object进行维护，如“ALTER”，“GRANT”，“REVOKE”时，就会使object变得无效，通过object的“LAST-DDL”属性可查看到这些变化。

当object变得无效时，Oracle会在第一次访问此object时试图去重新编译它，如果此时其他session已经把此object pin 到library cache中，就会出现问题，特别是当有大量的活动session并且存在较复杂的dependence时，在某种情况下，重新编译object可能会花几个小时时间，从而阻塞其他试图去访问此object的进程，这种情况我们可以通过library cache dump level 10，查找“ALTER…COMPILE”sql语句和带有“lock=X”或“pin=X”的object或handles得知。在某些时候，可能会报错，如“ORA-600[17285]”“ORA-4061”“ORA-4065”“ORA-6508”等。

综上所述，我们在对PL/SQL存储过程中经常引用到object进行修改，授权，收回授权时必须非常小心。实际上，解决这些问题大多要依靠应用程序的开发和维护，应用程序开发商应该考虑到某些方案的决策可能会给应用程序的伸缩性和性能带来负面影响。

以下列出几种可能产生“library cache pin”的情况及其避免方法：

1．  用户权限管理

当对用户的权限进行管理即进行“grant”“revoke”时，可能产生“library cache pin”。

建议的避免方法：通过角色来对最终用户授权或收回授权，从而避免产生“library cache pin”

2．  高峰时的object管理

在系统运行高峰对数据库object的管理可能产生“library cache pin”。

建议的避免方法：把对数据库object的管理安排到负载相对较小的时候。

3．  在PL/SQL包中存在大量的互跨的依赖性(dependency)

建议的避免方法：尽可能按等级来排列它们的结构。

详细分析步骤

下面给出两种方法来分析“library cache pin”，目的是找出哪些session在等待资源，哪些session正占着这些资源，而占着资源的这些session又在做什么，从而找到问题的根源并加以解决。

方法1

(1)。通过查询V$SESSION-WAIT找出等待“library cache pin”的session,其SQL语句如下:sql>select sid,substr(event,1,30),p1,p2,p3 from v$session_wait where wait_time=0 and event like ‘library cache pin%’;

SID            SUBSTR(EVENT,1,30)         P1            P2        P3

9                 library cache pin       15417016      10090832      20

154                library cache pin       15417016      11224168      20

341                library cache pin       15417016      11449936      20

349                library cache pin       15417016      16489792      20

390                library cache pin       15417016      11992536      20

160                library cache pin       15417016      6166600       20

20                 library cache pin       15417016      10868760      20

其中:

P1列是 Library Cache  Adress

P2列是 Library Cache  Pin Adress.

(2).把P1的值转换为十六进制,即15417016- - >EB3EB8,然后查询X$KGLPN表(Library Cache Object Pin),可找到相关ｓｅｓｓｉｏｎ，其SQL语句如下 (即把V$SESSION_WAIT中的P1与X$KGLPN中的KGLPNHDL相关连):

Sql>select ADDR,INDX,KGLPNADR,KGLPNUSE,KGLPNSES,KGLPNHDL,KGLPNLCK,KGLPNMOD,KGLPNREQ from x$kglpn where KGLPNHDL like’%EB3EB8%’;

ADDR         INDX         KGLPNADR        KGLPNUSE          KGLPNSES

KGLPNHDL   KGLPNLCK      KGNMOD        KGLPNREQ

05B0CB30        1            00FB9D40          0011B830             0011B830

00EB3EB8    033ECD10            0                 2

05B0CB30        2            00AB4468          000E56E0             000E56E0

00EB3EB8   004473A0            0                  2

05B0CB30        3            005E1848           000E7180             000E7180

00EB3EB8   00A30F60            0                  2

05B0CB30         4           00B6FDD8         00126E20              00126E20

00EB3EB8     00DD6738           0                 2

05B0CB30         5           0099FP50          000BD370             000BD370

00EB3EB8     00E07F78            0                 2

05B0CB30         6           00AEB650         001194B0              001194B0

00EB3EB8     00DB6BC8           0                 2

05B0CB30         7          00A5D818         000C0440            000C0440

00EB3EB8    0043A1A8            0                 2

05B0CB30         8           004001E8          00103A90           00103A90

00EB3EB8     004CA428            3                  0

其中:

KGLPNHDL  -----  Library Cache Handle Address

KGLPNADR  -----  Library Cache Pin Adress

KGLPNSES  ------  识别锁住此pin 的session

KGLPNREQ  -----  Pin 请求

KGNMODE  ------  Pin 锁

从以上查询结果可以看到，有一个session 正占着pin 锁(ＫＧＮＭＯＤ＝３)，而其它session正等待此pin锁(KGLPNREQ=2):

ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ

05B0CD98  8  004001E8 00103A90 00103A90  00EB3EB8  004CA428    3     0

(3).查询X$KGLOB(Library Cache Object),可找到相关的object,其SQL语句如下(即把V$SESSION_WAIT中的P1与X$KGLOB中的KGLHDADR相关连):

sql>select*from X$KGLOB where KGLHDADR like ‘%EB3EB8%’;

ADDR INDX KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNADLK KGLNAHSH  KGLNATIM  KGLNAPTM

KGLHDNSP   KGLHDLMD  KGLHDPMD  KGLHDFLG  KGLHDOBJ  KGLHDLDC  KGLHDIVC  KGLHDEXC  KGLHDLKC

KGLHDKMK  KGLHDDMK K GLHDAMK  KGLOBFLG  KGLOBSTA  KGLOBTYP  KGLOBCNU  KGLOBHS0  KGLOBHS1  KGLO

BHS2  KGLOBHS3  KGLOBHS4  KGLOBHS5  KGLOBHS6  KGLOBHS7  KGLOBPC0  KGLOBPC6  KGLOBMSZ  KGLOBPSZ  KGLOBPUS  KGLOBCXF  KGLOBSOR   KGLOBPRS  KGLOBDSK  KGLOBBUF  KGLOBUID  KGLOBSID  KGLOBOCT

05B0A230  30  00EB3EB  8   00EB3EB8

select count(*) from fa_facturas @decmovi where fa _cuenta  à KGLNAOBJ

= : 1

1569934176 22-ENE-97 0 1 3 1342242

816 00EB3C30 2 0 67014 9 0 1

0 1 1 0 65535 1470 0

0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 -4 -43

其中：

KGLNAOBJ ----相关object的名字(取前面80个字符)

这样，即可查出“library cache pin”是针对哪个object的(此处为“fa_facturas”).

(4). 把V$SESSION的SADDRL列与X$KGLPN的KGLPNUSE列相关连,并查询V$SESSION_WAIT,即可查出占着pin锁的session目前正在做什么,其SQL语句如下:

sql>select a.sid,a.username,a.program from v$session a,x$kglpn b where

a.saddr=b.kglpnuse and b.kglpnhdl like ‘%EB3EB8%’ and b.kgnmod<>0;

sql>select event from v$session_wait where sid=；\

通过以上查询,可看到此session正等待“Client Message”,即此用户可能已离开客户终端,这样通过把此session杀掉即可解决问题.

方法2

(1).通过查询V$SESSION_WAIT 找到正等待“library cache pin”的session(即等待者),其SQL语句如下;

sql>select sid Waiter,

substr(rawtohex(P2),1,30)Handle,

substr(rawtohex(P3),1,30)Pin_addr

from v$session_wait where wait_time=0 and event like＇library cache pin％＇；

(２)．通过查询ＤＢＡ＿ＬＯＣＫ＿ＩＮＴＥＲＮＡＬ和Ｖ$SESSION_WAIT,可得到与“library cache pin”等待相关的object的名字,其SQL语句如下:

sql>select to_char(SESSION_ID,’999’)sid,

substr(LOCK_TYPE,1,30)TYPE,

substr(lock_id1,1,23)Object_Name,

substr(mode_held,1,4)HELD,substr(mode_requested1,4)REQ,

lock_id2 Lock_addr

from dba_lock_internal

where

mode_requested<>’None’

and mode_requested<>mode_held

and session_id in (select sid from v$session_wait where wait_time=0

and event like ‘library cache pin%’);

(3).查出“library cache pin”占有者(即阻塞者)的session id,其SQL语句如下;

sql>select sid Holder, KGLPNUSE Sesion, KGLPNMOD Held, KGLPNREQ Req

from x$kglpn ,v$session

where KGLPNHDL in (select p1raw from v$session_wait

where wait_time=0 and event like ‘library cache pin%’)

and KGLPNOD<>0

and v$session,saddr=x$kglpn.kglpnuse;

(4).查出“library cache pin”占有者(阻塞者)正在等什么?

sql>select sid,substr(event,1,30),wait_time

from v$session_wait

where sid in (select sid from x$kglpn,v$session

where KGLPNHDL in (select p1raw from v$session_wait

where wait_time=0 and event like ‘library cache pin%’)

and KGLPNMOD<>0

and v$session.saddr=x$kglpn.kgpnuse)

;

(5).查出阻塞者正执行的SQL语句:

sql>select sid,sql_text

from v$session,v$sqlarea

where v$session.sql_address=v$sqlarea.address

and sid=;

这样,就可找到“library cache pin”等待的根源,从而解决由此引起的性能问题

另外,也可通过system dump 来分析“library cache pin”等待举例如下:

系统状态:

9 : waiting for ‘library cache pin’ [PIN:handle=EB3EB8]

154: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

341: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

341: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

390: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

160: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

20 : waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

阻塞者(Blockers):

~~~~~~~----------

PIN:handle=EB3EB8 263:last wait for ‘client message’

Object名字:

~~~~~~~~~

PIN: handle=EB3EB8   CRSR: select count(*)from fa_facturas@decmovi

Where fa_cuenta

= : 1

其他解决方法

当重新编译某个package时,应确认当前没有session在使用此package中的object.当我们重新编译某个package时,如果有session正在执行此package中的procedure或function,那么编译就会挂起.因为编译或分析(parse)package 或procedure或 function或 view时，Oracle需要先取得“library cache lock”和“library cache pin”以保证在编译或分析(parse)期间没有session正使用此object-因为我们正修改此object的定义并需要删除后用新的定义来重建此object.

我们可以用以下procedure来检查是否有session正在使用某个package,如果此procedure有结果返回，则此时不能编译此package.

sql>Connect SYS

password:

sql> set serveroutput on

sql> create or replace procedure who_is _using (obj_name varchar2) is

begin

dbms _output.enable(1000000);

for i in (SELECT distinct b.username,b.sid

FROM SYS.x$kglpn a,v$session b, SYS.x$kglob c

WHERE a.KGLPNUSE=b.saddr and upper(c.KGLNAOBJ)like upper(OBJ_NAME)and a. KGLPNHDL =c.KGLHDADR)loop dbms_output.put_line(‘(‘||to_char(i.sid)||’)’||i.username);

end loop;

end;

sql>execute who_is_using(‘my_package%’);

(14)-SCOTT  #即scott用户正在执行my_package

以下查询可得到正使用此package的session是否正占着“library cache pin”锁或正等待“library cache pin”锁：

sql>SELECT a. KGLPNMOD, a.KGLPNREQ,b.username,c,KGLNAOBJ,

c.KGLOBTYP

FROM

x$kglpn a,

v$session b,

x$kglob c

WHERE

a. KGLPNUSE=b.saddr and

upper(c.KGLNAOBJ) like upper(‘%my_package%’)

and

a.       KGLPNHDL =c.KGLHDADR;

与library cache pin 相关参数

在OPS8.1.5，8.1.6和8.1.7.0中，一定要在initsid.ora文件中设置

_SQLEXEC_PRLGRESSION_COST=0以避免因session等待“library cache pin”而造成的数据库性能严重下降或数据库挂起。

结束语

以上介绍了“library cache pin”的定义，产生原因及对数据库性能的影响，给出了详细的分析步骤和解决方法，本文适合较有经验的数据库管理员阅读，本文中给出的SQL语句请先测试后才能使用。