library cache lock和library cache bin實驗_2.0

library cache lock和library cache bin實驗

1. 首先執行calling過程,在calling過程中呼叫pining過程
SQL> create or replace PROCEDURE pining IS
  2  BEGIN
  3    NULL;
  4  END;
  5  /

Procedure created.

SQL>
SQL> create or replace procedure calling is
  2  begin
  3    pining;
  4    dbms_lock.sleep(3000);
  5  end;
  6  /

Procedure created.

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

       SID
----------
        41

SQL> grant all on dbms_lock to scott;

Grant succeeded.

SQL> call calling();

hang.....................

2.session 2執行
SQL> select sid from v$mystat where rownum<2;

       SID
----------
        38
SQL>    
SQL> alter procedure pining compile;   

hang.......................

3.session 3
SQL> select sid from v$mystat where rownum<2;

       SID
----------
        46

SQL> drop procedure pining;  

hang.......................

4.查詢
SQL> select event#,name,parameter1,parameter2,parameter3,wait_class#,wait_class from v$event_name where name like 'library cache%';

    EVENT# NAME                           PARAMETER1      PARAMETER2      PARAMETER3                WAIT_CLASS# WAIT_CLASS
---------- ------------------------------ --------------- --------------- ------------------------- ----------- -------------------------
       286 library cache pin              handle address  pin address     100*mode+namespace                  4 Concurrency
       287 library cache lock             handle address  lock address    100*mode+namespace                  4 Concurrency
       288 library cache load lock        object address  lock address    100*mask+namespace                  4 Concurrency
       289 library cache: mutex X         idn             value           where                               4 Concurrency
       290 library cache: mutex S         idn             value           where                               4 Concurrency
      1040 library cache revalidation                                                                         0 Other
      1041 library cache shutdown                                                                             0 Other

7 rows selected.

SQL>   

SQL> select s.sid,s.sql_id,s.status,s.username,s.event,s.blocking_session,s.last_call_et,s.p1,s.p1raw,s.p2,s.p2raw,s.p3,s.p3raw from v$session s where s.EVENT like 'library%';

       SID SQL_ID        STATUS   USERNAME   EVENT                BLOCKING_SESSION LAST_CALL_ET         P1 P1RAW                    P2 P2RAW                    P3 P3RAW
---------- ------------- -------- ---------- -------------------- ---------------- ------------ ---------- ---------------- ---------- ---------------- ---------- ----------------
        38 bvy6nfztw6b8u ACTIVE   SYS        library cache lock                 46          257 1915540152 00000000722CD2B8 1913174216 000000007208B8C8 3.8633E+14 00015F5E00010003
        46 az5qprppsq5fa ACTIVE   SYS        library cache pin                  41         1223 1915540152 00000000722CD2B8 1914076336 0000000072167CB0 3.8633E+14 00015F5E00010003

P1 列是Library Cache Handle Address,Pn欄位是10進製表示,PnRaw欄位是16進製表示


排查：
我們看到,library cache pin等待的物件的handle地址為:00000000722CD2B8
透過這個地址,我們查詢X$KGLOB檢視就可以得到物件的具體資訊:
Note: X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
col KGLNAOWN for a10
col KGLNAOBJ for a20
select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='00000000722CD2B8';

SQL> col KGLNAOWN for a10
SQL> col KGLNAOBJ for a20
SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
  2  from X$KGLOB
  3  where KGLHDADR ='00000000722CD2B8';

ADDR             KGLHDADR         KGLHDPAR         KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------------- ---------- -------------------- ---------- ----------------
00007FBEE017CC88 00000000722CD2B8 00000000722CD2B8 SYS        PINING               1587262593 0000000074304078

這裡KGLNAHSH代表該物件的Hash Value
由此我們知道,在PINING物件上正經歷library cache pin的等待.
然後我們引入另外一個內部檢視X$KGLPN:
Note:X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
                
select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '00000000722CD2B8' and b.KGLPNMOD<>0;

SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
  2  b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
  3  from v$session a,x$kglpn b
  4  where a.saddr=b.kglpnuse and b.kglpnhdl = '00000000722CD2B8' and b.KGLPNMOD<>0;

       SID USERNAME   PROGRAM                                          ADDR             KGLPNADR         KGLPNUSE         KGLPNSES         KGLPNHDL         KGLPNLCK         KGLPNMOD   KGLPNREQ
---------- ---------- ------------------------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ----------
        41 SYS        sqlplus@wang (TNS V1-V3)                         00007FBEE0335460 0000000072124DE0 0000000090E6F3C0 0000000090E6F3C0 00000000722CD2B8 00000000721253E0         2          0

透過聯合v$session,可以獲得當前持有該handle的使用者資訊,對於我們的測試sid=41的使用者正持有該handle:
SQL> select s.inst_id,s.sid,s.serial#,s.sql_id,s.status,s.username,s.event,s.blocking_session,s.WAIT_TIME,s.SECONDS_IN_WAIT,s.LAST_CALL_ET from gv$session s where s.sid=41;

   INST_ID        SID    SERIAL# SQL_ID        STATUS   USERNAME   EVENT                BLOCKING_SESSION  WAIT_TIME SECONDS_IN_WAIT LAST_CALL_ET
---------- ---------- ---------- ------------- -------- ---------- -------------------- ---------------- ---------- --------------- ------------
         1         41        363 1shkx2jasndx8 ACTIVE   SYS        PL/SQL lock timer                              0            1733         1733

接著查詢等待hold_sid 的sql,如下：
SQL> select sql_id,sql_text from v$sql where sql_id='1shkx2jasndx8';

SQL_ID        SQL_TEXT
------------- ----------------------------------------
1shkx2jasndx8 call calling()

這裡我們得到這個使用者正在執行calling這個儲存過程,接下來的工作就應該去檢查calling在作什麼了.我們這個calling作的工作是dbms_lock.sleep(3000)，這也就是PL/SQL lock timer正在等待的原因，至此就找到了Library Cache Pin的原因.


簡化查詢步驟：
獲得library cache pin 等待物件的資訊：
select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
  from x$kglob
 where kglhdadr in
       (select p1raw from v$session_wait where event like 'library%')

獲得持有等待物件的session資訊：
select a.PADDR,
       a.sid,
       a.SERIAL#,
       a.PROGRAM,
       a.SQL_ADDRESS,
       a.STATUS,
       a.SQL_HASH_VALUE,
       b.addr,
       b.kglpnadr,
       b.kglpnuse,
       b.kglpnses,
       b.kglpnhdl,
       b.kglpnlck,
       b.kglpnmod,
       b.kglpnreq
  from x$kglpn b, v$session a
 where a.SADDR = b.kglpnuse
   and b.kglpnmod <> 0
   and b.kglpnhdl in (select p1raw from v$session_wait where event like 'library%');


獲得持有等待物件的會話執行的程式碼：
select *
  from v$sqltext
 where (address, hash_value) in
       (select sql_address, sql_hash_value
          from v$session
         where sid in (select a.SID
                         from x$kglpn b, v$session a
                        where a.SADDR = b.kglpnuse
                          and b.kglpnmod <> 0
                          and b.kglpnhdl in
                              (select p1raw
                                 from v$session_wait
                                where event like 'library%')))
 order by piece;
 
總結可用如下sql查詢：
select  a.event,
         a.sid,
         a.SERIAL#,
         a.username,
         a.machine,
         a.wait_time,
         a.seconds_in_wait,
         a.state,
         a.blocking_session,
         p.kglpncnt,
         p.kglpnmod,
         p.kglpnreq,
         b.kglnaown,
         b.kglnaobj,
         b.kglfnobj,
         b.kglhdobj
    from v$session a, x$kglpn p, x$kglob b
   where p.kglpnhdl in (select kglpnhdl  from x$kglpn where kglpnreq <>0)
      and p.kglpnhdl=b.kglhdadr
      and a.P1RAW=rawtohex(p.kglpnhdl)
order by seconds_in_wait desc;