In this Document

Applies to:

Purpose

This article is intended to

•   help the reader understand causes of the ORA-4031
•   gather the diagnostics needed to narrow down the errors
•   answer some of the common questions asked about ORA-4031

Last updated June 2010
 

Video - FAQ for ORA-4031 Errors (08:00)

Common Bugs

Review Note: 4031.1 for latest bug information searchable by release level.


NOTE:  References below to script names are canned scripts that can be found in Note 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts.   Look in the section of the article titled 'Software Requirements/Prerequisites'. You can download the zip file clicking on the 'Click here' link.    The scripts are updated over time, so please verify you have the latest versions of the scripts from time to time.

Questions and Answers

How is memory allocated and deallocated in the SGA pools?

The SGA is comprised of fixed areas like the Log Buffers and the Buffer Cache(s) as well as memory pools (Shared Pool, Large Pool, Java Pool, and in 10g, the Streams Pool).  Memory allocated to the various pools are divided in heaps that can be composed of one or many sub heap memory structures. 

This is similar to many segments inside a tablespace. The tablespace will be the heap and the segments are the subheaps. The extents within the segment are like the various subheaps that can exist inside "parent" subheaps.   The goal in the shared memory areas is the same as a tablespace--avoid fragmentation.  To do this we allocate the chunk of memory that best fits the request for memory, coalescing adjacent free space as needed and detecting memory that can be flushed and reused.

Inside these pools, we use free list buckets.   They are structures of free lists and each list correspond to a specific size. Oracle does a binary search on the free list sizes to find the appropriate free list.  The first bucket that is greater or equal to the requested size will be returned.  At startup of the database, there are various sized chunks of memory created in each pool.  We will continue to walk the free list until we find a bucket which points to a large enough extent of memory.  The Shared Pool will utilize a Least Recently Used (LRU) algorithm to "age out" memory structures that have not been reused over time.

To get a better idea, see this information from a heapdump trace showing the free list bucket summary:

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 is a free list of memory chunks with sizes between 40 and 48 bytes. When a chunk of space is freed/deallocated, it is added to the bucket whose size is less than or equal to the chunk size.  If you find smaller chunks of memory available in the Shared Pool vs. a mixture of large chunks and small chunks, this can indicate the application is causing fragmentation and there is a increasing chance that a future large memory requests will fail with an ORA-04031 error.  See How can we determine if there are Application issues causing the problem? for more on tracing inefficient application code.

The Shared Pool and Large Pool divide their shared memory areas into subpools (starting with 9i).  Each subpool will have Free List Buckets containing pointers to memory chunks within the subpool.  The other pools are treated as one large memory area with a single Free List as described above.

When a memory chunk is allocated inside the memory pool, it will be associated with a memory type.  The chunk will be allocated as PERMANENT, FREEABLE, or RECREATABLE.   These memory chunks are then associated with a memory structure or element inside the pool.  For example, "KGLS heap".  These memory structures/elements are not always tracked/commented in the data dictionary (specifically those in the PERMANENT type).

Chunk types:

Normal (freeable) chunks - These chunks are allocated in such a way that the user can explicitly free 
the chunk once they have finished with the memory.

Free chunks - These chunks are free and available for reuse should a request come into the pool for 
this chunk size or smaller.

Recreatable chunks - This is a special form of "freeable" memory.  These chunks are placed on an 
LRU list when they are unpinned.   If memory is needed, we go to the LRU list and free "recreatable"
memory that hasn't been used for a while.

Permanent chunks - These chunks can be allocated in different ways.   Some chunks are allocated 
and will remain in use for the "life" of the instance.   Some "permanent" chunks are allocated but can 
be used over and over again internally as they are available. 

With an event set, permanent chunks are commented as other types of memory chunks for debugging purposes.

What are Subpools?

In Oracle 9i and later versions, the Shared Pool can be divided into subpools.  Each subpool is a "mini" shared pool, having its own set of Free Lists, memory structure entries, and LRU list.  This was a scalability change made to the Shared Pool/Large Pool to increase the throughput of these pools in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared/Large Pool for a single latch as in earlier versions.  The reserved area for the Shared Pool is divided equally throughout the subpools as well.

When you get an ORA-04031, the trace will indicate the subpool where the error occurred.

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

In this case, the sixth subpool is where the error occurred

The downside to using subpools is that there are cases where one subpool can get over-utilized. Once the subpool is selected, the search for memory chunks can fail even though another subpool might have adequate memory available. Starting with 10g, we do have functionality allowing the search to "switch" to another subpool if a memory request is not met in the selected subpool but that is not possible for all memory structures/elements.

NOTE:  There is a small subset of functionality which will use memory chunks across multiple subpools.
In other words, striping memory used across multiple subpools.

There is very little documented on these and generally a request for memory will use a round-robin
approach to find the memory chunk it needs from a "random" subpool.

Unbalanced use of the subpools can lead to ORA-04031.  We see this commonly with the memory allocation failures in the  "session param values" memory structure.  With 9i and higher, dynamic parameter settings are stored for each configured process and a subpool is selected at startup to manage all "session param value" entries.  If the PROCESSES parameter is set very high and you do not have high concurrent connections, this can cause unnecessary permanent memory allocations in this subpool and can lead to ORA-04031 problems.  Generally, the performance gains from having multiple Shared Pool latches outweigh the possible problems associated with over-utilized subpools.

An end-user has no visibility into subpools.  They are hidden below the implementation of the Shared/Large Pools.   NOTE: If the Shared Pool is using subpools, there will automatically be subpools created in the Large Pool as long as LARGE_POOL_SIZE>0.

Reference (this problem appears to have spanned many bugs):
Bug 4184298 - Subpool imbalance for "session parameters"  

What is the Reserved Area?

A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache.  For this reason, the Shared Pool should be sized to ensure that frequently used data is cached.  If there is not enough free space in the Shared Pool, then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold latch resources for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. Missed space requests for large size allocations can thus be very costly since it may cause many smaller pieces of pool memory
to be flushed to make room for the large space requests.

By default, Oracle configures a small Reserved Pool (or Reserved Area) inside the Shared Pool. This memory can be used to satisify large contiguous allocation requests when space is not available in the general shared pool list.
These large allocation requests are typically for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects.
After the memory allocated from the Reserved Pool is freed, it returns to the Reserved Pool.

The minimum size of allocations that can be placed in Reserved pool are controlled via hidden parameter _shared_pool_reserved_min_alloc. The default setting is 4400 bytes on recent versions and can be set as low as 4000 bytes.
Memory allocation requests larger than value of _shared_pool_reserved_min_alloc can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the general shared pool free list.

IF the failing size as indicated by the ORA-4031 error messages or LAST_FAILURE_SIZE column of V$SHARED_POOL_RESERVED  is > _shared_pool_reserved_min_alloc
that is an indication the Reserved area needs to be increased to accommodate these large space requests.

The Reserved pool size can be set to a fixed size by specifying shared_pool_reserved_size or as a percentage with hidden parameter _shared_pool_reserved_pct.  The parameter shared_pool_reserved_size overrides the parameter _shared_pool_reserved_pct.

In some application environments, 5% is too small.  If an ORA-04031 problem indicates a very large memory request failure, increase the size of the Reserved Area by manually setting SHARED_POOL_RESERVED_SIZE or if using SGA_TARGET>0, change the hidden parameter, _shared_pool_reserved_pct to 10 or 15 (see example below) to ensure that when the Shared Pool grows or shrinks automatically, the Reserved Area will change as well. 

The view V$SHARED_POOL_RESERVED can be used to determine failed request sizes and shared pool reserved area utilization.   Document: 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts contains a script for querying this view.

Warning:
If you decide to use the SHARED_POOL_RESERVED_SIZE parameter and SGA_TARGET>0, you may experience ORA-4031 situations on large memory requests because the MMAN process may be increasing the size of the Shared Pool over time, while the Reserved Area remains constant.   Thus the Reserved Area may start out at 10% of the Shared Pool size, but over time it may end up 2% or 3% (much smaller than expected in relation to the new Shared Pool size).

The Reserved Area handles bigger memory allocations in an attempt to decrease the chances of  fragmentation in the Shared Pool over time.

or add this in the pfile

For large allocations, Oracle attempts to allocate space in the Shared Pool in the following order:

   1.  From the unreserved part of the shared pool. 

   2.  From the reserved pool. If there is not enough space in the unreserved part of the Shared Pool, then Oracle checks whether the reserved pool has enough space. 

   3.  If there is not enough space in the unreserved and reserved parts of the Shared Pool, then Oracle attempts to free enough memory for the allocation.  It then retries the unreserved and reserved parts of the Shared Pool.  These mini-flushes will clean out RECREATABLE/FREEABLE memory chunks from the LRU list that have not been reused for a while.

What are the pools in the SGA used for?

The Shared Pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.  The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.   We allocate memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement.   Ideally, the Shared Pool should be used for caching shared SQL and to avoid the performance overhead caused by shrinking the shared SQL cache.

Many features of Oracle like Recovery Manager (RMAN), parallel processing/IO slave processing, and Shared Server are designed to utilize large shared memory chunks .  These features will put unnecessary stress on the Shared Pool and therefore we recommend you define a Large Pool using LARGE_POOL_SIZE or by using SGA_TARGET to help reduce memory stress in the Shared Pool in these scenarios.

The Java Pool memory is used for memory allocations associated with all session-specific Java code and data within the JVM.  Java pool memory is used in different ways, depending on what mode the Oracle server is running in.

If using Streams functionality, you can configure the Streams Pool to manage memory allocations needed for this functionality. 

The Shared Pool employs a LRU algorithm similar to what is found in the Buffer Cache.  Therefore, tuning the Shared Pool is more complex than other pools.  Most of the time, if an ORA-04031 errors occur in one of the other memory pools, this indicates that the pool is too small and you must increase the size of the problem pool to stop these errors in the future. 

The default settings for these other pools are usually sufficient, but to manually adjust these pools, you can alter the parameters  LARGE_POOL_SIZE, STREAMS_POOL_SIZE, and JAVA_POOL_SIZE.  Using SGA_TARGET these pools are automatically adjusted as needed by the MMAN process.

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 than 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 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 by 15% 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.

Note 146599.1 Diagnosing and Resolving Error ORA-04031

Is my Reserved Area sized properly?

An ORA-04031 error referencing a large failed requests indicates the Reserved Area is too fragmented.   You can investigate memory usage in the reserved area using the script from Note 430473.1

You can also investigate the efficiency of the size of your Reserved Area.  The goal is to have the "Hit %" stay as close to 100 as possible.   NOTE:  Failures in the Reserved Area do not always equate to ORA-04031 errors.  We perform mini-flushes to try to find matching memory requests and in many cases we will find the requested memory and avert the error message.   If you increase the size of the Reserved Area, you can increase the chances of taking needed memory from the Shared Pool.   We recommend you increase the Shared Pool and the Reserved Area sizes by the same amount.

Is there a way to find a "right" size for the Shared Pool?

You can configure the Shared Pool manually using the SHARED_POOL_SIZE parameter or have the pool auto-tuned using SGA_TARGET (10g and higher).  Part of the memory allocated for the Shared Pool is overhead memory (based on settings for some internal parameters).  Prior to 10g, this overhead memory was "on top" of the SHARED_POOL_SIZE parameter setting, but not reflected by the parameter SHARED_POOL_SIZE.  This appears to be a calculation error when you run a query on V$SGASTAT prior to 10G.

With 10g, this overhead memory is now included inside the SHARED_POOL_SIZE setting.  Some customers run into memory issues in the Shared Pool after a move to 10g if they do not account for this overhead memory.   From the example above, if the SHARED_POOL_SIZE is manually set to 64M and the overhead remains unchanged, this means the usable Shared Pool memory is only 54525952 bytes.

NOTE: Scripts like in the Metalink article 105813.1 SCRIPT TO SUGGEST MINIMUM SHARED POOL SIZE.   are useful in older releases of the database, but do not work consistently with Oracle 9.2.x and higher. For other pointers on Shared Pool sizing in 10g, see

Note 270935.1 Shared pool sizing in 10g
Note 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts

How much free memory is available in my SGA?

You can see the free memory for the Shared Pool in the view V$SGASTAT. The view is broken down into memory structure table entries like 'library cache', 'KGLS heap', 'CCursor'. Prior to 10g, there were only a handful of table entries tracked in this view so small memory structures were lumped together in the table entry 'miscellaneous'.

What is managed automatically through 10g ASMM and/or 11g AMM?

The Automatic Shared Memory Management functionality is "turned on" when SGA_TARGET>0 on Release 10g.  ASMM will manage the "best" size for these components in the SGA

•    Shared Pool 
•    Large Pool 
•    Java Pool
•    Buffer Cache (the default one managed by db_cache_size)
•    Streams Pool (new to 10g Release 2)

Memory is moved in "granule sized" chunks.  You can find the granule size for your database querying V$SGAINFO.  Refer to Note 947152.1 for more on problems with large granule sizes.  Very large SGAs will use very large granule sizes by default. 

The other buffer caches (managed through parameters DB_nK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE), Log Buffer, and Fixed SGA areas are not automatically tuned by MMAN.   However these settings do affect the actual memory available to MMAN in the SGA_TARGET setting.

The SGA_TARGET can be changed dynamically up to the setting for SGA_MAX_SIZE.  NOTE:   There are limitations on this for some platforms depending on how LOCK_SGA works with memory at the OS.

Case Study:
You configured SGA_TARGET to be 4G. 

You also configure 

DB_KEEP_CACHE_SIZE=256M
LOG_BUFFER=200M
DB_4K_CACHE_SIZE=512M.  

You also manually set a minimum size for the shared pool (SHARED_POOL_SIZE=1G).  

How does this affect MMAN?   This means that MMAN cannot manage the auto-tuned memory components with all 4G of memory.  MMAN can only access 2,206,203,904 bytes.

Per the configuration above, this memory has to be part of the SGA:

   Log Buffers             209,715,200 
   Keep Buffer Cache 268,435,456
   4K Buffer Cache     536,870,912
+ Shared Pool         1,073,741,824  (manually assigning this value means this is the Shared Pool
                                                            cannot shrink beyond this)
------------------------------------ 
Total                       2,088,763,392

There are benefits to manually setting a minimum size for the auto-tuned components of the SGA, but it does have an impact on the amount of memory that MMAN has access to when needing to grow and shrink various components.   We DO recommend setting default/explicit settings for the auto-tuned components in the SGA.  To Gauge how ASMM is working, issue the script from Note 430473.1

A rule of thumb for setting up ASMM is

SGA_TARGET = 256M * # of CPUs

This makes a lot of assumptions about your application memory usages, etc. and it may be more appropriate to monitor the current size of the managed pools the queries listed above

1.  Observe trends and find the right values for a minimum size for the various pools from the scripts above.
2.  Use these hard-coded values for the pool parameter settings in your initialization file.
3.  Set SGA_TARGET to 25% above the sum of the fixed and hard-coded settings.
4.  Set SGA_MAX_SIZE to 25% above the setting for SGA_TARGET.   NOTE:   This step is not for every environment.   Memory for SGA_MAX_SIZE is allocated for Oracle at startup.  Most customers find it difficult to rationalize the allocation of the memory to Oracle, but not being able to get to it automatically if needed.   Some customers do find benefits to being able to incease SGA_TARGET on the fly up to SGA_MAX_SIZE for periodic business processes requiring more memory for the SGA than normal application use.

Warning:  There have been a number of issues filed on 10.2.x with ASMM and ORA-4031.   Many customers do not set minimum sizes for the various auto-tuned pools instead relying on sga_target and MMAN to move memory around as is needed.   Internal tests and from discussions with development, it is better to find a minimum setting for these pool and manually set that in the spfile or init parameter file.   As a starting point, review data in v$sga_dynamic_components and manually assign values to the pools at 70 or 75% of the current_size.   If there are indications of failed attempts to shrink the shared pool below that number over time, then decrease the default setting by another 10% and monitor that the new value is a better minimum setting.   This will help with decreasing chances of seeing excessive pinging of memory between the buffer cache and the various pools.

Starting with 11g, auto-tuning manages PGA_AGGREGATE_TARGET as well.  11g auto-tuning (AMM) is configured using MEMORY_TARGET>0.  See Note 443746.1 for more changes with auto-tuning at 11g.

NOTE:  With 11g, IMMEDIATE memory requests to the pools in the SGA can be moved around in the SGA even when MEMORY_TARGET and SGA_TARGET are explicitly set to 0.  See Note 1269139.1

How many Subpools will I have by default?

The number of subpools is calculated using a simple algorithm.  First, a subpool must be at least 128MB in 9i releases and at least 256MB in 10g releases.  Second, there can be one subpool for every four CPUs on the system, up to 7 subpools. The number of subpools can be explicitly controlled using the init.ora parameter _kghdsidx_count. There is no parameter to explicitly control the size of each subpool. 

If someone configured a 12-CPU system with a 300MB shared pool on 9i, Oracle will create two subpools, each of size 150MB.  If the shared pool size was increased to 500MB, Oracle will create three subpools, each of size 166MB.

Because 128MB (and even 256MB on 10g) subpools can be small in many application environments, the memory per subpool will likely need to be increased.  There is no parameter to change the minimum size of the subpool; the only recourse is to decrease the number of subpools for a given shared pool size or increase the shared pool size so the size of the subpools increase.  Please remember that increasing the size of the shared pool does not necessarily increase the size of the subpool, because the number of subpools can increase if there are many CPUs on the system.

Change as of 10.2.0.3 and higher:  Per bug 4994956, the 10g and higher algorithm was increased to 512M per subpool. 

How do I control the number of subpools used?

The parameter _kghdsidx_count controls the number of subpools used.  Setting the value of this parameter to one "reverts" the shared pool behavior back to 8.1.7 behavior, e.g., one subpool.

or add this in the pfile

NOTE:  Subpools are created at startup when the SGA is created.  In both examples above, the database has to be restarted to change the number of subpools created.   Any change to the _kghdsidx_count will change the number of subpools in the Large Pool as well.

Are all ORA-04031 errors reported in the alert log?

No.  Some errors only show up at the client workstation.  Prior to 11g, any ORA-4031 error that occurs on a regular user process will not be documented in the alert log.  There can also be cases where an ORA-600 or ORA-7445 error is reported in the alert log but the internal error was a side-effect of a 4031 memory issue.    The associated trace for the internal error may contain diagnostic data usually included with a default ORA-4031 trace.

NOTE:  With 11g, bug 9209518 indicates that we do generate incident traces for ORA-4031 even when a miss for memory happens and we satisfy the memory request doing internal LRU operations.  The exception to this could be cases affected by flood control settings when ORA-4031 errors happen frequently.

How can we see a breakdown of the data in the "miscellaneous" structure in V$SGASTAT?

When running a query on v$sgastat, there are cases where you will see a very large value for "miscellaneous".  Until Oracle 10g Release 2, the basic design of the SGA structure internally remained unchanged.   There were table entries for various memory "comments" in the data dictionary where we keep statistics on the largest memory "comments" or structures.   The smaller structures are lumped together in the memory "comment" called miscellaneous because we tracked only a handful of structures.   If you run a script like SGAStat.sql from Note: 430473.1.  This script will report back the biggest allocation areas in the view V$SGASTAT for the Shared Pool.   You can adjust the script to look at any of the Pools in the SGA.

Per bug 3663344, there were occasional inconsistencies in the statistics reported in V$SGASTAT due to memory structures growing and shrinking over time.   Once a memory structure reached an internally controlled size, we moved the data about the memory structure from the general purpose table entry, "miscellaneous", to a specific memory structure table entry.   The problem was usually seen with negative numbers in some memory structures in v$sgastat or at times you would see an unusually large value in "miscellaneous".

The only way to "drill-down" into the way memory is allocated in the "miscellaneous" area is to get a heapdump trace.  You can issue this command

and at the next occurrences of the 4031 problem you will get a breakdown of the memory in the SGA and also the breakdown of memory used in the top five subheaps in the SGA.  In this scenario, you would expect one or more of the largest subheaps listed in this trace to be within 'miscellaneous'. Unfortunately, there isn't a way to see the entire breakdown within 'miscellaneous', but we only need to be concerned about larger than expected entries within 'miscellaneous'.

To get an immediate memory dump use these steps

Close the SQL*Plus session and find the heapdump trace file listed in the 'oradebug tracefile_name' command above.

If the problem is actually associated with permanent memory structures (tracked under the 'miscellaneous' table entry), there is not a way to get information on these memory areas unless you set the event 10235 level 65536.  This event should only be set under direction from Oracle Support.

What database parameters are relevant to ORA-04031 problems?

• CURSOR_SHARING

Literal replacement is a feature where Oracle replaces literal values in SQL statements to reduce the application Shared Pool footprint and decrease "hard" parsing.  The literal values are replaced with bind variables and if two or more sessions are executing the same SQL statement, they can both use the same cursor with the bind variable instead of creating two unsharable cursors.

For example, two users connected as SCOTT issued the SQL statements "select ename from emp where empno = 20" and "select ename from emp where empno =100".  If cursor_sharing is set to FORCE, Oracle will create one cursor with a bind variable so the statement becomes equivalent to "select ename from emp where empno = :b1".  The two users will share the same cursor object instead of creating two separate Library Cache parent objects and their corresponding child objects.

The parameter has three modes:

• EXACT: no attempt at literal replacement
• FORCE: all literals are replaced; statements are shared without regard for how the literal values may affect the execution plan.
• SIMILAR: all literal are replaced, but statements are shared only if the cursors have the same plan.

Invalidations are caused by either executing DDL against the objects, gathering stats, or granting/revoking privileges.  You should see associated "library cache pin" waits also.

References: 
Note 287059.1 Library Cache Pin/Lock Pile Up hangs the application 
Note 34579.1  WAITEVENT "library cache pin" Reference 
Note 115656.1 WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK

The implementation of SIMILAR is not optimal.  Even though cursors with literal will have the best plans, the match criteria is based on the values of the literals.  The implementation creates a mini-hash table based on literal values, and if the values are the same, the child cursors are shared.  This behavior can lead to an application's Shared Pool footprint equivalent to running with CURSOR_SHARING=EXACT.   Instead of creating lots of individual cursors with their own children, SIMILAR creates lots of child cursors under the same parent cursor object.  If you see hard parses at 50/sec or higher (Snapshot/AWR), CURSOR_SHARING=EXACT can be helpful.

To Gauge the effectiveness of your current setting for this parameter, refer to Note 208918.1

• CURSOR_SPACE_FOR_TIME

If this parameter is set, Oracle does not unpin the library cache object at the end of execute.  This means that the active amount of memory pinned within the library cache increases as more cursors are opened and executed, reducing the amount of memory that can be aged out of the Shared Pool.  This parameter must be used carefully and with knowledge about the total application footprint within the Shared Pool.  Setting of this parameter without this knowledge can lead to ORA-04031 errors.  Cursors that are on the Session Cached Cursor list do not have their SQL area heaps pinned.

Some sites use this parameter because it avoids the library cache pin and unpin code path.  This is not a recommended parameter to set for performance.  It is very difficult to know whether it is safe to set this parameter.

NOTE:  This parameter can help with datapump performance in 10g.   If investigating ORA-04031 errors in the "sql area" area in the Shared Pool, look for CURSOR_SPACE_FOR_TIME=true.   This can cause the allocations for "sql area" to grow to take up most of the Shared Pool in 10.1.x.

• DB_CACHE_SIZE

Review the size set for the parameter in the RDA. If using SGA_TARGET, this will default to 0. Any hard-coded setting for this parameter when using SGA_TARGET will act as a minimum size for MMAN when it attempts to shrink the Buffer Cache.

• DB_nK_CACHE_SIZE

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• DB_KEEP_CACHE_SIZE

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• DB_RECYCLE_CACHE_SIZE

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• JAVA_POOL_SIZE

If using SGA_TARGET, this will default to 0. Any hard-coded setting for this parameter when using SGA_TARGET will act as a minimum size for MMAN when it attempts to shrink the Java Pool. 

If you are not using SGA_TARGET, the default size for this memory area is usually sufficient unless your environment utilizes a lot of JVM objects.

If you need to see more detail on how the memory is allocated in the Java Pool, you can issue 

• LARGE_POOL_SIZE

The Large Pool is intended to offload larger memory allocations from the Shared Pool related to Shared Server (UGA), Parallel Processing (Buffer allocations), and RMAN backup operations, and sequential file IO (e.g. IO slave activity).  If you are not using these specific functionality areas, you can set the Large Pool to 0.  The Large Pool does not use a Least Recently Used (LRU) algorithm, so until a session releases larger memory allocations, they remain in the Large Pool.

If you need to see more detail on how the memory is allocated in the Large Pool, you can issue

If using SGA_TARGET, the parameter will show as 0, but you can hard-code a minimum size and MMAN will not attempt to shrink the Large Pool below that setting. 

• LOG_BUFFER

This SGA memory component is not auto-tuned, but the memory setting will affect the actual memory available to MMAN.

• OPEN_CURSORS

This parameter sets the upper bound for the number of cursor that a session can have open.  Normally, cursors are opened through an OCI call or through a PL/SQL call to open a cursor.

In versions of Oracle prior to 9.2.0.5, OPEN_CURSORS was used as a cache for PL/SQL cursors.  When PL/SQL closes a cursor PL/SQL has opened, it sees if it can cache the cursor using one of the cursors allocated through OPEN_CURSORS.  The cursor is not really closed, but may be closed and replaced by another cached cursor or a cursor the application explicitly opens.  If an application opened very few cursors using OCI, it could still have many open cursors for PL/SQL's cursor cache if the application uses PL/SQL and the PL/SQL opened and closed many different cursors.

If a user explicitly opened a cursor and the session could not open a new cursor (the number of open cursors was equal to OPEN_CURSORS), one of the cached cursor would be closed so the explicit open would succeed.

Reference:
Note 76684.1  Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
Note 208857.1  SCRIPT - to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

• PROCESSES / SESSIONS

These processes will impact the size of the shared pool starting in 9.2.x. Memory structures are located in the Shared Pool with 9.2.x to store dynamic parameter setting information per session/process.  NOTE:  The memory structure can take as much as 20 bytes per parameter for 32-bit databases and 32 bytes per parameter for 64-bit databases. At 10.2.x, there are over 1300 dynamic parameters, so this can add up quickly with a lot of users on the database.  You can review the high water mark for Sessions and Processes in the V$RESOURCE_LIMIT view.  If the hard-coded values for these parameters are much higher than the high water mark information, consider decreasing the parameter settings to free up some memory in the Shared Pool for other uses.

• SESSION_CACHED_CURSORS 

When a cursor is closed, Oracle divorces all association between the session and the library cache state. If no other session has the same cursor opened, the library cache object and its heaps are unpinned and available for an LRU operation. The parameter SESSION_CACHED_CURSORS controls the number of cursors "soft" closed, much like the cached PL/SQL cursors.

SESSION_CACHED_CURSORS cursors are not part of the open_cursors statistic; it is a separate list. Instead of being really closed, Oracle places the cursor on a session-private LRU list and keeps the cursor available for a subsequent parse. If the user executes a new statement, it will first search the Session Cached Cursor list and, if found, uses it.

This parameter was originally conceived for Oracle FORMS applications. To parallelize FORMS development, customers often developed separate forms and navigated from form to form. The FORMS behavior is to close all cursors from the previous form before navigating to the next form. This parameter allowed Oracle to "cache" closed cursors and reused them if the application opens a cursor for the same statement.

Setting this parameter to a high value increases the amount of Library Cache memory (monitored by reviewing the view V$SGASTAT).

In 9.2.0.5 onwards, this parameter has also been overloaded to control the number of cursors PL/SQL caches using cursors allocated through the setting for OPEN_CURSORS.

Reference:
Note 270097.1 ORA-4031 and Very Large Library Cache in Oracle 9.2 with Session_cached_cursors set. Library Cache Pin/Lock Pile Up hangs the application
Note 274496.1 ORA-7445 and ORA-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used

• SGA_TARGET

If this parameter is set, the MMAN process will attempt to grow and shrink auto-tuned memory components.  Interestingly, on 10.2, if you specify in the spfile an explicit setting for SGA_TARGET higher than the explicit value for SGA_MAX_SIZE the next startup will ignore the prior setting for SGA_MAX_SIZE and set it equal to the new SGA_TARGET setting.  This is not the behavior at 11g.

• SHARED_POOL_SIZE

If using SGA_TARGET, this will default to 0, but a hard-coded setting for this parameter will act as a minimum size for MMAN when it attempts to shrink the Shared Pool.   With 9i and 10g, more SGA fixed memory structures have been moved to the Shared Pool.   This means that when upgrading from Oracle7 and Oracle8/Oracle8i, you must perform additional tuning analysis on the 9i or 10g memory needs for the Shared Pool.   With 9i and higher, Oracle also implemented a new subpool functionality.   This can require additional tuning analysis as the application(s) will utilize memory differently.  In some cases too many subpools inside the Shared Pool, can cause one of the subpools to be over-utilized and lead to ORA-4031 problems.

Reference:

Note 270935.1 Shared pool sizing in 10g

If you need to see more detail on how the memory is allocated in the Shared Pool, you can issue

• SHARED_POOL_RESERVED_SIZE

This parameter defaults to 5% of the SHARED_POOL_SIZE setting. When using SGA_TARGET, this will be adjusted as the Shared Pool component grows and shrinks automatically.   If you are seeing consistent ORA-4031 errors that indicate memory request failures larger than 4000 bytes, the 5% default value may not be sufficient in your application environment.   You can change the hidden parameter, _shared_pool_reserved_pct, to 10.  This will cause the Reserved Area to utilize 10% of the Shared Pool.  For example,

• STATISTICS_LEVEL

There are additional memory structures in 10g related to Statistics tracking. This parameter controls all major statistics collections or advisories in the database and sets the statistics collection level for the database. The parameter can be set to BASIC, TYPICAL, or ALL.

The default setting of TYPICAL will put strain on the Shared Pool unless you tune the Shared Pool to accommodate the ongoing analysis activity in the database.  In some performance tuning cases, it is necessary to set STATISTICS_LEVEL to ALL. This will use more memory in the Shared Pool than the other settings, so using ALL can cause ORA-4031 problems if the Shared Pool is not tuned to handle the additional memory needs.

In some known bugs on 9i and 10g, the workaround is to set STATISTICS_LEVEL to BASIC. This uses the least Shared Pool memory, but you give up self-tuning functionality (Memory, Advisors, Object Statistics management, etc.).

• STREAMS_POOL_SIZE

This is new memory pool in 10g.  It is intended to alleviate stress on memory structures in the Shared Pool related to Streams operations.  Review the size set for the parameter in the RDA.

If using SGA_TARGET on 10g Release 2, the parameter will be auto-tuned and will show up as 0.  You can hard-code a minimum size with 10g and Release 2 and MMAN will not attempt to shrink the Streams Pool below that setting.

If you need to see more detail on how the memory is allocated in the Java Pool, you can issue

Also review any settings that would indicate the use of Shared Server (MTS_SERVERS, MTS_DISPATCHERS, etc).  If these parameters indicate that Shared Server is configured, you should not see memory structures in the Shared Pool related to Shared Server 

NOTE:  Some of the parameters listed above are only applicable if using SGA_TARGET.  Be sure to investigate exactly how much memory MMAN can work with to grow and shrink the auto-tuned memory components.

What should we look at in an RDA to help diagnose a 4031 error?

Review the RDA report because many of the "pieces of the puzzle" or in a single report.  However, unless the problem is a simple case of the memory component not configured or too small, other diagnostic information is often needed to find the root issue:

Under 'Overview', 'System Information', review the number of CPUs on this server.   The number of CPUs helps determine the number of subpools used in the Shared Pool.   For example,

Under 'Overview', 'Database Information', review the release level information (V$VERSION)

Under 'RDBMS', 'Database Parameters', review the appropriate parameters as described above. With the latest versions of the RDA report, the hidden parameter settings are included in this section. You should review the settings for '_PX_use_large_pool', '_kghdsidx_count', '_large_pool_min_alloc', '_library_cache_advice', '_shared_pool_reserved_pct', '_shared_pool_reserved_min_alloc', '_4031_dump_bitvec', '_4031_max_dumps', '_4031_dump_interval', '_4031_sga_dump_interval', '_4031_sga_max_dumps'.

Under 'RDBMS', 'V$RESOURCE_LIMIT', review the high water mark information for 'processes', 'sessions'. If the settings for PROCESSES and SESSIONS is much higher than high water mark numbers, decreasing these settings could help decrease some permanent memory structure allocations. In RAC environments, the parameter settings for 'ges*' parameters can also be important. There are some RAC/ORA-04031 bugs related to the 'ges%' parameters.

What is relevant in the default 4031 trace file?

With 9.2.0.5 and higher, a trace file is generated at the time of an ORA-04031 error (controlled by _4031_dump_bitvec).   On 9.2.x, the default: is 6639615 = 0x654fff , which means: 
    0x0004fff: Enable all dumps except: subheaps & top sga heap w/ contents 
    0x0050000: five minutes between process dumps 
    0x0600000: 60 minutes (6 x 10) minutes between sga heap dumps

With 10g, the default is 20479 = 0x004fff
   0x0004fff: Enable all dumps except: subheaps & top sga heap w/ contents  

and there are additional initialization parameters added as well:

_4031_dump_interval (default 300) -  minimum amount of time between 4031 diagnostic dumps, in units of seconds.
_4031_sga_dump_interval (default 3600) - minimum amount of time between SGA heap dump.

NOTE: Setting either of these to 0 means all errors generate a trace file.  If the ORA-04031 errors happen often in a short period of time, you can hang the database with too many trace files generated.
 
_4031_max_dumps (default 100) - sets the limit on the number of dumps per process.  0 turns off 4031 dumps.
_4031_sga_max_dumps (default 10) - sets the limit on the number of SGA dumps per instance.  0 turns off SGA dumps.

Be sure to check the header information and verify that the date information matches up with the reports of the errors.  After the header information about the trace file, you will see information like this

Scroll down the trace file until just after the Stack Trace listing,

Scrolling down a bit more in the trace file, we see the Library Cache information at the time of the error:

In the Library Cache Statistics information, look for 'hit ratio' percentages that would indicate fragmentation problems.  The goal is to keep the 'hit ratio' as close to 100% as possible.   Also review the reloads and invalids information.  Lots of invalids and reloads means more flushing of memory in the library cache and can be a sign of application inefficiencies and fragmentation. 

On 10gR2, the V$SGASTAT view contains more detail than prior releases.  An initial approach on 10gR2 is to run the SGAStat.sql query (from Note: 430473.1) every 30 minutes or so for several hours during peak activity.  Comparing the results of the memory entries will help identify where allocations are growing.

If explicitly setting the Heapdump event

you will see a more detailed view of memory

In some cases, it is necessary to dump additional diagnostic information on a subheap.   For example,

What is relevant in the Statspack/AWR report for a 4031 error?

When investigating the 4031 problem using the Statspack/AWR reports, focus on the following

- time period
- v$librarycache statistics
- parse vs execute and parse/sec (hard vs soft parses)
- Waits section
- Parameter section
- Latch contention

and

Further investigation of problems in the Library Cache is possible by reviewing a series of Statspack reports before and after the time of the error(s). With 10g you can find the Library Cache information in the Snapshots Report. Click the 'Performance' tab scroll to the bottom of the page. Click on the 'Snapshots' link and select a Snapshot generated by your database by clicking on the 'ID' link. Select the 'Report' tab and you will see information like the following in HTML format:

How can we determine if there are application issues causing the problem?

Common problems in the application code are

- not using bind variables

- multiple child cursors

- high parse ratios

Questions you should ask/consider in this case:

1.  How long does it take after a restart of the database for the problem to occur?
2.  Are you running Statspack reports or using AWR in 10g?   Are you seeing high hard parse counts?
3.  Is the application using bind variables or literals?
4.  How often are you gathering statistics?   When you gather statistics, this invalidates objects in the Library Cache that associated with the tables with new statistics.   This can be part of fragmentation problems in the shared pool.
5.  Is there evidence of high version counts in the Library Cache

There are queries to investigate further in each of those possibilities.

References:
Note 232443.1 How to Identify Resource Intensive SQL for Tuning

Not Using Bind Variables

Here is an example of not using a bind variable in an internal test. The test code did not complete for 2 to 3 minutes

Simply changing the test code to use a bind variable improved the performance dramatically and completed in 2 or 3 seconds

Not using bind variables will cause over usage of memory in the Shared Pool/Library Cache.

You want to investigate this problem when you see issues with high hard parse counts.

Multiple child cursors

Every child cursor allocates space in the Shared Pool. In some cases, known bugs in the past, too many child cursors were created and allocations grew beyond intended behavior.

Warning:
Note 403616.1 Many Child Cursors create in 10.2.0.3 on Windows and Linux

NOTE: The V$SQL_SHARED_CURSOR view includes 'Y' or 'N' columns

See Note 430473.1 for a script to better interpret the information in this view. 

High parse ratios

It is important to identify what could produce high parsing calls:

- Use of dynamic PL/SQL
- Execution of DDL statements during periods of high workload. Every time a DDL statement is executed, it will cause invalidation of all the statements referencing the object involved. Next time a sql statement referencing the object is executed, it will have to be reparsed and loaded into the shared pool.

Typical operations that cause this situation is the execution of:

- Grant/revoke command
- Alter view
- Alter package | procedure
- Analyze table |index
- DBMS_STATS
- Truncate table
- Alter index
- Alter table move

If an ORA-04031 error is associated with high parse ratios, you will also see latch contention for the library cache latch as well as indications of lots of invalidations and reloads in the Library Cache statistics in a Statspack or AWR report.

Note 1012047.6 How To Pin Objects in Your Shared Pool
Note 69925.1 PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL

Community discussion

For further discussion or questions about topics in this article, please visit 'Diagnosing and Resolving ORA-4031 errors'

References