[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:
--//使用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:
--//看看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:
--//使用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:
5.总结:
--//乱，我对这些信息的理解就是oracle似乎通过类似指针类的东西把这些chunk联系起来。
[20210902]library_cache对象级别转储.txt
-
lfree
2021-09-07 08:32:28 -
Oracle
- 原创