定位并解决library cache lock

定位并解决library cache lock的方法

作者：sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

1、查看具体是那些操作导致library cache lock等待事件的发生

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

点击(此处)折叠或打开

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

2、根据上面结果得到引起该事件的SQL，确认这些SQL能不能kill

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

点击(此处)折叠或打开

1. select count(tt.transport_point_no) countNo
   
2.   from tms_transport_point tt
   
3.   left join tms_unload_point_dtl ud
   
4.     on ud.transport_point_no = tt.transport_point_no
   
5.   left join store st
   
6.     on st.store_no = tt.store_no
   
7.   left join tms_quartzcenter q
   
8.     on tt.quartzcenter_no = q.quartzcenter_no
   
9.  where tt.store_type = '11'
   
10.    and ud.transport_point_no is null

2）SQL二：

点击(此处)折叠或打开

1. select distinct t.QUARTZCENTER_SIMPLE_NAME centerNo,
   
2.                 T.QUARTZCENTER_NAME centerName,
   
3.                 t.zone_no areaNo,
   
4.                 a.store_name areaName,
   
5.                 t.isSign,
   
6.                 nvl(p.system_param_value, 0) isBatchFlag,
   
7.                 nvl(t.edit_time, t.create_time) downTime
   
8.   from tms_quartzCenter T
   
9.   left join store a
   
10.     on t.zone_no = a.store_code
    
11.   left join tms_system_param p
    
12.     on t.quartzcenter_no = p.quartzcenter_no
    
13.    and p.system_param_name = 'IsDeliverBatchFlag'
    
14.  where t.status = '0'

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

3、经沟通，kill引起该等待事件的SQL

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

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

点击(此处)折叠或打开

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

该视图运行结果如下：

4、MOS 的文档【122793.1】里说导致librarycache lock通常有2种原因

点击(此处)折叠或打开

1. （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)
2. 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.
4. （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博客 ” ，链接：http://blog.itpub.net/31324175/viewspace-2126076/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31324175/viewspace-2126076/