Oracle Optimization-Cache objects and Data

Author: skate
When a performance bottleneck occurs in the system, try to consume as few unnecessary resources as possible. The last step is to balance CPU, memory, Io, network and other resources so that the database
It can run stably.

Oracle Database optimization is fundamental

1. Minimize resource consumption, such as optimizing SQL and reducing the resource consumption of SQL.
2. If the resource consumption cannot be further reduced, try to keep the data close to the CPU, that is, transfer the data from the hard disk to the memory (the memory read/write speed is fast)
Or change to a faster disk.

This article briefly summarizes how to cache data and database objects (that is, move data to the memory, increase the memory hit rate, and increase the overall Io speed)

1. cache data
2. Definition of cached data objects, such as package, procedure, PL/SQL, and SQL (that is, cursor)

The above two types of data exist in the share pool and buffer pool of the two most important parts of Oracle, And the hit rate of these two pools is also increased.
I/O speed, while I/O is the slowest technology development and the biggest bottleneck of the system.

1. cache data

Oracle Data occupies a large amount of storage space, rather than data in the database dictionary. Oracle data types are generally:

SQL> select se. segment_type from dba_segments SE group by se. segment_type;

Segment_type
------------------
Lobindex
Index Partition
Table Partition
Nested table
Rollback
Lob Partition
Lobsegment
Index
Table
Cluster
Type2 undo

11 rows selected

SQL>

Most of the time, putting the data in the memory will greatly improve the system performance.

The buffer pool is divided into three sub-pools, which are mainly managed by the LRU algorithm.

Default buffer pool: All data blocks exist by default and follow the LRU algorithm of the pool.

Keep Buffer Pool: if the specified data block is cached in the keep area, it is unlikely that the data block will be exchanged by other data blocks for some other operations, even if it is not used for a long time, only follows the LRU of the pool.

Recycle Buffer Pool: Set recycle because there are sometimes large and rarely used table operations. If you do not set a separate cache area, the data block in the default cache area is swapped out by the occasionally used data, which has a small space. Therefore, it is usually released after use, it only complies with the LRU algorithm of the pool.

Take table as an example:

Modify the table cache space

Alter table a_user storage (buffer_pool keep) Cache/nocache; --- cache the table a_user to the hottest end of the keep buffer pool/immediately release the table a_user from the keep buffer pool

Alter table a_user Cache/nocache --- cache the table a_user to the hottest end of the default buffer pool/immediately release the table a_user from the default buffer pool

Eg:

1)

SQL> ALTER TABLE a_user storage (buffer_pool keep) cache;

Table altered

2)

SQL> select T. table_name, T. cache, T. buffer_pool from user_tables t where T. table_name = upper ('A _ user ');

Table_name cache buffer_pool
-------------------------------------------------------------
A_user y keep

Note:

User_tables.cache: once this table is read into the buffer cache, it will be placed on the hot end of the linked list ~ Try not to squeeze buffer cache
User_tables.buffer_pool: puts this table into a special buffer cache. These special buffer caches are independent.

View v $ db_object_cache.kept: whether the object is resident in the Shared Pool (yes/no) depends on whether the object has used the PL/SQL Process

Dbms_shared_pool.keep "Keep" (permanently fixed in memory)

Eg:

SQL> select OC. Name, OC. type, OC. Kept from V $ db_object_cache OC where OC. type = 'table' and OC. Owner = 'hpo ';

Name type kept
----------------------------------------------------------------------------------------------------------------
A_user table Yes

This V $ db_object_cache view provides object statistics in the library cache (shared pool), provides more details than V $ librarycache, and is often used to find activity objects in the shared pool.

Therefore, if you have never used an object, it does not exist in this view. It will only appear in this view when it is used.

See introduction to the dynamic performance view of thinking 3: http://space.itpub.net/7607759/viewspace-22241

For example, modify the index buffer pool.

Alter index idx_org_type storage (buffer_pool keep) cache;

Partition tables and partition indexes do not seem to be able to place each partition in a different buffer pool.

2. Definition of cached data objects, such as package, procedure, PL/SQL, and SQL (that is, cursor)

The above section describes how to cache data in the buffer pool as much as possible to improve the data hit rate in the memory, avoid reading and writing data from the disk, and indirectly improve the system I/O capability;
The data cached by the buffer pool is the final target data of the user. to transmit the final target data of these users to the user, Oracle needs to use other
These actions are completed in the share pool. The functions include caching statement text, analyzing code, execution plan, and data dictionary.
Permission definitions of tables and columns in; Share pool mainly uses LRU algorithm, so how to cache the data as much as possible is what we will talk about below

Oracle is divided into the SQL engine and the PL/SQL engine, respectively, to complete the parsing of SQL and PL/SQL, and here the parsing is very resource-consuming, so we need to find a way
Minimize resolution and reuse code to improve efficiency

A. Code reuse

When determining whether a statement needs to be parsed (hard), the hash value of the statement is compared first. The following two methods help to obtain the same hash value, so that code can be reused, increase hit rate:

1) All developers use the same encoding specification (including uppercase and lowercase letters, spaces, and line breaks );

2) bind a variable. (increasing the hit rate may result in a bad execution plan because the optimizer does not know the exact value of the variable and cannot use the column chart statistics ).

Adjust the initialization parameters:

Open_cursor

This parameter specifies the maximum number of cursors that can be opened by each user session. Increasing this value can reduce the chance of re-parsing statements opened by the session and increase the hit rate, but requires a larger Shared Pool space. Make sure that this value is sufficient. Increasing this value will not affect the memory size.

Cursor_space_for_time

The default value is false. If it is set to true, shared SQL area is in the shared pool when cursor is enabled and cannot be swapped out ), this improves the SQL Execution efficiency. In addition, the private memory of the cursor in the PGA is not closed after the SQL statement is executed. It can be directly used during the next execution, saves time for memory allocation and release. This setting helps improve the efficiency of SQL Execution when the same SQL statement is executed repeatedly. However, this parameter will increase the use of the Shared Pool. If the Shared Pool is insufficient or the fragments are serious, this parameter will aggravate the problem. Therefore, you can set it to true only when the Shared Pool is large enough, if it is set to true, re-resolution can be reduced, the hit rate is increased, and cursor execution is accelerated (space for time ).
 
Generally, this parameter is not required to be enabled. Generally, enabling does not greatly improve the system performance. It is very frequent for parse, and SQL statements are executed frequently, causing serious fragmentation in the sharing pool, we recommend that you do not use it. Of course, this parameter has a special purpose in special cases. Otherwise, this parameter does not need to exist.

Session_cached_cursors

The default value is 0, that is, no cache cursor. If session_cached_cursors is set and a cursor is frequently called, it will be cached when it is called for the third time, the next time a cached cursor is called, it can save the parse process and improve the efficiency of SQL Execution. These caches are also managed using the LRU algorithm. Note that the session_cached_cursors value cannot exceed the open_cursors value. Before setting the session_cached_cursors parameter, you must first determine whether the size of the Shared Pool is sufficient to support buffering these SQL statements. Because session_cached_cursors is for each session, it is important to set session_cached_cursors when setting session_cached_cursors for OLTP systems with hundreds or even thousands of sessions, A large shared pool is required. If the Shared Pool space is insufficient after this parameter is adjusted, adjusting the size of the shared pool or reducing session_cached_cursors is the operation that DBA should perform.

Cursor_sharing

Defines the mode in which cursor shares, such as excat, force, and similar. If the default mode is used (precise), the system does not automatically merge and share cursor, only cursor with completely consistent writing can be shared. If it is set to similar, peeking will be performed during SQL parse. If you think it can be shared, the SQL will be shared, and Oracle will automatically convert unbound variables to bound variables. Note that if a field in a where condition has a bar chart, the peeking process considers the sharing of this SQL statement unsafe, and the SQL statement will not be shared, at this time, this cursor will generate a sub-cursor to form a new version. In this case, Parser considers that sharing is safe and does not generate a new version only when the values of non-bound variables are the same. If it is set to force, similar to similar, non-bound variables will be converted into binding variables. Unlike similar, Parser forcibly considers sharing to be safe, so it does not care about the column chart information, share the cursor directly.

The best recommendation for setting cursor_sharing is to use precision. during the development process, bind the variable to the place where the variable is bound, and do not use the variable where it is not used (when should it not be used ?), No. If similar is used but non-default values are used, you need to check for bugs and install patches as soon as possible. In addition, you need to test the application, some SQL statements may encounter errors (not bugs) when using variable binding in some versions. Force has many bugs and must be tested well.

SQL statement analysis can be divided into soft analysis and hard analysis. Reducing soft analysis and hard analysis, especially hard analysis, plays a key role in reducing CPU usage.
During SQL Execution, if a statement has been buffered, this SQL statement does not need to be analyzed and can be executed directly. Therefore, it ensures that the SQL statement can grow in the buffer.
The existence of time can reduce the occurrence of SQL analysis. Two parameters can be used to control the duration of SQL statements in the session Buffer Pool: open_cursors and session_cached_cursors.

How can we adjust these two parameters? Check the current usage of session_cached_cursors and open_cursors.

Select 'session _ cached_cursors 'parameter,
Lpad (value, 5) value,
Decode (value, 0, 'n'/A', to_char (100 * used/value, '000000') | '%') Usage
From (select max (S. Value) used
From v $ statname N, V $ sesstat s
Where n. Name = 'session cursor cache count'
And S. Statistic # = n. Statistic #),
(Select value from V $ parameter where name = 'session _ cached_cursors ')
Union all
Select 'open _ cursors 'parameter,
Lpad (value, 5) value,
To_char (100 * used/value, '000000') | '%' usage
From (select max (sum (S. Value) used
From v $ statname N, V $ sesstat s
Where n. Name in
('Opened cursors current ', 'session cursor cache count ')
And S. Statistic # = n. Statistic #
Group by S. Sid ),
(Select value from V $ parameter where name = 'open _ cursors ');

View system-level cursor hit rate, soft analysis and hard analysis Ratio

Select
To_char (100 * SESS/CILS, '192. 00') | '%' cursor_cache_hits,
To_char (100 * (CILS-sess-hard)/CILS, '2017. 00') | '%' soft_parses,
To_char (100 * hard/CILS, '192. 00') | '%' hard_parses
From
(Select value callfrom v $ sysstat where name = 'parse count (total )'),
(Select value hard from V $ sysstat where name = 'parse count (hard )'),
(Select value sess from V $ sysstat where name = 'session cursor cache hits ');

If the returned session_cached_cursors buffer usage is 100%, it indicates that the session_cached_cursors parameter is not large enough. If the size of the Sharing pool is sufficient, You can adjust this parameter until the usage is lower than 100%.

For systems without variable binding, if cursor_sharing is set to exact, pay attention to it when setting session_cached_cursors. Due to application reasons, the Reuse Rate of cursor is very low, if session_cached_cursors is set too high, the shared pool space is greatly occupied, and the performance of the shared pool may occur when the system load is high.

B. Retain large objects

Loading large objects is the main cause of sharding in the Shared Pool. because a large number of small objects need to be released from the shared pool to free up space, the response time will be affected.
To avoid this, we need to keep the large and frequently used objects in the Shared Pool. What objects need keep?

1) frequently used large objects, such as standard packages, use objects with shared memory exceeding the threshold value
2) triggers that are often executed in common tables
3) sequence, because when the sequence is released from the shared pool, the serial number is lost.

Use the alter system flush shared_pool command to refresh the shared pool, but not refresh the reserved object.

For example, use the following SQL statement to find objects with a length greater than 500 characters and a shared memory greater than 10000 bytes.

Select *
From v $ db_object_cache oC
Where length (OC. Name)> 500
And OC. Type in ('package', 'processed', 'function', 'package body ')
And OC. Kept = 'no'
And OC. sharable_mem> 10000

View anonymous PL/SQL statements with a length of more than 500 characters and a shared memory of more than 20000 bytes

Select *
From v $ sqlarea SQ
Where sq. command_type = 47
And length (sq. SQL _text)> 500
And sq. sharable_mem> 20000

To keep these objects keep in the Shared Pool, use dbms_shared_pool.keep. This package is not installed by default. You need to run the dbmspool. SQL script.

Sys @ skatedb> @/home/Oracle/10.2.0/db_1/rdbms/admin/dbmspool. SQL

Package created.

Grant succeeded.

View created.

Package body created.

Sys @ skatedb>

SQL> DESC dbms_shared_pool
Element type
----------------------------------
Sizes procedure
Keep procedure
Unkeep procedure
Aborted_request_threshold procedure

SQL> DESC dbms_shared_pool.keep
Parameter type mode default?
-----------------------------
Name varchar2 in
Flag char in Y

SQL> DESC dbms_shared_pool.unkeep
Parameter type mode default?
-----------------------------
Name varchar2 in
Flag char in Y

Aborted_request_threshold (threshold_size number, instead of reporting an error if the shared pool is not enough after LRU lookup and memory switching.
Ultra-large objects occupy excessive Shared Pool space.

Unkeep is the anti-operation of keep.

Sizes (minsize number): lists all objects larger than minsize in the shared pool. It is reasonable to find and set large objects in the shared pool.
Aborted_request_threshold of is very useful.

Flag description:

Value kind of object to keep
-----------------------------
-- P package/procedure/Function
-- Q Sequence
-- R trigger
-- T Type
-- Js java Source
-- JC Java class
-- Jr Java Resource
-- JD Java shared data
-- C cursor

If this flag is empty, its default value is 'P'

Retain package

SQL> exec dbms_shared_pool.keep ('package _ name', 'P ');

Keep squence (avoid sequence)

SQL> exec dbms_shared_pool.keep ('sequence _ name', 'q ');

Retain anonymous Blocks

SQL> select address, hash_value
2 from V $ sqlarea SQ
3 where sq. command_type = 47
4 and length (sq. SQL _text)> 500
5 and sq. sharable_mem> 20000
6;

Address hash_value
--------------------------
201710000a78655e8 1599878706

SQL> exec dbms_shared_pool.keep ('address', 'hash _ value', 'C ');

Note: Check what command 47 is.
SQL> select * From audit_actions where action = 47;

Action name
--------------------------------------
47 PL/SQL Execute

Eg:

View the anonymous block that requires keep

SQL> select address, hash_value
2 from V $ sqlarea SQ
3 where sq. command_type = 47
4 and length (sq. SQL _text)> 500
5 and sq. sharable_mem> 20000
6;

Address hash_value
--------------------------
2017100008e8532a8 97348712

1 rows selected

Check whether the current anonymous block is keep

SQL> select OC. Kept, Sq. Address, Sq. hash_value
2 from V $ db_object_cache oC,
3 V $ sqlarea SQ
4 where sq. SQL _text = OC. Name
5 and sq. hash_value = '20140901'
6;

Kept address hash_value
------------------------------
No route 00008e8532a8 97348712

SQL>

Keep anonymous Block

SQL> exec dbms_shared_pool.keep ('2017100008e8532a8, 100 ');

Begin dbms_shared_pool.keep ('2017100008e8532a8, 100'); end;

ORA-01426: Number Overflow
ORA-06512: In "SYS. dbms_utility", line 114
ORA-06512: In "SYS. dbms_shared_pool", line 45
ORA-06512: In "SYS. dbms_shared_pool", line 53
ORA-06512: In line 1

SQL> exec dbms_shared_pool.keep ('2017100008e8532a8, 100', 'C ');

PL/SQL procedure successfully completed

SQL>

Check if it is occupied by keep

SQL> select OC. Kept, Sq. Address, Sq. hash_value
2 from V $ db_object_cache oC,
3 V $ sqlarea SQ
4 where sq. SQL _text = OC. Name
5 and sq. hash_value = '20140901'
6;

Kept address hash_value
------------------------------
Yes 000000008e8532a8 97348712

SQL>

Cancel the keep of an object

SQL> exec dbms_shared_pool.unkeep ('2017100008e8532a8, 100', 'C ');

PL/SQL procedure successfully completed

SQL>

Check whether the current anonymous block is canceled keep
SQL> select OC. Kept, Sq. Address, Sq. hash_value
2 from V $ db_object_cache oC,
3 V $ sqlarea SQ
4 where sq. SQL _text = OC. Name
5 and sq. hash_value = '20140901'
6;

Kept address hash_value
------------------------------
No route 00008e8532a8 97348712

SQL>

The remaining keep packages and sequnce are similar.

Shared Pool fragmentation
Variable binding

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/wyzxg/archive/2010/03/16/5384762.aspx