oracle breakable parse lock 易碎解析鎖

非常舊的一個概念了，今天被人問起，本來是想直接google一個讓他看，可是半天也沒找到一個比較好的對此問題解釋的文章。
breakable parse lock
叫它鎖比較牽強，一般鎖是為了保護併發修改的，但是它的含義更準確的說是：一個SQL語句或著PL/SQL等物件在其依賴物件上加的/註冊上的 一個“依賴”，以null級別的library cache lock的形式表示出來。當其依賴物件進行DDL等變更的時候，透過檢視其上註冊列表，然後讓這些物件失效。文字還是比較繞，舉個例子就很清楚了
select count(*) from b;
這個SQL語句或者叫cursor就在其依賴物件B上加了一個null的library cache lock。如果哪天B進行了DDL操作了，ORACLE檢視B上註冊的依賴列表，就知道該讓哪些依賴物件失效了。


簡單的實驗：（11.2.0.3）
select count(*) from b where rownum<1;
select count(*) from b where rownum<2;
select count(*) from b where rownum<4;
select count(*) from b where rownum<3;
-------------------dump共享池
Alter session set events 'immediate trace name library_cache level 10';
SELECT A.VALUE || B.SYMBOL || C.INSTANCE_NAME || '_ora_' || D.SPID ||
       '.trc' TRACE_FILE
  FROM (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') A,
       (SELECT SUBSTR(VALUE, -6, 1) SYMBOL
          FROM V$PARAMETER
         WHERE NAME = 'user_dump_dest') B,
       (SELECT INSTANCE_NAME FROM V$INSTANCE) C,
       (SELECT SPID
          FROM V$SESSION S, V$PROCESS P, V$MYSTAT M
         WHERE S.PADDR = P.ADDR
           AND S.SID = M.SID
           AND M.STATISTIC# = 0) D;


/u01/app/oracle/diag/rdbms/dlsp/dlsp/trace/dlsp_ora_15991268.trc




直接搜尋物件MONITOR.B:
Bucket: #=87975 Mutex=700000379478ec0(0, 5631, 0, 6)
  LibraryHandle:  Address=7000003406362e0 Hash=e8d557a7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=MONITOR.B   FullHashValue=c81c887fb609499e2952a19ee8d557a7 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=18379 OwnerIdn=35
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=7 TotalPinCount=7
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0
    Concurrency:  DependencyMutex=700000340636390(0, 5, 0, 0) Mutex=700000340636410(1298, 74, 0, 6)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
      Lock=700000340636370[700000340636370,700000340636370]
      Pin=700000340636350[700000340636350,700000340636350]
      LoadLock=7000003406363c8[7000003406363c8,7000003406363c8]
    Timestamp:  Current=12-18-2013 16:44:01
    HandleReference:  Address=700000340636480 Handle=700000369523fe8 Flags=OWN[200]
    ReferenceList:
      Reference:  Address=700000339dca730 Handle=70000036b82f5a8 Flags=DEP[01]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=0
      Reference:  Address=700000352df4878 Handle=70000036b9f78d0 Flags=DEP[01]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=0
      Reference:  Address=70000034becb550 Handle=700000321fefd90 Flags=DEP[01]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=0
      Reference:  Address=700000327c7dd08 Handle=7000003477d14a8 Flags=DEP[01]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=0
      Reference:  Address=7000003417c4e50 Handle=70000035ee3f178 Flags=DEP[01]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=0
我們看到ReferenceList部分有5個依賴物件，這些物件就是註冊在物件B上的依賴物件，我們4個SQL為什麼是5個依賴物件，那是因為我的表沒做分析，動態取樣的語句也產生了一個CURSOR。
如果我們對錶B做了DDL操作，再DUMP，會發現這些依賴物件都是無效了（檢視FLAGS標誌）
Bucket: #=87975 Mutex=700000379478ec0(0, 5648, 0, 6)
  LibraryHandle:  Address=7000003406362e0 Hash=e8d557a7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=MONITOR.B   FullHashValue=c81c887fb609499e2952a19ee8d557a7 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=18379 OwnerIdn=35
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=3 ActiveLocks=0 TotalLockCount=17 TotalPinCount=18
    Counters:  BrokenCount=4 RevocablePointer=2 KeepDependency=0 BucketInUse=13 HandleInUse=13 HandleReferenceCount=0
    Concurrency:  DependencyMutex=700000340636390(0, 15, 0, 0) Mutex=700000340636410(1298, 172, 0, 6)
    Flags=PIN/TIM/[00000801]
    WaitersLists:
      Lock=700000340636370[700000340636370,700000340636370]
      Pin=700000340636350[700000340636350,700000340636350]
      LoadLock=7000003406363c8[7000003406363c8,7000003406363c8]
    Timestamp:  Current=12-18-2013 17:18:06
    HandleReference:  Address=700000340636480 Handle=700000369523fe8 Flags=OWN[200]
    ReferenceList:
      Reference:  Address=70000033f0e9d00 Handle=700000359c1d168 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
      Reference:  Address=70000033d9e6080 Handle=70000035783b5d8 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
      Reference:  Address=700000339dca730 Handle=70000036b82f5a8 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
      Reference:  Address=700000352df4878 Handle=70000036b9f78d0 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
      Reference:  Address=70000034becb550 Handle=700000321fefd90 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
      Reference:  Address=700000327c7dd08 Handle=7000003477d14a8 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
      Reference:  Address=7000003417c4e50 Handle=70000035ee3f178 Flags=DEP/INV[05]
        Timestamp=12-18-2013 16:44:01 InvalidatedFrom=8
    ObjectFreed=last freed from PNDL addn data FUP


由於這種null型別的library cache lock可以被DDL等操作打破，因此被稱為breakable parse lock，其實我自己不喜歡理解它是一種鎖，本質是在其依賴物件上註冊上他的資訊，等依賴物件失效的時候，知道通知哪些物件失效。