library cache pin/library cache lock案例一则

案例：每月最忙的莫过于月报流程了，特别是在分布式系统中，不同数据库间的操作，资源抢占严重，效率低下。所幸这几个月的调优还是有些收效的^_^。看看这月初遇到的问题吧：话说A库还是很强大的吧，每次都能够抵抗住我无情的摧残，所以这次我是抱着摧残的想法一下压了几十个月报的进程上去的，很普遍的，这样的情况容易产生library cache lock/library cache pin。什么是library cache lock/library cache pin呢？Library cache lock是用来控制对librarycache object的并发访问的。Lock管理并发，pin管理一致性，lock是针对于librarycache handle, 而pin是针对于heap。一般情况下产生pin是因为创建，编译对象等，而在获取pin住某个对象之前需要先获取这个对象的heap中的lock，pin住对象可以保证该对象不会被更改或者删除。也可以认为pin就是一种lock，lock有三种方式null share exclusive，当然pin也有这三种方式。

其中share（S）是读取某个对象时获得的，exclusive（X）是创建或者修改某个library cache object时获得的 null仅仅是用来确保对象的依赖性的，如某个对象改变时用来通知依赖的所有其他对象。比如某个sql中所引用的某个表被修改之后那么null锁被打破，在下次执行该sql时就需要重新编译。

在这次统计流程中就出现了大量的library cache lock，只要是执行B过程的所有会话都在等待，查询等待事件：

select * from v$session_wait a where a.sid = ...;

全部是library cache lock

实际上可以用sys用户查询数据字典基表：x$kglpn来查询什么会话阻塞了产生library cache pin（或者library cache lock）的会话：

SELECT a.sid,
       a.serial#,
       a.username,
       a.status,
       a.osuser,
       a.machine,
       a.program,
       b.addr
FROM   v$session a, x$kglpn b
WHERE  a.saddr = b.kglpnuse
AND    b.kglpnmod <> 0
AND    b.kglpnhdl IN
       (SELECT p1raw FROM v$session_wait WHERE event LIKE '%library cache%');

前面已经提到，当某个过程中依赖的对象发生改变的时候，oracle会自动重新编译该对象，这次产生的大量library cache lock很有可能就是因为这个导致的，而在编译的时候该过程正在执行，导致产生了library cache pin，而多个会话同时执行的时候都检测到了该过程的变化，都想要重新编译该过程（在10g不会再出现这种情况，oracle只允许第一个执行编译操作），这就需要获取该过程的lock，但是创建或者修改对象需要获得的是exclusive锁，这又产生了library cache lock等待。所有的过程都僵死在那里了。

使用上面的sql可以查出具体是哪些会话。

检查等待的对象

SELECT kglnaown, kglnaobj, kglnadlk, sw.sid
FROM   x$kglob t, v$session_wait sw
WHERE  t.kglhdadr = sw.p1raw
AND    sw.sid IN (SELECT sid
                  FROM   v$session a, x$kglpn b
                  WHERE  a.saddr = b.kglpnuse
                  AND    b.kglpnmod <> 0
                  AND    b.kglpnhdl IN
                         (SELECT p1raw
                           FROM   v$session_wait
                           WHERE  event LIKE 'library cache%'));

查询是哪个会话在访问该对象：

select * from v$access where object='P_TEST';

查询对象的信息：

select object_name,object_type,owner from dba_objects where object_name='P_TEST' and owner='xx';

很有可能该对象已经失效，并且有某个过程正在编译该对象：

SELECT lo.*, do.object_name
FROM   v$locked_object lo, dba_objects do
WHERE  do.object_name = 'P_TEST'
AND    lo.object_id = do.object_id;

当然还可以使用10046事件来分析，这就需要能够读懂跟踪文件中的信息了。

下次遇到不同的案例将会再分享。