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，其实我自己不喜欢理解它是一种锁，本质是在其依赖对象上注册上他的信息，等依赖对象失效的时候，知道通知哪些对象失效。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1220125/，如需转载，请注明出处，否则将追究法律责任。