模拟library cahe lock/pin等待事件以及问题定位

今天打算复习一下library cache lock/pin等待事件，于是模拟了以下的实验。

1.在u1用户下创建测试需要的存储过程
create or replace procedure p
as
begin
dbms_lock.sleep(10000);
end;
/

2.在A会话中执行该存储过程
exec p；

3.在B会话中重新编译存储过程
alter procedure p;

4.查询library cache pin等待事件的相关会话
select saddr,sid,username,event,p1raw from v$session where event='library cache pin';
SADDR           SID USERNAME   EVENT                          P1RAW
-------- ---------- ---------- ------------------------------ --------
33B35BE4        151 U1         library cache pin              2D6CF718

5.查询持有library cache pin的会话以及pin住的对象
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",kglnaown "Owner",kglnaobj "Object"        
      FROM x$kglpn p, v$session s,x$kglob o                                        
      WHERE p.kglpnuse=s.saddr
      AND p.kglpnhdl=o.kglhdadr and  p.kglpnhdl='2D6CF718'; 
       SID       Mode        Req Owner      Object       
---------- ---------- ---------- ---------- ----------       
       151          0          3 U1         P1            
       143          2          0 U1         P1          
从输出结果可以看到会话151被回话143阻塞，143会话以模式2 pin住对象p1。   
                                                                                        
6.在C会话中drop存储过程
drop procedure p;

7.查询查看library cache lock等待事件的相关会话     
select saddr,sid,username,event,p1raw from v$session where event='library cache lock';
SADDR           SID USERNAME   EVENT                          P1RAW                                        
-------- ---------- ---------- ------------------------------ --------                             
33B2422C        136 U1         library cache lock             2D6CF718     

8.查询持有library cache lock的会话以及lock住的对象
select user_name,kglnaobj "Owner",kgllkses saddr,kgllkreq req,kgllkmod mod,kglnaobj object
 from x$kgllk lock_a
 where kgllkmod > 0
 and exists (select lock_b.kgllkhdl from x$kgllk lock_b
 where kgllkses = '33B2422C' /* blocked session */
 and lock_a.kgllkhdl = lock_b.kgllkhdl
 and kgllkreq > 0);
USER_NAME  Owner      SADDR           REQ        MOD OBJECT          
  ---------- ---------- -------- ---------- ---------- ----------
U1         P1         33B35BE4          0          3 P1   
U1         P1         33B2C5A4          0          1 P1   
这里出现了两行结果，不过从mod列可以判断33B2C5A4这个会话持有的lock模式为1(如果没记错的话数字1表示null)，所以正在阻塞136会话的是会话地址为33B35BE4的会话。

你也可以通过以下sql做进一步验证
select sid,saddr,event,q.sql_text from v$session s,v$sql q
 where saddr in ('33B35BE4','33B2C5A4') and s.sql_id=q.sql_id;
       SID SADDR    EVENT                          SQL_TEXT                       
   ---------- -------- ------------------------------ ----------------------------------------       
 143 33B2C5A4 PL/SQL lock timer              BEGIN p1; END;
 151 33B35BE4 library cache pin              alter procedure p1 compile    
从输出结果发现会话地址为33B35BE4的会话正在编译p1,所以该会话持有的lock模式肯定会x，而会话136正是被它所阻塞。

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

转载于:http://blog.itpub.net/20801486/viewspace-719143/