How to fix ORA-04031 errors

Translation: fenng
Date: 24--200-2004
Source: http://www.dbanotes.net
Version: 1.01

 

Diagnose and resolve ORA-04031 errors

When we try to allocate large blocks of continuous memory in the Shared Pool, Oracle first clears all the objects currently not used in the pool to merge idle memory blocks. A ORA-04031 error occurs if the request is still not met by a large enough single block of memory.

When this error occurs, you get the following explanation:

04031, 00000, "unable to allocate %s bytes of shared memory (/"%s/",/"%s/",/"%s/",/"%s/")" // *Cause: More shared memory is needed than was allocated in the shared // pool. // *Action: If the shared pool is out of memory, 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 parameters "shared_pool_reserved_size" and // "shared_pool_size". // If the large pool is out of memory, increase the INIT.ORA // parameter "large_pool_size".

1. instance parameters related to the Shared Pool

Before proceeding, it is necessary to understand the following instance parameters:

• Shared_pool_size
  This parameter specifies the size of the Shared Pool, in bytes. You can accept numeric values or numbers followed by the suffix "K" or "M ". "K" indicates kilobytes, and "M" indicates megabytes.
• Shared_pool_reserved_size
  The Shared Pool space reserved for the shared pool memory for large continuous requests is specified. When the Shared Pool fragment forces Oracle to find and release a large unused pool to meet the current request, this parameter can be used together with the shared_pool_reserved_min_alloc parameter to avoid performance degradation.
• The ideal value of this parameter should be large enough to meet any request scanning for memory in the reserved list without refreshing the object from the shared pool. Since the operating system memory can limit the size of the shared pool, you should set this parameter to 10% of the shared_pool_size parameter.
• The value of shared_pool_reserved_min_alloc controls the reserved memory allocation. If a large part of the Shared Pool cannot be found in the idle list, the memory will be allocated a larger space from the Reserved List. The default value is sufficient for most systems. If you increase the value, the Oracle server will allow less allocation from the Reserved List and request more memory from the shared pool list. This parameter is hidden in Oracle 8i and later versions. Submit the following statement to search for this parameter value:

  SELECT   nam.ksppinm NAME, val.ksppstvl VALUE    FROM x$ksppi nam, x$ksppsv val   WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'ORDER BY 1;

  10g Note: a new feature of Oracle 10g is called "Automatic Memory Management", which allows DBAs to retain a shared memory pool for shared pool, buffer cache, Java pool, and large pool. Generally, when the database needs to allocate a large object to the shared pool and cannot find continuous available space, it will automatically use the free space of other SGA structures to increase the size of the Shared Pool. Since space allocation is automatically managed by Oracle, ora-4031 errors are much less likely. Automatic Memory Management is activated when the initialization parameter sga_target is greater than 0. The current settings can be obtained by querying the V $ sga_dynamic_components view. For more information, see the 10 Gb Management Manual.

2. diagnose ORA-04031 errors

Note: most common ORA-4031 production is related to the shared pool size, most of the diagnostic steps in this article are about the shared pool. For other aspects such as large_pool or java_pool, the memory allocation algorithms are similar. Generally, they are caused by insufficient structure.

The ORA-04031 may be because the shared pool is not large enough, or because the database cannot find enough memory blocks due to fragmentation issues.

ORA-04031 errors are typically caused by fragments in the library cache or shared pool reserved space. When increasing the size of the Shared Pool, consider adjusting the application, use the shared SQL and adjust the following parameters:

SHARED_POOL_SIZE,SHARED_POOL_RESERVED_SIZE,SHARED_POOL_RESERVED_MIN_ALLOC.

First determine whether the ORA-04031 error is produced by fragments of the library high-speed buffer in the reserved space of the Shared Pool. Query submitted:

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

The ORA-04031 error is caused by the lack of continuous 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 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 <shared_pool_reserved_min_alloc

Or

Request_failures is equal to 0 and last_failure_size <shared_pool_reserved_min_alloc

This is because the lack of continuous space in the Library Buffer causes ORA-04031 errors.

In the first step, reduce shared_pool_reserved_min_alloc to put more objects into the reserved space of the Shared Pool and increase shared_pool_size.

3. Fixed ORA-04031 errors

• Oracle bug

  We recommend that you add the latest patchset to your system in Oracle. Most ORA-04031 errors are related to bugs that can be avoided by using these patches.

  The following table summarizes the most common bugs, possible environments, and patches related to this error.

  Bug	Description	Workaround	Fixed
  <Vulnerability: 1397603>	ORA-4031/SGA memory leak of permanent memory occurs for buffer handles	_ Db_handles_cached = 0	901/8172
  <Vulnerability: 1640583>	ORA-4031 due to leak/cache buffer chain contention from and-Equal Access	Not available	8171/901
  <Vulnerability: 1318267>	Insert as select statements may Not be shared when they shoshould be If timed_statistics. It can lead to ORA-4031	_ Sqlexec_progression_cost = 0	8171/8200
  <Vulnerability: 1193003>	Cursors may not be shared in 8.1 When they shocould be	Not available	8162/8170/901
  <Vulnerability: 2104071>	ORA-4031/excessive "Miscellaneous" shared pool usage possible. (effecpins)	None-> This is known to affect the XML parser.	8174,901 3, 9201
  <Note: 263791.1>	Several Number of bugs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset	Not available	9205

• ORA-4031 that appears when compiling Java code

  If the memory overflow occurs during Java code compilation, you will see an error:

  A SQL exception occurred while compiling: : ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal") 

  The solution is to close the database and set the java_pool_size parameter to a large value. The "Shared Pool" mentioned in the error message here is actually misleading in the shared global zone (SGA) overflow. It does not mean that you need to increase the value of the shared_pool_size parameter. On the contrary, you must increase the value of the java_pool_size parameter, restart the system and try again. Reference: <BUG: 2736601>.

• Small shared pool size

  In many cases, too small a shared pool can cause a ORA-04031 error. The following information helps you adjust the size of the Shared Pool:

  • Library high-speed buffer hit rate

    Hit rate helps you measure the usage of the shared pool, and how many statements need to be parsed rather than reused. The following SQL statement helps you calculate the hit rate of high-speed buffer in your database:

    SELECT SUM(PINS) "EXECUTIONS",             SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"             FROM V$LIBRARYCACHE; 

    If the loss exceeds 1%, try to increase the size of the Shared Pool to reduce the high-speed cache loss of the database.

  • Share pool size Calculation

    To calculate the size of the Sharing pool that best fits your workload, see:

    <Note: 1012046.6>: how to calculate your shared pool size.

• Shared Pool fragments

  Each time the SQL or PL/SQL statements to be executed are parsed and loaded into the shared pool, a specific continuous space is required. The first resource to be scanned by the database is the free and available memory in the shared pool. Once idle memory is used up, the database needs to find a memory that has been allocated but is not used for reuse. If such an exact size of large memory is not available, continue to look for it according to the following criteria:

  • The chunk size is larger than the request size.
  • The space is continuous.
  • Large block of memory is available (rather than being used)

  In this way, the memory is separated, and the remaining memory is added to the corresponding idle space list. After a database is operated in this way for a period of time, fragments will occur in the Shared Pool Structure.

  When there is a fragmentation problem in the Shared Pool, allocating a free space will take more time and the database performance will also decline (throughout the operation process, "chunk allocation" is controlled by a latch called "Shared Pool latch") or an ORA-04031 error errors (when the database cannot find a continuous idle memory block ).

  For more information, see <Note: 61623.1>.

  If shared_pool_size is large enough, most ORA-04031 errors are caused by dynamic SQL fragments in the shared pool. Possible reasons are as follows:

  • Non-shared SQL
  • Generate unnecessary resolution calls (soft resolution)
  • No bound variable is used.

  To reduce the generation of fragments, You need to determine the several possible factors described above. You can take the following methods, of course, not limited to these types: Application adjustment, database adjustment, or instance parameter adjustment.

  For more information, see <Note: 62143.1>. This comment also includes details about how the Shared Pool works.

  The following view helps you identify non-shared SQL/PLSQL in the Shared Pool:

  • V $ sqlarea View

    This view stores the SQL statements executed in the database and PL/SQL block information. The following SQL statements can be displayed with literal statements or with Bound variables:

    SELECT   SUBSTR (sql_text, 1, 40) "SQL", COUNT (*),         SUM (executions) "TotExecs"    FROM v$sqlarea   WHERE executions < 5GROUP BY SUBSTR (sql_text, 1, 40)  HAVING COUNT (*) > 30ORDER BY 2;

    Note: The value "30" after having can be adjusted as needed to obtain more detailed information.

  • X $ ksmlru View

    This fixed table x $ ksmlru traces the Shared Pool, causing other objects to be swapped out (age out) applications. This fixed table can be used to mark what causes large applications.

    If many objects are refreshed in the shared pool, the response time may be incorrect, and the database high-speed buffer latches may compete when the objects are reloaded into the Shared Pool.

    An unusual aspect of this x $ ksmlru table is that the content of this table will be erased if someone selects the content from the table. In this way, this fixed table only stores the largest allocation that has ever occurred. This value is re-set after selection so that the next large allocation can be marked, even if they are not as large as previously allocated. Because of this resetting, the results after the query is submitted cannot be obtained again, and the output results from the table should be saved with caution. To monitor this fixed table, run the following operations:

    SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

    This table can only be queried by logon with the Sys user.

  • X $ ksmsp view (similar to heapdump Information)

    This view can be used to find the currently allocated free space and help you understand the degree of fragmentation in the shared pool. As described above, the first place to find enough large memory allocated for the cursor is the Free List ). The following statement shows the large memory in the idle list:

    SELECT   '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",         COUNT (*) "Count", MAX (ksmchsiz) "Biggest",         TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"    FROM x$ksmsp   WHERE ksmchsiz < 140 AND ksmchcls = 'free'GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)UNION ALLSELECT   '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz / 20),         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",         TRUNC (SUM (ksmchsiz)) "Total"    FROM x$ksmsp   WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)UNION ALLSELECT   '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz / 50),         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",         TRUNC (SUM (ksmchsiz)) "Total"    FROM x$ksmsp   WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)UNION ALLSELECT   '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz / 500),         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",         TRUNC (SUM (ksmchsiz)) "Total"    FROM x$ksmsp   WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)UNION ALLSELECT   '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz / 1000),         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",         TRUNC (SUM (ksmchsiz)) "Total"    FROM x$ksmsp   WHERE ksmchsiz >= 4108 AND ksmchcls = 'free'GROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz / 1000);

4. ORA-04031 error with large pool

A large pool is an optional memory area that provides large memory allocation for the following operations:

• MTS session memory and Oracle XA Interface
• Oracle backup and recovery operations and memory (buffer) for I/O Server Processes)
• Parallel Execution message Buffering

The large pool does not have an LRU list. This is different from the reserved space in the shared pool. The reserved space and other allocated memory in the Shared Pool use the same LRU list. A large block of memory will never be swapped out of a large pool. The memory must be explicitly allocated and released by each session. A request that does not have enough memory produces a ORA-4031 error like this:

ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame") 

When this error occurs, you can check several items:

• 1-use the following statement to check v $ sgastat and learn the memory used and idle:

  SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool'; 

• 2-you can also use heapdump level 32 to dump the heap of a large pool and check the size of the idle large memory.

  If the memory allocated from the large pool is the whole number of large_pool_min_alloc subsections, it helps to avoid fragments. The size of large_pool_min_alloc is smaller than that of large_pool_min_alloc. In general, you will see that more memory is used when a large pool is used than the Shared Pool. Generally, to resolve ORA-4031 errors in a large pool, you must increase the size of large_pool_size.

 

5. ORA-04031 and Shared Pool refresh

There are some tips to improve the ability to share the cursor, thus sharing pool fragments and ORA-4031 will be reduced.The best way is to adjust the variable bound to the application.. In addition, when the application cannot be adjusted, consider using different cursor_sharing parameters and Force values (note that this will lead to a change in the execution plan, so we recommend that you test the application first ). When none of the above techniques can be used, and the fragmentation problem is serious in the system, refreshing shared persistence may help reduce the fragmentation problem. However, you must consider the following:

• Refresh will delete all unused cursors from the shared pool. In this way, after the Shared Pool is refreshed, most SQL and PL/SQL cursors must be hard parsed. This improves CPU usage and increases latch activity.
• When the application does not use the Bind Variable and many users perform similar operations (such as in the OLTP system), the fragmentation problem will soon occur after the refresh. So the sharing pool is not a solution for poorly designed applications.
• Refreshing a large shared pool may cause system suspension, especially when the instance is busy. We recommend that you refresh the pool during off-peak hours.

6. Advanced Analysis of ORA-04031 errors

If none of the above techniques resolve a ORA-04031 error, additional trace information may be required to get a snapshot of the shared pool where the problem occurs.

Adjust the init. ora parameter and add the following event to get the problem tracking information:

event = "4031 trace name errorstack level 3" event = "4031 trace name HEAPDUMP level 3" 

If the problem can be reproduced, the event can be set at the Session Layer. Use the following statement before executing the problem statement:

SQL> alter session set events '4031 trace name errorstack level 3'; SQL> alter session set events '4031 trace name HEAPDUMP level 3'; 

Send this tracking file to Oracle support personnel for troubleshooting.

Note: in Oracle 9.2.0.5 and Oracle 10g, each time a ORA-4031 error occurs, a trail file is automatically created, which can be found in the user_dump_dest directory. If your system is of the above version, you do not need to perform the steps described above.