library cache pin等待分析

上次跟大家分享的時候，我說如果有DDL沒執行完，會導致所有對它的操作被hang在library cache lock上。
可是前幾天CRM庫上在執行DDL期間，後臺絕大多數的等待是library cache pin。把這個錯誤糾正一下。


SESSION 1:執行DDL操作：
alter table wxh_tbd modify object_name not null;--------------表稍微大點，可以讓這個操作的時間長點。

SESSION 2:執行查詢操作
select count(*) from wxh_tbd where rownum=1;

會發現連查詢都會被hang住，後臺在等待library cache lock.

根據v$session_wait的p1raw可以知道等待是發生在wxh_tbd表的handler上的

SQL> col KGLNAOBJ for a20
SQL> select KGLNAOBJ from x$kglob where kglhdadr = '2A7938F4';

KGLNAOBJ
--------------------
WXH_TBD

alter session set events 'immediate trace name library_cache level 10';----------dump出來看看
BUCKET 103643:
  LIBRARY OBJECT HANDLE: handle=2a7938f4 mutex=2A7939A8(0)
  name=SCOTT.WXH_TBD ----------------------------------------------------LCO的名字
  hash=cfe366d2fa989e1901d16ba139c394db timestamp=07-05-2011 11:12:47
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=1 hpc=0002 hlc=0002
  lwt=2A793950[307D2360,307D2360] ltm=2A793958[2A793958,2A793958]
  pwt=2A793934[2A793934,2A793934] ptm=2A79393C[2A79393C,2A79393C]
  ref=2A793970[2A793970,2A793970] lnd=2A79397C[2A76CC74,2A759F44]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2f1ccdd4     2 DEP[01]
     2f1453f8     2 DEP[01]
    LOCK OWNERS:
        lock     user  session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    307dad4c 32f3cc94 32f3cc94     1 X    [00]-------------------------------SESSION 1以x模式擁有了library cache lock
    LOCK WAITERS:
        lock     user  session count mode
    -------- -------- -------- ----- ----
    307d2344 32f2a014 32f2a014     0 S---------------------------------------SESSION 2想以s模式擁有library cache lock但是不能獲得，產生等待
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    30785520 32f3cc94 32f3cc94        0     1 X    0701-------------------------------SESSION 1以x模式擁有了library cache pin
   
問題到這裡，似乎還是比較簡單的，我們繼續

SESSION 3:執行跟SESSION 2一樣的查詢
select count(*) from wxh_tbd where rownum=1;
這個時候檢視後臺等待，又多了一個library cache pin等待，根據p1raw引數，可以知道等待是發生在查詢sql上的

SQL> col KGLNAOBJ for a80
SQL> select KGLNAOBJ from x$kglob where kglhdadr = '2A5E541C';

KGLNAOBJ
--------------------------------------------------------------------------------
select count(*) from wxh_tbd where rownum=1

   
alter session set events 'immediate trace name library_cache level 10';----------dump出來看看
BUCKET 51618:
  LIBRARY OBJECT HANDLE: handle=2a7520c4 mutex=2A752178(1)
  name=select count(*) from wxh_tbd where rownum=1 ----------------------------------------------------LCO的名字
  hash=a359d0118530b1534deb83cbbad4c9a2 timestamp=07-05-2011 11:10:39
  namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
  kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=3 hpc=0006 hlc=0006
  lwt=2A752120[2A752120,2A752120] ltm=2A752128[2A752128,2A752128]
  pwt=2A752104[2A752104,2A752104] ptm=2A75210C[2A75210C,2A75210C]
  ref=2A752140[2A752140,2A752140] lnd=2A75214C[328DB424,2A75DCA8]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2f145048     0 [60]
    LOCK OWNERS:
        lock     user  session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    30798c70 32f2a014 32f2a014     1 N    [00]-----------------------------SESSION 2以NULL模式獲得了cursor上的library cache lock
    30798b90 32f2b2dc 32f2b2dc     1 N    [00]-----------------------------SESSION 3以NULL模式獲得了cursor上的library cache lock
    LIBRARY OBJECT: bject=2f214e44
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 2f214dd0  2f214a84 2a751f80-------------------------------------子lco的handler，根據它從dump檔案裡找到遊標的子lco
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 2a752054 2f214edc I/P/A/-/-    0 NONE   00
        
-----------------遊標的子lco        
  LIBRARY OBJECT HANDLE: handle=2a751f80 mutex=2A752034(0)---------------------子lco
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=3 hpc=0006 hlc=0006
  lwt=2A751FDC[2A751FDC,2A751FDC] ltm=2A751FE4[2A751FE4,2A751FE4]
  pwt=2A751FC0[307642C4,307642C4] ptm=2A751FC8[2A751FC8,2A751FC8]
  ref=2A751FFC[2F214A84,2F214A84] lnd=2A752008[2A752008,2A752008]
    CHILD REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2f214a84     0 CHL[02]
    LOCK OWNERS:
        lock     user  session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    30798b20 32f2a014 32f2a014     1 N    [00]-----------------------------SESSION 2以NULL模式獲得了cursor上的library cache lock
    30793810 32f2b2dc 32f2b2dc     1 N    [00]-----------------------------SESSION 3以NULL模式獲得了cursor上的library cache lock
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    30764040 32f2a014 32f2a014        0     0 X    0041-----------------------------SESSION 2以X模式獲得了子lco上的library cache pin
    PIN WAITERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    307642a8 32f2b2dc 32f2b2dc        0     0 S    0000-----------------------------SESSION 3想以S模式獲得了子LCO上的library cache pin產生等待
   
檢視這個情況下，wxh_tbd下的lock有什麼變化
BUCKET 103643:
  LIBRARY OBJECT HANDLE: handle=2a7938f4 mutex=2A7939A8(0)
  name=SCOTT.WXH_TBD
  hash=cfe366d2fa989e1901d16ba139c394db timestamp=07-05-2011 11:12:47
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=1 hpc=0002 hlc=0002
  lwt=2A793950[307D2360,307D2360] ltm=2A793958[2A793958,2A793958]
  pwt=2A793934[2A793934,2A793934] ptm=2A79393C[2A79393C,2A79393C]
  ref=2A793970[2A793970,2A793970] lnd=2A79397C[2A76CC74,2A759F44]
    DEPENDENCY REFERENCES:
    reference latch flags
    --------- ----- -------------------
     2f1ccdd4     2 DEP[01]
     2f1453f8     2 DEP[01]
    LOCK OWNERS:
        lock     user  session count mode flags
    -------- -------- -------- ----- ---- ------------------------
    307dad4c 32f3cc94 32f3cc94     1 X    [00]
    LOCK WAITERS:
        lock     user  session count mode
    -------- -------- -------- ----- ----
    307d2344 32f2a014 32f2a014     0 S
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    30785520 32f3cc94 32f3cc94        0     1 X    0701
發現SESSION 3沒有對wxh_tbd產生任何影響

我們基本可以總結出如下的順序：
1)
SESSION 1,獲得了wxh_tbd上的x模式的library cache lock和x模式的library cache pin
2)
SESSION 2,首先獲得cursor上的null模式的library cache lock和子lco上null模式的library cache lock和x模式的library cache pin,然後在
獲得cursor參照物件wxh_tbd上s模式的library cache lock時發生等待，因為session 1已經以x模式持有了library cache lock.
3)
session 3跟session 2的過程差不多。首先獲得cursor上的null模式的library cache lock和子lco上null模式的library cache lock和x模式的library cache pin
但是由於session 2已經獲得了子LCO上x模式的 library cache pin，因此發生等待。但是這個過程似乎與dump出來的不符，因為dump出來的內容是，SESSION 3
等待的是s模式的library cache pin非X模式。ORACLE比較聰明啦，發現已經有會話在硬解析了，他自己就以S模式等著了，就像READ BY OTHER SESSION似的，發現
已經有會話在讀取資料塊了，自己就等著了，不去物理讀了。SESSION 3之所以沒產生wxh_tbd上的library cache lock等待，是因為還沒到那一步，直接在子lco
的library cache pin上就堵住了

如果這個時候，存在大量併發的這個查詢(SQL文字一樣)，後臺就會產生大量的library cache pin.
如果發出的語句都不一樣，那就是大量的library cache lock了，這個lock就是wxh_tbd上的。

 

會話	CURSOR		WXH_TBD
	LIBRARY CACHE LOCK	LIBRARY CACHE PIN	LIBRARY CACHE LOCK	LIBRARY CACHE PIN
SESSION 1			X	X
SESSION 2	NULL	X	S
SESSION 3	NULL	S