In-depth analysis of Oracle Library Cache

In-depth analysis of Oracle Library Cache

In-depth analysis of Oracle Library Cache

Each object in the database cache is divided into multiple parts in the database cache for storage. Oracle aims to achieve more flexible memory management, because it is slower than searching for small contiguous memory. if a database cache object (such as an execution plan of an SQL statement), the memory occupied by it is cut into four small blocks, which are stored separately in the database cache and are not connected to each other. In order to combine the four small blocks, the cache object of the Oracle database is allocated with a small block of memory, which contains the addresses of the other four small blocks of memory, there are also some basic information about the cached object in this database, such as the name and type. This memory is called the handle of the cached object in the database ).

The Library Cache structure is as follows:

When accessing the database cache object, such as soft resolution, you must read the execution plan from the database cache. Oracle first finds the handle and reads the information in the handle, which is called a database cache Get. If the database cache does not contain the object's handle information, Oracle will re-allocate memory and construct a handle in the database cache. This means that the database cache handle is not hit (Get Miss ). On the contrary, if an object handle is found in the database cache, the database cache handle hits (Get Hit ). Get Miss occurs during hard parsing. Soft Parsing is Get Hit.

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

After the other memory block addresses in the handle are retrieved, each access to a memory block is called a database cache Pin. If the corresponding memory block is no longer in the memory, this is Pin Miss, and the Pin is not hit. The opposite is Pin Hit.

When the objects in the database cache change, some other objects will become invalid (Invalidation ). For example, if you modify the structure of a table, the execution plan of the SQL statement selected for the table will become invalid. In fact, most DDL operations on tables will invalidate the execution plan of related SQL statements. Even Grant and Revoke operations that seem unrelated to execution plan consumption will be ineffective. If you have a table named TAB1, you have published an operation like "Grant a permission on tab1 to a user" or "revoke a permission on tabl from a user, all execution plans related to the TAB1 table will be invalid. After the cache is invalid, all memory blocks except the handle are cleared. After the object is used, the object must be reloaded. In the preceding example, if you use DDL statements for TAB1, all execution plans that use TAB1 statements will be invalid. When you use these statements, you must re-parse the statements.

Case:
At 16:54:51 SCOTT @ prod> select * from dept1;
DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00. 06
16:54:03 SYS @ prod> select NAMESPACE, GETS, PINS, RELOADS, INVALIDATIONS from v $ librarycache
NAMESPACE GETS PINS RELOADS INVALIDATIONS
-------------------------------------------------------------------------
SQL AREA 7722 30134 30 97
TABLE/PROCEDURE 10906 8328 88 0
BODY 608 801 0 0
TRIGGER 24 41 0 0
INDEX 96 62 0 0
CLUSTER 485 300 0 0
QUEUE 36, 168, 0, 0
App context 14 14 0 0
RULESET 3 3 0 0
Subpartition 5 5 0 0
EDITION 58 99 0 0
DBLINK 5 0 0 0
Object id 59 0 0 0
SCHEMA 3584 0 0 0
DBINSTANCE 1 0 0 0
15 rows selected.
Elapsed: 00:00:00. 01
Perform DDL operations on the Access Object dept1, resulting in invalid execution plan.
16:55:01 SCOTT @ prod> grant all on dept1 to hr;
Grant succeeded.
Elapsed: 00:00:00. 26
At 16:55:24 SCOTT @ prod> select * from dept1;
DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00. 04
16:55:29 SCOTT @ prod & gt;
16:55:08 SYS @ prod> r
1 * select NAMESPACE, GETS, PINS, RELOADS, INVALIDATIONS from v $ librarycache
NAMESPACE GETS PINS RELOADS INVALIDATIONS
-------------------------------------------------------------------------
SQL AREA 7781 30422 32 100
TABLE/PROCEDURE 10980 8420 88 0
BODY 623 817 0 0
TRIGGER 26 43 0 0
INDEX 96 62 0 0
CLUSTER 488 302 0 0
QUEUE 36, 168, 0, 0
App context 14 14 0 0
RULESET 3 3 0 0
Subpartition 5 5 0 0
EDITION 59 101 0 0
DBLINK 5 0 0 0
Object id 59 0 0 0
SCHEMA 3595 0 0 0
DBINSTANCE 1 0 0 0
15 rows selected.
Elapsed: 00:00:00. 03
16:55:34 SYS @ prod>

 

Therefore, you need to pay attention to using DDL statements. If you do not need to use DDL immediately, we 'd better wait until the database is not busy and then execute DDL. As a DBA, these 1.1 statements must be kept in mind that, unless executed immediately, any DDL statements will be executed when the database is not busy.

Additionally, the invalidation is not the same as the aging of the database cache object. Aging means that all memory blocks with objects in the handle are cleared. If it is invalid, only the object handle is retained in the database cache, but all other memory blocks are cleared. When an invalid object is called again, Oracle calls it to the database cache again. This operation is called Reload. Generally, Reload is irrelevant to Get, because Reload occurs only after the object is invalid, and the invalid object does not affect the object handle. Okay. Now let's take a look at the meaning of these values.

We have already talked about Get, Pin and their hit and miss, and what are Invalidation and Reload. In the OLTP system, the Get hit rate should be above 90%. The ratio of Reload to Pin times should be less than 1%. If the value range is exceeded, the usage of the database cache is incorrect. There are two main causes of the problem: No variable bound or the library cache is too small. However, the ratio of Reload to Pin is too high, or it may be caused by DDL execution during busy hours.

In addition, the first column of V $ librarycache is NAMESPACE, that is, NAMESPACE. It is equivalent to the type of objects stored in the database cache. What does namespace mean? Is the range where the object name is located. For example, the names of tables and columns are not in the same range. That is to say, the table name and column name can be repeated, and the user name and table and column name are not in the same range. The user name can also be the same as the table and column name. I can have a table named AA or a user named AA. The two AA instances are in different ranges and their namespaces are different. It seems that there is a phone number 12345678 in Beijing and a 12345678 phone number in Shanghai. Although the two phone numbers are the same, they do not cause problems because they are in different ranges. This range can also be said to be type.

I will show all the rows of V $ librarycache (only part of the column ):
SQL> select * from v $ librarycache;
NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS
------------------------------------------------------------------
SQL AREA 21811 4149. 190225116 120258 105272
TABLE/PROCEDURE 25152 16372. 650922392 60649 46008
BODY 4360 4098. 939908257 5931 5537
TRIGGER 320 251. 784375 1655 1576
INDEX 453 128. 282560706 2065 1531
CLUSTER 755 728. 964238411 2339 2296
OBJECT 0 0 1 0 0
PIPE 0 0 1 0 0
Java source 0 0 1 0 0
Java resource 0 0 1 0 0
Java data 0 0 1 0 0

We can see that there are a total of 11 namespaces in the database cache. We can basically say that there are 11 types of information in the database cache. A very important indicator is the library cache hit rate, which is the GETHITRAIO of all objects in the library cache. The calculation method is as follows:

Select 1-sum (gethits)/sum (gets) fromv $ librarycache;

This ratio should be above 90%.

Another important factor is the ratio of Reload to Pin, which should be less than 1%.

If the ratio is not reached, the solution is simple, the problem is that the SQL statement is not shared, or the sharing pool is too small.

For the Library cache Hit rate, you can also view the Library Hit % item in the STATSPACK report. We have already said this.

For more details, please continue to read the highlights on the next page:

1 2 Next Page