[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聯絡起來。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2790702/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Java synchronized物件級別與類級別的同步鎖Javasynchronized物件
- [20190930]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20191013]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20210902]為什麼會使用多個共享記憶體段.txt記憶體
- 測試物件和測試級別物件
- 杉巖：淺談物件儲存和塊儲存區別物件
- [20191003]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- 物件和函式的區別就是物件可以儲存狀態物件函式
- 一些轉儲和清除記憶體物件和物理物件的命令(轉)記憶體物件
- [20170406]關於檔案頭轉儲.txt
- [20150910]關於物件級檢查點.txt物件
- jquery物件和DOM物件的區別及互相轉化jQuery物件
- [20210507]分析library cache轉儲.txt
- [20161129]奇怪的轉儲命令.txt
- 物件儲存物件
- SAP PLM文件許可權物件的控制級別物件
- Spark2 Dataset持久化儲存級別StorageLevelSpark持久化
- 一些轉儲和清除記憶體物件和物理物件的命令記憶體物件
- [20210524]分析library cache轉儲 3.txt
- [20210524]分析library cache轉儲 4.txt
- 物件儲存、檔案儲存、塊儲存這三者之間有什麼區別？物件
- 儲存—物件儲存_Minio物件
- 在JavaScript中,DOM物件與jQuery物件的區別與轉換JavaScript物件jQuery
- [轉]在.Net中使用Oracle的表型別和物件型別Oracle型別物件
- C++永久物件儲存 （Persistent Object Storage for C++） (轉)C++物件Object
- [20210508]分析library cache轉儲 2.txt
- [20160407]sql語句父子游標的堆轉儲.txtSQL
- 塊儲存 檔案儲存 物件儲存物件
- [原]SAP批次管理由物料級別轉換到工廠級別的方法
- Unirech：阿里雲國際站的物件儲存oss與自建儲存的區別阿里物件
- 轉摘_儲存型別與連線方式型別
- Windows安全指南之域級別策略(轉)Windows
- [20200512]oracle的事務隔離級別.txtOracle
- [20210602]分析library cache轉儲 5.txt
- laravelS使用redis stream完成每日億級別資料儲存LaravelRedis
- java基本型別和物件之間的轉換Java型別物件
- [20191219]oracle timestamp資料型別的儲存.txtOracle資料型別
- 怎樣玩轉千萬級別的資料