0106library cache pin的快速定位与解决-白红宇

[20150106]library cache pin的快速定位与解决.txt

--昨天别人的系统遇到library cache pin问题，导致前台业务停顿，出现问题后请求协助.

--我以前也遇到，也是手忙脚乱。我自己写过一个定位的脚本：

$ 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

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

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