背景

  在使用oracle 10704 event分析獲取鎖lock及死鎖deadlock系列九 
  http://blog.itpub.net/9240380/viewspace-1819341/，本來想分析易碎解析鎖，發現沒有找到任何有價值的資訊，本文想回答這個問題。

結論

1,ORACLE的易碎解析鎖，從實質說不是鎖，而是一種類似於latch一種的東西
2,易碎解析鎖會持null級別鎖，只要基表發生ddl,馬上就會被中斷
3,首次對於基表DDL，會在library cache中增加新的引用物件
4,新增基於基表的SELECT或DML查詢，也會對應增新的引用物件
5,基表會在library cache分配1個bucket
  基於基表的select或DML也會分配1個bucket
6,關於trc檔案內容講解，請見下述測試
7，關於library cache dump內容是非常且複雜的，還需要進一步測試
8，研究這個目的在於瞭解ORACLE共享池分配的原理及相關概念，這樣可進一步解決與共享池相關的故障  

引發新的問題

1,library cache dump產生的TRC檔案的組織結構，還需進一步理解
2,梳理出來共享池分配的大體機制

測試

1，即使沒有找到鎖相關的資訊，而解析鎖是用於解析SQL，且儲存在共享池中，所以我們換個思路，轉儲library cache看下是否有什麼線索
   測試仍基於上文資料


SQL> select count(a) from t_parselock where rownum=1;


  COUNT(A)
----------
         0




SQL> alter session set tracefile_identifier='parse_lock_test';


Session altered.


SQL> Alter session set events 'immediate trace name library_cache level 10';


Session altered.




[oracle@seconary ~]$ cd /oracle/diag/rdbms/guowang/guowang/trace/
[oracle@seconary trace]$ ll -l *parse_lock_test*
-rw-r----- 1 oracle oinstall 12439872 Oct 30 06:04 guowang_ora_10493_parse_lock_test.trc


直接在TRC檔案過濾scott.t_parselock，可見它有3個引用物件（請檢視ReferenceList），也就是說這3個物件是依賴物件
Bucket: #=82298 Mutex=d5fcfbb8(0, 31, 0, 6) 
  LibraryHandle:  Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD --可見持鎖模式為0，即null鎖，且其狀態為valid
    ObjectName:  Name=SCOTT.T_PARSELOCK   
      FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84 
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=0 TotalLockCount=10 TotalPinCount=12 
    Counters:  BrokenCount=6 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=11 HandleInUse=11 
    Concurrency:  DependencyMutex=cf334810(0, 14, 0, 0) Mutex=cf334888(241, 130, 0, 6) 
    Flags=PIN/TIM/[00002801] 
    WaitersLists:  
      Lock=cf3347f0[cf3347f0,cf3347f0] 
      Pin=cf334800[cf3347d0,cf3347d0] 
    Timestamp:  Current=10-30-2015 03:48:37 
    HandleReference:  Address=cf334900 Handle=de1e0d78 Flags=OWN[200] 
    ReferenceList:  
      Reference:  Address=c8f173c8 Handle=de6d8460 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05] --flags=dep/inv[05]表示這個依賴物件已經無效了
        Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8 
    LibraryObject:  Address=c8fb5060 HeapMask=0000-0701-0201 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000] 
      DataBlocks:  
        Block:  #='0' name=KGLH0^e15417a pins=0 Change=NONE   
          Heap=cf358c70 Pointer=c8fb5190 Extent=c8fb5030 Flags=I/-/-/A/-/- 
          FreedLocation=0 Alloc=0.398438 Size=4.000000 LoadTime=4609490310 
        Block:  #='8' name=KGLS^e15417a pins=0 Change=NONE   
          Heap=c8fb5438 Pointer=c95e43f8 Extent=c95e3638 Flags=I/-/-/A/-/- 
          FreedLocation=0 Alloc=0.703125 Size=4.000000 LoadTime=0    




官方文件上說，這種解析鎖非常脆弱，只要對基表發生DDL，其依賴物件馬上變成無效
SQL> alter table t_parselock add b int;


Table altered.




再次檢視TRC檔案的內容（重作一次DUMP LIBRARY CACHE）


Bucket: #=82298 Mutex=d5fcfbb8(0, 45, 0, 6) 
  LibraryHandle:  Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 
    ObjectName:  Name=SCOTT.T_PARSELOCK   
      FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84 
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=3 ActiveLocks=0 TotalLockCount=17 TotalPinCount=20 
    Counters:  BrokenCount=8 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=18 HandleInUse=18 
    Concurrency:  DependencyMutex=cf334810(0, 19, 0, 0) Mutex=cf334888(103, 203, 0, 6) 
    Flags=PIN/TIM/[00000800] 
    WaitersLists:  
      Lock=cf3347f0[cf3347f0,cf3347f0] 
      Pin=cf334800[cf3347d0,cf3347d0] 
    Timestamp:  Current=10-30-2015 03:48:37 
    HandleReference:  Address=cf334900 Handle=de1e0d78 Flags=OWN[200] 
    ReferenceList:  
      Reference:  Address=c8e3b3c8 Handle=de572d58 Flags=DEP[01] --DDL後增1個引用物件，可否理解發生一次DDL，會增加一個引用物件呢
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8f173c8 Handle=de6d8460 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05] 
        Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8 
    ObjectFreed=last freed from PNDL addn data FUP 




繼續測試，驗證下上述的想法,可見這下引用物件沒有變化，可見僅在首次發生DDL才會增加引用物件個數
SQL> alter table t_parselock add c int;


Table altered.


Bucket: #=82298 Mutex=d5fcfbb8(0, 56, 0, 6) 
  LibraryHandle:  Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 
    ObjectName:  Name=SCOTT.T_PARSELOCK   
      FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84 
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=5 ActiveLocks=0 TotalLockCount=24 TotalPinCount=28 
    Counters:  BrokenCount=10 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=25 HandleInUse=25 
    Concurrency:  DependencyMutex=cf334810(0, 25, 0, 0) Mutex=cf334888(129, 291, 0, 6) 
    Flags=PIN/TIM/[00000800] 
    WaitersLists:  
      Lock=cf3347f0[cf3347f0,cf3347f0] 
      Pin=cf334800[cf3347d0,cf3347d0] 
    Timestamp:  Current=10-30-2015 03:48:37 
    HandleReference:  Address=cf334900 Handle=de1e0d78 Flags=OWN[200] 
    ReferenceList:  
      Reference:  Address=c8e3b3c8 Handle=de572d58 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8f173c8 Handle=de6d8460 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05] 
        Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8 
    ObjectFreed=last freed from PNDL addn data FUP 


所以我分析基於基表不同的SQL才會產生新增的引用物件
SQL> select a,b,c from t_parselock;


no rows selected


SQL> select a,c from t_parselock where rownum<=3;


no rows selected


SQL> select b,c from t_parselock where rownum<=9;


no rows selected


確實和我想法一樣，新增3個與基表相關的SELECT查詢語句，即新增3個引用物件
Bucket: #=82298 Mutex=d5fcfbb8(0, 69, 0, 6) 
  LibraryHandle:  Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 
    ObjectName:  Name=SCOTT.T_PARSELOCK   
      FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84 
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=6 ActiveLocks=0 TotalLockCount=27 TotalPinCount=31 
    Counters:  BrokenCount=10 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=35 HandleInUse=35 
    Concurrency:  DependencyMutex=cf334810(0, 29, 0, 0) Mutex=cf334888(123, 344, 0, 6) 
    Flags=PIN/TIM/[00002801] 
    WaitersLists:  
      Lock=cf3347f0[cf3347f0,cf3347f0] 
      Pin=cf334800[cf3347d0,cf3347d0] 
    Timestamp:  Current=10-30-2015 03:48:37 
    HandleReference:  Address=cf334900 Handle=de1e0d78 Flags=OWN[200] 
    ReferenceList:  
      Reference:  Address=c8e313c8 Handle=cfa4f048 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8e333c8 Handle=cf7b2218 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8e363c8 Handle=cf3f05c8 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8e3b3c8 Handle=de572d58 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8f173c8 Handle=de6d8460 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01] 
        Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0 
      Reference:  Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05] 
        Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8 
    LibraryObject:  Address=c8e34c80 HeapMask=0000-0701-0701 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000] 
      DataBlocks:  
        Block:  #='0' name=KGLH0^e15417a pins=0 Change=NONE   
          Heap=cf9f68e0 Pointer=c8e34d68 Extent=c8e34c50 Flags=I/-/-/A/-/- 
          FreedLocation=0 Alloc=0.710938 Size=4.000000 LoadTime=4620871670 
        Block:  #='8' name=KGLS^e15417a pins=0 Change=NONE   
          Heap=c8e34f80 Pointer=c918d690 Extent=c918c8d0 Flags=I/-/-/A/-/- 
          FreedLocation=0 Alloc=0.984375 Size=4.000000 LoadTime=4620871700 
        Block:  #='9' name=KGLS^e15417a pins=0 Change=NONE   
          Heap=c8e35098 Pointer=c918c700 Extent=c918b8d0 Flags=I/-/-/A/-/- 
          FreedLocation=0 Alloc=0.429688 Size=4.000000 LoadTime=4620871720 
        Block:  #='10' name=KGLS^e15417a pins=0 Change=NONE   
          Heap=c8e35120 Pointer=c918b7e0 Extent=c918a8d0 Flags=I/-/-/A/-/- 
          FreedLocation=0 Alloc=0.210938 Size=4.000000 LoadTime=4620871720




繼續研究library cache dump檔案內容，我們獲取select a,b,c from t_parselock 這個查詢SQL對應的遊標資訊，我的目的就是想把如下的資訊與上述的引用物件相關的資訊建立聯絡
我們發現v$sqlarea.address對應如下的libraryhandle後面的address,而identifier對應sqlarea.hash_value
而且基表會在library cache佔用一個bucket,而且使用基表的sql也會分配一個bucket
Bucket: #=106866 Mutex=d4161a00(0, 10, 0, 6) 
  LibraryHandle:  Address=cff78da8 Hash=37c9a172 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 
    ObjectName:  Name=select a,b,c from t_parselock


      FullHashValue=058e0a73d46e5dd347bf262e37c9a172 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=935960946 OwnerIdn=84 
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1 
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0 
    Concurrency:  DependencyMutex=cff78e58(0, 0, 0, 0) Mutex=cff78ed0(123, 20, 0, 6) 
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] 
    WaitersLists:  
      Lock=cff78e38[cff78e38,cff78e38] 
      Pin=cff78e48[cff78e18,cff78e18] 
    Timestamp:  Current=10-30-2015 06:58:19 
    LibraryObject:  Address=c8e36c80 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
      DataBlocks:  
        Block:  #='0' name=PCUR^37c9a172 pins=0 Change=NONE   
          Heap=cf358c70 Pointer=c8e36d68 Extent=c8e36c50 Flags=I/-/P/A/-/- 
          FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4620871600 
      ChildTable:  size='16' 
        Child:  id='0' Table=c8e37b80 Reference=c8e375d0 Handle=cf3f05c8 
    NamespaceDump:  
      Parent Cursor:  sql_id=4ggt65svwm8bk parent=0xc8e36d68 maxchild=1 plk=n ppn=n 

個人簡介：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1819427/，如需轉載，請註明出處，否則將追究法律責任。