定位并解决library cache lock的方法

注意：此处SQL状态可以是active和INACTIVE

点击(此处)折叠或打开

SELECT SE.INST_ID, --实例
SQ.SQL_TEXT, /*SQL文本*/
SQ.SQL_FULLTEXT, /*SQL全部文本*/
SE.SID, /*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/
'ALTER SYSTEM kill SESSION ''' || SE.SID || ',' || SE.SERIAL# ||
''';' kill,
SQ.OPTIMIZER_COST AS COST_, /* COST 值*/
SE.LAST_CALL_ET CONTINUE_TIME, /*执行时间可能是单个sql也可能是整个功能*/
SE.PREV_EXEC_START, /*SQL execution start of the last executed SQL statement*/
SE.EVENT, /*等待事件*/
SE.LOCKWAIT, /*是否等待LOCK(SE，P)*/
SE.MACHINE, /*客户端的机器名。(WORKGROUP\PC-201211082055)*/
SE.USERNAME, /*创建该会话的用户名*/
SE.LOGON_TIME /*登陆时间*/
FROM GV$SESSION SE,
GV$SQLAREA SQ ,
x$kglpn p
WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE(+)
AND p.kglpnuse=SE.saddr(+) AND p.kglpnmod <> 0
AND SE.SQL_ID = SQ.SQL_ID(+)
AND SQ.INST_ID(+) = SE.INST_ID
and p.kglpnhdl in ( select p1raw from gv$session_wait where event in ('library cache pin','library cache lock' ,'library cache load lock') )

经查发现是下面两个SQL，且状态是INACTIVE
1）SQL一：

点击(此处)折叠或打开

2）SQL二：

点击(此处)折叠或打开

与业务再次确认，可以进行KILL

kill方法1：
根据”1、查看具体是那些操作导致library cache lock等待事件的发生“根据这个视图查出来的“kill”列结果进行kill

kill方法2：
如果方法1kill不掉这些SQL，则用下面SQL进行kill

点击(此处)折叠或打开

SELECT SE.INST_ID, --实例
SQ.SQL_TEXT, /*SQL文本*/
SQ.SQL_FULLTEXT, /*SQL全部文本*/
'kill -9 '||s.spid||';' kill,
SQ.OPTIMIZER_COST AS COST_, /* COST 值*/
SE.LAST_CALL_ET CONTINUE_TIME, /*执行时间可能是单个sql也可能是整个功能*/
SE.PREV_EXEC_START, /*SQL execution start of the last executed SQL statement*/
SE.EVENT, /*等待事件*/
SE.LOCKWAIT, /*是否等待LOCK(SE，P)*/
SE.MACHINE, /*客户端的机器名。(WORKGROUP\PC-201211082055)*/
SE.USERNAME, /*创建该会话的用户名*/
SE.LOGON_TIME /*登陆时间*/
FROM GV$SESSION SE,
GV$SQLAREA SQ ,
x$kglpn p,
GV$process s
WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE(+)
AND p.kglpnuse=SE.saddr
AND SE.SQL_ID = SQ.SQL_ID(+)
AND SE.INST_ID = SQ.INST_ID(+)
and s.addr=SE.paddr(+)
and p.kglpnhdl in ( select p1raw from gv$session_wait where event in ('library cache pin','library cache lock' ,'library cache load lock') )

该视图运行结果如下：

点击(此处)折叠或打开

（1）A DML operation that is hangingbecause the table which is accessed is currently undergoing changes (ALTERTABLE). This may take quite a long time depending on the size of the table andthe type of the modification (e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on atable with thousands of records)
In this case,V$LOCK will show that the session doing the 'ALTER TABLE' with an exclusive DMLenqueue lock on the table object (LMODE=6, TYPE=TM where ID1 is the OBJECT_IDof the table). The waiting session however does not show up in V$LOCK yet so inan environment with a lot of concurrent sessions the V$LOCK information will beinsufficient to track down the culprit blocking your operation.
（2）The compilation of package willhang on Library Cache Lock and Library Cache Pin if any users are executing aprocedure/function defined in the same package.

ITPub博客