Usage of LibraryCache in OracleSharedPool,

Usage of LibraryCache in OracleSharedPool,
1. Shared Pool Composition
The Shared Pool is composed of multiple intervals (Extent), which are composed of multiple consecutive memory blocks (chunks). These memory blocks are of different sizes. From the logic function perspective, the Shared pool consists of three parts: Library Cache, Dictionary Cache, and Control Structure. Here, we mainly introduce the Library Cache.
2. Library Cache

Library Cache is mainly used to store executable objects in the system (such as SQL statements, PL/SQL blocks, and related objects) and related information (parsing information, executable code, related objects and information, related control structures, etc.) to achieve fast access and sharing of these executable objects and their related information.

3. Hash Bucket

? ? The Hash Bucket is mainly used to quickly locate and access specific executable objects. Each Hash Bucket corresponds to a linked list that connects multiple executable objects, the form of these executable objects in the linked list is the Handle of the executable object. When you need to locate or access a specific object, by performing specific hash calculations on a specific SQL or PL/SQL object, the system can quickly learn the hash bucket where the specific object should be located, and the linked list of each hash bucket is relatively short, especially in the later version of Oracle, the linked list of the hash bucket is shorter. In this way, it is very fast to locate and access specific objects in the hash bucket linked list. As for the number of hash buckets in the Library Cache, the system automatically calculates the number based on the size of the Shared Pool to ensure that the number of hash buckets is sufficient, it does not affect the efficiency of location and access because the number of objects in the hash bucket is too large. The hash bucket is as follows.

4. Hash Latch

? ? As mentioned above, the hash bucket and Its linked list will cause conflicts and problems when multiple users access these hash buckets and linked lists at the same time, to enable users to access and share hash buckets and their linked lists in an orderly manner, Oracle uses a mechanism called hash Latch to protect these shared resources, when a user needs to access or modify these hash buckets and their linked lists, he must first obtain the hash Latch on them. During this period, other users or sessions that need to obtain the same hash Latch need to wait, until the user holding the hash Latch is released, it is suggested that not every hash bucket has a hash Latch, but that a hash Latch overwrites multiple hash buckets, if different users do not access the same hash bucket and Its linked list, they only need to wait for the hash Latch to be released if it is overwritten by the same hash Latch. The hash Latch is as follows.

5. Library Cache Handle

As mentioned above, each hash bucket connects multiple executable objects through a linked list, and these executable objects exist in the form of object handles, these handles contain related properties of these objects, such as names, tags, pointers to the object memory address, and so on. Library Cache Handle is as follows.

6. Library Cache Object
The Handle on the linked list in the hash bucket corresponds to the executable Object (Library Cache Object), which is composed of several independent Heap objects. Specifically, when Handle on the linked list in the hash bucket points to these executable objects, it actually points to its first heap 0, which records the Heap) such as pointer information, these heap details include:
? ? 1) Dependency Table: Other Object Information on which the current Object (Library Cache Object) depends, such as the Table, view, and index on which the Object depends.
? ? 2) Child Table: the sub-LCO of the current object and its related information.
? ? 3) Data Block: the SQL statement, execution plan, execution text, and other information of the current object.
? ?...

? ? The Library Cache Object is as follows.

7. The Library Cache is as follows.

? 8. Parent Cursor/Child Cursor (Parent Cursor/Child Cursor)

? ? ? When an Oracle user submits executable objects such as SQL or PL/SQL, a cursor object is generated in the Library Cache, which can be shared by multiple sessions, this not only saves resources but also improves efficiency. At the same time, each Cursor is expressed and limited by two parts, namely, the Parent Cursor and Child Cursor, which can be queried and understood through the dynamic view of the system. The parent cursor and child cursor are as follows.