Sharedpool 3: librarycache structure-dumpLibraryHandle

Introduction to Library cache the main function of Library cache is to store SQL statements submitted by users and the parsing tree related to SQL statements (the parsing tree is the presentation of all objects involved in SQL statements ), execution Plan, user-submitted PL/SQL blocks (including anonymous blocks, stored procedures, packages, functions, etc.) and the code that can be executed by Oracle after conversion, to manage these memory structures, the library cache also stores many control structures, including lock, pin, dependency table, and mutex in 11g.

Library cache consists of a hash table, which is an array composed of hash buckets.
Each hash bucket is a two-way linked list containing the library cache handle.
The library cache handle points to the library cache object and a reference list.
Library cache objects are further divided into dependent tables, sub-tables, and authorization tables.
For the same SQL statement, the HASH value is the same and put in the same library cache handle. For example, if a variable is bound, different sub-cursors under the same library cache handle are used. Different SQL statements have the same HASH value in different library cache handle of the same hash bucket. The Hash algorithm Oracle uses a lot of hash in internal management. The purpose of using hash is to quickly find and locate.
Hash the value, generate an index number, and place the value in the corresponding hash bucket according to the index number.
According to the hash algorithm, multiple index numbers are generated. Each index number corresponds to a hash bucket (a value column ).
When looking for a value, hash the searched value to generate an index number. The value must be in the corresponding hash bucket of the index number, therefore, the system directly jumps to the hash bucket for traversal. In this way, when locating data, we can greatly reduce the number of traversal tasks.

The database cache is managed using the hash bucket.
1. The number of hash buckets is automatically calculated based on the size specified by shared_pool_size.
2. Each hash bucket is followed by multiple handles.
The handle describes some attributes of the object in the library cache, including the name, Tag, pointer to the memory address of the object SQL statement in the Library cache check process 1. An SQL statement enters
2. Convert SQL text into ASCII values
3. Calculate the hash function for parameters such as ASCII
4. Obtain the number of the hash bucket.
5. Put the object handle library cache handle into the corresponding hash bucket-parent cursor
6. A process must process an object, such as an SQL statement.
7. hash the statement to get a number.
8. Go to the hash bucket for an inbound scan.
The object handle exists and is directly used
The object handle exists, and the object is swapped out of memory and loaded to the object.
The handle does not exist. Re-construct an object and mount it to the hash bucket.
Select owner, name from v $ db_object_cache where child_latch = 1; displays the objects cached in the library cache and their sizes.
######################################## ########### 3. Use x $ kglob to find the parent cursor address of the SQL statement, and DUMP library cache to find the SQL statement in the address of the parent cursor in the TRACE file. Session 1
SYS @ bys3> select * from bys. dept;
SYS @ bys3> col KGLNAOBJ for a40
SYS @ bys3> select kglhdadr, kglhdpar, kglnaobj, kglobhd0 from x $ kglob where kglnaobj like 'select * from bys. dept % ';
Kglhdadr kglhdpar kglnaobj KGLOBHD0
------------------------------------------------------
23ACB574 248344B8 select * from bys. dept 242B7D1C -- 23ACB574 sub-cursor address
248344B8 248344B8 select * from bys. dept 23FE4684 -- 248344B8 parent cursor address, parent cursor heap 0 address-Actually the descriptor-23FE4684

Select KGLHDADR, KGLHDPAR, KGLNAOBJ, KGLOBHD0 from x $ kglob where kglnaobj like 'select * from bys. dept % 'and KGLHDADR <> KGLHDPAR; locate the sub-cursor
Select KGLHDADR, KGLHDPAR, KGLNAOBJ, KGLOBHD0 from x $ kglob where kglnaobj like 'select * from bys. dept % 'and KGLHDADR = KGLHDPAR; find the parent cursor

SYS @ bys3> select KSMCHPTR, KSMCHCOM, KSMCHCLS, KSMCHSIZ from x $ ksmsp where KSMCHPAR = '23fe4684 '; -- find the CHUNK address where the parent cursor is located through the heap 0 descriptor found in the previous step.
KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ
------------------------------------------
241D3A44 KGLH0 ^ a8dc75cd recr 4096

Open a new session: DUMP library cache and search for SQL statements.
SYS @ bys3> alter session set events 'immediate trace name heapdump level 100 ';
SYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_000038.trc
#################
Query in VI: the SQL statement is found in the Chunk starting from the address 241D3A44.
Chunk 241d3a44 sz= 4096 recreate "KGLH0 ^ a8dc75cd" latch = (nil)
Ds 23fe4684 sz= 4096 ct = 1
Dump of memory from 0x241D3A44 to 0x241D4A44
241D3A40 80001001 241D2A44 00000000 [...... D * ......]
241D3A50 00000000 00000000 00000000 000A0FFF [......]

..................

Chunk 2405e530 sz= 4096 recreate "SQLA ^ bf04295e" latch = (nil)
Dump of memory from 0x2405E530 to 0x2405F530
2405E530 80001001 2405E164 2526A6EC 237A8414 [... d ...... $ ...... & %. z #]
..................
2405E680 00000000 00000000 00000000 00000000 [......]
2405E690 00000000 00000000 F4A4D8E8 735 DAFAF [......] s]
2405E6A0 00000000 00000049 11390064 [... I... \... $ d.9.]
2405E6B0 01748F8F 18150001 407EC500 0A8A0500 [... t .......~ @ ......]
2405E6C0 00020103 00090203 093A0000 1E070604 [......]
2405E6D0 F0836A0A A8E0F183 E036E426 83e0000a8 [. j ......]
2405E6E0 03F383F5 02030302 008E0000 00000025 [......]
2405E6F0 2405E6A4 110B3B04 656C6573 2A207463 [...... select *]
2405E700 6f0000620 7962206D 65642E73 00257470 [from bys. dept %.]
2405E710 0000003D 2405E6EC 110B3B34 0000000D [=... $4; ......]
2405E720 00000801 00170040 00010369 00000018 [...... @ ...... I ......]
............
######################################## ################################### Use x $ kglob to locate SQL statement sub-cursor address and heap 6 address, and DUMP library cache to view the content in the TRACE file. Heap 0 heap 0 -- belongs to the parent cursor-library cache handle. There are also two heap in the Child cursor. heap 0 stores the address pointing to heap 6.
SYS @ bys3> select * from bys. dept;
SYS @ bys3> col KGLNAOBJ for a25
SYS @ bys3> select kglhdadr, kglhdpar, kglnaobj, KGLOBHS0, kglobhd0, KGLOBHS6, KGLOBHD6 from x $ kglob where kglnaobj like 'select * from bys. dept % ';
Kglhdadr kglhdpar kglnaobj KGLOBHS0 KGLOBHD0 KGLOBHS6 KGLOBHD6
-----------------------------------------------------------------------------
2358B76C 23D0705C select * from bys. dept 4348 2526A068 4060 243F2238
23D0705C 23D0705C select * from bys. dept 4516 23D65B44 0 00
Parent cursor LibraryHandle address KGLHDPAR: 23D0705C sub-cursor LibraryHandle address KGLHDADR: 2358B76C,

Parent cursor heap 0 description KGLOBHD0: 23D65B44, child cursor heap 0 Description: 2526A068, child cursor heap 6 Description KGLOBHD6: 243F2238

This can be viewed by DUMP,Alter session set events 'immediate trace name heapdump_addr level 2, addr0x23b31e80'; Similar to the following:Desc = 0x23b31e80This can correspond to the information of the KGLOBHD0 field found above -- I added it later and cannot match it.

**************************************** **************
Heap dump heap name = "KGLH0 ^1020848"Desc = 0x23b31e80
Extent sz = 0xff4 alt = 32767 het = 28 rec = 9 flg = 2 opc = 0
Parent = 0x200010b4 owner = 0x23b31e4c nex = (nil) xsz = 0xfe4 heap = (nil)
Fl2 = 0x26, nex = (nil), dsxvers = 1, dsxflg = 0x0
Dsx first ext = 0x240b19c4
EXTENT 0 addr = 0x240b19c4
Chunk 240b19cc sz = 44 p

SYS @ bys3> select KSMCHPTR, KSMCHCOM, KSMCHCLS, KSMCHSIZ from x $ ksmsp where KSMCHPAR = '23d65b44 ';
-- Find the CHUNK address of the parent cursor heap 0 descriptor found in the previous step and view the CHUNK address description status of the parent cursor.
KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ
------------------------------------------
243F1D68 KGLH0 ^ a8dc75cd recr 4096
SYS @ bys3> select KSMCHPTR, KSMCHCOM, KSMCHCLS, KSMCHSIZ from x $ ksmsp where KSMCHPAR = '2526a068 ';
-- Use the sub-cursor heap 0 descriptor found in the previous step to find and view the CHUNK address status of the sub-cursor heap 0:
KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ
------------------------------------------
23FDD830 KGLH0 ^ a8dc75cd recr 4096 -- KGLH0 ^ a8dc75cd -- H0 heap 0
SYS @ bys3> select KSMCHPTR, KSMCHCOM, KSMCHCLS, KSMCHSIZ from x $ ksmsp where KSMCHPAR = '243f2238 ';
-- Use the sub-cursor heap 6 descriptor found in the previous step to find and view the CHUNK address description status of the sub-cursor heap 6
KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ
------------------------------------------
23FDB830 SQLA ^ a8dc75cd recr 4096 -- SQLA ^ a8dc75cd-SQL area

SYS @ bys3> col SQL _text for a25
SYS @ bys3> select SQL _id, hash_value, address, child_address, SQL _text from v $ SQL where SQL _text like 'select * from bys. dept % ';
-- View the SQL _ID, HASH_VALUE ADDRESS CHILD_AD, and other information of the SQL statement, and compare it with the following DUMP information.
SQL _ID HASH_VALUE ADDRESS CHILD_AD SQL _TEXT
----------------------------------------------------------------
F4yk5kundsxfd 2833020365 23D0705C 2358B76C select * from bys. dept
New session DUMP
SYS @ bys3> alter session set events 'immediate trace name library_cache level 16 ';
Session altered.
Level 1: Dump library cache statistics; level 2: Dump hash table Summary
Level 4: Dump basic information of the library cache object; level 4: Dump detailed information of the library cache object
Level 16, added heap sizes information.
SYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
---------------------------------------------------

/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_22403.trc

######################################## #
Bucket :#= 30157 Mutex = 0x26ab5658 (2e0000, 78, 0, 6)
LibraryHandle: Address = 0x23d0705c Hash = a8dc75cd LockMode = 0 PinMode = 0 LoadLockMode = 0 Status = VALD
# Address = 0x23d0705c is consistent with x $ kglob. KGLHDPAR parent cursor Address. Hash = a8dc75cd is consistent with x $ ksmsp. KSMCHCOM, and the conversion to hexadecimal is consistent with v $ SQL. HASH_VALUE;
ObjectName: Name = select * from bys. dept # Content of the executed statement, with x $ kglob. KGLNAOBJ
FullHashValue = 23667d1d9b8972be27a4596a8dc75cd Namespace = SQL AREA (00) Type = CURSOR (00) Identifier = 2833020365 OwnerIdn = 0
# Identifier = 2833020365 is consistent with v $ SQL. HASH_VALUE, Namespace = SQL area (00) SQL AREA
Statistics: InvalidationCount = 0 ExecutionCount = 1 LoadCount = 2 ActiveLocks = 0 TotalLockCount = 1 TotalPinCount = 1
Counters: BrokenCount = 1 RevocablePointer = 1 KeepDependency = 1 Version = 0 BucketInUse = 0 HandleInUse = 0 HandleReferenceCount = 0
Concurrency: DependencyMutex = 0x23d070c4 (0, 1, 0, 0) Mutex = 0x23d07120 (46, 39, 0, 6)
Flags = RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock = 0x23d070b4 [0x23d070b4, 0x23d070b4]
Pin = 0x23d070a4 [0x23d070a4, 0x23d070a4]
LoadLock = 0x23d070e8 [0x23d070e8, 0x23d070e8]
Timestamp: Current = 18:03:50 # Timestamp
HandleReference: Address = 0x23d0718c Handle = (nil) Flags = [00]
ReferenceList:
Reference: Address = 0x23fdcb14 Handle = 0x2345e2a8 Flags = ROD [21]
LibraryObject: Address = 0x243f1dc8 HeapMask = 0000-0001-0001-0000 Flags = EXS [0000] Flags2 = [0000] PublicFlags = [0000]
DataBlocks:
Block: # = '0' name = KGLH0 ^ a8dc75cd pins = 0 Change = NONE
Heap = 0x23d65b44 Pointer = 0x243f1e1c Extent = 0x243f1d84 Flags = I/-/P//-/-
FreedLocation = 0 Alloc = 1.546875 Size = 3.988281 LoadTime = 4405191980
ChildTable: size = '16'
Child: id = '0' Table = 0x243f2d18 Reference = 0x243f2390 Handle = 0x2358b76c # sub pointer
Children:
Child: childNum = '0'
LibraryHandle: Address = 0x2358b76c Hash = 0 LockMode = 0 PinMode = 0 LoadLockMode = 0 Status = VALD
# Sub-cursor LibraryHandle Address = 0x2358b76c, consistent with x $ kglob. KGLHDADR sub-cursor Address
Name: Namespace = SQL AREA (00) Type = CURSOR (00)
Statistics: InvalidationCount = 0 ExecutionCount = 1 LoadCount = 1 ActiveLocks = 0 TotalLockCount = 1 TotalPinCount = 2
Counters: BrokenCount = 1 RevocablePointer = 1 KeepDependency = 0 Version = 0 BucketInUse = 0 HandleInUse = 0 HandleReferenceCount = 0
Concurrency: DependencyMutex = 0x2358b7d4 (0, 0, 0, 0) Mutex = 0x23d07120 (46, 39, 0, 6)
Flags = RON/PIN/PN0/EXP/coronary heart disease/[10012111]
WaitersLists:
Lock = 0x2358b7c4 [0x2358b7c4, 0x2358b7c4]
Pin = 0x2358b7b4 [0x2358b7b4, 0x2358b7b4]
LoadLock = 0x2358b7f8 [0x2358b7f8, 0x2358b7f8]
ReferenceList:
Reference: Address = 0x243f2390 Handle = 0x23d0705c Flags = chlorophyll [02]
LibraryObject: Address = 0x23fdd890 HeapMask = 0000-0001-0001-0000 Flags = EXS [0000] Flags2 = [0000] PublicFlags = [0000]
Dependencies: count = '1' size = '16' table = '0x23fde7a4'
Dependency: num = '0'
Reference = 0x23fddcb0 Position = 18 Flags = DEP [1, 0001]
Handle = 0x2433a460 Type = TABLE (02) Parent = BYS. DEPT
ReadOnlyDependencies: count = '1' size = '16'
ReadDependency: num = '0' Table = 0x23fde7f0 Reference = 0x23fddc1c Handle = 0x2345e2a8 Flags = DEP/ROD/KPP [61]
Accesses: count = '1' size = '16'
Dependency: num = '0' Type = 0009.
DataBlocks:
Block: # = '0' name = KGLH0 ^ a8dc75cd pins = 0 Change = NONE
Heap = 0x2526a068 Pointer = 0x23fdd8e4 Extent = 0x23fdd84c Flags = I/-//-/-
##### Heap = 0x2526a068 address of the sub-cursor Heap 0, consistent with x $ kglob. KGLOBHD0
FreedLocation = 0 Alloc = 1.382812 Size = 3.964844 LoadTime = 4405191980
Block: # = '6' name = SQLA ^ a8dc75cd pins = 0 Change = NONE
Heap = 0x243f2238 Pointer = 0x23fdc624 Extent = 0x23fdb84c Flags = I/-/A/-/E
# Heap = 0x243f2238 this is the address of the Child cursor Heap 6, consistent with x $ kglob. KGLOBHD6
FreedLocation = 0 Alloc = 3.785156 Size = 3.964844 LoadTime = 0
NamespaceDump: -- subcursor Information
Child Cursor: Heap0 = 0x23fdd8e4 Heap6 = 0x23fdc624 Heap0 Load Time = 01-04-2014 18:03:50 Heap6 Load Time = 01-04-2014 18:03:50
NamespaceDump:
Parent Cursor: SQL _id = f4yk5kundsxfd parent = 0x243f1e1c maxchild = 1 plk = n ppn = n
# Parent cursor, SQL _id = f4yk5kundsxfd consistent with v $ SQL. SQL _id,

######################################## #######

Heap 0 and heap 6 of the Parent and Child cursor of the dump SQL statement

SYS @ bys3> select kglhdadr, kglhdpar, kglnaobj, KGLOBHS0, kglobhd0, KGLOBHS6, KGLOBHD6 from x $ kglob where kglnaobj like 'select * from bys. test % ';
Kglhdadr kglhdpar kglnaobj KGLOBHS0 KGLOBHD0 KGLOBHS6 KGLOBHD6
-----------------------------------------------------------------------------
240EDA68 239948A4 select * from bys. test 4348 23B31E80 4060 245908B0
239948A4 239948A4 select * from bys. test 4500 241368BC 0 00

Alter session set events 'immediate trace name heapdump_addr level 2, addr 0x241368BC '; -- use this statement to DUMP the Parent and Child cursor heap 0 and heap 6.

I will post the opening of the Three DUMP files:

Parent cursor heap 0: desc = field corresponds to x $ kglob. KGLOBHD0, heap name = "KGLH0 ^ heap name also indicates heap 0
**************************************** **************
Heap dump heap name = "KGLH0 ^1020848" desc = 0x241368bc
Extent sz = 0xff4 alt = 32767 het = 28 rec = 9 flg = 2 opc = 0
Parent = 0x200010b4 owner = 0x24136888 nex = (nil) xsz = 0xfe4 heap = (nil)
Fl2 = 0x26, nex = (nil), dsxvers = 1, dsxflg = 0x0
Dsx first ext = 0x245903fc
EXTENT 0 addr = 0x245903fc
Chunk 24590404 sz = 44 perm "perm" alo = 44
Dump of memory from 0x24590404 to 0x24590430

Sub-cursor heap 0: -- My version is 11.2.0.4, and some versions are dumped out of heap name = "CCURSE, which intuitively indicates that it is a sub-cursor .. In this example, only the desc = field corresponds to x $ kglob. KGLOBHD6.

**************************************** **************
Heap dump heap name = "KGLH0 ^ 1020848" desc = 0x23b31e80
Extent sz = 0xff4 alt = 32767 het = 28 rec = 9 flg = 2 opc = 0
Parent = 0x200010b4 owner = 0x23b31e4c nex = (nil) xsz = 0xfe4 heap = (nil)
Fl2 = 0x26, nex = (nil), dsxvers = 1, dsxflg = 0x0
Dsx first ext = 0x240b19c4
EXTENT 0 addr = 0x240b19c4
Chunk 240b19cc sz = 44 perm "perm" alo = 44

Sub-cursor heap 6: -- It can be seen from the DUMP name that it is an SQL region that stores SQL Execution plans and so on.. -- Some versions of DUMP show the heap name = "SQL AREA", which is shown in 11.2.0.4 ..

**************************************** **************
Heap dump heap name = "SQLA ^ 1020848" desc = 0x245908b0
Extent sz = 0xff4 alt = 32767 het = 184 rec = 0 flg = 2 opc = 2
Parent = 0x200010b4 owner = 0x245907e4 nex = (nil) xsz = 0xfe4 heap = (nil)
Fl2 = 0x27, nex = (nil), dsxvers = 1, dsxflg = 0x0
Dsx first ext = 0x24b12c54
EXTENT 0 addr = 0x24b12c54
Chunk 24b12c5c sz = 44 perm "perm" alo = 44