[20160317]Library Cache Pin and Library Cache Lock Wait Events.txt

--這篇blog主要是測試Library Cache Pin and Library Cache Lock Wait Events,不講解細節,僅僅記錄演示過程.

1.環境:
SCOTT@book(84,45)> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create procedure lcp
is
begin
sys.dbms_lock.sleep(3600);
end;
/

--session 1:
SCOTT@book(84,55)> exec lcp()

--session 2:

SCOTT@book(101,2981)> alter procedure lcp compile;
--掛起

2.出現等待時間library cache pin;
SYS@book> set numw 15
SYS@book> @ &r/wait
P1RAW            P2RAW            P3RAW                         P1              P2              P3             SID         SERIAL#            SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00                    1650815232               1               0             236             595              18 SQL*Net message to client                WAITED SHORT TIME                 2               0
000000007CE0BE30 000000007B533C10 000166ED00010003      2095103536      2069052432 394643070058499             101            2981              29 library cache pin                        WAITING                    66984739              67

SYS@book> @ &r/lcp
            SID         SERIAL# W_P1R            H_WAIT               H_P1R            H_P2R            H_P2R              USERS_BLOCKED SQL_ID             HASH_VALUE SQL_TEXT
--------------- --------------- ---------------- -------------------- ---------------- ---------------- ---------------- --------------- ------------- --------------- ------------------------------
             84              55 000000007CE0BE30 PL/SQL lock timer    00               00               00                             1 bbqu7d410xm43        34524291 BEGIN lcp(); END;
            101            2981 000000007CE0BE30 library cache pin    000000007CE0BE30 000000007B533C10 000166ED00010003               1 g4q89x3ttj92q      4086867030 alter procedure lcp compile

C100
---------------------------------------------------
alter system kill session '84,55' immediate ;
alter system kill session '101,2981' immediate ;

--執行alter system kill session '84,55' immediate ;就ok了.

$ cat lcp.sql
column h_wait format A20
column sql_text format a30
SELECT   s.SID,s.serial#, waiter.p1raw w_p1r, holder.event h_wait, holder.p1raw h_p1r, holder.p2raw h_p2r, holder.p3raw h_p2r,
         COUNT (s.SID) users_blocked, SQL.sql_id, SQL.hash_value, SQL.sql_text
    FROM v$sql SQL, v$session s, x$kglpn p, v$session_wait waiter, v$session_wait holder
   WHERE s.sql_hash_value = SQL.hash_value
     AND p.kglpnhdl = waiter.p1raw
     AND s.saddr = p.kglpnuse
     AND waiter.event LIKE 'library cache pin'
     AND holder.SID = s.SID
GROUP BY s.SID,s.serial#, waiter.p1raw, holder.event, holder.p1raw, holder.p2raw, holder.p3raw, SQL.sql_id, SQL.hash_value, SQL.sql_text;

select 'alter system kill session '''||sid||','||serial#||''' immediate ;' c100 from (
SELECT   s.SID,s.serial#, waiter.p1raw w_p1r, holder.event h_wait, holder.p1raw h_p1r, holder.p2raw h_p2r, holder.p3raw h_p2r,
         COUNT (s.SID) users_blocked, SQL.sql_id, SQL.hash_value, SQL.sql_text
    FROM v$sql SQL, v$session s, x$kglpn p, v$session_wait waiter, v$session_wait holder
   WHERE s.sql_hash_value = SQL.hash_value
     AND p.kglpnhdl = waiter.p1raw
     AND s.saddr = p.kglpnuse
     AND waiter.event LIKE 'library cache pin'
     AND holder.SID = s.SID
GROUP BY s.SID,s.serial#, waiter.p1raw, holder.event, holder.p1raw, holder.p2raw, holder.p3raw, SQL.sql_id, SQL.hash_value, SQL.sql_text );

3.再開啟第3個會話:
SCOTT@book(123,535)> alter procedure lcp compile;
--掛起

SYS@book> @ &r/wait
P1RAW            P2RAW            P3RAW                         P1              P2              P3             SID         SERIAL#            SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00                    1650815232               1               0             236             595              52 SQL*Net message to client                WAITED SHORT TIME                 2               0
000000007CE0BE30 000000007D8AFE50 000166ED00010003      2095103536      2106261072 394643070058499             123             535              29 library cache lock                       WAITING                    16444382              16
000000007CE0BE30 000000007B533C10 000166ED00010003      2095103536      2069052432 394643070058499             101            2981              29 library cache pin                        WAITING                   291174802             291

--sid=123(第3個會話) 出現library cache lock. sid = 101(第2個會話) 出現library cache lock.

SYS@book> @ &r/lcl

display library cache lock problem

INST_ID SADDR            HANDLE                       MOD             REQ OBJECT               SQL_ID             HASH_VALUE USER_NAME            C50
-------- ---------------- ---------------- --------------- --------------- -------------------- ------------- --------------- -------------------- --------------------------------------------------
       1 0000000085ABF040 000000007CE0BE30               1               0 LCP                  551ha8v2ngx9s      3309827384 SCOTT                alter system kill session '84,55' immediate;
       1 0000000085B117E0 000000007CE0BE30               3               0 LCP                  551ha8v2ngx9s      3309827384 SCOTT                alter system kill session '101,2981' immediate;

display wait library cache lock

SID SERIAL# SPID PID P_SERIAL# EVENT C50
---- --------------- ------ ------- --------------- ------------------- --------------------------------------------------
123 535 65297 35 240 library cache lock alter system kill session '123,535' immediate;

--實際上要殺那個很重要,講先解決library cache pin .
--注意看MOD,REQ欄位. session 1 的mod=1,session 2 的mod=3. 要解決問題應該選擇kill session 1.
--如果kill 其他session並不能解決問題.

4.解決問題:
alter system kill session '84,55' immediate ;
--也就是kill session 1:

SYS@book> @ &r/lcl
display library cache lock problem
no rows selected

display wait library cache lock
no rows selected

SYS@book> @ &r/lcp
no rows selected
no rows selected

--session 2:
SCOTT@book(101,2981)> alter procedure lcp compile;
alter procedure lcp compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
--估計測試時間太長,實際上我重複測試沒有出現這個錯誤.如果kill session 2,session 3 出現library cache pin等待事件.

--session 3:
SCOTT@book(123,535)> alter procedure lcp compile;
Procedure altered.

--補充一些,如果從v$sql查詢SQL_ID=551ha8v2ngx9s,無法查到,這裡的物件是lcp過程名.要查詢x$kglob .
-- select * from x$kglob where kglobt03='551ha8v2ngx9s' or kglnahsh=3309827384;
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
               kglhdpar, '父遊標控制程式碼地址',
               '子游標控制程式碼地址')
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03 ,
           kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;

SYS@book> @ &r/sharepool/shp4   551ha8v2ngx9s      3309827384
old 17: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 17: WHERE kglobt03 = '551ha8v2ngx9s' or kglhdpar='551ha8v2ngx9s' or kglhdadr='551ha8v2ngx9s' or KGLNAHSH= 3309827384
TEXT           KGLHDADR         KGLHDPAR         C40 KGLOBHD0         KGLOBHD6                KGLOBHS0        KGLOBHS6        KGLOBT16   N0_6_16             N20        KGLNAHSH KGLOBT03             KGLOBT09
-------------- ---------------- ---------------- ---- ---------------- ---------------- --------------- --------------- --------------- --------- --------------- --------------- ------------- ---------------
父遊標控制程式碼地址 000000007CE0BE30 000000007CE0BE30 LCP 000000007D9B79F0 00                          4688               0               0      4688           16976      3309827384                             0

--或者查詢select * from V$DB_OBJECT_CACHE where hash_value=3309827384;

Record View
As of: 2016/03/17 10:49:25

OWNER:               SCOTT
NAME:                LCP
DB_LINK:
NAMESPACE:           TABLE/PROCEDURE
TYPE:                PROCEDURE
SHARABLE_MEM:        16976
LOADS:               12
EXECUTIONS:          0
LOCKS:               2
PINS:                1
KEPT:                NO
CHILD_LATCH:         128312
INVALIDATIONS:       1
HASH_VALUE:          3309827384
LOCK_MODE:           EXCLUSIVE
PIN_MODE:            SHARED
STATUS:              VALID
TIMESTAMP:           2016-03-17/09:33:38
PREVIOUS_TIMESTAMP:
LOCKED_TOTAL:        25
PINNED_TOTAL:        24
PROPERTY:
FULL_HASH_VALUE:     5e855748c192403c52860a46c547f538

-- shareable_mem的計算:
SELECT kglnaobj
      ,kglobhs0
      ,kglobhs2
      ,kglobhs4
      ,kglobhd0
      ,kglobhd2
      ,kglobhd4
FROM x$kglob
WHERE kglobt03 = '551ha8v2ngx9s' OR kglnahsh = 3309827384;

KGLNAOBJ KGLOBHS0 KGLOBHS2 KGLOBHS4 KGLOBHD0 KGLOBHD2 KGLOBHD4
--------- -------- --------------- --------------- ---------------- ---------------- ----------------
LCP 4688 8192 4096 000000007D9B79F0 000000007E521910 000000007E521BA8

--視乎儲存過程還使用堆0,堆2,堆4與sql語句不同.4688+8192+4096 =16976.
--如果看檢視V$DB_OBJECT_CACHE,SHARABLE_MEM對應 kglobhs0 + kglobhs1 + kglobhs2 + kglobhs3 + kglobhs4 + kglobhs5 + kglobhs6 相加.

--補上檢查library cache lock的指令碼,由於要訪問x$,僅僅以sys使用者執行:
$ cat lcl.sql
PROMPT
PROMPT display library cache lock problem
PROMPT

column object format a20
column user_name format a20
SELECT inst_id
      ,kgllkses saddr
      ,kgllkhdl handle
      ,kgllkmod MOD
      ,kgllkreq REQ
      ,kglnaobj object
      ,KGLLKSQLID sql_id
      ,kglnahsh hash_value
      ,user_name
      ,   'alter system kill session '''
       || s.sid
       || ','
       || s.serial#
       || ''''
       || ' immediate;'
          c50
--,lock_a.*
FROM x$kgllk lock_a, v$session s
WHERE    s.saddr = lock_a.kgllkses and
kgllkmod > 0
       AND EXISTS
              (SELECT lock_b.kgllkhdl
                 FROM x$kgllk lock_b
                WHERE     kgllkses IN (SELECT saddr
                                         FROM v$session
                                        WHERE event like 'library cache lock') /* blocked session */
                                        --WHERE event like 'library cache pin') /* blocked session */
                      AND lock_a.kgllkhdl = lock_b.kgllkhdl
                      AND kgllkreq > 0);

PROMPT
PROMPT display wait library cache lock
PROMPT
SELECT s.sid
      ,s.serial#
      ,p.spid
      ,p.pid
      ,p.serial# p_serial#
          ,s.event
      ,   'alter system kill session '''
       || s.sid
       || ','
       || s.serial#
       || ''''
       || ' immediate;'
          c50
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.event= 'library cache lock';
--AND s.event= 'library cache pin';

0317Library Cache Pin/Lock Wait Events

相關文章