[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

--感觉这种方式定位更加简单。