0106library cache pin的快速定位與解決

[20150106]library cache pin的快速定位與解決.txt

--昨天別人的系統遇到library cache pin問題，導致前臺業務停頓，出現問題後請求協助.
--我以前也遇到，也是手忙腳亂。我自己寫過一個定位的指令碼：

http://blog.itpub.net/267265/viewspace-754965/

$ 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 ;' 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 );

--不過我去了現場，正好是別人解決的，別人的方法很獨特，自己做一個記錄：

1.建立測試環境：
SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--會話1：
CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
   DBMS_LOCK.sleep (1000);
END;
/

SCOTT@test> @spid
       SID    SERIAL# C50
---------- ---------- --------------------------------------------------
       396       1151 alter system kill session '396,1151' immediate;

SPID
------
28205

--執行指令碼：
SCOTT@test> exec proc1

--開啟會話2執行：
CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
   DBMS_LOCK.sleep (1000);
END;
/

--開啟會話3執行lcp.sql指令碼：
SCOTT@test> @lcp

       SID    SERIAL# W_P1R            H_WAIT               H_P1R            H_P2R            H_P2R            USERS_BLOCKED SQL_ID        HASH_VALUE SQL_TEXT
---------- ---------- ---------------- -------------------- ---------------- ---------------- ---------------- ------------- ------------- ---------- ------------------------------
       396       1151 00000000BE53E508 PL/SQL lock timer    00               00               00                           1 7ap74x3urn7f7 4118420935 BEGIN proc1; END;

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
-------------------------------------------------------------------------------------------------------------------------
alter system kill session '396,1151' immediate ;

--很明顯只要kill掉會話1的session就ok了。

2.他的定位方法：

SCOTT@test> SELECT * FROM V$DB_OBJECT_CACHE WHERE  type='PROCEDURE' and LOCKS!='0';

Record View
As of: 2015/1/6 16:14:18

OWNER:               SCOTT
NAME:                PROC1
DB_LINK:            
NAMESPACE:           TABLE/PROCEDURE
TYPE:                PROCEDURE
SHARABLE_MEM:        25168
LOADS:               1
EXECUTIONS:          0
LOCKS:               2
PINS:                1
KEPT:                NO
CHILD_LATCH:         9222
INVALIDATIONS:       0
HASH_VALUE:          301736966
LOCK_MODE:           EXCLUSIVE
PIN_MODE:            SHARED
STATUS:              VALID
TIMESTAMP:           2013-03-04/16:07:26
PREVIOUS_TIMESTAMP: 
LOCKED_TOTAL:        4
PINNED_TOTAL:        3
PROPERTY:           
FULL_HASH_VALUE:     373f969e72de08d9a24f345d11fc2406

--LOCKS>＝1並不表示locks，也許是正在執行！我感覺查詢應該是locks>pins才有問題。大家可以測試看看。

SCOTT@test> column object format a30
SCOTT@test> select * from V$ACCESS WHERE object='PROC1';
SID OWNER  OBJECT   TYPE
---- ------ -------- ----------
396 SCOTT  PROC1    PROCEDURE

SCOTT@test> select sid,serial# from v$session where sid=396;
       SID    SERIAL#
---------- ----------
       396       1151

--很明顯結果都是一樣的。

3.kill 有問題會話。
SCOTT@test> alter system kill session '396,1151' immediate;
System altered.

SCOTT@test> SELECT * FROM V$DB_OBJECT_CACHE WHERE  type='PROCEDURE' and LOCKS!='0';
no rows selected

SCOTT@test> SELECT * FROM V$DB_OBJECT_CACHE WHERE  type='PROCEDURE' and name='PROC1';

Record View
As of: 2015/1/6 16:25:09

OWNER:               SCOTT
NAME:                PROC1
DB_LINK:            
NAMESPACE:           TABLE/PROCEDURE
TYPE:                PROCEDURE
SHARABLE_MEM:        25168
LOADS:               2
EXECUTIONS:          1
LOCKS:               0
PINS:                0
KEPT:                NO
CHILD_LATCH:         9222
INVALIDATIONS:       0
HASH_VALUE:          301736966
LOCK_MODE:           NONE
PIN_MODE:            NONE
STATUS:              VALID
TIMESTAMP:           2013-03-04/16:07:26
PREVIOUS_TIMESTAMP: 
LOCKED_TOTAL:        4
PINNED_TOTAL:        4
PROPERTY:           
FULL_HASH_VALUE:     373f969e72de08d9a24f345d11fc2406

--感覺這種方式定位更加簡單。