“library cache lock” 与 “library cache pin” 等待事件

ALLSTARS_ORACLE

Oracle使用两种数据结构来进行shared pool的并发控制:Lock和Pin ,Lock比pin具有更高的级别.Lock在handle上获得,在Pin一个对象之前,必须首先获得该handle的Lock。
Lock主要有三种模式:Null,Share,Exclusive
在读取访问对象时,通常需要获取Null(空)模式以及Share(共享)模式的Lock
在修改对象时,需要获得Exclusive(排他)Lock
在Lock了Library Cache的对象以后,一个进程在访问之前必须Pin该对象
同样Pin有三种模式,Null,Shared和Exclusive
只读模式时获得共享Pin
修改模式获得排他Pin
一个实例中的Library Cache包括了不同类型对象的描述,如:游标,索引,表,视图,过程,等等.这些对象不能在他们被使用的时候改变,他们在被使用的时候会被一种library locks and pins的机制锁住.
一个会话中,需要使用一个对象,会在该对象上先得到一个library lock(null, shared or exclusive模式的),这是为了防止其他会话也访问这个对象。
locks/pins会在SQL语句期间一直保持,在结束的时候才释放每个被SQL语句使用/修改的对象会一直保持locked或pinned,其他的请求会等待'library cache pin'或'library cache lock'事件,直到超时.

可能发生library cache pin和library cache lock的情况的原因主要有以下3种：
1、在存储过程或者函数正在运行时被编译。
2、在存储过程或者函数正在运行时被对它们进行授权、或者移除权限等操作。
3、对某个表执行DDL期间，有另外的会话对该表执行DML或者DDL。
找出  Waiting a和 Holding会话.

select /*+ ordered */ h1.sid holding_session,
w1.sid waiting_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
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
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((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
/



找出Holding会话的详细信息（主要取出 serial#值）.
select ses.sid, ses.serial#,ses.program,sq.sql_text  from x$kgllk lck , v$session ses,v$sql sq
where kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0)
and lck.KGLLKUSE = ses.saddr
and ses.sql_hash_value=sq.hash_value and kgllkpNC<>'00'
Order by lck.KGLNAOBJ
/



我们通过“存储过程或者函数正在运行时被编译”可以模拟出的‘library cache lock’ 和 ‘library cache ping’来验证上面的脚本，由于生产系统环境比较复杂，目前在bcv环境尚无法完全模拟出故障。

                        
1.）首先创建测试用的存储过程
sqlplus "/as sysdba"


Create or replace procedure dummy is
begin
null;
end;
/
2.）开启 Session 1 on instance1: 执行存储过程Dummy;

select distinct sid from v$mystat;


Begin
Dummy;
Dbms_lock.sleep(1000);
End;
/

3.）开启 Session 2 ， 编译存储过程dummy .
select distinct sid from v$mystat;

alter procedure dummy compile;

=> session 2  将会被 'library cache pin' 堵塞。


4.）开启 Session 3， 再次编译存储过程dummy .

select distinct sid from v$mystat;

alter procedure dummy compile;


=> session 3 将会被 'library cache lock'堵塞。