如何簡單閱讀library cache dump

源起于對實例的systemdump分析，現把library cache dump的部分單拿出來，做個簡單的猜想。

SYS@moe SQL>col KGLNAOBJ for a40
SYS@moe SQL>set line 123
SYS@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 000000009E287B88
000000009BF74618 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           4096
000000009C17E6D8SQLA^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_TEXT
f6hhpzwv5jrna  911793802000000009BF74618 000000009BED2700
select * 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相關的東東體現一部分出來

其他的目前還沒看出來是什么，這個有點難猜，而且這方面的資料也不太好找，當然有些可能是我猜錯的，歡迎大家指出。