概念掃盲

首先理解一下有關shared pool中的這兩個概念：

1、Hash bucket

當一條 SQL 語句進入library cache 的時候，先將 SQL 文字轉化為對應 ASCII 數值，然後對該這些 ASCII 數值進行 hash 函式的運算，傳入函式的是 SQL 語句的名稱（name，對於 SQL 語句來說其 name 就是 SQL 語句的文字）以及名稱空間（namespace，對於 SQL語句來說是“SQL AREA”，表示共享遊標。可以從檢視 v$librarycache 裡找到所有的 namespace）。運用hash 函式後得到一個值，該值就是 hash bucket 的號碼，從而該 SQL 語句被分配到該號的 hash bucket 裡去。實際上，hash bucket 就是透過串連起來的物件控制程式碼才體現出來的，它本身是一個邏輯上的概念，是一個邏輯組，而不像物件是一個具體的實體。SQL 語句相關的物件有很多（最直觀的就是 SQL 語句的文字），這些物件都存放在 library cache 裡，它們都透過控制程式碼來訪問。可以把 library cache理解為一本書，而SQL 語句的物件就是書中的頁，而控制程式碼就是目錄，透過目錄可以快速定位到指定內容的頁。

2、library cache object handle

如上面所說，library cache使用多個 hash bucket 來管理，每個 hash bucket後面都串連著多個控制程式碼（library cache object handle），這些物件控制程式碼存放了物件的名稱（name）、物件所屬的名稱空間（namespace）、有關物件的一些標記（比如物件是否為只讀、為本地物件還是遠端物件、是否被 pin 在記憶體中等等）以及有關物件的一些統計資訊等。而且，物件控制程式碼中還存放了當前正在 lock 住和pin 住該物件的使用者列表、以及當前正在等待 lock 和pin 該物件的使用者列表。

Library Cache Lock/Pin

Library cache latch

Library cache latch用來控制對library cache object的併發訪問。

由於在對object操作過程中，在handle持有的 lock很容易被打破(修改或刪除等dml操作)， Oracle為了保護這個lock，引入了library cache latch機制，也就是說在獲得library cachelock之前，需要先獲得library cache latch，當獲得library cache lock之後就釋放librarycache latch。如果某個librarycache object沒有在記憶體中，那麼這個lock就不能被獲取，這個時候需要獲得一個library cache load lock latch，然後再獲取一個librarycache load lock,當load lock獲得之後就釋放library cache load lock latch。

Pin和Lock

Library cache Handle裡儲存了lock和pin的資訊。而且在Library cache handle 和child cursor 上都有lock 和pin。它們稱為library cache lock和library cache pin。

Library Cache lock模式

（1）Share(S)：當讀取一個library cache object的時候獲得

（2）Exclusive(X)：當建立/修改一個library cache object的時候獲得

（3）Null(N)：用來確保物件依賴性

NULL鎖主要的目的就是標記某個物件是否有效。比如一個SQL語句在解析的時候獲得了NULL 鎖，如果這個SQL的物件一直在共享池中，那麼這個NULL鎖就會一直存在下去，當這個SQL語句所引用的表被修改之後，這個NULL鎖就被打破了，因為修改這個SQL語句的時候會獲得Exclusive 鎖，由於NULL鎖被打破了，下次執行這個SQL的時候就需要從新編譯。

Library Cache pin模式
0 no lock/pin held（N）

2 share mode（S）
3 exclusive mode（X）

Library cachelock/pin是用來控制對librarycache object的併發訪問的。Lock管理併發，pin管理一致性，lock是針對於librarycache handle, 而pin是針對於heap。

比如使用者A以N模式持有某個handle lock，並且把該handle中的object以S模式pin到了記憶體中，此刻使用者B發出一個請求，想要編輯該object，首先B會在handle上獲得X模式的lock，然後再獲取該object上X模式的pin，此時如果使用者A還持有S的pin，使用者B將等待A釋放該pin後再去以X模式的pin去編譯該object。

示例

借用eygle的方法借用sleep函式測試一下Library cache pin等待發生過程（前人栽樹，後人乘涼，嘻嘻）

SQL> create or replace procedure pining

2 is

3 begin

4 null;

5 end;

6 /

Procedure created.

SQL> create or replace procedure calling

2 is

3 begin

4 pining;

5 dbms_lock.sleep(5000);

6 end;

7 /

calling過程呼叫pining過程時，這樣在Libiary中解析pining過程時lock=N，pin=S

sessionA：

SQL> exec calling;

sessionB：

SQL> grant execute on pining to scott;

Grant succeeded.

/*奇怪的是grant並沒有觸發pin等待，可能是oracle10g的增強，在Oracle10gR2中，library cache pin被library cache mutex 所取代。具體原因有待驗證，總之，不影響測試*/

SQL> revoke execute on pinling from scott;

回收許可權會使回話掛起

再新開一個會話研究一下：

檢視一下library cache pin等待資訊

SQL> col state for a12

SQL> col event for a20

SQL> select sid,seq#,event,p1,p1raw,state from v$session_wait where event like 'library%';

SID SEQ# EVENT P1 P1RAW STATE

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

130 24 library cache pin 645719616 267CE640 WAITING

輸出結果中P1 為Library Cache Handle Address，pnraw記錄的是十六進位制的表示，透過這個地址結合X$KGLOB檢視，檢視一下物件資訊

SQL> col KGLNAOWN for a12

SQL> col KGLNAOBJ for a15

SQL> select addr,kglhdadr,kglnaown,kglnaobj,kglnahsh from x$kglob where kglhdadr like '267CE640';

ADDR KGLHDADR KGLNAOWN KGLNAOBJ KGLNAHSH

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

B7ECBC18 267CE640 SYS PINING 2030642293

kglnahsh代表的就是hash_value

x$kglpn檢視中包含著Libiary Cache pin等待的記錄，可以透過該檢視與v$session結合找出哪些會話持有該handle，下面的顯示sid為133

SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ

2 from v$session a,x$kglpn b

3 where a.saddr=b.kglpnuse and b.kglpnhdl = '267CE640' and b.KGLPNMOD<>0

4 /

SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL

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

KGLPNLCK KGLPNMOD KGLPNREQ

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

133 SYS sqlplus@node1 B7ECAFC0 27BF3F6C 29EECDC0 29EECDC0 267CE640

(TNS V1-V3)

27BB3D1C 2 0

接下來的查詢可以看出該會話等待PL/SQL lock timer

SQL> col p1text for a10

SQL> select SID,SEQ#,EVENT,P1TEXT from v$session_wait where sid=133;

SID SEQ# EVENT P1TEXT

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

133 21 PL/SQL lock timer duratio

透過v$sqltext，或者v$sqlarea就可以查出具體的sql

SQL> select a.sid,a.serial#,c.spid,b.address,b.hash_value,b.sql_text

2 from v$session a,v$sqltext b,v$process c

3 where a.sid='133' and a.sql_hash_value=b.hash_value and a.paddr=c.addr

4 /

SID SERIAL# SPID ADDRESS HASH_VALUE

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

SQL_TEXT

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

133 7 4544 267CEA34 3918317640

BEGIN calling; END;

[oracle@node1 ~]$ ps -ef | grep 4544 | grep -v 'grep'

oracle 4544 4543 0 16:16 ? 00:00:00 oracleorcl_dup (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

[oracle@node1 ~]$ kill -9 4544

/*由於用到的是sleep函式，此時該會話被休眠，如果資料庫中kill session會話也將處於掛起狀態*/

此時會話被終止，revoke會話將結束等待

SQL> exec calling;

BEGIN calling; END;

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

另一個會話：

SQL> revoke execute on pining from scott;

Revoke succeeded.

此處分享一個SQL，網上找到的，用來查詢當前的堵塞程式，謝謝無私奉獻的不知名網友~

select Distinct /*+ ordered */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

od.to_owner object_owner,

od.to_name object_name,

oc.Type,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_requested,

xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql

from dba_kgllock w, dba_kgllock h, v$session w1,

v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh

where

(((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and

(((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr

and od.to_address = w.kgllkhdl

and od.to_name=oc.Name

and od.to_owner=oc.owner

and w1.sid=xw.KGLLKSNM

and h1.sid=xh.KGLLKSNM

and (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)

and (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) order by h1.sid;

查詢結果如下：

如何避免pin和lock事件

1、當對使用者的許可權進行管理即進行”grant” “revoke”時,可能產生”library cache pin”，可以透過角色來對終端使用者進行授權或收回授權,而不要用顯性的方式即直接對終端使用者授權或收回授權,從而避免產生”library cache pin”。

2、高峰時的object管理在系統執行高峰時對資料庫object的管理可能產生”library cache pin”，儘量把對資料庫object的管理安排到負載相對較小的時侯。

3、如果Package或過程中存在複雜的、互動的依賴以來關係極易導致library cache pin的出現，在PL/SQL包中存在大量的互跨的依賴性(dependency)，儘可能按等級來排列它們的結構。

4、合理控制shared pool大小。

Oracle 之pin和lock