模擬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正是被它所阻塞。