Diagnose and resolve ORA-04031 errors

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 and the SHARED_POOL_RESERVED_MIN_ALLOC parameter can be used together 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 with sufficient size cannot be found in the idle list of the shared pool, the memory will be allocated a space larger than this value from the Reserved List. The default value is sufficient for most systems. If you increase this 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.

1 2 3 Next Page