How to solve ORA-04031 errors

How to solve ORA-04031 errors

Translation: fenng

Diagnose and solve ORA-04031 errors

For most applications, the size of the shared pool is important for Oracle performance. Saving the data dictionary cache and fully resolver or compiled PL / SQL blocks and SQL statements in the shared pool.

When we try to allocate a large-scale continuous memory failure in a shared pool, Oracle first refreshes all objects currently not used in the pool, and merges the idle memory block. If there is still no big enough large block memory to meet the request, an ORA-04031 error will occur. When this error occurs, the error message you get is as follows:

Error: ORA 4031 Text: Unable to allocate% s bytes of shared memory (% s,% s,% s) ------------------------- -------------------------------------------------- ------------------------------------- Cause: more shared memory is needed Than Was allocated in the shared pool Action:. Either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the init.ora parameter "shared_pool_size".

1. Example parameters related to shared pool

Before proceeding, understand the following example parameters is important:

Shared_pool_size - This parameter specifies the size of the shared pool, and the unit is byte. You can accept the numeric value or the number of times to keep up with the suffix "k" or "m". "K" represents the kilobyte, "M" represents megabytes. Shared_pool_reserved_size - Specifies the shared pool space for large continuous requests for a shared pool memory. This parameter and shared_pool_reserved_min_alloc parameters can be used to avoid performance degradation when the current request is to be used to satisfy the current request. The ideal value of this parameter should be large enough to meet any request scan for the reserved list without having to refresh the object from the shared pool. Since the operating system memory can limit the size of the shared pool, in general, you should set this parameter 10% size of the shared_pool_size parameter. Shared_pool_reserved_min_alloc - The value of this parameter controls the allocation of the memory. If there is no size in the shared pool, the memory is not found in the shared pool idle list, the memory is assigned a large space from the reserved list. The default value is enough for most systems. If you increase this value, the Oracle server will allow less allocation from this reserved list and request more memory from the shared pool list. This parameter is hidden in Oracle 8i.

2. Diagnostics ORA-04031 Error ORA-04031 Error is typically because of the fragments in the library cache or shared pool retention space. Consider adjusting the application using shared SQL and adjusts the following parameters while increasing the shared pool size.

Shared_pool_size, shared_pool_reserved_size, shared_pool_reserved_min_alloc.

First, it is determined whether or not ORA-04031 error is generated by a library high-speed buffer fragment in the shared pool retention space. Submitted query:

SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v $ shared_pool_reserved; if: REQUEST_FAILURES> 0 and LAST_FAILURE_SIZE> SHARED_POOL_RESERVED_MIN_ALLOC

Then ORA-04031 error is due to the lack of continuous spaces because the shared pool retention is missing. To solve this problem, you can consider increasing shared_pool_reserved_min_alloc to reduce the number of objects to the shared pool retention space, and increase Shared_Pool_ReServed_size and Shared_Pool_Size to increase the available memory of the shared pool retention space.

If: request_failures> 0 and last_failure_size

Then because the lack of continuous space in the library cache causes ORA-04031 errors.

The first step should consider lowering shared_pool_reserved_min_alloc to put more objects into shared pool retention spaces and increase Shared_Pool_Size. 3. Solve ORA-04031 Error

Oracle bug To solve this error (if you can be called wrong), the first step in the diagnosis is to use the latest patch set on your platform. Most ORA-04031 errors are related to BUG, ​​which can be avoided by using these patches.

The most common bugs, possible environment and patches of this problem are summarized and related to this error.

BUG described Workaround Fixed ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles _db_handles_cached = 0 901/8172 ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access not available 8171/901 . INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST = 0 8171/8200 Cursors may not be shared in 8.1 When the Should Be Not Available 8162/8170/901

Some bugs in the shared pool structure cause this error, but usually a large number of shared SQL / PLSQL statements will also cause this error. Once the latest patch has been turned out, we strongly recommend adjusting the database and app when you encounter this problem.

To get a complete information of known bugs, you can refer to: : Main Issues Affecting The Shared Pool On Oracle 7, Oracle8 and Oracle8i.

• Every time the shared pool is required, the SQL or PL / SQL statement of the SQL or PL / SQL statement is required to load the shared pool. There is a specific continuous space. The first resource to be scanned by the database is the idle free memory in the shared pool. Once the idle memory is exhausted, the database is to find a piece of memory that has been assigned but still not used. If this exact size is unavailable, you will continue to follow the following criteria:

Such a large block of memory is separated, and the remaining added to the corresponding free space list. When the database operates a period of time in this way, the shared pool structure will be fragments.

When there is a problem with fragmentation of shared pools, allocating a free space will cost more, the database performance will also decline (during the whole operation, "chunk allocation" is controlled by a latch called "Shared Pool Latch") Or ORA-04031 error ErroRRS appears (when the database cannot find a continuous free memory block).

-------------------------------------------------- ----------------------------------- Reference : You can get detail about shared pool debris discuss. -------------------------------------------------- ----------------------------------- If Shared_Pool_Size is large enough, most ORA-04031 errors are Dynamic SQL fragments in the shared pool. Possible reasons are as follows:

◇ Non-shared SQL ◇ Generate unnecessary parsing call (soft analysis) ◇ No bind variable

To reduce the generation of fragments You need to be determined as several possible factors described earlier. Some methods can be taken, of course, not only limited to these types: application adjustment, database adjustment, or instance parameter adjustment.

-------------------------------------------------- ------------------------------------ Please refer to , describe all of these details content. This comment also includes details of how the shared pool works. -------------------------------------------------- ------------------------------------

The following view helps you indicate that the shared pool is not shared SQL / PLSQL:

V $ SQLAREA view

This view saves the information of the SQL statement and PL / SQL blocks executed in the database. The following SQL statement can be displayed to you with Litral's statement or a statement with binding variables:

SELECT SUBSTR (SQL_Text, 1,40) "SQL", Count (*), SUM (Executions) "Totexecs" from V $ SQLAREA WHERE EXECUTIONS <5 Group by Substr (SQL_Text, 1,40) HAVING Count (*)> 30 ORDER BY 2;

Note: The "30" value in the statement Having can be adjusted as needed to obtain more detailed information.

X $ KSMLRU view There is a fixed table X $ KSMLRU Tracking shared pool to cause other objects to replace (Age Out). This fixed table can be used to mark what has led to a large application.

If many objects are phased in a shared pool, it can lead to a response time issue and may cause the library to high-speed buffer competition in the object overloaded into the shared pool.

About an unusual place for this x $ ksmlru table is that if someone selects the content of this table from the table, it will be erased. This allows this fixed table to store only the biggest allocation that has happened. This value is reset after the selection is reset that the next big allocation can be marked, even if they are not as large as the previous assignment. Because of this reset, the results submitted after the query cannot be obtained again, and the result of the output in the table should be careful. Monitor this fixed table runs the following: SELECT * from x $ kSMLRU where ksmlrsiz> 0;

In Oracle8i, this table cannot be selected by the user outside of the SYS user.

? Small shared pool size

Finally, a small shared pool can cause ORA-04031 errors, but be careful when the fragment is really a problem. There is usually a delay when the error has been found, but it will increase the impact of performance when finding a free memory in the fragment of a large shared pool.

The following information will help you adjust the size of the shared pool:

The reservoir cache medium hit rate helps you measure the use of shared pools, based on how many SQL / PLSQL needs to be resolved instead of reuse. The following SQL statement helps you calculate the hit rate of the library cache:

Select SUM (Pins) "Executions", SUM (Reloads) "Cache Misses While Executing" from V $ librarycache;

If MISSES is more than 1% over Executions, it should be tried to reduce the loss of the library cache by increasing the shared pool.

Shared Pool Size Calculation To calculate the shared pool size that best suits the current workload, reference: : How to Calculate your shared pool size.

4. Advanced analysis of ORA-04031

If you use the above solution, this error still appears, set the following events in the initsid.ora file and restart instances:

Event = "4031 TRACE Name Errostack Level 3"

A tracking file will be generated when the next error occurs. This tracking file can be provided to Oracle support staff to solve the problem.

Related documents : Oracle8 Tuning Documentation Guide : Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i :. HOW TO CALCULATE YOUR SHARED POOL SIZE : Tuning Library Cache Latch Contention : Resolving Shared Pool Fragmentation in Oracle7 : This document is the English original.

All Note words of documents can be found from MetaLink.Oracle.com. Need Oracle's CSI account.

The translation of this article is based on the purpose of learning. Of course, the original English author retains the copyright. If the technical details on the translation are not very satisfied, you can refer to the original English document. Please contact me by fenng@itpub.net.

Original source: http://www.dbanotes.net/oracle/ora-04031.htm