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

library cache lock\pin的查詢與處理

相關文章