11g library cache lock分析思路

<pre name="code" class="sql">create or replace procedure prc_test1 
is
begin
  loop
  execute immediate 'select * from dual';
end loop;
end; 
########################################
SESSION 20 执行存储过程:               #
########################################

SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	20	    0	       0

SQL> exec prc_test1;


此时的等待事件:
SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid=20;

       SID BLOCKING_SESSION	    P1 P1RAW			P2	   P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
	20		     537557208 00000000200A78D8        293	    0 latch: shared pool

#########################################
SESSION 1137编译存储过程:               #
#########################################

SQL>  select * from v$mystat where rownum<2;  

       SID STATISTIC#	   VALUE
---------- ---------- ----------
      1137	    0	       0

SQL> alter procedure prc_test1 compile;

SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid in (20,1137);

       SID BLOCKING_SESSION	    P1 P1RAW			P2	   P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
	20		     537557208 00000000200A78D8        293	    0 latch: shared pool
      1137		 20  832829180 0000000031A3F6FC  831962136 3.2728E+14 library cache pin


SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
    FROM x$kglpn p, v$session s 
    WHERE p.kglpnuse=s.saddr
    AND kglpnhdl like '%&P1RAW%'

SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
    FROM x$kglpn p, v$session s 
    WHERE p.kglpnuse=s.saddr  2    3  ;

       SID	 Mode	     Req KGLPNHDL
---------- ---------- ---------- --------
      1137	    0	       3 31A3F6FC
	20	    2	       0 31A3F6FC

可以发现持有者为20
######################################
SESSION 22删除存储过程:              #
######################################
SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	22	    0	       0

SQL> drop procedure prc_test1;


SQL>  select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid in (20,1137,22);

       SID BLOCKING_SESSION	    P1 P1RAW			P2	   P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
	20		     537557208 00000000200A78D8        293	    0 latch: shared pool
	22	       1137  832829180 0000000031A3F6FC  618590516 3.2728E+14 library cache lock
      1137		 20  832829180 0000000031A3F6FC  831962136 3.2728E+14 library cache pin

 查看X$KGLLK表
The X$KGLLK table (accessibleonly as SYS/INTERNAL) contains all the library object locks (both held &requested) for all sessions and is more complete than the V$LOCK view 

althoughthe column names don't always reveal their meaning.
--X$KGLLK 表只能被SYS/INTERNAL用户访问，其包含所有library object locks的信息（held和requested）。
 
 
--查看等待事件为librarycache lock的session 的session address (SADDR):
 
SQL> select sid,saddr from v$session where event='library cache lock';

       SID SADDR
---------- --------
	22 2F391B2C

--从x$kgllk查看具体的锁信息：
SQL> select kgllkhdl Handle, kgllkreq Request,kglnaobj Object
  from x$kgllk
 where kgllkses = '2F391B2C'
   and kgllkreq > 0;  2    3    4  

HANDLE	    REQUEST OBJECT
-------- ---------- ------------------------------------------------------------
31A3F6FC	  3 PRC_TEST1
 
KGLLKREQ: This will show you the library cache lock requested by this session(KGLLKREQ > 0)
KGLNAOBJ:contains the first 80 characters of the name of the object.
KGLLKHDL:corresponds with the 'handle address' of the object
 
 
--然后根据KGLLKHDL从X$KGLLK查看KGLLKMOD > 0的session，其正在持有该锁：
 
SQL> select kgllkses saddr, kgllkhdl handle,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 = '2F391B2C' /* blocked session*/
           and lock_a.kgllkhdl =lock_b.kgllkhdl
           and kgllkreq > 0);  2    3    4    5    6    7    8  

SADDR	 HANDLE 	 MOD OBJECT
-------- -------- ---------- ------------------------------------------------------------
2F397004 31A3F6FC	   1 PRC_TEST1
2E063BA8 31A3F6FC	   3 PRC_TEST1
 

SQL>  select sid,saddr from v$session where saddr in ('2F397004','2E063BA8','2F391B2C');

       SID SADDR
---------- --------
	20 2F397004
	22 2F391B2C  ----被堵塞 library cache lock
      1137 2E063BA8  ----被堵塞 library cache pin
 


SQL> select * from dba_kgllock where KGLLKHDL in (  select KGLLKHDL from dba_kgllock where kgllkmod =3);

KGLLKUSE KGLLKHDL   KGLLKMOD   KGLLKREQ KGLL
-------- -------- ---------- ---------- ----
2F391B2C 31A3F6FC	   0	      3 Lock
2F397004 31A3F6FC	   1	      0 Lock
2E063BA8 31A3F6FC	   3	      0 Lock
2E063BA8 31A3F6FC	   0	      3 Pin
2F397004 31A3F6FC	   2	      0 Pin



SQL>  select sid,serial#,blocking_session,p1,p1raw,p2,p3,event from v$session where event='library cache lock';

       SID    SERIAL# BLOCKING_SESSION	       P1 P1RAW 		   P2	      P3 EVENT
---------- ---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
	22	  121		  1137	832829180 0000000031A3F6FC  618590516 3.2728E+14 library cache lock


SQL> select sid,serial#,blocking_session,p1,p1raw,p2,p3,event from v$session
  2  where sid=1137;

       SID    SERIAL# BLOCKING_SESSION	       P1 P1RAW 		   P2	      P3 EVENT
---------- ---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
      1137	  504		    20	832829180 0000000031A3F6FC  619961492 3.2728E+14 library cache pin



SQL> select sid,serial# ,saddr from v$session where saddr in (
select KGLLKUSE from dba_kgllock where KGLLKHDL in (  select KGLLKHDL from dba_kgllock where KGLLKREQ =3))  2  ;

       SID    SERIAL# SADDR
---------- ---------- --------
	20	  236 2F397004
	22	  121 2F391B2C
      1137	  504 2E063BA8


查看所有持有者:
SQL>  select sid, serial#, saddr
  from v$session
 where saddr in (select KGLLKUSE
                   from dba_kgllock
                  where KGLLKHDL in
                        (select KGLLKHDL from dba_kgllock where KGLLKREQ >0)
                    and kgllkmod > 0);  2    3    4    5    6    7  

       SID    SERIAL# SADDR
---------- ---------- --------
	20	  236 2F397004
      1137	  504 2E063BA8