共享内存shared pool （4）：Library cache 转储文件

　　上一篇blog只是从概念上理解Library cache，本篇则是将Library cache从内存中dump出来，看看其结构。

基本命令

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level LL';

其中LL代表Level级别,对于9.2.0及以后版本,不同Level含义如下:
Level =1 ,转储Library cache统计信息
Level =2 ,转储hash table概要
Level =4 ,转储Library cache对象，只包含基本信息
Level =8 ,转储Library cache对象，包含详细信息(包括child references,pin waiters等)
Level =16,增加heap sizes信息
Level =32,增加heap信息

--生成dump文件，level 7 包含（level 1，2，4）
SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 7';

Session altered.

SQL> select value from v$diag_info where name like 'De%';

VALUE
--------------------------------------------------------------------------------
/u01/diag/rdbms/oracle/beijing/trace/beijing_ora_18571.trc

SQL> 

解释文件中各个部分

******************************************************
Library Cache Dump

LIBRARY CACHE STATISTICS:
namespace           gets hit ratio      pins hit ratio    reloads   invalids
-------------- --------- --------- --------- --------- ---------- ----------
SQL AREA           25409     0.601    183722     0.944        780        261
TABLE/PROCEDURE     43224     0.919     42030     0.858       1189          0
BODY                4555     0.973      7393     0.978         15          0
TRIGGER              245     0.869       401     0.918          1          0
.........


这部分信息是 level 1 对应的信息，也是v$librarycache中显示的.
******************************************************

SGA:360dbff4 Flags=2c7
DebugContext:  DebugNameSpace=0 DebugType=0 DebugLevel=0x0 
LIBRARY CACHE HASH TABLE: size=131072 count=6881
Buckets with more than 20 objects:
NONE
Hash Chain Size     Number of Buckets
---------------     -----------------
              0                124370
              1                  6524  --包含1个library cache handle的Bucket为6524个
              2                   177  --包含2个library cache handle的Bucket为177个
              3                     1  --包含3个library cache handle的Bucket为1个
              4                     0
              5                     0
              6                     0
              7                     0
              8                     0
              9                     0
             10                     0
             11                     0
             12                     0
             13                     0
             14                     0
             15                     0
             16                     0
             17                     0
             18                     0
             19                     0
             20                     0
            >20                     0
 
            
这部分信息是 level 2 对应的信息.
LIBRARY CACHE HASH TABLE: size=131072 count=6881 
count：即整个LIBRARY CACHE中library cache handle的个数

count=6881 = 1*6524 + 2*177 + 3*1
******************************************************

Bucket: #=84 Mutex=360dd188(430000, 31, 0, 6) 
  LibraryHandle:  Address=31dd2720 Hash=da040054 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 
    ObjectName:  Name=select   LOW_OPTIMAL_SIZE,           HIGH_OPTIMAL_SIZE,           OPTIMAL_EXECUTIONS,           ONEPASS_EXECUTIONS,           MULTIPASSES_EXECUTIONS,           TOTAL_EXECUTIONS    from   GV$SQL_WORKAREA_HISTOGRAM    where  INST_ID = USERENV('Instance')

      FullHashValue=1cb59bc0340f2b79174a091cda040054 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3657695316 OwnerIdn=0 
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=4 ActiveLocks=0 TotalLockCount=3 TotalPinCount=1 
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=2 HandleInUse=2 
    Concurrency:  DependencyMutex=31dd2788(0, 0, 0, 0) Mutex=31dd27d4(67, 72, 0, 6) 
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] 
    WaitersLists:  
      Lock=31dd2778[31dd2778,31dd2778] 
      Pin=31dd2780[31dd2768,31dd2768] 
    Timestamp:  Current=03-11-2015 01:23:44 
    LibraryObject:  Address=310ba104 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
      ChildTable:  size='16' 
        Child:  id='0' Table=310bb080 Reference=310ba70c Handle=31dd249c 
    NamespaceDump:  
      Parent Cursor:  sql_id=1fkh93md0802n parent=0x310ba188 maxchild=1 plk=n ppn=n 
  LibraryHandle:  Address=33535000 Hash=a80a0054 LockMode=0 PinMode=0 LoadLockMode=0 Status=0 
    ObjectName:  Name=SELECT COUNT(*) FROM MGMT_JOB_EXEC_EVENT_PARAMS WHERE EXECUTION_ID=:B1 

      FullHashValue=c5d8c8ca1cdd0550b7315484a80a0054 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=0 OwnerIdn=0 
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=0 TotalLockCount=0 TotalPinCount=0 
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0 
    Concurrency:  DependencyMutex=33535068(0, 1, 0, 0) Mutex=335350b4(67, 23, 0, 6) 
    Flags=RON/PIN/PN0/DBN/[10010040] 
    WaitersLists:  
      Lock=33535058[33535058,33535058] 
      Pin=33535060[33535048,33535048] 
    Timestamp:  
    NamespaceDump:  
      Parent Cursor:  sql_id=bfcanhkn0n02n parent=(nil) maxchild=0 plk=n ppn=n 
    
这部分信息是 level 4 对应的信息.
Bucket: #=84  84应该是索引号了。Bucket只是一类相关联的LibraryHandle的标识，是一个逻辑概念
LibraryHandle： 此bucket中包含 2 个 LibraryHandle


******************************************************