library cache lock\pin的查找与处理

1、对于library cache pin的处理方法
假如在系统中出现了这种情况，首先使用下面语句进行确定阻塞存在
SELECT s.sid,kglpnmod "Mode", kglpnreq "Req", SPID "OS Process",s.blocking_session,q.sql_id,q.sql_text
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o,v$sqlarea q
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event = 'library cache pin%'
and s.paddr=o.addr and s.sql_id = q.sql_id(+);


该语句的查询结果，可以看到
       SID    Mode   Req  OS Process   SQL_ID                   SQL_TEXT
------------- ------ ---- ----------   ----------------         -------------------------------------
       135       2      0       5768   bc5ksqwnksxtk            BEGIN library_call; END;
       197       0      3       7409   d2gr0dzuz721h            alter procedure library_call compile


可以看到mode大于0的为阻塞源，直接查杀135的会话即可


具体查询machine，模块类、machine等的，可以再次结合实际的情况再进一步查询。


2、对于library cache lock
假如系统出现这种情况，首先使用下面语句进行确定阻塞存在
select d.sid,kgllkses saddr, kgllkhdl handle,kgllkmod mod, kglnaobj object,d.blocking_session,e.sql_text from x$kgllk c,v$session d,v$sqlarea e
where c.kgllkmod > 0 and exists
(select 1 
from x$kgllk a,v$session b where a.kgllkhdl = c.kgllkhdl and a.kgllkses = b.saddr and a.kgllkreq > 0 and b.event='library cache lock')
and c.kgllkses = d.saddr and d.sql_id = e.sql_id(+);


 SID SADDR            HANDLE                  MOD    OBJECT               BLOCKING_SESSION    SQL_TEXT
-----------------------------------------------------------------------------------------------------------------
 79 00000000F570D610 00000000E3B73F00          1      LIBRARY_LOCK                            BEGIN library_call; END;
135 00000000F57DD190 00000000E3B73F00          1      LIBRARY_LOCK         79                 alter procedure library_lock compile
135 00000000F57DD190 00000000E3B73F00          3      LIBRARY_LOCK         79                 alter procedure library_lock compile


可以看到79正在阻塞，并且成为阻塞源。


3、oradebug使用
SQL> ORADEBUG setmypid 
Statement processed.
SQL> ORADEBUG setinst all;
Statement processed.
SQL> oradebug hanganalyze 3;
Hang Analysis in /oracle/app/oracle/diag/rdbms/oratest/oratest1/trace/oratest1_ora_17309.trc


可以看到已经把相关信息反馈到相关文件中
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (oratest.oratest1)
                   os id: 5768
              process id: 38, oracle@oracle1 (TNS V1-V3)
              session id: 135
        session serial #: 45
    }
    is waiting for 'library cache pin' with wait info:
    {
                      p1: 'handle address'=0xe3b73f00
                      p2: 'pin address'=0xe3976100
                      p3: '100*mode+namespace'=0x1556b00010003
            time in wait: 2 min 9 sec
           timeout after: 12 min 50 sec
                 wait id: 99
                blocking: 0 sessions
             current sql: alter procedure library_lock compile
             short stack: ksedsts()+465 60 in()+1373 ()+917 in()+201             wait history:
              * time between current wait and wait #1: 0.001650 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 23.046879 sec
                     wait id: 98              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000017 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000002 sec
                     wait id: 97              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000070 sec
              3.       event: 'log file sync'
                 time waited: 0.003374 sec
                     wait id: 96              p1: 'buffer#'=0x49ce
                                              p2: 'sync scn'=0x10bcf4
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (oratest.oratest1)
                   os id: 15417
              process id: 37, oracle@oracle1 (TNS V1-V3)
              session id: 79
        session serial #: 27
    }
    which is waiting for 'PL/SQL lock timer' with wait info:
    {
                      p1: 'duration'=0x0
            time in wait: 2 min 14 sec
           timeout after: 1 min 5 sec
                 wait id: 66
                blocking: 1 session
             current sql: BEGIN library_call; END;
             short stack: ksedsts()+465 60 27 +969             wait history:
              * time between current wait and wait #1: 0.011006 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 17.717954 sec
                     wait id: 65              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000039 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 64              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000003 sec
              3.       event: 'SQL*Net break/reset to client'
                 time waited: 0.000130 sec
                     wait id: 63              p1: 'driver id'=0x62657100
                                              p2: 'break?'=0x0
    }
 
Chain 1 Signature: 'PL/SQL lock timer'<='library cache pin'
Chain 1 Signature Hash: 0xa7a4880c
-------------------------------------------------------------------------------
 
===============================================================================
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [LEAF] [LEAF_NW] 
[level  5] :   1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW] 
 
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[78]/1/79/27/0xf570d610/15417/LEAF/
[134]/1/135/45/0xf57dd190/5768/NLEAF/[78]


*** 2015-06-18 11:37:59.854
===============================================================================
END OF HANG ANALYSIS
===============================================================================


*** 2015-06-18 11:37:59.854
===============================================================================
HANG ANALYSIS DUMPS:
  oradebug_node_dump_level: 3
===============================================================================
 
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[78]/1/79/27/0xf570d610/15417/LEAF/
[134]/1/135/45/0xf57dd190/5768/NLEAF/[78]
 
 
No processes qualify for dumping.
 
===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================


*** 2015-06-18 11:37:59.854
Oradebug command 'hanganalyze 3' console output: 




4、基表说明
SQL> desc x$kgllk;
名称 类型
---------- -----------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW(4)
KGLLKUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLLKSES RAW(4) ---owner地址
KGLLKSNM NUMBER ---SID
KGLLKHDL RAW(4) ---library cache object 句柄
KGLLKPNC RAW(4) ---the address of the call pin
KGLLKPNS RAW(4) ---对应跟踪文件中的session pin值
KGLLKCNT NUMBER
KGLLKMOD NUMBER ---持有锁的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLLKREQ NUMBER ---请求锁的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLLKFLG NUMBER ---cursor的状态﹐8(10g前)或2048(10g)表示这个sql正在运行﹐
KGLLKSPN NUMBER ---对应跟踪文件的savepoint的值
KGLLKHTB RAW(4)
KGLNAHSH NUMBER ---sql的hash值(对应v$session的sql_hash_value)
KGLLKSQLID VARCHAR2(13) ---sql ID，sql标识符
KGLHDPAR RAW(4) ---sql地址(对应v$session的sql_address)
KGLHDNSP NUMBER
USER_NAME VARCHAR2(30) ---会话的用戶名
KGLNAOBJ VARCHAR2(60) ---对象名称或者已分析并打开cursor的sql的前60个字符
3) x$kglpn
X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
它是与x$kgllk相对应的表﹐是关于pin的相关信息。它主要用于解决library cache pin
引用该表的视图有﹕
DBA_KGLLOCK
SQL> desc x$kglpn;
名称 类型
------------ ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLPNSES RAW(4) ---owner地址
KGLPNHDL RAW(4) ---句柄
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER ---持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNREQ NUMBER ---请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNDMK NUMBER
KGLPNSPN NUMBER ---对应跟踪文件的savepoint的值
----------------------
x$kglpn  kglpnuse 会话的saddr KGLLKMOD 持有的锁 KGLPNREQ 请求锁模式
x$kgllk  kgllkuse 会话的saddr KGLPNMOD持有的锁 KGLLKREQ 请求锁模式
Kglhdlmd是Library cache lock的模式，为0时表示没有锁，1是NULL锁，2是共享锁，3是独占锁。Kglhdpmd是Library cache pin的模式，0是没有Pin，2是共享Pin，3是独占Pin
x$kgllk KGLLKSNM NUMBER ---SID
-----------------------------------------x$kglob
 父游标、子游标都有记录
 kglhdadr: 本记录游标地址
 kglhpadr: 父游标地址
 kglhdobj:LIBRARY OBJECT(代表 library object handle 的物理地址)
 kglobhd0:heap0 的地址
 ......
 kglobhd7:heap7的地址
一个sql语句至少有一个子游标，所有在x$kglob里至少有2个library cache object
一个sql的library cache 至少有2个堆heap 0 heap 6

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29371470/viewspace-1704901/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29371470/viewspace-1704901/