[20210902]library_cache物件級別轉儲.txt

[20210902]library_cache物件級別轉儲.txt

--//昨晚看書APress Oracle Core Essential Internals for DBAs and Developers.pdf，在附錄部分：
library_cache N  

Dumps library cache information. This dump uses a bitmap strategy. Contents are combined by adding the dump values.
Strangely, some of the 11g dumps contain less information than the 10g equivalents.

1 = v$librarycache (approximately) and (for 10g) summaries of permanent space allocated for key structures
2 = hash chain summary and (for 10g) permanent allocation summaries  
4 = list of buckets with header structures for objects, and linked lists on hash chains (sufficient to see details of
    the lock/pin/mutex information for an object)
8 = 4 + dependencies, "data" blocks, accesses, translations, etc.
16 = 8 + heap dumps of each object's "data" blocks; file will be very big
32 = 16 + complete raw dump of all chunks in the heaps; file will be huge

{x$kglob.kglhdadr} (e.g., v$sql.child_address). For versions prior to 11g,  if you convert an object (child) address to
decimal, you can dump the summary information for the object. If you want to use the address in the hexadecimal form you
get from the x$ or v$ structures, it has to start with "0x".

{x$kglob.kglhdpar} (e.g., v$sql.address). For versions prior to 11g, if you convert an object (parent) address to
decimal, you can dump the summary information for the object with a short list of the handles of its children. Again,
you can prepend hexadecimal versions of the addresses with 0x.

library_cache_object {level} {address}

For 11.2 only (it is recognized in 11.1, but always seems to fail with an "in-flux" error. This is the 11g replacement
for the object-level dump in 10g.
    
Dumps details of a single library cache object. The address should be the  {x$kglob.kglhdadr} (e.g.
v$sql.child_address), or {x$kglob.kglhdpar} (e.g. v$sql.address). Hexadecimal addresses should have "0x" prepended, or
you can convert to decimal.

The levels use the bitmap method; levels I have found useful are:

0 – simple, short, dump
16 – detailed dump
48 – highly detailed dump, including child details if you supply a parent address
 
--//為了便於閱讀簡單排一下版。
library_cache N,N可以直接使用父子游標handle地址。
library_cache_object {level} {address}

1.環境：
SCOTT@book> @ ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> variable dname varchar2(20);
SCOTT@book> exec :dname := 'abcdefghijk'

PL/SQL procedure successfully completed.

SCOTT@book> select * from dept where dname = :dname;
no rows selected

select * from dept where dname = :dname;
select * from dept where dname = :dname;
select * from dept where dname = :dname;
select * from dept where dname = :dname;
select * from dept where dname = :dname;

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3645914938 6ypp0mrcp0gtu            0  d9503f3a

SYS@book> @ sharepool/shp4 6ypp0mrcp0gtu 0
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address  000000007C500490 000000007C501910 select * from dept where dname = :dname           1          0          0 000000007C5003D8 000000007C500F60       8600       8088       3096     19784      19784 3645914938 6ypp0mrcp0gtu          0
parent handle address 000000007C501910 000000007C501910 select * from dept where dname = :dname           1          0          0 000000007C501858 00                     4720          0          0      4720       4720 3645914938 6ypp0mrcp0gtu      65535

2.使用library_cache N轉儲看看:
--//library_cache N,N可以直接使用父子游標handle地址。
SYS@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_65305_0002.trc

SYS@book> oradebug setmypid
Statement processed.

SYS@book> oradebug dump library_cache 0x000000007C501910
Statement processed.
--//沒有資訊.

SYS@book> select child_address,address from v$sql where sql_id='6ypp0mrcp0gtu';
CHILD_ADDRESS    ADDRESS
---------------- ----------------
000000007C500490 000000007C501910
--//對應子父游標的地址.
--//7C501910 = 2085624080

SYS@book> oradebug dump library_cache 0x7C500490
Statement processed.

SYS@book> oradebug dump library_cache 2085624080
Statement processed.
--//按照上面介紹這種方式視乎是11g之前的版本有效.現在應該使用library_cache_object {level} {address} .

3.使用library_cache_object {level} {address} 轉儲看看:
SYS@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_65305_0003.trc

--//使用level=0,  0 – simple, short, dump
SYS@book> oradebug dump library_cache_object 0 0x000000007C501910
Statement processed.

--//轉儲看到內容如下:
Processing Oradebug command 'dump library_cache_object 0 0x000000007C501910'
LibraryHandle:  Address=0x7c501910 Hash=d9503f3a LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=select * from dept where dname = :dname
    FullHashValue=9b8c72fdc70ccb3e6f56a09dd9503f3a Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3645914938 OwnerIdn=83
  Statistics:  InvalidationCount=0 ExecutionCount=7 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0x7c5019c0(0, 2, 0, 0) Mutex=0x7c501a50(1, 72, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
  WaitersLists:
    Lock=0x7c5019a0[0x7c5019a0,0x7c5019a0]
    Pin=0x7c501980[0x7c501980,0x7c501980]
    LoadLock=0x7c5019f8[0x7c5019f8,0x7c5019f8]
  Timestamp:  Current=09-02-2021 10:04:11
  HandleReference:  Address=0x7c501ae0 Handle=(nil) Flags=[00]
  LibraryObject:  Address=0x7c5008a0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='16'
      Child:  id='0' Table=0x7c501750 Reference=0x7c501190 Handle=0x7c500490 --//子游標控制程式碼
  NamespaceDump:
    Parent Cursor:  sql_id=6ypp0mrcp0gtu parent=0x7c500940 maxchild=1 plk=y ppn=n
*** 2021-09-02 10:53:23.943
Oradebug command 'dump library_cache_object 0 0x000000007C501910' console output: <none>

--//使用level=16, 16 – detailed dump
SYS@book> oradebug dump library_cache_object 16 0x000000007C501910
Statement processed.

--//轉儲看到內容如下:
*** 2021-09-02 10:54:37.909
Processing Oradebug command 'dump library_cache_object 16 0x000000007C501910'

LibraryHandle:  Address=0x7c501910 Hash=d9503f3a LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=select * from dept where dname = :dname
    FullHashValue=9b8c72fdc70ccb3e6f56a09dd9503f3a Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3645914938 OwnerIdn=83
  Statistics:  InvalidationCount=0 ExecutionCount=7 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0x7c5019c0(0, 2, 0, 0) Mutex=0x7c501a50(1, 73, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
  WaitersLists:
    Lock=0x7c5019a0[0x7c5019a0,0x7c5019a0]
    Pin=0x7c501980[0x7c501980,0x7c501980]
    LoadLock=0x7c5019f8[0x7c5019f8,0x7c5019f8]
  Timestamp:  Current=09-02-2021 10:04:11
  HandleReference:  Address=0x7c501ae0 Handle=(nil) Flags=[00]
  ReferenceList:
    Reference:  Address=0x7c4fe440 Handle=0x7c4ff1a0 Flags=ROD[21]
  LibraryObject:  Address=0x7c5008a0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    DataBlocks:
      Block:  #='0' name=KGLH0^d9503f3a pins=0 Change=NONE
        Heap=0x7c501858 Pointer=0x7c500940 Extent=0x7c500820 Flags=I/-/P/A/-/-
        ~~~~~~~~~~~~~~~--//父遊標的堆0描述符
        FreedLocation=0 Alloc=2.460938 Size=3.976562 LoadTime=21890541630
    ChildTable:  size='16'
      Child:  id='0' Table=0x7c501750 Reference=0x7c501190 Handle=0x7c500490 --//子游標控制程式碼
    Children:
      Child:  childNum='0'
        LibraryHandle:  Address=0x7c500490 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=0 ExecutionCount=7 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=8
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
          Concurrency:  DependencyMutex=0x7c500540(0, 0, 0, 0) Mutex=0x7c501a50(1, 73, 0, 6)
          Flags=RON/PIN/PN0/EXP/CHD/[10012111]
          WaitersLists:
            Lock=0x7c500520[0x7c500520,0x7c500520]
            Pin=0x7c500500[0x7c500500,0x7c500500]
            LoadLock=0x7c500578[0x7c500578,0x7c500578]
          ReferenceList:
            Reference:  Address=0x7c501190 Handle=0x7c501910 Flags=CHL[02]
          LibraryObject:  Address=0x7c4ff420 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Dependencies:  count='2' size='16' table='0x7c500258'
              Dependency:  num='0'
                Reference=0x7c4ff9d8 Position=0 Flags=DEP[0001]
                Handle=0x7c5ee880 Type=NONE(255) Parent=SCOTT
              Dependency:  num='1'
                Reference=0x7c4ffa78 Position=14 Flags=DEP[0001]
                Handle=0x7c4fce90 Type=TABLE(02) Parent=SCOTT.DEPT
            ReadOnlyDependencies:  count='1' size='16'
              ReadDependency:  num='0' Table=0x7c5002f0 Reference=0x7c4ff8d8 Handle=0x7c4ff1a0 Flags=DEP/ROD/KPP[61]
            Authorizations:  count='1' size='16' entryeize='16'
            Accesses:  count='1' size='16'
              Dependency:  num='1' Type=0009
            Translations:  count='1' size='16'
              Translation:  num='0' Original=0x7c4fce90 Final=0x7c4fce90
            DataBlocks:
              Block:  #='0' name=KGLH0^d9503f3a pins=0 Change=NONE
                Heap=0x7c5003d8 Pointer=0x7c4ff4c0 Extent=0x7c4ff3a0 Flags=I/-/P/A/-/-
                ~~~~~~~~~~~~~~~--//子游標的堆0描述符
                FreedLocation=0 Alloc=5.796875 Size=7.953125 LoadTime=21890541630
              Block:  #='6' name=SQLA^d9503f3a pins=0 Change=NONE
                Heap=0x7c500f60 Pointer=0x7c4fdcf0 Extent=0x7c4fd0b0 Flags=I/-/-/A/-/E
                ~~~~~~~~~~~~~~~--//子游標的堆6描述符
                FreedLocation=0 Alloc=7.359375 Size=7.898438 LoadTime=0
          NamespaceDump:
            Child Cursor:  Heap0=0x7c4ff4c0 Heap6=0x7c4fdcf0 Heap0 Load Time=09-02-2021 10:04:11 Heap6 Load Time=09-02-2021 10:04:11
            ---//與前面的Pointer一樣。
  NamespaceDump:
    Parent Cursor:  sql_id=6ypp0mrcp0gtu parent=0x7c500940 maxchild=1 plk=y ppn=n
*** 2021-09-02 10:54:37.912
Oradebug command 'dump library_cache_object 16 0x000000007C501910' console output: <none>

--//看看Heap0=0x7c4ff4c0 Heap6=0x7c4fdcf0 0x7c4ff420在那裡，
SYS@book> @ fcha 7c4ff4c0
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C4FF370          1          1 KGLH0^d9503f3a         4096 recr           4095 000000007C5003D8

SYS@book> @ fcha 7c4fdcf0
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C4FD080          1          1 SQLA^d9503f3a          4096 recr           4095 000000007C500F60

SYS@book> @ fcha 7c4ff420
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C4FF370          1          1 KGLH0^d9503f3a         4096 recr           4095 000000007C5003D8
--//分別指向子游標的堆0,子游標的堆6。

--//使用level=48, 48 – highly detailed dump, including child details if you supply a parent address
SYS@book> oradebug dump library_cache_object 48 0x000000007C501910
Statement processed.

--//略.主要包含chunk的具體內容，資訊量有點大。

4.繼續看看子游標控制程式碼的情況.

SYS@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_65305_0004.trc

--//使用level=0,  0 – simple, short, dump,注：地址為子游標控制程式碼
SYS@book> oradebug dump library_cache_object 0 0x000000007C500490
Statement processed.

--//轉儲看到內容如下:
*** 2021-09-02 10:59:33.619
Processing Oradebug command 'dump library_cache_object 0 0x000000007C500490'

LibraryHandle:  Address=0x7c500490 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
  Statistics:  InvalidationCount=0 ExecutionCount=7 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=8
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0x7c500540(0, 2, 0, 0) Mutex=0x7c501a50(1, 76, 0, 6)
  Flags=RON/PIN/PN0/EXP/CHD/[10012111]
  WaitersLists:
    Lock=0x7c500520[0x7c500520,0x7c500520]
    Pin=0x7c500500[0x7c500500,0x7c500500]
    LoadLock=0x7c500578[0x7c500578,0x7c500578]
  LibraryObject:  Address=0x7c4ff420 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
  NamespaceDump:
    Child Cursor:  Heap0=0x7c4ff4c0 Heap6=0x7c4fdcf0 Heap0 Load Time=09-02-2021 10:04:11 Heap6 Load Time=09-02-2021 10:04:11
*** 2021-09-02 10:59:33.620
Oradebug command 'dump library_cache_object 0 0x000000007C500490' console output: <none>

--//使用level=16, 16 – detailed dump ,注：地址為子游標控制程式碼
SYS@book> oradebug dump library_cache_object 16 0x000000007C500490
Statement processed.

--//轉儲看到內容如下:
*** 2021-09-02 11:05:23.612
Processing Oradebug command 'dump library_cache_object 16 0x000000007C500490'

LibraryHandle:  Address=0x7c500490 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
  Statistics:  InvalidationCount=0 ExecutionCount=7 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=8
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0x7c500540(0, 2, 0, 0) Mutex=0x7c501a50(1, 80, 0, 6)
  Flags=RON/PIN/PN0/EXP/CHD/[10012111]
  WaitersLists:
    Lock=0x7c500520[0x7c500520,0x7c500520]
    Pin=0x7c500500[0x7c500500,0x7c500500]
    LoadLock=0x7c500578[0x7c500578,0x7c500578]
  ReferenceList:
    Reference:  Address=0x7c501190 Handle=0x7c501910 Flags=CHL[02]
  LibraryObject:  Address=0x7c4ff420 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    Dependencies:  count='2' size='16' table='0x7c500258'
      Dependency:  num='0'
        Reference=0x7c4ff9d8 Position=0 Flags=DEP[0001]
        Handle=0x7c5ee880 Type=NONE(255) Parent=SCOTT
      Dependency:  num='1'
        Reference=0x7c4ffa78 Position=14 Flags=DEP[0001]
        Handle=0x7c4fce90 Type=TABLE(02) Parent=SCOTT.DEPT
    ReadOnlyDependencies:  count='1' size='16'
      ReadDependency:  num='0' Table=0x7c5002f0 Reference=0x7c4ff8d8 Handle=0x7c4ff1a0 Flags=DEP/ROD/KPP[61]
    Authorizations:  count='1' size='16' entryeize='16'
    Accesses:  count='1' size='16'
      Dependency:  num='1' Type=0009
    Translations:  count='1' size='16'
      Translation:  num='0' Original=0x7c4fce90 Final=0x7c4fce90
    DataBlocks:
      Block:  #='0' name=KGLH0^d9503f3a pins=0 Change=NONE
        Heap=0x7c5003d8 Pointer=0x7c4ff4c0 Extent=0x7c4ff3a0 Flags=I/-/P/A/-/-
        FreedLocation=0 Alloc=5.796875 Size=7.953125 LoadTime=21890541630
      Block:  #='6' name=SQLA^d9503f3a pins=0 Change=NONE
        Heap=0x7c500f60 Pointer=0x7c4fdcf0 Extent=0x7c4fd0b0 Flags=I/-/-/A/-/E
        FreedLocation=0 Alloc=7.359375 Size=7.898438 LoadTime=0
  NamespaceDump:
    Child Cursor:  Heap0=0x7c4ff4c0 Heap6=0x7c4fdcf0 Heap0 Load Time=09-02-2021 10:04:11 Heap6 Load Time=09-02-2021 10:04:11
*** 2021-09-02 11:05:23.613
Oradebug command 'dump library_cache_object 16 0x000000007C500490' console output: <none>

5.總結:
--//亂，我對這些資訊的理解就是oracle似乎透過類似指標類的東西把這些chunk聯絡起來。