Overview of library cache and dictionary cache in Oracle

The main function of library cache is to store user-submitted SQL statements, SQL statement-related parse tree (parse tree is the representation of all objects involved in SQL statements), execution plan, user-submitted PL/SQL program block (including anonymous blocks, stored procedures, packages, Functions, and so on, and the code they convert to be executed by Oracle. To manage these memory structures, the library cache contains a number of control structures, including lock, pin, dependency table, and so on.

The library cache also holds a lot of information about database objects, including tables, indexes, and so on. Information about these database objects is obtained from the dictionary cache. If the user modifies the object information in the library cache, such as adding a column to the table, the changes are returned to the dictionary cache.

All the information units stored in the library cache are called objects (object), which can be divided into two classes: a class called a storage object, which is the database object mentioned above. They are created by explicit SQL statements or Pl/sql programs and must be deleted by explicit SQL commands if you want to delete them. Such objects include tables, views, indexes, packages, functions, etc., and the other is called the Transition object, which is referred to as the user-submitted SQL statements or submitted Pl/sql anonymous block, and so on. These transition objects are generated during the execution of SQL statements or Pl/sql programs, and are cached in memory. If the instance is closed, it is deleted, or it is swapped out because of insufficient memory.

After the user submits the SQL statement or pl/sql the program block to the shared pool, an executable object is generated in the library cache, which is called a cursor (cursor). Instead of confusing the cursor here with the standard SQL (ANSI SQL) cursor, the cursor in standard SQL refers to the SQL form that returns multiple records, which need to be defined, opened, and closed. The cursor mentioned below, if not specifically stated, refers to an executable object in the library cache. Cursors can be shared by all processes, that is, if all 100 processes execute the same SQL statement, then all 100 processes can share the cursors produced by the SQL statement, thus saving memory.

Each cursor is represented by at least two objects 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. What you see from the V$sqlarea view is information about the parent cursor, and one or more objects are called child cursors (children cursor), and if the SQL text is the same, the user who might submit the SQL statement is different, or the user submits the SQL statement that involves the same noun, and so on. It is possible to generate different child cursors. Because the text of these SQL statements is exactly the same, but the context is different, such an SQL statement is not an executable object and must be refined into multiple child cursors before it can be executed. A child cursor contains a program code block for an execution plan or a Pl/sql object.

Before introducing the internal management mechanism of the library cache, the so-called hash algorithm is introduced briefly.

The hash algorithm is used internally by Oracle in the process of implementing management. The hash algorithm is a technique that can be used for fast locating and locating. The so-called hash algorithm, is based on the value to find, the value of the use of a certain hash function after the value of the index number. Enter a list of values for the index number (which can be understood as a two-dimensional array), and then compare the values that are contained in it to find the value. This eliminates the need to scan the entire list of values to find the value, which is obviously much less efficient than the hash lookup method. where each index number corresponds to the value listed in Oracle is called a hash bucket.

Let's enumerate one of the simplest hash algorithms. Suppose our list of values can have up to 10 elements, that is, 10 hash bucket, each bucket can contain up to 10 values. Then the corresponding two-dimensional array is t[10][10]. We can define the hash algorithm for n MOD 10. With this algorithm, all incoming data can be placed evenly in 10 hash bucket, hash bucket number from 0 to 9. For example, we put 1 to 100 through this hash function evenly into the 10 hash bucket, when looking for 32 where, as long as the MOD 10 equals 2, so that 32 must be located in the 2nd hash bucket, so to t[2][10] to find, The 2nd hash bucket has 10 numbers, one by one to compare 2nd hash bucket Whether there are 32 on it. We can see here that we need to compare 11 times (1+10) in order to find the value 32. If you do not use the hash algorithm, but the way to traverse the scan, you need to compare 100 times.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

The library cache is managed using multiple hash bucket, and its hash algorithm is certainly much more complex than the one we listed earlier. Each hash bucket is followed by multiple handles (the handle is called the Library cache object handle), which describes some attributes of the object in the library cache, including the name, tag, pointer to the memory address where the object is. In fact, the hash bucket is embodied by the concatenated object handle, which is itself a logical concept, a logical group rather than an object as a concrete entity. Oracle automatically calculates the number of hash buckets based on the shared pool size specified by Shared_pool_size, and the larger the shared pool, the more object handles can be mounted.

When an SQL statement enters the library cache, the SQL text is converted to the corresponding ASCII value, and the ASCII values are then hashed, and the parameters of the incoming function include the name of the SQL statement (name, For SQL statements, the name is the text of the SQL statement, and the namespace (namespace, which is "SQL area" for the SQL statement, represents the shared cursor.) You can find all the namespace from the view V$librarycache. The hash function is used to get a value that is the number of the hash bucket, so that the SQL statement is assigned to the hash bucket of the number.

When a process needs to process an object, such as processing a newly entered SQL statement, it applies the hash function algorithm to the SQL statement to determine the number of the hash bucket it is in, and then goes into the hash bucket to scan to determine if the same SQL statement exists. It is possible that the handle of the object exists, but the object to which the handle is pointing has been swapped out of memory. At this point the corresponding object must be loaded again (reload). It is also possible that the handle of the object does not exist, meaning that the SQL statement was first executed, when the process had to rebuild an object handle to hang onto the hash bucket and then reload the object. The SQL statement has many objects (most intuitively the text of the SQL statement) that are stored in the library cache and are accessed by handles. You can read the library cache as a book, and the SQL statement object is the page in the book, and the handle is the directory, through the directory can quickly navigate to the specified content of the page.

The object handle holds the name of the object, the namespace to which the object belongs (namespace), some tags on the object (such as whether the object is read-only, local or remote, whether the pin is in memory medium), and some statistics about the object. One of the most important things to store is a pointer to the heap 0 object. Heap 0 is used to store information that is directly related to objects, such as object types, object-related tables, actual execution plans, and execution of Pl/sql machine code. Heap is composed of one or more chunk, these chunk can be distributed in the library cache, do not need continuous distribution.

We can query view V$db_object_cache to show which objects in the library cache are cached and the size of these objects. For example, we can use the following SQL statement to display the top 3 size objects in each namespace:

SELECT *

From (select Row_number ()-Partition by namespace

ORDER BY Sharable_mem Desc) Size_rank,

Namespace

Sharable_mem,

SUBSTR (name, 1,) name

From V$db_object_cache

ORDER BY Sharable_mem Desc)

where Size_rank <= 3

Order by namespace, Size_rank;

The dictionary cache is a memory area that is specifically used to hold objects owned by the SYS schema. Use dictionary cache with behavior units, and unlike other such as buffer cache to data block, so dictionary cache is also called row cache. The purpose of constructing dictionary cache is to speed up the parsing of SQL statements, because dictionary cache holds all table definitions, storage information, user rights information, constraint definitions, rollback segment information, table statistics, and so on. Basically we don't have to pay too much attention to it.

Oracle has no initialization parameters to control how much memory the library cache and dictionary cache should occupy, we can only control the size of the shared pool. As previously said, a usable chunk in shared pool, if the data dictionary information is stored, then it belongs to the dictionary cache. Otherwise, if information such as SQL text or execution plan is stored, it belongs to the library cache. Sometimes a chunk originally may put the SQL text, later because the memory is not enough to be covered by the data dictionary information, then the chunk from the library cache into dictionary cache. So we can't control the size of the library cache and dictionary cache individually.