如何简单阅读library cache dump
源起于对实例的systemdump分析，现把library cache dump的部分单拿出来，做个简单的猜想。
SYS@moe SQL>col KGLNAOBJ for a40SYS@moe SQL>set line 123SYS@moe SQL>select * from scott.dept; DEPTNODNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SYS@moe SQL>selectkglhdadr,kglhdpar,kglnaobj,KGLOBHS0,kglobhd0,KGLOBHS6,KGLOBHD6 from x$kglobwhere kglnaobj like 'select * from scott.dept%'; KGLHDADR KGLHDPAR KGLNAOBJ KGLOBHS0 KGLOBHD0 KGLOBHS6 KGLOBHD6---------------- ---------------------------------------------- ---------- ---------------- --------------------------000000009BED2700 000000009BF74618 select *fromscott.dept 4488 000000009BED3100 8088 000000009E287B88000000009BF74618 000000009BF74618 select *fromscott.dept 4720 000000009BECF350 000
KGLHDADR为librarycache handle的地址
KGLHDPAR为父地址
KGLNAOBJ为librarycache object
KGLOBHD0为heap0地址
KGLOBHD6为heap6地址
SYS@moe SQL>select KSMCHPTR,KSMCHCOM,KSMCHCLS,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009BECF350'; 父游标hd0 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ---------------- ---------------- ------------------000000009E287400 KGLH0^3658de8a recr 4096
KSMCHPTR为chunk地址
KSMCHPAR为x$kglob中查询出来的heap的地址
SYS@moe SQL>selectKSMCHPTR,KSMCHCOM,KSMCHCLS ,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009BED3100'; 子游标hd0 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ---------------- ---------------- ------------------000000009E286400 KGLH0^3658de8a recr 4096 SYS@moe SQL>selectKSMCHPTR,KSMCHCOM,KSMCHCLS ,KSMCHSIZ from x$ksmsp where KSMCHPAR='000000009E287B88'; 子游标hd6 KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ---------------- ---------------- ------------------000000009C1826D8SQLA^3658de8a recr 4096000000009C17E6D8SQLA^3658de8a freeabl 4096 SYS@moe SQL>selectsql_id,hash_value,address,child_address,sql_text from v$sql where sql_text like'select * from scott.dept%'; SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS------------- ---------- --------------------------------SQL_TEXTf6hhpzwv5jrna 911793802000000009BF74618 000000009BED2700select * from scott.dept SYS@moe SQL>alter session set events'immediate trace name library_cache level 16'; Session altered.
下面是trc部分：
Bucket: #=56970 Mutex=0xa3b59180(0, 23, 0,6)
LibraryHandle: Address=0x9bf74618 Hash=3658de8a LockMode=0 PinMode=0 LoadLockMode=0Status=VALD
Address=0x9bf74618这个值是x$kglob.KGLHDPAR的值
Hash=3658de8a这个值是x$ksmsp.KSMCHCOM的值，转成10进制是911793802，是v$sql. HASH_VALUE的值
SYS@moe SQL>selectto_number('3658de8a','xxxxxxxxxxx') from dual;
TO_NUMBER('3658DE8A','XXXXXXXXXXX')
-----------------------------------
911793802
ObjectName: Name=select * from scott.dept 这里是执行的语句，是x$kglob.KGLNAOBJ的值，也是v$sql. SQL_TEXT的值
FullHashValue=66438da3ebbc48a3e34215ff3658de8a Namespace=SQL AREA(00)Type=CURSOR(00) Identifier=911793802 OwnerIdn=0
Identifier=911793802是v$sql.HASH_VALUE的值
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x9bf746c8(0, 3, 0, 0) Mutex=0x9bf74758(492, 58, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x9bf746a8[0x9bf746a8,0x9bf746a8]
Pin=0x9bf74688[0x9bf74688,0x9bf74688]
LoadLock=0x9bf74700[0x9bf74700,0x9bf74700]
Timestamp: Current=08-04-2016 13:55:02
HandleReference: Address=0x9bf747e0 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x9e2857c0 Handle=0x9bf4e818 Flags=ROD[21]
LibraryObject: Address=0x9e2874b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000]PublicFlags=[0000]
DataBlocks: 这个datablocks指的是如下图中的红框
Block: #='0' name=KGLH0^3658de8a pins=0Change=NONE 这里的name=KGLH0^3658de8a指的是heap0
Heap=0x9becf350 Pointer=0x9e287550 Extent=0x9e287430 Flags=I/-/P/A/-/-
Heap=0x9becf350指的是父游标的hd0，对应x$kglob. KGLOBHD0
FreedLocation=0 Alloc=2.460938 Size=3.976562 LoadTime=39359924950
ChildTable: size='16'
Child: id='0' Table=0x9e288360 Reference=0x9e287db8Handle=0x9bed2700
Handle=0x9bed2700这个指的是子游标handle对应x$kglob. KGLHDADR
Children:
Child: childNum='0'
LibraryHandle: Address=0x9bed2700 Hash=0 LockMode=0 PinMode=0LoadLockMode=0 Status=VALD
Address=0x9bed2700这个指的是子游标handle地址，对应x$kglob. KGLHDADR
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1ActiveLocks=0 TotalLockCount=1 TotalPinCount=2
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x9bed27b0(0, 0, 0, 0) Mutex=0x9bf74758(492,58, 0, 6)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x9bed2790[0x9bed2790,0x9bed2790]
Pin=0x9bed2770[0x9bed2770,0x9bed2770]
LoadLock=0x9bed27e8[0x9bed27e8,0x9bed27e8]
ReferenceList:
Reference: Address=0x9e287db8 Handle=0x9bf74618 Flags=CHL[02]
LibraryObject: Address=0x9e2864b0 HeapMask=0000-0001-0001-0000Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
Dependencies: count='1' size='16' table='0x9e2872e8'
Dependency: num='0'
Reference=0x9e286a70 Position=20 Flags=DEP[0001]
Handle=0x9bf4e5f8 Type=TABLE(02) Parent=SCOTT.DEPT这个是引用的表名
Handle=0x9bf4e5f8这个是引用的对象的handle地址，Parent=SCOTT.DEPT 这个是引用的表名
ReadOnlyDependencies: count='1' size='16'
ReadDependency: num='0' Table=0x9e287380 Reference=0x9e286968Handle=0x9bf4e818 Flags=DEP/ROD/KPP[61]
Accesses: count='1' size='16'
Dependency: num='0' Type=0009
DataBlocks:
Block: #='0' name=KGLH0^3658de8a pins=0 Change=NONE
Heap=0x9bed3100 Pointer=0x9e286550 Extent=0x9e286430 Flags=I/-/-/A/-/-
Heap=0x9bed3100这个是子游标heap0的地址，对应x$kglob. KGLOBHD0
FreedLocation=0 Alloc=2.078125 Size=3.937500 LoadTime=39359924950
Block: #='6' name=SQLA^3658de8a pins=0 Change=NONE
Heap=0x9e287b88 Pointer=0x9c183348 Extent=0x9c182708 Flags=I/-/-/A/-/E
Heap=0x9e287b88这个是子游标heap6的地址，对应x$kglob. KGLOBHD6
FreedLocation=0 Alloc=6.492188 Size=7.898438 LoadTime=0
NamespaceDump:
Child Cursor: Heap0=0x9e286550 Heap6=0x9c183348 Heap0 LoadTime=08-04-2016 13:55:02 Heap6 Load Time=08-04-2016 13:55:02
NamespaceDump:
ParentCursor: sql_id=f6hhpzwv5jrna parent=0x9e287550 maxchild=1 plk=n ppn=n
sql_id=f6hhpzwv5jrna这个是父游标的sql_id，对应v$sql. SQL_ID
Bucket: #=68887Mutex=0xa3bcd788(0, 23, 0, 6)
LibraryHandle: Address=0x9bf4e5f8 Hash=804f0d17 LockMode=0 PinMode=0LoadLockMode=0 Status=VALD
Address=0x9bf4e5f8这个是引用的对象的handle地址，可以看到后面的lockmode与pinmode
ObjectName: Name=SCOTT.DEPT 这个是对象的名称
FullHashValue=1383925607dd84fd07c34017804f0d17 Namespace=TABLE/PROCEDURE(01)Type=TABLE(02) Identifier=87106 OwnerIdn=83
Type=TABLE(02)对象是表，以02代表表 Identifier=87106这个是object_id OwnerIdn=83这个是user_id
SYS@moe SQL>selectobject_id from dba_objects where owner='SCOTT' and object_name='DEPT';
OBJECT_ID
----------
87106
SYS@moe SQL>selectuser_id from dba_users where username='SCOTT';
USER_ID
----------
83
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x9bf4e6a8(0, 1, 0, 0) Mutex=0x9bf4e738(492,47, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=0x9bf4e688[0x9bf4e688,0x9bf4e688]
Pin=0x9bf4e668[0x9bf4e668,0x9bf4e668]
LoadLock=0x9bf4e6e0[0x9bf4e6e0,0x9bf4e6e0]
Timestamp: Current=08-24-2013 12:04:21
HandleReference: Address=0x9bf4e7b0 Handle=0x9bf386a0 Flags=OWN[200]
ReferenceList:
Reference: Address=0x9e286a70 Handle=0x9bed2700 Flags=DEP[01]
Timestamp=08-24-2013 12:04:21 InvalidatedFrom=0
LibraryObject: Address=0x9e2844b0 HeapMask=0000-0701-0701-0000Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
所以dump时，是把父游标与子游标dump在一起的，通过父游标去定位它下面的所有子游标，其中block:#=0是heap0，block:#=6是heap6
父游标的dump中Name指向sql文本，而子游标的Name指向SQL AREA(00) Type=CURSOR(00)
父游标与子游标都存在handle、heap0，但是只有子游标有heap6
Lock与pin除了在sql或是游标上，也发生面引用的对象上，在这里会把对象与user相关的东东体现一部分出来
其他的目前还没看出来是什么，这个有点难猜，而且这方面的资料也不太好找，当然有些可能是我猜错的，欢迎大家指出。
