library cache pin和library cache lock的诊断分析

最近运行一个分批删除数据的procedure，感觉运行比较慢，临时取消了该job，然后再次重建procedure时，session hang住了，应该是library cache pin的等待事件了，oracle用两种结构pin和lock来控制shared pool的并发访问控制。

访问一个对象将其pin到内存中，在pin之前需要先获取该对象的handle的锁定，lock的索引主要有null share exclusive，当然pin该对象也有上述三种状态。

Session a ：execute delete_data

临时取消了该procedure的运行，实际进程并没有马上释放该对象的pin

Session b：

SQL> create or replace procedure delete_data

2 as

3 begin

4 loop

Delete from TEXTAUTO_FOLDERARTICLE where LASTPOSTDATE

Exit when sql%notfound;--dml语句中隐式游标属性进行控制

Commit;

End loop;

9 Commit;

10 end;

11 /

出现了library cache pin等待事件：

SQL> select event,count(*) from v$session group by event;

EVENT COUNT(*)

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

SQL*Net message from client 92

library cache pin 1

jobq slave wait 1

rdbms ipc message 10

smon timer 1

pmon timer 1

db file scattered read 1

Streams AQ: qmn slave idle wait 1

SQL*Net message to client 1

Streams AQ: qmn coordinator idle wait 1

Streams AQ: waiting for time management or cleanup tasks 1

11 rows selected.

分析一下上述的library cache pin是如何造成的：首先系统运行了该procedure，此时该对象已经被pin到内存，占用了一个share pin和一个null lock，而此时在进程还没有释放此pin之前再次create procedure，此时要获取一个exclusive pin和exclusive lock，由于之前的share pin和exclusive pin不共存，此时就会产生一个library cache pin等待。

这里注意一下两个基表x$kglpn和x$kgllk，对于解决library cache pin和lock太轻松了。

SQL> desc x$kglpn;

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

KGLPNADR RAW(8)

KGLPNUSE RAW(8)

KGLPNSES RAW(8)

KGLPNHDL RAW(8)

KGLPNLCK RAW(8)

KGLPNCNT NUMBER

KGLPNMOD NUMBER

KGLPNREQ NUMBER

KGLPNDMK NUMBER

KGLPNSPN NUMBER

通过等待事件的p1raw参数联合kglpnhdl来获取kglpnuse，这里的kglpnmod和kglpnreq和v$lock的lmode和request基本相同，kglpnmod=2表示此时这个session占有了library cache pin而造成了kglpnreq=3这个session的等待。

SQL> select kglpnuse,kglpnhdl,kglpnmod,kglpnreq from x$kglpn where kglpnhdl in (select p1raw from v$session where event='library cache pin');

KGLPNUSE KGLPNHDL KGLPNMOD KGLPNREQ

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

00000000D8227590 00000000DD876D98 0 3

00000000DA225340 00000000DD876D98 2 0

联合v$session的saddr来获取sid然后可以通过v$process来kill掉没有释放的进程。

SQL> select b.spid,a.sid from v$session a,v$process b where a.saddr in ('00000000DA225340') and a.paddr=b.addr;

SPID SID

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

11232 195

而如果此时再重新编译一下该procedure，同样需要获取一个该对象的lock锁定，由于上述重建procedure还在视图获取该对象的library cache lock的exclusive lock，那么当然此时出现library cache lock是当然的。

SQL> alter procedure delete_data compile;

SQL> select event,count(*) from v$session group by event;

EVENT COUNT(*)

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

SQL*Net message from client 91

library cache pin 1

jobq slave wait 1

rdbms ipc message 10

smon timer 1

pmon timer 1

db file scattered read 1

library cache lock 1

Streams AQ: qmn slave idle wait 1

SQL*Net message to client 1

Streams AQ: qmn coordinator idle wait 1

SQL> desc x$kgllk;

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

KGLLKADR RAW(8)

KGLLKUSE RAW(8)

KGLLKSES RAW(8)

KGLLKSNM NUMBER

KGLLKHDL RAW(8)

KGLLKPNC RAW(8)

KGLLKPNS RAW(8)

KGLLKCNT NUMBER

KGLLKMOD NUMBER

KGLLKREQ NUMBER

KGLLKFLG NUMBER

KGLLKSPN NUMBER

KGLLKHTB RAW(8)

KGLNAHSH NUMBER

KGLLKSQLID VARCHAR2(13)

KGLHDPAR RAW(8)

KGLHDNSP NUMBER

USER_NAME VARCHAR2(30)

KGLNAOBJ VARCHAR2(60)

获取blocking session的方法基本相同，也是通过p1raw参数联合kgllkhdl来获取kgllkuse，最后联合v$session来获取sid，进而通过v$process获取spid，杀掉进程后即可

SQL> select kgllkhdl,kgllkuse,kgllkreq,kgllkmod from x$kgllk where kgllkhdl in (Select p1raw from v$session where event='library cache lock');

KGLLKHDL KGLLKUSE KGLLKREQ KGLLKMOD

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

00000000DD876D98 00000000DA225340 0 1

00000000DD876D98 00000000D8227590 0 3

00000000DD876D98 00000000D821FC40 3 0

通过上述的library cache pin和library cache lock等待事件的分析和诊断，可以看出数据字典基表可以提供我们非常的信息，而帮助解决一些比较棘手的案例，当然上述我们也可以利用oradebug然后通过trace文件来诊断，不过相对trace文件较难以阅读，需要一定的功底，后续如果有对该等待事件trace的解决案例再拿出来与大家分享。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1059119/，如需转载，请注明出处，否则将追究法律责任。