How to troubleshoot Ora-04031 errors

Diagnose and resolve ORA-04031 errors

When we attempt to allocate a large chunk of contiguous memory in a shared pool, Oracle first clears all objects not currently in use in the pool, merging the blocks of free memory. If there is still not enough large chunks of memory to satisfy the request, a ORA-04031 error is generated.

When this error occurs, you get the error interpretation information similar to the following:
04031, 00000, "Unable to allocate%s bytes of shared memory (\"%s\ ", \"%s\ ", \"%s\ ", \"%s\ ")"
*cause:more shared memory is needed than were allocated in the shared
Pool.
*action:if the shared pool is an 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. Shared pool-related instance parameters

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 a numeric value or a number followed by the suffix "K" or "M". K "stands for kilobytes," M "represents megabytes.

Shared_pool_reserved_size

Specifies the shared pool space reserved for shared pool memory for large sequential requests. This parameter, together with the Shared_pool_reserved_min_alloc parameter, can be used to avoid performance degradation when the shared pool fragment forces the Oracle to find and release chunks of unused pool to satisfy the current request.

The ideal value for this parameter should be large enough to satisfy any request scan of memory in the reserved list without refreshing the object from the shared pool. Since operating system memory can limit the size of the shared pool, you should generally set this parameter to the 10% size of the shared_pool_size parameter.

Shared_pool_reserved_min_alloc the value of this parameter controls the allocation of reserved memory. If a large enough size chunk exists within the shared pool idle list that is not found, the memory allocates a larger space than this value from the reserved list. The default value is sufficient for most systems. If you increase this value, then the Oracle server will allow fewer allocations from this reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and later versions. Submit the following statement to find 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, called automatic memory management, allows DBAs to keep a shared memory pool to share Pool,buffer cache, Java pool, and large pool. In general, when a database needs to allocate a large object to a common Pool and cannot find contiguous free space, the size of the shared pool will be increased automatically using the free space of the other SGA structures. Since the space allocation is automatically managed by Oracle, the likelihood of ora-4031 errors is greatly reduced. 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. Please refer to the 10g Management manual for more information.

2. Diagnosing ORA-04031 errors

Note: Most common ORA-4031 are generated in connection with the shared pool SIZE, and most of the diagnostic steps in this article are about shared pools. For other aspects such as Large_pool or Java_pool, the memory allocation algorithm is similar, generally because the structure is not large enough.

ORA-04031 may be because the SHARED POOL is not large enough, or the database cannot find a large enough block of memory because of a fragmentation problem.

ORA-04031 errors are usually due to fragmentation in the library cache or in the shared pool reserve space. Consider adjusting your application when you increase the size of the shared pool, using shared SQL and adjusting the following parameters:

Shared_pool_size,
Shared_pool_reserved_size,
Shared_pool_reserved_min_alloc.

The first decision is whether the ORA-04031 error is generated by the library cache fragments in the shared pool reserved space. Submit a query under:

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 the ORA-04031 error is due to the lack of contiguous space in the shared pool reserved space. To solve this problem, consider increasing the shared_pool_reserved_min_alloc to reduce the number of objects that buffer into the shared pool reserve space and increase the shared_pool_reserved_size and shared_pool_size To increase the available memory for the shared pool reserved space.

If:

Request_failures > 0 and Last_failure_size < Shared_pool_reserved_min_alloc

Or

Request_failures equals 0 and Last_failure_size < Shared_pool_reserved_min_alloc

The ORA-04031 error is caused by the lack of contiguous space in the library cache.

The first step should consider lowering the shared_pool_reserved_min_alloc to put more objects into the shared pool reserve space and increase the shared_pool_size.

3. Resolve ORA-04031 Errors

ORACLE bugs

Oracle recommends the latest Patchset for your system. Most of the ORA-04031 errors are related to bugs and can be avoided by using these patches.

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

BUG Description Workaround Fixed

<Bug:1397603> ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles _db_handles_cached = 0 901/8 172
<Bug:1640583> ORA-4031 due to Leak/cache buffer chain contention from and-equal access not available 8171/901
<Bug:1318267> INSERT as SELECT statements may
Not is shared when they should is
If Timed_statistics. It can leads to ORA-4031 _sqlexec_progression_cost=0
8171/8200
<Bug:1193003> Cursors is not being shared in 8.1
When they should is not available 8162/8170/901
<Bug:2104071> ora-4031/excessive "Miscellaneous" shared pool usage possible. (Many PINS)  None-> this was known to affect the XML parser. 8174, 9013, 9201
<Note:263791.1> Several number of BUGs related to ORA-4031 Erros were fixed on the 9.2.0.5 Patchset not available 9 205

ORA-4031 that occurs when compiling Java code

If the memory overflows when you compile the Java code , 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 workaround is to close the database and set the parameter java_pool_size to a larger value. The "Shared Pool", which is mentioned in the error message, is actually misleading to share the global area (SGA) overflow and does not mean that you need to add shared_pool_size, instead you have to increase the value of the java_pool_size parameter, restart the system, and then try again. Reference: <Bug:2736601>.

Small shared pool Size

In many cases, a shared pool is too small to cause ORA-04031 errors. The following information helps you adjust the size of the shared pool:

Library Cache Hit Ratio

Hit rate helps you measure the use of shared pools, how many statements need to be parsed rather than reused. The following SQL statement helps you calculate the hit ratio of the library cache:

Select SUM (PINS) "Executions",
SUM (reloads) "CACHE MISSES while executing"
From V$librarycache;

If you lose more than 1%, try reducing the library cache loss by increasing the size of the shared pool.

Shared Pool Size calculation

To calculate the size of the shared pool that best suits your workload, refer to:

<note:1012046.6>: How to CALCULATE YOUR SHARED POOL SIZE.

Shared Pool Fragmentation

Each time, a specific contiguous space is required to load the parsed form of an executed SQL or a/PL statement into a shared pool. The first resource to be scanned by the database is the free available memory in the shared pool. Once the free memory is exhausted, the database is looking for a piece of memory that has been allocated but not yet used for reuse. If such an exact size chunk of memory is not available, continue to look for the following criteria:

Large chunk (chunk) size larger than the requested size

Space is continuous.

Large chunks of memory are available (not in use)

Such chunks of memory are separated and the remainder is added to the corresponding free space list. After the database has been operating in this manner for a period of time, the shared pool structure will become fragmented.

When the shared pool is fragmented, allocating an empty space will take more time, and the database performance will decrease (during the entire operation, "chunk allocation" is controlled by a latch called "Shared pool latch") or appears ORA-04031 Error errors (when the database cannot find a contiguous block of free memory).

Refer to <note:61623.1>: You can get a detailed discussion of shared pool fragmentation.

If Shared_pool_size is large enough, most of the ORA-04031 errors are caused by dynamic SQL fragmentation in the shared pool. The possible causes are as follows:

Non-shared SQL

Generate unnecessary parsing calls (soft parsing)

Binding variable not used

To reduce the fragmentation you need to be sure that there are several possible factors that are described earlier. You can take some of the following methods, which are not limited to these types of applications: application tuning, database tuning, or instance parameter tuning.

Please refer to <note:62143.1>, which describes all of these details. This note also includes details on how the shared pool works.

The following view helps you identify unshared sql/plsql in a shared pool:

V$sqlarea View

This view holds the information for the SQL statements and PL-SQL blocks that are executed in the database. The following SQL statement can be displayed to you with a literal statement or a statement with a bound variable:

Select SUBSTR (Sql_text, 1, +) "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: After having the value "30" can be adjusted as needed to obtain more detailed information.

X$KSMLRU View

This fixed table X$KSMLRU tracks the applications in the shared pool that cause other objects to be swapped out. This fixed table can be used to mark what is causing a large application.

If many objects are periodically refreshed in a shared pool, they can cause response time problems and may cause library cache latch Contention when the object is overloaded into a shared pool.

An unusual thing about this X$KSMLRU table is that if someone chooses from a table, the contents of the table will be erased. This fixed table only stores the largest allocations that have ever occurred. This value is reset after selection so that the next big allocations can be marked, even if they are not as large as previously allocated. Because of such a reset, the results after the query is submitted cannot be obtained again, and the results from the output of the table should be carefully saved. Monitor this fixed table to run the following actions:

SELECT * from X$ksmlru WHERE ksmlrsiz > 0;

This table can only be queried with the SYS user login.

X$ksmsp view (similar to heap heapdump information)

Using this view will help you understand the extent of the shared pool fragmentation by finding out which free space is currently allocated. As we described earlier, the first place to find enough large chunks of memory allocated for cursors is the free list. The following statement shows large chunks of memory in the free list:

Select ' 0 (<140) ' Buckets, KSMCHCLS, ten * TRUNC (KSMCHSIZ/10) "from",
COUNT (*) "Count", MAX (ksmchsiz) "biggest",
TRUNC (AVG (Ksmchsiz)) "Avgsize", TRUNC (SUM (ksmchsiz)) "Total"
From X$ksmsp
WHERE Ksmchsiz < ksmchcls = ' free '
GROUP by Ksmchcls, ten * TRUNC (KSMCHSIZ/10)
UNION All
SELECT ' 1 (140-267) ' Buckets, Ksmchcls, TRUNC (KSMCHSIZ/20),
COUNT (*), MAX (Ksmchsiz), TRUNC (AVG (Ksmchsiz)) "Avgsize",
TRUNC (SUM (ksmchsiz)) "Total"
From X$ksmsp
WHERE Ksmchsiz between and 267 and ksmchcls = ' free '
GROUP by Ksmchcls, TRUNC (KSMCHSIZ/20)
UNION All
SELECT ' 2 (268-523) ' Buckets, Ksmchcls, 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, * TRUNC (KSMCHSIZ/50)
UNION All
SELECT ' 3-5 (524-4107) ' Buckets, Ksmchcls, 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, * TRUNC (ksmchsiz/500)
UNION All
SELECT ' 6+ (4108+) ' bucket, Ksmchcls, 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, TRUNC (ksmchsiz/1000);

　　4. ORA-04031 Error with Large Pool

The large pool is an optional memory area that provides large memory allocations for the following operations:

MTS session memory and Oracle XA interface

Memory used by Oracle backup and restore operations and I/O server processes (buffering)

Parallel execution of message buffering

The large pool does not have an LRU list. This differs from the reserved space in the shared pool, which uses the same LRU list for the reserved space and other allocated memory in the shared pool. Large chunks of memory will never be swapped out of the pool, and the memory must be explicitly allocated and freed by each session. A request that does not have enough memory will produce a ORA-4031 error like this:

ora-04031:unable to allocate XXXX bytes of shared memory

("Large Pool", "Unknown Object", "Session heap", "frame")

This error occurs when you can check several things:

1-Check the V$SGASTAT with the following statement to learn about the used and idle memory: SELECT pool,name,bytes from v$sgastat where pool = ' large pool ';

2-You can also use the heapdump level 32来 dump large pool heap and check the size of the free chunk memory

The memory allocated from the large pool if it is the whole block number of the Large_pool_min_alloc subsection helps to avoid fragmentation. Any request to allocate a chunk size smaller than Large_pool_min_alloc will allocate the size of the large_pool_min_alloc. In general, you will see the use of large pools relative to share pools said to use more memory. It is common to resolve ORA-4031 errors in large pools to increase the size of large_pool_size.

5. ORA-04031 and shared pool refreshes

There are some tricks that increase the ability of cursors to be shared, so that both shared pool fragmentation and ORA-4031 are reduced. The best way is to tweak your app to use bound variables. Also consider using the cursor_sharing parameter and force different values when the application cannot be adjusted (note that this will cause the execution plan to change, so it is recommended to test the application first). When the above techniques are not available, and the fragmentation problem is more severe in the system, refreshing the shared hold may help mitigate the fragmentation problem. However, it must be considered as follows:

Flushing will cause all unused cursors to be removed from the shared pool. In this way, after the shared pool refreshes, most of the SQL and PL $ cursors must be hard-resolved. This will increase the use of the CPU and will also increase the activity of latch.

When an application does not use a binding variable and is performed similarly by many users (such as in an OLTP system), a fragmentation problem will occur shortly after the refresh. So a shared pool is not a solution for poorly designed applications.

Refreshing a large shared pool may cause the system to hang, especially when the instance is busy, and it is recommended to refresh at non-peak times

　6. Advanced Analysis of ORA-04031 errors

If none of the aforementioned technical content resolves the ORA-04031 error, additional trace information may be required to get a snapshot of the shared pool where the problem occurred.

Adjust the Init.ora parameter to add the following event to get trace information for the problem:

event = "4031 Trace name Errorstack level 3"

event = "4031 Trace name Heapdump level 3"

If the problem is reproducible, the event can be set at the session level by using 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 trace file to Oracle support for troubleshooting.

IMPORTANT NOTE: In Oracle 9.2.0.5 and Oracle 10g versions, a trace file is automatically created each time a ORA-4031 error occurs and can be found in the User_dump_dest directory. If your system is the above version, you do not need to follow the steps in the previous description.

Http://itlab.idcquan.com/Oracle/optimize/39682.html

How to resolve Ora-04031 errors (RPM)