上一篇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
******************************************************