library cache lock与library cache pin

/*
Author : Nico Wang
Date   : 2007-04-04
Purpose: 深刻理解library cache lock与library cache pin

*/

需要用到的几个脚本：
1. ShowBlocking.sql
/*
ShowBlocking.sql
找出会话之间的阻塞关系
*/
set linesize 200
col blocking_status format a15
col blocking_session format a15
col p1text format a15
col p1raw format a20
col p2text format a15
col event format a50

select sid, blocking_session_status as bstatus,
blocking_session as bsession,p1text,p1raw,p2text,event
from v$session
where lower(username)='&username'

2. ShowObj.sql
/*
根据ShowBlocking.sql得到的p1raw值，获取对象的名称.
*/
col owner format a8
col object format a70

select kglnaown as owner, kglnaobj as Objects
from x$kglob
where kglhdadr='&p1raw'
/

Session1:
           SID = 506
           
SQL> create or replace procedure sp_lock_test
  2  as
  3  begin
  4          null;
  5          dbms_lock.sleep(600);
  6  end sp_lock_test;
  7  /

Procedure created.

Elapsed: 00:00:00.04
SQL> begin
  2  sp_lock_test;
  3  end;
  4  /
......runing...
      
/*
如果sleep的时间设置的太长,修改对象时可能会报"ORA-04021"错误:
SQL> alter procedure sp_lock_test compile;
alter procedure sp_lock_test compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SCOTT.SP_LOCK_TEST
*/
       
Session2 :
                 SID = 501
                 
SQL>alter procedure sp_lock_test compile;

此时,session2 处于等待模式,这时,查看v$session_wait或v$session,可以看到session2 提交了
Library cache pin等待事件:

SQL> @e:\showblocking.sql
Enter value for username: scott
old   4: where lower(username)='&username'
new   4: where lower(username)='scott'

 SID BSTATUS        BSESSION P1TEXT          P1RAW                P2TEXT          EVENT
---- ------------ ---------- --------------- -------------------- --------------- ---------------------------
 501 VALID               506 handle address  384ED074             pin address     library cache pin
 506 NO HOLDER               duration        000186A0                             PL/SQL lock timer
 521 NO HOLDER               driver id       54435000             #bytes          SQL*Net message from client
 527 NO HOLDER               driver id       54435000             #bytes          SQL*Net message from client

SQL>

由上可以看到session1(sid=506)阻塞了session2(SID=501), session2所提交的事件是library cache pin.
此时在开一个session3,同样修改对象:
                 
Session 3 :
           SID = 521
           
SQL> alter procedure sp_lock_test compile;     

这时, session3 也要处于等待模式,查看 session3会提交什么事件呢:

SQL> @e:\showblocking.sql                                                                                                                           
Enter value for username: scott                                                                                                               
old   4: where lower(username)='&username'                                                                                                    
new   4: where lower(username)='scott'                                                                                                        
                                                                                                                                              
 SID BSTATUS       BSESSION P1TEXT          P1RAW       P2TEXT          EVENT                                       
---- ----------- ---------- --------------- ----------- --------------- -----------------------------
 501 VALID              506 handle address  384ED074    pin address     library cache pin            
 506 NO HOLDER              duration        000186A0                    PL/SQL lock timer            
 521 VALID              501 handle address  384ED074    lock address    library cache lock           
 527 NO HOLDER              driver id       54435000    #bytes          SQL*Net message from client  
                                                                                                                                              
SQL>
可以看到session3(sid=521)被session2(sid=501)所阻塞,session3 提交了library cache lock等待事件.

整个过程: 一个session要修改内存中的对象时,首先要获取library cache lock,再获取library cache pin,才能做修改!

          session1
             |
             |阻塞session2
            \|/
          session2
             |
             |阻塞session3
            \|/
          session3   

(1)Session1(sid=506): 以null的方式持有library cache lock,同时以Share的方式持有library cache pin;
(可以在trace文件中看到锁的模式(null,share,exclusive));

(2)session2(sid=501): 因为session1是以null的方式持有library cache lock,所以Session2可以以Exclusive的
方式持有library cache lock,但library cache pin被session1以Share的方式持有,所以session2不能以Exclusive
的方式获得library cache pin,故session2提交library cache pin等待事件!Session2被Session1所阻塞(可以从
v$session.blocking_session列观察到,在这里我用了列别名BSESSION);
                                                                                                        
(3)session3(sid=521): 因为session2是以Exclusive的方式持有了library cache lock,所以当session3以Exclusive
的方式请求library cache lock时,它被session2所阻塞,故session3提交library cache lock等待事件.


查看被锁住的资源:

1.查询等待library cache lock/pin的session
/*
ShowPinSession.sql
*/
col event format a20

select sid, event, p1raw, seconds_in_wait, wait_time
from $session_wait
where event = 'library cache pin'
and state = 'WAITING'
/

2.查询被锁住的对象及拥有者
/*
根据ShowPinSession.sql得到的p1raw值，获取对象的名称.
*/
col owner format a8
col object format a70

select kglnaown as owner, kglnaobj as Objects
from x$kglob
where kglhdadr='&p1raw'
/

3.查询哪些session在等待这个资源
/*
ShowRequestSession.sql
*/

col SID_SERIAL format a15

select s.sid||','||s.serial# SID_SERIAL, kglpnmod "Mode Held", kglpnreq "Request"
from x$kglpn p, v$session s
where p.kglpnuse = s.saddr  
and kglpnhdl   = '&p1raw'
/

alter session set events 'immediate trace name library_cache level 4';

Session1开始后的dump:

BUCKET 130073:
  LIBRARY OBJECT HANDLE: handle=3dae9440 mutex=3DAE94F4(0)
  name=SCOTT.SP_LOCK_TEST
  hash=3bc5e7d14f78d9089dcbb8cda129fc19 timestamp=04-04-2007 15:13:06
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-009d-20bf lock=N pin=S latch#=1 hpc=0002 hlc=0002
  lwt=3DAE949C[3DAE949C,3DAE949C] ltm=3DAE94A4[3DAE94A4,3DAE94A4]
  pwt=3DAE9480[3DAE9480,3DAE9480] ptm=3DAE9488[3DAE9488,3DAE9488]
  ref=3DAE94BC[3DAE94BC,3DAE94BC] lnd=3DAE94C8[36C47BA0,3D9437E0]
    LIBRARY OBJECT: object=3883c1cc
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
  BUCKET 130073 total object count=1
--------------------------------------------------------------------------------  
 
Session2开始后的dump:
 
  BUCKET 130073:
  LIBRARY OBJECT HANDLE: handle=3dae9440 mutex=3DAE94F4(0)
  name=SCOTT.SP_LOCK_TEST
  hash=3bc5e7d14f78d9089dcbb8cda129fc19 timestamp=04-04-2007 15:13:06
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-009d-20bf lock=X pin=S latch#=1 hpc=0004 hlc=0004
  lwt=3DAE949C[3DAE949C,3DAE949C] ltm=3DAE94A4[3DAE94A4,3DAE94A4]
  pwt=3DAE9480[3A3B2E44,3A3B2E44] ptm=3DAE9488[3DAE9488,3DAE9488]
  ref=3DAE94BC[3DAE94BC,3DAE94BC] lnd=3DAE94C8[36C47BA0,3D9437E0]
    LIBRARY OBJECT: object=3883c1cc
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
  BUCKET 130073 total object count=1
 
--------------------------------------------------------------------------------  

Session3开始后的dump:  
 
  BUCKET 130073:
  LIBRARY OBJECT HANDLE: handle=3dae9440 mutex=3DAE94F4(0)
  name=SCOTT.SP_LOCK_TEST
  hash=3bc5e7d14f78d9089dcbb8cda129fc19 timestamp=04-04-2007 15:13:06
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-009d-20bf lock=X pin=S latch#=1 hpc=0006 hlc=0004
  lwt=3DAE949C[3A3C5508,3A3C5508] ltm=3DAE94A4[3DAE94A4,3DAE94A4]
  pwt=3DAE9480[3A3B2E44,3A3B2E44] ptm=3DAE9488[3DAE9488,3DAE9488]
  ref=3DAE94BC[3DAE94BC,3DAE94BC] lnd=3DAE94C8[36C47BA0,3D9437E0]
    LIBRARY OBJECT: object=3883c1cc
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
  BUCKET 130073 total object count=1
--------------------------------------------------------------------------------