LIBRARY CACHE PIN

 

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$是fixed table
x$kglpn  library cache pin信息
x$kglob library cache object信息

关联:
V$SESSION_WAIT中的P1与X$KGLPN中的KGLPNHDL相关连
V$SESSION_WAIT中的P1与X$KGLOB中的KGLHDADR相关连
select p1raw from v$session_wait where event like '%library cache pin%';
select * from x$kglpn cpin,X$KGLOB obj where cpin.kglpnhdl = obj.KGLHDADR;
select * from v$session_wait s,X$KGLOB obj where p1raw=obj.KGLHDADR;

 

SELECT s.sid, kglpnmod "Mode", kglpnreq Req, SPID "OS Process"
  FROM v$session_wait w, x$kglpn p, v$session s, v$process o
 WHERE p.kglpnuse = s.saddr
   AND kglpnhdl = w.p1raw
   and w.event like '%library cache pin%'
   and s.paddr = o.addr;
/*其中kglpnmod 当前pin mode, kglpnreq Req发出?类的ping Request

KGLM0 0 nolock/pinheld
KGLMN 1 nullmode
KGLMS 2 sharemode
KGLMX 3 exclusivemode*/

 

 

SID Mode Req OS Process

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

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

 

20 rows selected

 

       我那位run存储过程的同事所在的session是396，从上述结果里我们可以看出来396现在想以Share模式（即Req=2）去持有library cache pin，同时现在持有上述library cache pin的是session 341，且341的持有模式也是Share（即Mode=2）。

       本来Share和Share是可以共享的，但不幸的是在396之前，session 354想以Exclusive模式（即Req=3）去持有上述librarycache pin，这直接导致了396需要处于等待的Queue中，同时处于Queue中的还有363和304。

 

我为什么这么说呢，因为oracle对library cache pin的解释中有一句非常经典的话：

An X request (3) will be blocked by anypins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

 

所以从AWR报告和上述查询结果中我们可以得出如下结论：

       1、 我那位run存储过程的同事为什么run了1个多小时还没有run完是因为这个存储过程正在经历严重的library cache pin等待；

       2、 而为什么会导致严重的library cache pin等待是因为session 341和354联手达到了这一效果，即341以Share模式持有library cache pin，接着354想以Exclusive模式持有，这直接导致所有的后续请求全部被处于等待的Queue中。也就是说341阻塞了354，而354又间接阻塞了396。

 
既然知道了原因，那我们去看一下session 341在做什么事情：
SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;
DECODE(SQL_HASH_VALUE,0,PREV_H
------------------------------
784727971
 
间隔10秒钟后再次执行：--间隔10秒查询的目的是判断session 的内容是否改变，从而判断出session 在干什么
SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;
DECODE(SQL_HASH_VALUE,0,PREV_H
------------------------------
784727971
 
间隔10秒钟后再次执行：
SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;
DECODE(SQL_HASH_VALUE,0,PREV_H
------------------------------
784727971
 
SQL> select sql_text from v$sqltextwhere hash_value=784727971 order by piece;
SQL_TEXT
----------------------------------------------------------------
begin -- Call the procedurep_adj_rrp_main(o_vc_flag => :o_vc_flag); end;

 

 

查询方法一

--通过查询V$SESSION_WAIT找出正在等待”library cache pin”的session

SELECTsid,

       SUBSTR (event, 1, 30),

       TO_CHAR(p1,'xxxxxxxx') p1_16,

       --P1RAW P1_16,

       p2,

       p3

  FROMv$session_wait

 WHEREwait_time = 0 ANDeventLIKE'library cache pin%';

--P1 列是Library Cache Handle Address

--P2 列是Library Cache Pin Address.

 

--找到相关session pin状态

SELECTADDR,

       INDX,

       KGLPNADR,-- Library Cache Pin Address

       KGLPNUSE,

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

       KGLPNHDL,--Library Cache Handle Address

       kGLPNLCK,

       KGLPNMOD,-- Pin 锁

       KGLPNREQ-- Pin 请求

  FROMx$kglpn

 WHEREKGLPNHDLLIKE'%EB3EB8%';--p1_16

 

 --询X$KGLOB (Library Cache Object),可找到相关的object

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

  FROMX$KGLOB

 WHEREKGLHDADRLIKE'%EB3EB8%';--p1_16

 

 --查出占着pin锁的session目前正在做什么

SELECTa.sid, a.username, a.program

  FROMv$session a, x$kglpn b

 WHEREa.saddr = b.kglpnuse ANDb.kglpnhdlLIKE'%EB3EB8%'--p1_16

  ANDb.kgnmod <> 0;

 

   --查出阻塞者正执行的SQL语句

 SELECTsid, sql_text

  FROMv$session, v$sqlarea

 WHEREv$session.sql_address = v$sqlarea.address ANDsid =&sid;

 

 查询方法二

 

--通过查询DBA_LOCK_INTERNAL和V$SESSION_WAIT,可得到与”library cache pin” 等待相关的object的名字

SELECTTO_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_requested, 1, 4) REQ,

       lock_id2 Lock_addr

  FROMdba_lock_internal

 WHEREmode_requested <> 'None'ANDmode_requested <> mode_held

       ANDsession_idIN

              (SELECTsid

                 FROMv$session_wait

                WHEREwait_time = 0 ANDeventLIKE'library cache pin%');

 

 --查出”library cache pin”占有者(即阻塞者)的session id

 SELECTsid Holder,

       KGLPNUSE Sesion,

       KGLPNMOD Held,

       KGLPNREQ Req

  FROMsys.x$kglpn, v$session

 WHEREKGLPNHDLIN(SELECTp1raw

                      FROMv$session_wait

                     WHEREwait_time = 0 ANDeventLIKE'library cache pin%')

       ANDKGLPNMOD <> 0

       ANDv$session.saddr = x$kglpn.kglpnuse;

 

 --查出”library cache pin”占有者(阻塞者)正在等什么

 SELECTsid, SUBSTR (event, 1, 30), wait_time

  FROMv$session_wait

 WHEREsidIN

          (SELECTsid

             FROMx$kglpn, v$session

            WHEREKGLPNHDLIN

                     (SELECTp1raw

                        FROMv$session_wait

                       WHEREwait_time = 0

                             ANDeventLIKE'library cache pin%')

                  ANDKGLPNMOD <> 0

                  ANDv$session.saddr = x$kglpn.kglpnuse);

 

 --查出阻塞者正执行的SQL语句

 SELECTsid, sql_text

  FROMv$session, v$sqlarea

 WHEREv$session.sql_address = v$sqlarea.address ANDsid =&sid;

 

 

 

 常见的原因及解决方法
     
          “LIBRARY CACHE PIN”通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,如安装应用程序,升级,安装补丁程序等,但object的重新编译也可能发生在object变得无效时.

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

 

         有一次我同事在空调的时候调用一个过程,而一调用就马上出现LIBRARY CACHE PIN,RAC双机环境,后面用上面的SQL根本查不到有用的信息,其实该被调用过程无效!!!将执行过程停止,并将失效过程编译过去,就正常了.

          当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进行修改,授权,收回授权时必须非常小心.实际上,解决这些问题大多要依靠应用程序的开发和维护,应用程序开发商应该考虑到某些方案的决策可能会给应用程序的伸缩性和性能带来负面影响.