Shared Pool One: Library cache logical Structure

What is a shared pool?

The Shared pool is part of the SGA because it is part of the SGA, which means it can be accessed by all processes. The Shared pool is composed of many memory blocks on the physical level, which are called Chunk. But chunk are of varying size, in memory a chunk is contiguous.

Divided by Logical structure: The Shared pool consists of 3 parts: the Library cache,dictionary cache and control Structure.

Because the library cache is the most important in the shared pool , this article mainly explains the structure of the library cache : library cache latch,library cache lock, Library Cache pin.

What is the Library Cache?

The Library cache is comprised of shared SQL areas, private SQL zone (private sqlareas, if a shared server is configured), PL/SQL stored procedures and packages, and some control information ( For example locks and library cache handles) memory

Library Cache Logical Structure

• Hash Bucket/bucket

In Oracle, the hash algorithm is used extensively in the process of implementing management. The so-called hash algorithm, is based on the value to be found, the value of a certain hash algorithm after the index number (hash bucket), through the index number (hash bucket) to the corresponding list (bucket), and then into the identified list, Make a comparison of the values contained therein to find the value.

The physical structure of a linked list (bucket) is made up of chunk. The number of chunk in each linked list (bucket) is not fixed, the chunk size is the same in the same list (bucket), the size of chunk in different buckets is different.

• Library Cache Handle

Our access to all objects in the library cache is achieved by using the library cache handle, which means that we want to access the library cache object, we must first find the library cache handle.

Library cache handle points to the library cache Object;library cache handle contains the name of the library object, namespace, timestamp, reference list, The lock object and the list information for the Pin object, and so on. The library cache handle is also used by the library cache to record which user has lock on this handle, or which user is waiting to get the lock. So here we also know that the library cache lock is happening on the handle.

• Library Cache Object

The library cache object is made up of separate heap, which says that the library cache handle points to the library cache object, in fact handle is pointing to the first heap, the heap We call it heap 0. Heap 0 Records pointer information to other heap.

1. Dependency Table-Other LCO information that the current LCO relies on, such as tables, views, etc. that are dependent on the SQL statement. (Lco:library Cache object)
2. Chile Table----Save the child Lco information for the current LCO.
3. Data blocks---Save SQL statements, execute plans, execute text, and more

• Parent Cursor/child Cursors

When a user submits an SQL statement or a PL/SQL program block to the shared pool of Oracle, an executable object generated in the library cache is called a cursor.

Instead of confusing the cursor here with standard SQL (ANSI SQL) cursors, standard SQL cursors refer to the SQL form that returns multiple records, which need to be defined, opened, and closed. The cursors mentioned below, if not specifically stated, refer to the executable object in the library cache.

Cursors can be shared by all processes, meaning that if 100 processes execute the same SQL statement, the 100 processes can use the cursors produced by the SQL statement at the same time, saving memory.

Each cursor is a minimum of two objects represented by two or more objects in the library cache. An object, called the parent cursor, contains the name of the cursor and other information that is independent of the submitting user (the V$sqlarea view is all about the parent cursor), and one or more objects are called child Cursors (children cursors). For example, the SQL text is the same, but the user committing the SQL statement is different, it is possible to generate different child cursors.

Reference blog:

http://czmmiao.iteye.com/blog/1273261

http://blog.csdn.net/robinson1988/article/details/6037925

Shared Pool One: Library cache logical Structure