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