一、概述一个实例中的library cache包括了不同类型对象的描述,如:游标,索引,表,视图,过程,等等.这些对象不能在他们被使用的时候改变,他们在被使用的时候会被一种library locks and pins的机制锁住.一个会话中,需要使用一个对象,会在该对象上先得到一个library lock(null, shared or exclusive模式的)这是为了,防止其他会话也访问这个对象(例如:重编译一个包或视图的时候,会加上exclusive类型的锁)或更改对象的定义.
总的来说，library cache pin和library cache lock都是用于share pool的并发控制的。pin和lock都可以看作是一种锁。locks/pins会在SQL语句执行期间一直保持,在结束的时候才释放。
每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件'library cache pin'或'library cache lock'直到超时.超时,通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。
二、library cache pin和library cache lock成因lock主要有三种模式: Null,share(2),Exclusive(3).在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.在修改对象时,需要获得Exclusive(排他)锁定.
同样pin有三种模式,Null,shared(2)和exclusive(3).只读模式时获得共享pin,修改模式获得排他pin.
模式为shared(2)的pin会阻塞任何exclusive(3)的pin请求。模式为shared(3)的pin也会阻塞任何exclusive(2)的pin请求。
不同的操作会对对象请求不同的lock/pin1、所有的DDL都会对被处理的对象请求排他类型的lock和pin2、
当要对一个过程或者函数进行编译时，需要在library cache中pin该对象。在pin该对象以前，需要获得该对象handle的锁定，如果获取失败，就会产生library cache lock等待。如果成功获取handle的lock，则继续在library cache中pin该对象，如果pin对象失败，则会产生library cache pin等待。如果是存储过程或者函数，可以这样认为：如果存在library cache lock等待，则一定存在library cache pin等待；反过来，如果存在library cache pin等待，不一定会存在library cache lock等待；但如果是表引起的，则一般只有library cache lock等待，则不一定存在library cache pin。
可能发生library cache pin和library cache lock的情况：1、在存储过程或者函数正在运行时被编译。2、在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。3、对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。4、PL/SQL对象之间存在复杂的依赖性
每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件'library cache pin'或'library cache lock'直到超时.超时,通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误。
例如：SES1:执行：exec p_sleep;假设存储过程p正在运行，且运行时间很长SES2:执行：grant execute on p_sleep to system对p进行编译，如果之前没有其他会话lock存储过程p的handle，则本会话会将获取p的handle锁定；但会话pin p时会失败，此时在SES2上产生library cache pin等待。如果超过5分钟仍然不能完成pin p，则会报错：ORA-04021: 等待锁定对象 SUK.P_SLEEP 时发生超时。此时，本会话会释放p的handle lock。(也可能是ORA-04020错误)SES3:执行：grant execute on p_sleep to system在这个会话中继续编译p，则该会话在获取p的handle锁定时会失败，在本会话产生library cache lock等待。如果SES2超时，则本会话会获取p的handle lock，v$session_wait上的等待事件也由library cache lock变成ibrary cache pin，直到超时。
library cache pin查询v$session_wait视图中library cache pin对应的P1、P2、P3P1 = Handle address这个就是引起library cache pin等待的对象被pin到library cache中的handle。一般用P1RAW(十六进制)代替p1(十进制)可以用以下sql查询那个用户下的那个对象正在被请求pin：SELECT kglnaown "Owner", kglnaobj "Object"FROM x$kglobWHERE kglhdadr='&P1RAW'; 返回的OBJECT可能是具体的对象，也可能是一段SQL。
P2 = Pin address自身的pin地址。一般用P2RAW(十六进制)代替P2(十进制)P3 = Encoded Mode & Namespace
在library cache pin和library cache lock(一)我们简单介绍了介绍library cache pin和library cache lock的成因，下面介绍如何解决library cache pin和library cache lock等待。
三、解决方法
有两种方法可以查询library cache pin的相关信息，推荐使用第二种。
使用这种方法前，有必要先了解以下表或视图：x$kglob、x$kgllk、x$kglpn、DBA_KGLLOCK1) x$kglob该基表主要是library cache object的相关信息。X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject引用该基表的视图有﹕GV$ACCESS、GV$OBJECT_DEPENDENCY、GV$DB_OBJECT_CACHE、GV$DB_PIPES、DBA_LOCK_INTERNAL﹑DBA_DDL_LOCKS2) x$kgllk该基表保存了library cache中对象的锁信息，主要用于解决library cache lock。其名称含义是：[K]ernel Layer [G]eneric Layer [L]ibrary Cache Manager ( defined and mapped from kqlf ) Object Locks X$KGLLK - Object [L]oc[K]s 引用该基表的视图有﹕DBA_DDL_LOCKS ﹑DBA_KGLLOCK ﹑GV$ACCESS ﹑GV$OPEN_CURSOR SQL> desc x$kgllk;名称 类型---------- -----------ADDR RAW(4)INDX NUMBERINST_ID NUMBERKGLLKADR RAW(4)KGLLKUSE RAW(4) ---会话地址(对应v$session的saddr)KGLLKSES RAW(4) ---owner地址KGLLKSNM NUMBER ---SIDKGLLKHDL RAW(4) ---句柄KGLLKPNC RAW(4) ---the address of the call pinKGLLKPNS RAW(4) ---对应跟踪文件中的session pin值 KGLLKCNT NUMBER KGLLKMOD NUMBER ---持有锁的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive) KGLLKREQ NUMBER ---请求锁的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive) KGLLKFLG NUMBER ---cursor的状态﹐8(10g前)或2048(10g)表示这个sql正在运行﹐KGLLKSPN NUMBER ---对应跟踪文件的savepoint的值KGLLKHTB RAW(4)KGLNAHSH NUMBER ---sql的hash值(对应v$session的sql_hash_value)KGLLKSQLID VARCHAR2(13) ---sql ID，sql标识符KGLHDPAR RAW(4) ---sql地址(对应v$session的sql_address) KGLHDNSP NUMBERUSER_NAME VARCHAR2(30) ---会话的用戶名KGLNAOBJ VARCHAR2(60) ---对象名称或者已分析并打开cursor的sql的前60个字符3) x$kglpnX$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s 它是与x$kgllk相对应的表﹐是关于pin的相关信息。它主要用于解决library cache pin引用该表的视图有﹕DBA_KGLLOCK
SQL> desc x$kglpn;名称 类型------------ ----------------------------ADDR RAW(4)INDX NUMBERINST_ID NUMBERKGLPNADR RAW(4)KGLPNUSE RAW(4) ---会话地址(对应v$session的saddr)KGLPNSES RAW(4) ---owner地址KGLPNHDL RAW(4) ---句柄KGLPNLCK RAW(4)KGLPNCNT NUMBERKGLPNMOD NUMBER ---持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive) KGLPNREQ NUMBER ---请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)KGLPNDMK NUMBERKGLPNSPN NUMBER ---对应跟踪文件的savepoint的值4) DBA_KGLLOCKDBA_KGLLOCK是一个视图，它联合了x$kgllk和x$kglpn的部分信息。 通过查询，我们可以知道DBA_KGLLOCK视图的构建语句：SQL> SELECT * FROM DBA_VIEWS WHERE VIEW_NAME='DBA_KGLLOCK';select kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk union all select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype from x$kglpn了解了用到的几个主要视图或表的结构，我们就可以写出编写查询来查看相关信息：方法一、只能查询library cache pin相关信息
SQL> SELECT distinct decode(kglpnreq,0,'holding_session: '||s.sid,'waiting_session: '||s.sid) sid,2 s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",a.sql_text,kglnaown "Owner", kglnaobj "Object"3 FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x4 WHERE p.kglpnuse=s.saddr5 AND kglpnhdl=sw.p1raw6 and kglhdadr=sw.p1raw7 and event like 'library cache%'8 and (a.hash_value, a.address) IN (9 select10 DECODE (sql_hash_value,11 0,12 prev_hash_value,13 sql_hash_value14 ),15 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)16 from v$session s217 where s2.sid=s.sid18 )19 ;SID SERIAL# Pin Mode Req Pin SQL_TEXT Owner Object-------------------- ---------- ---------- ---------- ---------------------------------------- ------------ --------------------blocked_sid: 16 195 0 3 grant execute on p_s SUK P_SLEEPblocker_sid: 20 15 2 0 begin p_sleep; end; SUK P_SLEEP得到这个结果后，你可以根据实际情况kill掉阻塞的会话或者被阻塞的会话。
方法二、可以查询library cache pin和library cache lock 的信息
select Distinct /*+ ordered */ w1.sid waiting_session, h1.sid holding_session, w.kgllktype lock_or_pin, od.to_owner object_owner, od.to_name object_name, oc.Type, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held, decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested,xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xhwhere (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr and h.kgllkuse = h1.saddr And od.to_address = w.kgllkhdl And od.to_name=oc.Name And od.to_owner=oc.owner And w1.sid=xw.KGLLKSNMAnd h1.sid=xh.KGLLKSNM And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) ;
WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN OBJECT_OWNER OBJECT_NAME TYPE MODE_HELD MODE_REQUESTED WAIT_SQL HOLD_SQL--------------- --------------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------- --------- -------------- ------------------------------------------------------------ ------------------------------------------------------------18 19 Lock SUK P_SLEEP PROCEDURE Exclusive Exclusive grant execute on p_sleep to system grant execute on p_sleep to system19 12 Pin SUK P_SLEEP PROCEDURE Share Exclusive grant execute on p_sleep to system begin p_sleep; end;23 25 Lock SUK P_SLEEP2 PROCEDURE Exclusive Exclusive grant execute on p_sleep2 to system grant execute on p_sleep2 to system25 14 Pin SUK P_SLEEP2 PROCEDURE Share Exclusive grant execute on p_sleep2 to system begin p_sleep2; end;为了避免这种情况，可以在编译过程或函数等对象时，先查看一下是否有会话正在使用该对象，查询语句如下：SELECT Distinct sid using_sid, s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",kglnaown "Owner", kglnaobj "using_Object"FROM x$kglpn p, v$session s,x$kglob x WHERE p.kglpnuse=s.saddr AND kglpnhdl=kglhdadr And p.KGLPNUSE = s.saddr And kglpnreq=0 And upper(kglnaobj) = upper('&obj'); 如果有结果返回，则等待这些对话的操作执行完毕再重新编译，也可以把这些会话kill。
四、其他解决方法上面主要是用到SQL查询相关信息的解决方法，也可以使用10046、trace等方法实现。至于用那种方法好，见仁见智。
