[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
--感觉这种方式定位更加简单。