LIBRARY CACHE PIN解决方法

lovetaorush

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

一、以下列出几种可能产生”library cache pin”的情况及其避免方法:
1. 用户权限管理
当对用户的权限进行管理即进行”grant” “revoke”时,可能产生”library cache pin”.
建议的避免方法: 通过角色来对最终用户进行授权或收回授权,而不要用显性的方式即直接对最终用户授权或收回授权,从而避免产生”library cache pin”
2. 高峰时的object管理
在系统运行高峰时对数据库object的管理可能产生”library cache pin”.
建议的避免方法: 把对数据库object的管理安排到负载相对较小的时侯.
3. 在PL/SQL包中存在大量的互跨的依赖性(dependency)
建议的避免方法: 尽可能按等级来排列它们的结构.

二、解决这个问题基本思路如下：
1. v$session_wait.p1raw 可以知道library cache pin等待的对象的handle的地址
2. 通过等待对象的handle可以和x$kglob.kglhdadr相关联，可以了解这个等待对象的基本信息，那这个对象的锁（pin）的情况是如何？
3. 再用x$kglob.kglhdadr关联x$kglpn.kglpnhdl，知道谁在持有这个pin（锁），在用x$kglpn.kglpnuse和v$session.saddr关联,了解持有锁（pin）的会话信息。
    如果要了解这个会话在等待什么，可以通过v$session_wait知道，也可以通v$session.sql_hash_value和v$session.sql_address等字段
    关联v$sqltext,v$sqlarea视图了解这个会话的动作.

三、具体步骤：
    select sid,seq#,event,p1,p1raw,p1text,p2,p2raw,p2text,p3,p3raw,p3text,wait_time,seconds_in_wait,state
    from v$session_wait
    where event like 'library%';
SID       SEQ# EVENT                     P1             P1RAW            P1TEXT                 P2          P2RAW            P2TEXT         
---- ---------- --------------------------- ---------------- ---------------------------------------- ---------------- ------------------
23        481 library cache pin        6540622432  0000000185D9FA60    handle address          6482206672 00000001825E9FD0  pin address   

    P3    P3RAW            P3TEXT                 WAIT_TIME SECONDS_IN_WAIT  STATE
    ----- ---------------------------------------- ---------- ---------------- -----------------
    301   000000000000012D 100*mode+namespace         0              32         WAITING

P1RAW='0000000185D9FA60'是会话等待的对象的handle的值，通过这个值可以在x$kglob里查看这个对象的基本信息，如下：

    select addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj
    from sys.x$kglob  
    where kglhdadr='0000000185D9FA60';
ADDR             KGLHDADR         KGLHDPAR         KGLNAOWN       KGLNAOBJ         KGLNAHSH     KGLHDOBJ
---------------- ---------------- ---------------- ----------------------------------------------------------------
0000002A965B38D0 0000000185D9FA60 0000000185D9FA60 SYS            PINNG            1626462215 00000001843E6F68

    select a.PADDR,a.sid,a.SERIAL#,a.PROGRAM,a.SQL_ADDRESS,a.STATUS,a.SQL_HASH_VALUE,b.addr,b.kglpnadr,b.kglpnuse,b.kglpnses,
                    b.kglpnhdl,b.kglpnlck,b.kglpnmod,b.kglpnreq
    from x$kglpn b, v$session a
    where a.SADDR = b.kglpnuse
    and b.kglpnhdl = '0000000185D9FA60'
    and b.kglpnmod <> 0;
PADDR               SID    SERIAL# PROGRAM            SQL_ADDRESS      STATUS   SQL_HASH_VALUE ADDR             KGLPNADR         
---------------- ---------- ---------- ------------------------------------------------ ---------------- -------- --------------
0000000180433138    33         18 plsqldev.exe       0000000185D8ACD0  ACTIVE       3937586639 0000002A965CF1D0 00000001825EA050

    KGLPNUSE         KGLPNSES         KGLPNHDL         KGLPNLCK           KGLPNMOD   KGLPNREQ
    ---------------- ---------------- ---------------- ---------------- ---------------- ------
    00000001804A8D50 00000001804A8D50 0000000185D9FA60 00000001825E8E98          2          0

查看持有该锁（pin）的会话的基本信息，到这个有如下几种分析情况：
    1. 字段status的值如果是“killed”，表明这个会话被关闭了，虽然会话被标记为“killed”但会话持有的资源可没有释放，这个时候要查看视图v$process,
查询会话对应os进程是否还存在。
    select * from v$process where addr in ('&paddr');  ---这里变量paddr等于‘0000000180433138’
    如果这个查询里有值，说明os进程没有释放资源，可以通过os命令：  kill -9 spid 杀掉进程（spid是v$process对应os的服务器进程）

    2. 字段status的值如果为“ACTIVE”，说明持有pin的会话是活跃的，那就看看这个会话在等待什么，在执行什么操作
    查看会话在等待什么？
    select * from v$session_wait  where sid=33
    查看会话的动作是什么？
    select * from v$sqlarea where hash_value='&sql_hash_value';

到此，library cache pin的原因已经找到剩下就是如何处理，一般快速处理是先kill掉看看是否可以解决，如果无法解决，那就看应用。