Buffercache and sharedpool (5. problem diagnosis summary)

[In-depth analysis -- eygle] learning notes 1.2.7 diagnose and solve the primary problem of ORA-04031 error SharedPool is fundamentally only one, is the performance impact of excessive fragments. 1.2.7.1 what is a ORA-04031 error when trying to allocate large blocks of continuous memory in the Shared Pool fails (many times it is due to too many fragments, not really insufficient memory)

[In-depth analysis -- eygle] learning notes 1.2.7 diagnose and solve the ORA-04031 error S hared Pool is the main problem in the root only one, is the performance impact of excessive fragments. 1.2.7.1 what is a ORA-04031 error when trying to allocate large blocks of continuous memory in the Shared Pool fails (many times it is due to too many fragments, not really insufficient memory)

[In-depth analysis-eygle] Study Notes

1.2.7 diagnose and resolve ORA-04031 errors

SThere is only one major problem with the hared Pool, namely the performance impact caused by excessive fragments..

1.2.7.1 what is a ORA-04031 Error

When an attempt to allocate large blocks of continuous memory in the Shared Pool fails (in many cases, due to excessive fragments, rather than insufficient memory), Oracle first clears all the objects currently not used in the shared pool, merge idle memory blocks. If you still don't have enough memory to meet your needs, a ORA-04031 error will occur.

The following pseudocode describes the generation of error 04031:

Scan free lists -- Scan Free Lists

If (request size of RESERVED Pool size) -- if the requested reserved pool Space

Scan reserved list -- scan the reserved list

If (chunk found) -- if the memory block that meets the condition is found

Check chunk size and perhaps truncate -- check the size, which may need to be split

Return -- return

Do LRU operation for n objects -- if the reserved pool is not requested or sufficient memory cannot be found

Scan free lists -- execute the LRU operation instead, release the memory, and re-scan

If (request sizes exceeds reserved pool min alloc)-if the request is greater

_ Shared_pool_reserved_min_alloc

Scan reserved list -- scan the reserved list

If (chunk found) -- if the memory block that meets the condition is found

Check chunk size and perhaps truncate -- check the size, which may need to be split

Return -- if it is found in Freelist or reservedlist, return success

Signal ORA-4031 error -- otherwise the ORA-04031 error is reported.

[Oracle @ felix ~] $ Oerr ora 1, 4031

04031,000 00, "unable to allocate % s bytes ofshared memory (\" % s \", \ "% s \")"

// * Cause: More shared memory is needed than was allocated in the shared

// Pool or Streams pool.

// * Action: If the shared pool is out of memory, either use

// DBMS_SHARED_POOL package to pin large packages,

// Reduce your use of shared memory, or increase the amount

// Available shared memory by increasing the value of

// Initialization parameters SHARED_POOL_RESERVED_SIZE and

// SHARED_POOL_SIZE.

// If the large pool is out of memory, increase the initialization

// Parameter LARGE_POOL_SIZE.

// If the error is issued from an Oracle Streams or XStream process,

// Increase the initialization parameter STREAMS_POOL_SIZE or increase

// The capture or apply parameter MAX_SGA_SIZE.

[Oracle @ felix ~] $

1.2.7.2 bind the variable and cursor_sharing

If SHARED_POOL_SIZE is set to large enough, and can exclude the factors of the Bug, then most of the ORA-04031 errors are caused by a large number of SQL code in the Shared Pool, leading to excessive memory fragments..

Possible causes include:

(1) Insufficient SQL sharing;

(2) A large number of unnecessary resolution calls;

(3) No bound variable is used.

In fact, writing and adjusting applications are always the most important content. The adjustment of the Shared Pool should start with the application. Basically, binding variables can fully reduce Latch competition between Shared Pool and Library Cache, thus improving performance.

Repeated SQL hard parsing will not only consume a large amount of CPU resources, but also occupy more memory, seriously affecting database performance. Using Bound variables can fully share SQL statements, implements soft SQL parsing to improve system performance.

(1) create a table disease record resolution statistical record:

At 15:46:52 scott @ felixSQL> create table felix (id number );

Table created.

At 15:47:48 scott @ felix SQL> select name, value fromv $ mystat a, V $ statname B where a. STATISTIC # = B. STATISTIC # AND NAME LIKE 'parse % ';

NAME VALUE

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

Parse time cpu 28

Parse time elapsed 82

Parse count (total) 294

Parse count (hard) 180

Parse count (failures) 0

Parse count (describe) 0

6 rows selected.

15:54:32 scott @ felix SQL>

(2) Insert data cyclically. The following code does not use the bound variable:

Felix SQL> begin

For I in 1 .. 10 loop

Execute immediate 'insert into felixvalues ('| I | ')';

End loop;

Commit;

End;

/

PL/SQL procedure successfully completed.

(3) Check the statistical information after completion. Note that the number of hard resolutions has increased by 10, that is, each INSERT operation requires an independent Resolution:

At 16:02:22 scott @ felix SQL> select name, value fromv $ mystat a, V $ statname B where a. STATISTIC # = B. STATISTIC # AND NAME LIKE 'parse % ';

NAME VALUE

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

Parse time cpu 32

Parse time elapsed 89

Parse count (total) 336

Parse count (hard)190

Parse count (failures) 1

Parse count (describe) 0

6 rows selected.

16:02:29 scott @ felix SQL>

Query the V $ SQLAREA view to find the SQL statements that cannot be shared. Note that each SQL statement is executed only once. These SQL statements not only consume intensive SQL resources, you also need to use the shared memory to store these different SQL codes:

SELECT SQL _text, version_count, parse_cils, executions

FROM v $ sqlarea

WHERE SQL _text LIKE 'insert into felix % ';

SQL _TEXT version_countparse_cils EXECUTIONS

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

Insert into felix values (9) 1 1 1

Insert into felix values (5) 1 1 1

Insert into felix values (8) 1 1 1

Insert into felix values (1) 1 1 1

Insert into felix values (4) 1 1 1

Insert into felix values (6) 1 1 1

Insert into felix values (3) 1 1 1

Insert into felix values (7) 1 1 1

Insert into felix values (2) 1 1 1

Insert into felix values (10) 1 1 1

10 rows selected.

Refactor the test table and perform the second test:

Scott @ felix SQL> drop table felix purge;

Scott @ felix SQL> create table felix (id number );

Begin

For I in1.. 10 loop

Executeimmediate 'insert into felix values (: v1) 'using I;

End loop;

Commit;

End;

/

For this SQL statement, only one copy exists in the shared pool, which is parsed once and executed 10 times. This is the advantage of variable binding:

SELECT SQL _text, version_count, parse_cils, executions

FROMv $ sqlarea

WHEREsql_text LIKE 'insert into felix % ';

SQL _TEXT VERSION_COUNT PARSE_CALLSEXECUTIONS

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

Insert into felix values (: v1) 1 1 1

During application development, bind variables should be considered first (in JAVA applications, you can use PreparedStatement to bind variables ),However, if the application does not properly use the binding variable, Oracle provides a new initialization parameter starting from 8.1.6 to force variable binding on the Server. This parameter is cursor_sharing. At first, this parameter has two optional settings: exact and force.

The default value is exact, indicating exact match. force indicates that the binding is executed on the Server.Using this parameter in 8i can greatly improve the performance of some applications, but it also has some side effects. For example, the optimizer cannot generate a precise execution plan, change the SQL Execution Plan (Therefore, when the cursor_sharing parameter is enabled, make sure that your application passes through this mode.Adequate Testing).

Starting from Oracle 9i, Oracle introduced the mechanism of binding the variable Peeking. During the first execution of SQL, the specific value is used in the PGA of the Session to generate a precise execution plan, in order to improve the accuracy of the execution plan, however, the Peeking method takes effect only during the first hard parsing, so there may still be problems that may lead to subsequent SQL errors. At the same time,In Oracle 9i, The cursor_sharing parameter has 3rd options: similar. This parameter specifies that when Oracle has column chart information, different variable values are re-parsed, so that you can use the column chart to more accurately develop the SQL Execution Plan. That is, when the column chart information exists, similar performs the same as exact. When the column chart information does not exist, similar performs the same as force.

Apart from bugs,Under normal circumstances, because of the Similar judgment mechanism, SQL may also be unable to be shared.After collecting information about a Column chart (Hisogram), if the SQL statement does not use a bound variable, when the SQL statement uses a Column with Column chart information, the database considers every constant passed by SQL to be unreliable and generates a Cursor for each SQL statement, which is called UNSAFE BINDS. A large number of Version_Count may cause the database to generate a large number of cursor: pin S wait on X waiting. To solve this problem, you can set CURSOR_SHARING to Force or delete the column chart information on the corresponding field.

1.2.7.3 use the Flush Shared Pool to alleviate the sharing Pool Problem

An emergency solution that forces the refresh of the Shared Pool.

Alter system flushshared_pool;

Refreshing the sharing pool can help merge fragments (smallchunks), strengthen the aging SQL statement, and release the sharing pool. However, this is generally not recommended because:

(1) Flush Shared PoolThis will cause unused cursor to be cleared out of the Shared Pool.If these SQL statements need to be executed subsequently,The database will experience a lot of hard parsing, the system will experience severe CPU contention, and the database will produce fierce Latch competition.

(2)If the application does not use Bound variables and a large number of similar SQL statements are not executed, the Flush Shared Pool may only provide a short improvement and the database will return to the original state soon.

(3) IfThe Shared Pool is large and the system is very busy. refreshing the Shared Pool may cause system suspension.For similar systems, tryWhen the system is idle.

1.2.7.4 setting and function of the SHARED_POOL_RESERVED_SIZE Parameter

Shared_pool_reserved_size,This parameter specifies the reserved Shared Pool space to meet future large continuous Shared Pool space requests.. When the shared pool has too many fragments, a large part of space is requested, which may cause Oracle to search for and release the Shared Pool memory to meet the request. This may cause serious performance degradation, set the shared_pool_reserved_size parameter and use the shared_pool_reserved_min_alloc parameter to avoid performance degradation.

The ideal value of this parameter should be large enough to meet any memory requests to the reserved list.Instead of refreshing objects from the shared pool. The default value of this parameter is 5% of shared_pool_size. The recommended value of this parameter is 10% ~ of shared_pool_size ~ The size of 20%. The maximum value cannot exceed 50% of shared_pool_size.

The value of shared_pool_reserved_min_alloc controls the usage and allocation of reserved memory. If a large memory block request cannot be found in the idle LIST of the shared pool, the memory will be allocated a larger space than this value from the reserved list.

If the ORA-04031 errors that your system often encounters are all request memory blocks larger than 4400, you may need to add the shared_pool_reserved_size parameter settings.

However, if the primary cause of LRU merging and aging and 04031 error occurs, the memory requests are between 4100 and ~ Therefore, lowering the value of _ shared_pool_reserved_min_alloc while increasing the value of SHARED_POOL_RESERVED_SIZE is usually helpful. Setting _ shared_pool_reserved_min_alloc = 4100 increases the probability that the Shared Pool successfully meets the request. It should be noted that the modification of this parameter should be combined with the modification of Shared Pool Size and Shared Pool Reserved Size. Setting _ shared_pool_reserved_min_alloc = 4100 is a proven reliable method. We do not recommend setting a lower value.

QueryV $ shared_pool_reservedView can be used to determine the cause of the Shared Pool problem:

16:26:38 sys @ felix SQL> S SELECT free_space,

Avg_free_size,

Used_space,

Avg_used_size,

Request_failures,

Last_failure_size

FROMv $ shared_pool_reserved;

FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZEREQUEST_FAILURES LAST_FAILURE_SIZE

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

7255512 196094.919 8155392 220416 0 0

17:04:04 sys @ felix SQL>

If request_failures> 0 and last_failure_size> shared_pool_reserved_min_alloc, A ORA-04031 error may be caused by a lack of contiguous space in the reserved space of the Shared Pool. To solve this problem, you can increase shared_pool_reserved_min_alloc to reduce the number of objects cached in the reserved space of the Shared Pool, and increase shared_pool_reserved_size and shared_pool_size to increase the available memory of the Shared Pool.

If request_failures> 0 and last_failure_size

1.2.7.5 others

In addition,Some specific SQL statements, larger pointers, or larger packages may cause ORA-04031 errors.In many ERP software, this situation is very common.In this case, you can consider Pin this large object to the shared pool to reduce the burden of dynamic requests and allocation..

Using the dbms_shared_pool.keep system package, you can pin these objects to the memory. The most common SYS. STANDARD and SYS. DBMS_STANDARD are common candidate objects.

Note: To use the DBMS_SHARED_POOL system package, first run the dbmspool. SQL script, which automatically calls the prvtpool. plb script to create the required object.

There are still many factors that cause ORA-04031 errors. By setting relevant parameters such as session_cached_cursors, cursor_space_for_time, you can also solve some performance problems and bring about targeted performance improvement, which will not be discussed too much here.

1.2.8 Library Cache Pin and Library Cache Lock analysis

Oracle uses two data structures for concurrent access control of Library Cache: lock and pin.

The Lock can be considered as a resolution Lock, while the Pin can be considered as a short-term Lock for the purpose of reading or changing the object content. The Library Cache Object is separated and multiple locks are used for protection. One important purpose is to improve concurrency.

The Lock has a higher level than the Pin. Lock is obtained on the handle object. Before pin an object, you must first obtain the Lock of the handle. Handle can be understood as the Buffer Header of the Libray Cache object, which contains the name, tag, and memory address pointer to a specific object of the library Cache object.

Repeat the chart mentioned above, and we can clearly see the relationship between Object Handles and Heaps:

There are three major lock modes: Null, share, and Exclusive.When reading an access object, you usually need to get the lock in the Null (Null) mode and share (share) mode.When modifying an object, you need to obtain Exclusive locking..The fundamental function of Library Cache Lock is to control concurrent access to the same Library Cache Object by multiple Oracle clients. It prevents incompatible access by locking the Library Cache Object Hadle.

Common use or protection includes:

1. One client prevents other clients from accessing the same object

2. A client can maintain a relatively long dependency by locking (for example, preventing other clients from modifying objects)

3. You also need to get the lock when locating the object in the Library Cache.

After the Library Cache object is locked, a process must pin the object before access. The same pin has three modes: Null, shared, and exclusive. The shared pin is obtained in read-only mode, and the exclusive pin is obtained in modify mode. Generally, we obtain a shared pin during access, execution, and Package. If the exclusive pin is held, the database will wait.

To achieve better performance, starting from Oracle10gR2,Library Cache Pin has been gradually Mutex)This change is more obvious in Oracle Database 11g.

1.2.8.1 library cache pin wait event

The library cache pin is used to manage concurrent accesses to the library cache. pin an Object will cause the corresponding heap to be loaded into the memory (if not previously loaded ), pins can be obtained in the Null, Share, and Exclusive modes, and pin can be considered as a specific form of lock.

When the library cache pin waits for an event, it usually indicates that the pin is held by another user in incompatible mode.. The waiting time of the library cache pin is 3 seconds. One second is used by the PMON background process, that is, a maximum of 3 seconds can be waited before the pin is obtained. Otherwise, the system times out.

Ibrary cache pin parameters include P1 (KGL Handle Address), P2 (Pin Address), and P3 (Encoded Mode & Namespace ).

Library cache pin usually occurs when compiling or re-compiling objects such as PL/SQL, VIEW, and TYPES.

When the Object becomes invalid, Oracle tries to re-compile the Object when accessing the Object for the first time. If other sessions pin the Object to the library cache, the problem may occur, in particular, when there are a large number of active sessions and there is a complicated dependence. In some cases, recompiling an Object may take several hours to block other processes that attempt to access the Object.

The recompile process includes the following steps:

(1) The library cache object of the stored procedure is locked in exclusive mode. This lock is obtained on handle. Exclusive locking prevents other users from performing the same operation and other users from creating new objects that reference this process.

(2) pin the object in Shared Mode to perform security and error checks.

(3) The shared pin is released, and the object is re-configured in exclusive mode for re-compilation.

(4) invalidate all objects dependent on this process.

(5) release the Exclusive Lock and Exclusive Pin.

From Oracle 10g, the above test will not see the same effect, because Oracle 10g has enhanced object compilation and reconstruction. Note that when you re-replace a process, Oracle will first perform the check. If the code is identical before and after, the replace operation will not actually be performed (because there is no change), and The LAST_DDL_TIME of the object will not change, this means that Latch competition can be reduced.

If version_count is too high, you can query the V $ SQL _SHARED_CURSOR view. This view shows the specific reason why SQL cannot be shared. If it is caused by normal factors, the corresponding field is marked as "Y". In case of exceptions (such as this case), the query results may all be "N ", this indicates that Oracle considers this behavior as normal. In the current system settings, these SQL statements should not be shared, so it can be determined that it is caused by a specific parameter setting. The biggest initialization parameter for sharing with SQL is cursor_sharing. In this case, the cursor_sharing parameter is set to similar, which prevents sharing of large quantum pointers.

1.2.9 V $ SQL and V $ SQLAREA View

I have mentioned a frequently asked question:V $ SQL and V $ SQLAREAWhat are the differences between the two views? Therefore, the two views are very similar in structure.

The differences between V $ SQLAREA and V $ SQL are that V $ SQL reserves an entry for each SQL statement, while V $ SQLAREA performs group by Based on SQL _TEXT, use version_count to calculate the number of sub-pointers.