常用定位library cache lock的方法

常用定位library cache lock的方法

经常看到PUB上有兄弟说什么LIBRARY CACHE LOCK和PIN的错误不知道该如何处理，而且定位不到问题出在哪里,我来说几句吧,以我工作上的经验，希望能对大家有用
   一般来说,这类错误是因为在包或过程被频繁调用的过程中，DDL语句引起的，那我们该怎么处理呢?其实我们可以这样来操作来查原因，老板要的一般都是为什么产生这个故障和谁操作导致的这个故障，特别是故障处理好后，这个问题就要回答老板了。

1、预先在数据库中建立DDL级的触发器,我认为这个是必要的,因为这个对生产影响不大，但是却可以让我们监控到不少有用的信息.，比如记录在abc表中，可以记录登陆用户，操作语句，操作时间等等信息。

  2、在数据库中出现大量的libriary cache lock 的等待事件的时候，系统出现严重的问题了，我们可以立即从这个时间点左右着手，比如12日21日中午12点到12点半之间出问题，如下语句
select * from dba_objects where
last_ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND last_ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and (object_type like '%PACK%' or object_type like 'FUNCTION' OR object_type='PROCEDURE')
AND STATUS='INVALID'
order by last_ddl_time desc

其实通过这个基本上就发现是什么问题了，基本上就只会有一两个对象比如包BBB失效

3、然后找包关联的对象，是否在我们的触发器记录的表中有记录，接着执行如下语句（切记，这个记录DDL动作的语句发挥作用了）

select * from abc where ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and schema_object in (SELECT referenced_name FROM DBA_DEPENDENCIES WHERE NAME='BBB' )
ORDER BY DDL_time desc
（请注意，这个BBB就是上面我查出来的，举例说比如失效的包）

这样查出来的，绝对就是引起这次事故的罪魁祸首的动作了。（ddl_time和 schema_object 是abc表的字段，记录了登陆者操作DDL的时间和对象）


以上方式是我在工作中经常采用的，很好用，一般不会有问题。


当然我上面并没有说明解决问题的方法，解决问题的方法是如下。但是有的时候发现问题原因，追究问题原因是非常非常重要的，可以避免下次再发生，当然通过DUMP systemstate等方式，比较复杂，我的这个思路操作起来应该比较简便，很明了。另外，建立DDL级的触发器，个人认为是必须的！所以上面的方法我想说出来，希望对大家有用！


解决问题的方法步骤

  1、查看具体产生library cache lock 的对象，比如不哪些包和存储过程

SELECT KGLNAOWN,KGLNAOBJ
    FROM x$kglob
   WHERE kglhdadr in( select P1RAW from v$session_wait where event like 'library cache%');


2、  查看具体是那些用户做了这个操作导致 library cache lock
  select sid, program ,machine from v$session where paddr in (
       SELECT s.paddr
        FROM x$kglpn p, v$session s
       WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0
   and kglpnhdl in ( select p1raw  from v$session_wait where event  in ('library cache pin','library cache lock' ,'library cache load lock')  )        );


3、、以下语句用来杀掉会话（前面查看，然后到这步是决定是否要杀掉进程解决这个问题）
select 'kill -9 '||spid from v$process where addr in (
       SELECT s.paddr
        FROM x$kglpn p, v$session s
       WHERE p.kglpnuse=s.saddr --AND p.kglpnmod <> 0
   and kglpnhdl in ( select p1raw  from v$session_wait where event  in (' library cache pin','library cache lock' )  )        );



看过我这帖子的兄弟希望能补充补充，看看有没有说的不对的地方，或者说考虑不全面的地方，谢谢！


附：DDL触发器的语句

CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
  SQL_TEXT   ORA_NAME_LIST_T;
  STATE_SQL  VARCHAR2(4000); --DDL$TRACE.DDL_SQL%TYPE;
  V_ERR_INFO VARCHAR2(200);
BEGIN
  FOR I IN 1 .. ORA_SQL_TXT(SQL_TEXT) LOOP
    STATE_SQL := STATE_SQL || SQL_TEXT(I);
  END LOOP;

  INSERT INTO SYSTEM.ABC
    (LOGIN_USER,
     AUDSID,
     IPADDRESS,
     SCHEMA_USER,
     SCHEMA_OBJECT,
     DDL_TIME,
     DDL_SQL)
  VALUES
    (ORA_LOGIN_USER,
     USERENV('SESSIONID'),
     SYS_CONTEXT('userenv', 'ip_address'),
     ORA_DICT_OBJ_OWNER,
     ORA_DICT_OBJ_NAME,
     SYSDATE,
     STATE_SQL);
EXCEPTION
  WHEN OTHERS THEN
    V_ERR_INFO := SUBSTRB(SQLERRM, 1, 198);
END TR_TRACE_DDL;

3、、以下语句用来杀掉会话（前面查看，然后到这步是决定是否要杀掉进程解决这个问题）
目的找出spid ,也可以用下面的sql:
SELECT spid
   FROM v$process p, v$session s
  WHERE p.addr = s.paddr
    AND s.SID IN (SELECT sid
                    FROM v$session_wait b
                   WHERE b.EVENT in (' library cache pin','library cache lock' )  )
前提是sesion 没有被 killed 掉, 如果被killed , v$session.paddr <> v$process.addr.