[Oracle]--Library cache lock 故障解決一例

By H.sdon

 DATE \@ "dddd, MMMM dd, yyyy" Sunday, March 06, 2011

今天收到同事電話，說是資料庫中一張名為acct_balance進行操作是奇慢，第一反映是不是掃行計劃有問題，結果我錯了，現將過程記錄下來。

用pl/sql連上資料庫情況：1、對acct_balance表的查詢很慢，正常少於0.1s完成，現在要60s完成；2、使用explain plan對語句進行分析，過析比正常情況下慢很多。

下面為處理過程：
1、從v$session_wait中查詢有問題的wait
Sql>select event,count(*) from v$session_wait group by event

2、如果有library cache lock時，檢視lock的都是些什麼語句

SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status, c.piece, c.sql_text
FROM v$session a, v$process b, v$sqltext c
WHERE b.addr=a.paddr AND a.sql_address=c.address(+)
and a.sid in (select sid from v$session_wait where event = 'db file sequential read')
and a.sid =2646
ORDER BY a.sid,c.piece

3、發現有Library語句我們需要進一步blocker會話是誰

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event like '%library cache lock%'
and s.paddr=o.addr

 

結果中發現

 SID       Mode        Req OS Process

---------- ---------- ---------- ------------

       396          0          2 6381970

       396          0          2 6381970

       396          0          2 6381970

       396          0          2 6381970

       341          3          0 4092132

       341          3          0 4092132

從上可以看出341以exclusive模式lock住library cache lock,為時396被迫等待，事情差不多能解決了，我直接kill了341的程式，acct_balance表恢復正常

4、故障原因：

1）主機在3月4日晚6點自動執行對地州查詢使用者授權時，grant和revoke語句阻塞在library cache中，造成library cache lock,阻塞程式一直停留在GRANT SELECT ON ACCT.ACCT_BALANCE TO UQRY過程中，使其它對acct_balance表訪問的語句無法正常命中library cache資料，從而導致對acct_balance訪問速度下降。

2）進一步對阻礙的原因進行跟蹤，發現系統中存在使用plsql工具的可疑帳號，該帳號客戶端名為YNTELCOM,使用者名稱為GH@BYN,登陸時間為2010年3月4日13：00點左右，因無法抓取出該帳號操作記錄，阻礙真正原因暫不確定。推斷原因為：①、操作人員執行不可預知SQL語句；②、操作人員使用非正常手段退出plsql工具。

注：關連表資訊

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)表示這個正在執行﹐
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