Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the internal principle of library cache in sga. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

I. Overview

Library cache (library cache) is a memory area in shared pool. Its main function is to cache the parsing plan, parsing tree and other objects corresponding to the just executed sql statements and pl/sql (including stored procedures, packages, functions, triggers). When the same sql or pl/sql is executed again, you can directly utilize those related objects in library cache without having to parse from scratch again. This improves the execution efficiency of these statements.

Check the location of library cache in memory.

The objects cached in library cache are called library cache objects (library cache object). All library cache objects are stored in library cache in the structure of library cache object handles (library cache object handle). Oracle accesses library cache objects by accessing library cache object handles.

The library cache object handle is a c language complex structure customized by oracle, which is stored in library cache in the way of hash table, which means that oracle will store and access the corresponding library cache object handle through hash operation.

The following figure analyzes the composition of the library cache from the perspective of hash:

As you can see from the figure above, the entire library cache can be seen as a set of hash bucket, each hash bucket corresponding to a different hash value. For a single bucket, all library cache object handles with the same hash value are stored, and different library cache object handles in the same hash bucket are linked with pointers, that is, different library cache object handles in the same hash bucket actually form a linked list of library cache object handles (library cache object handles). A library handle manages a libirary cache object (lco), handle acts as metadata and pointer to the actual lco, and the lco holds the actual information.

When running select * from employees, oracle will calculate the hash value of sql text to find the relevant hash bucket and traverse the database cache object handle list. If you can find it, you can directly use parsing plan, parsing tree and other information. If you can't find it, you need to parse from scratch, generate parsing tree, parsing plan, etc., and store it in library cache object handle.

Library cache object handle structure diagram:

Library cache object handle is composed of name, namespace and other attributes, focusing on the analysis of name, namespace, heap 0 these three attributes.

The property "name" represents the name of the library cache object corresponding to the library cache object handle. For example, if it is the library cache object handle corresponding to the sql statement, the attribute name is the sql text of the sql statement; if it is the library cache object handle corresponding to the table, the attribute name is the table name of the table.

The property "namespace" represents the grouping name of the library cache object corresponding to the library cache object handle, and different types of library cache object handles may belong to the same group.

Attribute "heap 0 pointer": here we will explain that library cache object handle is similar to the structure of c language, and there are some substructures nested in library cache object handle, where heap 0 pointer is the pointer to the substructure heap 0.

In library cache, the number of bucket initializations is controlled by the hidden parameter _ kgl_bucket_count parameter.

If the value is 9, 2 ^ 9 * 256 "131072 bucket should be allocated theoretically.

Through library cache dump, it can be found that the bucket is 131072. In library cache dump, size is the number of Buckest and count is the number of Object Handles.

Heap 0 pointer (heap 0 object) is a pointer to the substructure heap 0. The structure of heap 0 is as follows:

Heap 0 is also a complex structure with many properties.

The tables attribute: records the collection of library communication object handle addresses related to the library cache object where the heap 0 resides. Tables is divided into many subcategories.

Data blocks property.

II. Dump verification

Create an identical table for sys users and test users, respectively.

SQL > show user

USER is "SYS"

SQL > create table t as select * from v$mystat

Table created.

Set up another connection and log in with the database user test

SQL > show user

USER is "TEST"

SQL > create table t as select * from v$mystat

Table created.

Execute select * from t where t.STATISTIC#=671 on the basis of two users

Query the sql_id and hash_value of the sql

Execute library cache dump

SQL > alter session set events' immediate trace name library_cache level 10'

Session altered.

If you query dump through sql_id or hash value, you can find the content about the sql in dump, as shown in the following figure

The above figure is an example to analyze the structure of library cache.

Bucket: # = 114283 Mutex=0x9bdc28e8 (0,20,0,6)

LibraryHandle: Address=0x84e5fb00 Hash=cb15be6b LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

-- bucket number. Hash=cb15be6b uses hexadecimal here, and the value converted to decimal is 3407199851, which is exactly the same as the value found in v$sqltext above.

ObjectName: Name=select * from t where t.STATISTIC#=671

-- the name of object is the name attribute mentioned above, that is, lco (library cache objects)

FullHashValue=9581b1f4c50e25f2e8500930da5a0101 Namespace=SQL AREA (00) Type=CURSOR (00) Identifier=0 OwnerIdn=0

The namespace mentioned above is not quite clear what fullhashvalue means.

ChildTable: size='16'

Child: id='0' Table=0x87b03f60 Reference=0x87b039c8 Handle=0x98efe438

Child: id='1' Table=0x87b03f60 Reference=0x87b03d10 Handle=0x83595530

-- two child cursor

These are the internal principles of library cache in sga. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.