What is an ORA-04031 Error?

The memory pool in the SGA are comprised of memory chunks in various sizes. When the database starts is started, you have a large chunk of memory allocated in the various pools and tracked in free list hash buckets. Over time, as memory is allocated and deallocated, the memory chunks are moved around into different free list buckets inside the pool according to their size. An ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a memory chunk large enough to satisfy an internal allocation request on behalf of a user's operation.

The Shared Pool is managed differently from the other memory pools.  The Shared Pool stores information related to the dictionary and library cache.  However, these memory areas are managed using free lists and a Least Recently Used (LRU) algorithm.  The ORA-04031 is signaled on the Shared Pool after searching all the free lists, aging all objects possible from the LRU list, and scanning the free list multiple times.  This means the ORA-04031 is very difficult to predict.  There can be many contributing factors to the ORA-04031 and the trace information provide at the time of the error is associated with the "victim session" in the memory condition and not the cause. The allocation code is complicated, but a simplified version of the allocation algorithm is sketched below:

scan regular free list for match, if not found
 large request, scan reserved list
   if (chunk found)
      check chunk size and perhaps truncate
   if (chunk is not found)
      scan regular free list
          if (chunk found)
             check chunk size and perhaps truncate
             all done
          if (chunk is not found)
              do LRU operations and repeat

 small request, scan regular free list 
   do LRU operations and repeat search
    if (chunk found)
       check chunk size and perhaps truncate
       all done
    if (chunk is not found) 
   do LRU operations and repeat

NOTE:  There are internal checks to limit the number of times these searches repeat prior to reporting ORA-04031 error.

The sum of the free space, which one may obtain through v$sgastat or x$ksmsp, is not important. What is important is the size of the largest chunk that can be freed or merged after some LRU operations.  From a heapdump trace we can see free list buckets and information about the chunks of memory in each bucket.

Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48

This shows that bucket 1 has 443 chunks of memory where the maximum size is 40 bytes and the average is 40 bytes. Bucket 2 includes memory chunks with sizes between 40 and 48 bytes.  The average size in this case is 40 bytes and the maximum size is 40 bytes.  Finding out what caused fragmentation in a memory pool is not always feasible. Sometimes the problem is an Oracle functionality issue, but in a large percentage of the cases, inefficient application coding can be the root issue.

The 4031 error can occur in the Large Pool, Java Pool, Streams Pool (new to 10g), or the Shared Pool.  The error message will indicate which pool had the problem.  If the error indicates a problem in a pool other than the Shared Pool, this usually indicates the problem pool is configured too small for the application environment.  Increase the size of the problem pool in increments of 50MB or 100MB and monitor for continued problems.  If using the 10g, Automatic Shared Memory Management (ASMM) functionality, the MMAN process will attempt to shrink and grow different components in the SGA as memory is needed over time. You may need to increase the setting for SGA_TARGET to allow MMAN more memory to manage behind the scenes if you experience ORA-04031 errors in the Large Pool, Streams Pool, or Java Pool.

The Shared Pool is little more complicated to tune. For example

ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","SELECT /*+ FIRST_ROWS */ * F...","sql area","kafco : qkacol"):4031:375:2008:ocicon.c

In this case, the problem occurred in the Shared Pool. The error message also includes information on the size of the memory request that failed. In our example, the failure was on a request for 4192 bytes in the SQL Area.

NOTE:  The Shared Pool is used in an ASM environment as well.  There have been reports of ORA-04031 on 10.1. x ASM instances because the default size can be too small to accommodate the diskgroup management activities. In these cases, set the SHARED_POOL_SIZE parameter to 50M and increase the setting in increments of 10M if the problems persist.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3637/viewspace-664661/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3637/viewspace-664661/