[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联系起来。