Oracle library cache handle与内存中执行计划探究

Oracle library cache handle与内存中执行计划探究

执行一条简单的查询sql，以测试的方式，查找在shared pool中的library cache handle，父游标、子游标、以及heap 6（执行计划）关联性。

SQL> select * from test where id=1;

        ID NAME
---------- ----------
         1 xx
         1 a1
         1 a1

做一个 library cache dump trace（将当前会话内存中的library cache handle dump出来）

SQL> alter session set events 'immediate trace name library_cache level 11';
SQL> oradebug setmypid
SQL> oradebug tracefile_name
/u02/app/oracle/diag/rdbms/orcl/ora11g/trace/ora11g_ora_26109.trc

在生成的trace文件中，可以很清楚的看见父游标以及子游标library cache handle地址

Bucket: #=123617 Mutex=0x176284b58(0, 5, 0, 6) 
  LibraryHandle:  
  Address=0x17d800820 -->父游标handle地址 Hash=9c49e2e1 LockMode=0 PinMode=0 LoadLockMode=0 
  Status=VALD 
    ObjectName:  Name=select * from test where id=1 
      FullHashValue=1083d63518cc498ce2e9e88d9c49e2e1 
      Namespace=SQL AREA(00) Type=CURSOR(00) 
      Identifier=2622087905 OwnerIdn=0 
    Statistics:  InvalidationCount=0 ExecutionCount=1 
    LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1 
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 
    Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 
    Concurrency:  DependencyMutex=0x17d8008d0(0, 1, 0, 0) 
    Mutex=0x17d800960(131, 23, 0, 6) 
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] 
    WaitersLists:  
      Lock=0x17d8008b0[0x17d8008b0,0x17d8008b0] 
      Pin=0x17d800890[0x17d800890,0x17d800890] 
      LoadLock=0x17d800908[0x17d800908,0x17d800908] 
    Timestamp:  Current=06-03-2019 16:56:44 
    HandleReference:  Address=0x17d8009e8 Handle=(nil) Flags=[00] 
    ReferenceList:  
      Reference:  Address=0x16a459910 Handle=0x161bf01a8 Flags=ROD[21] 
    LibraryObject:  Address=0x16a45b600 HeapMask=0000-0001-0001-0000 
    Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
      DataBlocks:  
        Block:  #='0' name=KGLH0^9c49e2e1 pins=0 Change=NONE   
          Heap=0x161bf1e28 Pointer=0x16a45b6a0 Extent=0x16a45b580 
          Flags=I/-/P/A/-/- 
          FreedLocation=0 Alloc=2.460938 Size=3.976562 
          LoadTime=4829035440 
      ChildTable:  size='16' 
        Child:  id='0' Table=0x16a45c4b0 Reference=0x16a45bf08 
        Handle=0x17d800490 -->子游标handle地址
    NamespaceDump:  
      Parent Cursor:  sql_id=f5ug8jqf4msr1 parent=0x16a45b6a0 
      maxchild=1 plk=n ppn=n 

进一步根据子游标的handle地址，可以查到sql的统计信息

Bucket: #=90277 Mutex=0x1761e19f8(0, 5, 0, 6) 
  LibraryHandle:  Address=0x161bf01a8 Hash=ef5760a5 LockMode=0 
  PinMode=0 LoadLockMode=0 Status=VALD 
    ObjectName:  Name=1083d63518cc498ce2e9e88d9c49e2e1 Child:0  
      FullHashValue=e11a8417cc456d4e1946d16fef5760a5 
      Namespace=SQL AREA STATS(75) 
      Type=CURSOR STATS(102) Identifier=4015480997 OwnerIdn=0 
    Statistics:  InvalidationCount=0 ExecutionCount=0 
    LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1 
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 
    Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 
    Concurrency:  DependencyMutex=0x161bf0258(0, 1, 0, 0) 
    Mutex=0x161bf02e8(131, 9, 0, 6) 
    Flags=RON/PIN/TIM/KEP/KPR/[00012805] 
    WaitersLists:  
      Lock=0x161bf0238[0x161bf0238,0x161bf0238] 
      Pin=0x161bf0218[0x161bf0218,0x161bf0218] 
      LoadLock=0x161bf0290[0x161bf0290,0x161bf0290] 
    Timestamp:  Current=06-03-2019 16:56:44 
    ReferenceList:  
      Reference:  Address=0x16a45aab8 Handle=0x17d800490 Flags=ROD/KPP[61] 
    LibraryObject:  Address=0x16a459600 
    HeapMask=0001-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
      ReadOnlyDependencies:  count='1' size='16' 
        ReadDependency:  num='0' Table=0x16a45a4d0 Reference=0x16a459910 
        Handle=0x17d800820 Flags=DEP/ROD[21] 
      DataBlocks:  
        Block:  #='0' name=KGLH0^ef5760a5 pins=0 Change=NONE   
          Heap=0x161bf00f0 Pointer=0x16a4596a0 Extent=0x16a459580 Flags=I/-/P/A/-/- 
          FreedLocation=0 Alloc=0.960938 Size=3.976562 LoadTime=4829035440 
    NamespaceDump:  ---sql统计信息
      STATS:  phd=0x17d800820 chd=0x17d800490 planhsh=50e369bc flg=0 Parse Count=1 
      Disk Reads=2 Disk Writes=0 Physical read requests=2 Physical read bytes=16384 
      Physical write requests=0 Physical write bytes=0 IO Interconnect bytes=16384 
      Buffer Gets=29 Rows Processed=3 Serializable Aborts=0 Fetches=2 Execution 
      count=1 PX Server Execution Count=0 Full Execution Count=1 
      CPU time=24540 Elapsed time=40921 
      Avg Hard Parse Time=21837 Application time=0 Concurrency time=0 
      Cluster/RAC time=0 User I/O time=17626 Plsql Interpretor time=0 JVM time=0 Sorts=0 

同样的也可以根据x$kglob再次验证父游标、子游标获取library cache的handle地址，如下

select kglobhd0,kglobhd6,kglhdadr,kglhdpar,kglnaobj,kglobtyd,
kglobt03 from x$kglob t  where kglnaobj like 'select * from test where id=1%'；

其中kglhdadr=kglhdpar的那一行为父游标，获得的父游标、子游标信息与做trace的结果一致。

或者也可以根据x$kglcursor_child_sqlid获取

select kglhdadr child_cursor_addr,kglhdpar par_cursor_addr,
kglnaobj sqltext,kglobhd0,
kglobhd6 from x$kglcursor_child_sqlid where kglhdpar='000000017D800820'

此刻可以更进一步通过x$kglob的kglobhd6列（heap 6）获取sql执行计划

我们使用地址kglobhd6=‘000000016A45BCD8’,再做一个trace将 heap 6（执行计划）trace出来

SQL>  alter session 
set events 'immediate trace name heapdump_addr level 2,addr 0x16A45BCD8';

SQL> oradebug setmypid


SQL> oradebug tracefile_name
/u02/app/oracle/diag/rdbms/orcl/ora11g/trace/ora11g_ora_26109.trc

sql的执行计划，在内存中已16进制的形式得以展现