【效能調整】等待事件(八) library cache locks and pins
Library cache locks and library cache pin waits:
Library cache lock和pin等待能夠hang住例項，並且在少數情況下整個rac叢集的例項都能被其hang住。
為什麼需要library cache locks?
Library cache locks(庫快取鎖)又被稱作parse locks(解析鎖)，用來維護物件以及物件的依賴物件(如sql)之間的依賴機制。例如，如果一個物件定義需要修改或者解析鎖將被終止，那麼依賴物件必須失效。這種依賴由library cache locks維護。舉例：如果從一個表中刪除一個欄位，那麼所有依賴於這個表的sql都要失效，並且在下次訪問這個物件的時候需要重新解析,Library cache locks就是為實現這種追蹤機制而設計的。
為什麼需要library cache pins?
Library cache pins處理當前依賴物件的執行。例如：當一個會話正在執行或者訪問一個依賴物件(sql)的時候，其基礎物件不能被修改。所以，在解析鎖釋放一個library cache object(庫快取物件)之前，必須獲得獨佔模式的library cache pins（通俗的說先library cache lock到object，如果需要修改資料字典，那麼就要pin住）。如果一個session正在執行一個sql，那麼library cache pins將不可用，將會在library cache pins等待。一般來說，這種情況發生在長時間執行的sql語句。
比較繞，通俗的講：
哪種情況下使用library cache lock?
這個事件控制客戶之間library cache的一致性。在一個object控制程式碼請求一個lock，是為了不讓其他的客戶訪問相同的物件。這個客戶可以在相對長的一段時間內維護依賴（例如，其他的客戶不能修改object），這個鎖同時也獲得了object的位置。
資料庫物件相關的解析或者編譯sql或者pl/sql語句(table,view,procedure,function,package,package body,trigger,index,cluster,synonym)時使用，當解析或者編譯完成時釋放，cursor(sql and plsql區域)，pipes和其他transient物件不使用這種lock。
這種lock是死鎖敏感的，並且操作是同步的。
哪些地方使用library cache pin?
這個事件管理library cache 的一致性，pin住一個object將導致heaps被load進記憶體，如果一個客戶想要修改或者檢查某個object，這個客戶必須在lock之後請求一個pin。
Pin不是死鎖敏感的，因為是搶佔式的獲得的，同樣操作也是同步的。
為什麼需要兩種型別的鎖？
Lock和pin都是為了保護library cache的物件訪問的，locks管理程式間的一致性，而pin管理cache的一致性。為了訪問一個物件，一個程式必須先lock物件控制程式碼，然後再pin住物件heap。
請求lock和pin都會產生等待，這是一個肯能的競爭源頭，但是並沒有NOWAIT的請求模式。
如何降低library cache lock和pin?
降低library cache lock：
通過增大shared pool size，或者設定cursor_sharing為similar，或者將一些特殊操作，例如批量收集統計資訊的job與其他的維護job從oltp中分離，儘量在系統不繁忙的時候進行。
降低library cache pin：
減少物件定義的修改，比如alter truncate drop gather statistics等操作儘量在系統不繁忙時進行。
X$kgllk,x$kglpn,x$kglob
X$kgllk是所有object上的鎖結構的表，x$kglob則是資源結構表,x$kglpn是所有的library cahe pin的表。
X$kglob.kglhdadr為資源結構(resource structure)的指標，可以推測，kglhdadr就是KGL handle address，即kgl的控制程式碼地址。X$kgllk作為一個鎖的結構，x$kgllk.kgllkhdl這個欄位可以認為是kgl lock handle，指向x$kglob.kglhdadr來lock object。同樣的，x$kglpn作為一個pin的結構，x$kglpn.kglpnhdl這個欄位可以認為是kgl pin handle，指向x$kglob.kglhdadr來pin一個resource。給出一個類推：x$kglob作為一個資源結構，而x$kgllk作為library cache locks的鎖結構。對於library cache pin，x$kglpn作為一個pin結構，x$kglpn使用kglpnhdl來pin住資源。結合下面一個例子看看就很清楚了。
建立一個簡單的例子來create library cache locks和pin waits
Create or replace procedure test_kgllk(l_sleep in Boolean,
l_conpile in boolean) as
Begin
If (l_sleep) then
sys.dbms_lock.sleep(60);
elsif (l_compile) then
execute immediate 'alter procedure test_kgllk compile';
end if;
End;
/
上面的例子中，建立了一個過程，這個過程接受兩個boolean引數:sleep和compile。
第一個引數為true則允許過程sleep一分鐘，第二個引數為true則允許過程重新編譯自己。
然後在兩個不同的session中分別執行：
Session 1:exec test_kgllk(true,false);
Session 2:exec test_kgllk(false,true);
這時候兩個session將都會等待。
下面來做一些測試：
在session 145:
SQL> select sid from v$mystat where rownum = 1;
SID
----------
145
SQL> exec test_kgllk(true,false);
在session 149:
SQL> select sid from v$mystat where rownum = 1;
SID
----------
149
SQL> exec test_kgllk(false,true);
在session 138:
SQL> select sid from v$mystat where rownum = 1;
SID
----------
138
SQL>
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 --ses.ksuudlna username,
4 --ses.ksuseunm machine,
5 --ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 pn.kglpncnt pin_cnt,
8 pn.kglpnmod pin_mode,
9 pn.kglpnreq pin_req,
10 w.state,
11 w.event,
12 w.wait_Time,
13 w.seconds_in_Wait
14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,
15 --,lk.kgllkhdl,lk.kglhdpar
16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
20 and ob.kglhdadr = pn.kglpnhdl
21 and pn.kglpnuse = ses.addr
22 and w.sid = ses.indx
23 order by seconds_in_wait desc;
SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------- ---------------------------------------------------------------- ---------- ---------------
145 19 TEST_KGLLK 3 2 0 WAITING PL/SQL lock timer 0 18
149 24 TEST_KGLLK 0 0 3 WAITING library cache pin 0 15
149 24 TEST_KGLLK 3 2 0 WAITING library cache pin 0 15
SQL>
一分鐘後session145執行完成，檢視：
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 --ses.ksuudlna username,
4 --ses.ksuseunm machine,
5 --ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 pn.kglpncnt pin_cnt,
8 pn.kglpnmod pin_mode,
9 pn.kglpnreq pin_req,
10 w.state,
11 w.event,
12 w.wait_Time,
13 w.seconds_in_Wait
14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,
15 --,lk.kgllkhdl,lk.kglhdpar
16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
20 and ob.kglhdadr = pn.kglpnhdl
21 and pn.kglpnuse = ses.addr
22 and w.sid = ses.indx
23 order by seconds_in_wait desc;
SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------- ---------------------------------------------------------------- ---------- ---------------
149 24 TEST_KGLLK 0 0 3 WAITING library cache pin 0 72
149 24 TEST_KGLLK 3 2 0 WAITING library cache pin 0 72
再開一個session，在session 137：
SQL> select sid from v$mystat where rownum = 1;
SID
----------
137
SQL> exec test_kgllk (false, true);
可以看到：
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 --ses.ksuudlna username,
4 --KSUSEMNM module,
5 --ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 lk.kgllkcnt lck_cnt,
8 lk.kgllkmod lock_mode,
9 lk.kgllkreq lock_req,
10 w.state,
11 w.event,
12 w.wait_Time,
13 w.seconds_in_Wait
14 from x$kgllk lk, x$kglob ob, x$ksuse ses, v$session_wait w
15 where lk.kgllkhdl in (select kgllkhdl from x$kgllk where kgllkreq > 0)
16 and ob.kglhdadr = lk.kgllkhdl
17 and lk.kgllkuse = ses.addr
18 and w.sid = ses.indx
19 order by seconds_in_wait desc ;
SID SERIAL# OBJ_NAME LCK_CNT LOCK_MODE LOCK_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------- ---------------------------------------------------------------- ---------- ---------------
149 24 TEST_KGLLK 1 1 0 WAITING library cache pin 0 115
149 24 TEST_KGLLK 1 3 0 WAITING library cache pin 0 115
145 19 TEST_KGLLK 1 1 0 WAITING SQL*Net message from client 0 58
137 7 TEST_KGLLK 0 0 2 WAITING library cache lock 0 21
137 7 TEST_KGLLK 1 1 0 WAITING library cache lock 0 21
SQL>
而cancel掉137的執行，則有：
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 --ses.ksuudlna username,
4 --KSUSEMNM module,
5 --ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 lk.kgllkcnt lck_cnt,
8 lk.kgllkmod lock_mode,
9 lk.kgllkreq lock_req,
10 w.state,
11 w.event,
12 w.wait_Time,
13 w.seconds_in_Wait
14 from x$kgllk lk, x$kglob ob, x$ksuse ses, v$session_wait w
15 where lk.kgllkhdl in (select kgllkhdl from x$kgllk where kgllkreq > 0)
16 and ob.kglhdadr = lk.kgllkhdl
17 and lk.kgllkuse = ses.addr
18 and w.sid = ses.indx
19 order by seconds_in_wait desc ;
SID SERIAL# OBJ_NAME LCK_CNT LOCK_MODE LOCK_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------- ---------------------------------------------------------------- ---------- ---------------
SQL>
現在只剩下149的session還在執行中，查詢pin的資訊：
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 --ses.ksuudlna username,
4 --ses.ksuseunm machine,
5 --ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 pn.kglpncnt pin_cnt,
8 pn.kglpnmod pin_mode,
9 pn.kglpnreq pin_req,
10 w.state,
11 w.event,
12 w.wait_Time,
13 w.seconds_in_Wait
14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,
15 --,lk.kgllkhdl,lk.kglhdpar
16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
20 and ob.kglhdadr = pn.kglpnhdl
21 and pn.kglpnuse = ses.addr
22 and w.sid = ses.indx
23 order by seconds_in_wait desc;
SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------- ---------------------------------------------------------------- ---------- ---------------
149 24 TEST_KGLLK 0 0 3 WAITING library cache pin 0 358
149 24 TEST_KGLLK 3 2 0 WAITING library cache pin 0 358
SQL>
上面的一些測試可以這樣概述，一個過程正在編譯本身，一個過程正在編譯一個正在執行的過程
對於一個過程正在編譯一個正在執行的過程，測試一下：
Create or replace procedure sys.test_kgllk1(l_sleep in Boolean,
l_compile in boolean) as
Begin
If (l_sleep) then
sys.dbms_lock.sleep(60);
elsif (l_compile) then
execute immediate 'alter procedure test_kgllk compile';
end if;
End;
/
在session 145：
SQL> exec test_kgllk(true,false);
在session149：
SQL> exec test_kgllk1(false,true);
在session138：
SQL> select a.SID,
2 a.SEQ#,
3 a.EVENT,
4 a.P1TEXT,
5 a.P2TEXT,
6 a.P3TEXT,
7 a.WAIT_CLASS,
8 a.STATE
9 from v$session_wait a
10 where a.sid in (145, 149);
SID SEQ# EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------
145 200 PL/SQL lock timer duration Idle WAITING
149 172 library cache pin handle address pin address 100*mode+namespace Concurrency WAITING
可以看到，145正在等待pl/sql lock timer，而149在等待library cache pin，其中library cache pin的引數如下：
P1: handle address
P2: pin address
P3: 100*mode+namespace
這樣就很好理解，當過程編譯本身的時候，出現的:
PIN_CNT PIN_MODE PIN_REQ
---------- ---------- ----------
3 2 0
0 0 3
第一行是在等待lock timer，此時pin count為3，pin mode 為2，顯然正在執行的過程已經擁有了pin，而第二行的pin count為0，pin mode為0，pin mode為3，顯然是其本身又在請求pin，這個執行過程將掛起，因為執行過程中的過程永遠也得不到對其本身的pin。
而新增了137session執行exec test_kgllk (false, true);
149session是在等待pin，而137session在等待lock。
149session擁有了library cache lock，而137在等待library cache lock
149session在等待library cache pin（其本身）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-676381/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- 【效能調整】等待事件(一)事件
- 【效能調整】等待事件(二)事件
- 【效能調整】等待事件(三) 常見等待事件（一）事件
- 【效能調整】等待事件(四) 常見等待事件（二）事件
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin 等待事件事件
- 【效能調整】等待事件(五)log相關等待事件
- 等待事件Cursor: Pin S Wait On X和Library Cache Load Locks可能意味著過度的記憶體調整事件AI記憶體
- 【效能調整】等待事件(九) latch原理事件
- latch:library cache lock等待事件事件
- 【效能調整】等待事件read by other session事件Session
- 【效能調整】等待事件 enq: SQ - contention事件ENQ
- 解決library cache pin等待事件事件
- enq：Library cache lock/pin等待事件ENQ事件
- library cache pin等待事件的模擬事件
- 【效能調整】等待事件(十) 10g中的latch等待事件
- 【效能調整】等待事件(七) io相關等待查詢指令碼事件指令碼
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- 俺也談談 library cache lock 等待事件事件
- 模擬cache buffers chains與library cache pin等待事件AI事件
- 【效能調整】等待事件(六) direct path read&write事件
- oracle library cache相關的等待事件及latchOracle事件
- library cache pin等待分析
- 'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'型別的等待事件Mutex型別事件
- 定位Library Cache pin,Library Cache lock等待的解決方法
- buffer cache深度分析及效能調整（五）
- buffer cache深度分析及效能調整（六）
- buffer cache深度分析及效能調整（四）
- 等待模擬-library cache 軟解析
- 查詢Library Cache Pin等待原因
- 由row cache lock等待事件引起的效能問題事件
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 分析解決因”library cache pin”等待