Diagnosing and Resolvi…

Skip to content

9876

Diagnosing and Resolving Error ORA-04031 on the Shared Pool orOther Memory Pools [Video] [ID 146599.1]

---

Modified:30-Oct-2012Type:TROUBLESHOOTINGStatus:PUBLISHEDPriority:2

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 andlater
Information in this document applies to any platform.

Purpose

Checked for relevance on 24-Feb-2010

The purpose of this document is to provide an easy to use, step bystep guide to resolving ORA-04031 errors.

In case of conflicting information between note and videopresentation, information contained in the note takes precedenceover information that may be recorded or shown on video.

Video - Diagnosing and Resolving 4031 errors(10:00)

Troubleshooting Steps

When any attempt to allocate a large piece of contiguous memoryin the shared pool fails Oracle first flushes all objects that arenot currently in use from the pool and the resulting free memorychunks are merged. If there is still not a single chunk largeenough to satisfy the request the ORA-04031 error is returned.NOTE: These errors can occur on an ASM instance as well. Thedefault SHARED_POOL_SIZE should be sufficient in most environments,but can be increased if you are experiencing ORA-04031errors.

The message that you will get when this error appears is thefollowing:

04031, 00000, "unable to allocate %s bytesof shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"

// *Cause: More shared memory is needed than was allocated in theshared
// pool.
// *Action: If the shared pool is out of memory, either usethe
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amountof
// 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 with the SharedPool

Before continuing, understanding the following instanceparameters will be essential:

• SHARED_POOL_SIZE - This parameter specifies the size of theshared pool in bytes and can accept a numerical values or a numberfollowed by the suffix "K" or "M" where "K" means "multiply by1000" and "M" means "multiply by 1000000"
  
• SHARED_POOL_RESERVED_SIZE - It specifies the shared pool spacewhich is reserved for large contiguous requests for shared poolmemory. This parameter along with theSHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid theoccurrence of this error from situations where shared poolfragmentation forces Oracle to search for and free
  chunks of unused pool to satisfy the current request.
  
  Ideally, this parameter should be large enough to satisfy anyrequest scanning for memory on the reserved list without flushingobjects from the shared pool. Since the operating system memory mayconstraint the size of the shared pool, in general, you should setthis parameter to 10% of the SHARED_POOL_SIZE parameter.
  
• SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parametercontrols allocation of reserved memory. Memory allocation largerthan this value can allocate space from the reserved list if achunk of memory of sufficient size is not found on the shared poolfree lists. The default value is adequate for most systems. If youincrease the value, then the Oracle server will allow fewerallocations from the reserved list and will request more memoryfrom the shared pool list. This parameter is hidden in Oracle 8iand higer, but it can be found by executing the following SQLstatement:
  
  
  SELECT nam.ksppinm NAME,
         val.ksppstvlVALUE
  FROM x$ksppi nam,
       x$ksppsvval
  WHERE nam.indx = val.indx
  AND nam.ksppinm LIKE '%shared%'
  ORDER BY 1;
  
  
  Note: This parameter was obsoleted with 8i.The parameter can still be modified via the underscore parameter_SHARED_POOL_RESERVED_MIN_ALLOC.

10g Note: In Oracle 10g anew feature called "Automatic Shared Memory Management" allows theDBA to reserve a pool of shared memory that is used to allocate theshared pool, the buffer cache, the java pool and the largepool.

In general, when the database needs to allocate a large object intothe shared pool and cannot find contiguous space available, it willautomatically increase the shared pool size using free space fromother SGA structure.

Since the space allocation is automatically managed by Oracle, theprobability of getting ora-4031 errors may be greatly reduced.Automatic Shared Memory Management is enabled when the parameterSGA_TARGET is greater than zero and the current setting can beobtained quering the V$SGA_DYNAMIC_COMPONENTS and V$SGA_RESIZE_OPSviews.

It is not possible restrict the size of auto-tuned components inthe SGA if using SGA_TARGET. If you want to exercise more controlon shared pool size, please do not use SGA_TARGET parameter. Usingexplicit settings for the auto-tuned components is recommended asminimum sizes to keep auto-tuning from being too aggressive movingmemory in the SGA.

Please refer to the 10g Administration Manual for furtherreference.

 

11g Note: In Oracle 11g anew feature called "Automatic Memory Mmanagement" allows the DBA toconfigure the database to manage shared memory areas as well as PGAmemory.

In general, when the database needs to allocate a large object intothe shared pool and cannot find contiguous space available, it willautomatically increase the shared pool size using free space fromother SGA structure and/or PGA. During times of heavy workload, PGAmay automatically increase using free memory from the SGAstructures.

Since the space allocation is automatically managed by Oracle, theprobability of getting ora-4031 errors may be greatly reduced.Automatic Memory Management is enabled when the parameterMEMORY_TARGET is greater than zero and the current setting can beobtained quering the V$MEMORY_DYNAMIC_COMPONENTS andV$MEMORY_RESIZE_OPS views.

It is not possible restrict the size of auto-tuned components inthe SGA if using MEMORY_TARGET. If you want to exercise morecontrol on the components in the SGA or PGA do not useMEMORY_TARGET or SGA_TARGET. SGA_TARGET and explicit settings forthe auto-tuned components are recommended as minimum sizes to keepauto-tuning from being too aggressive moving memory for thedatabase workload.

Please refer to the 11g Administration Manual for furtherreference.

2. Diagnosing error ORA-04031

Note: most common ORA-4031 occurrences arerelated to the SHARED POOL SIZE, therefore the diagnostic stepsprovided in this article will mostly address issues related to theshared pool. For other areas like large_pool or java_pool where thememory allocation algorith is simpler, normally the error is causedby an undersized structure.

ORA-04031 error can be due to either an inadequeate sizing ofthe SHARED POOL size or due to heavy fragmentation leading thedatabase to not finding large enough chuncks of memory. NOTE: Therehave been many reports where the default size for shared_pool_sizeon an ASM instance is too small. If you experience ORA-04031 erroron your ASM instance, increase the shared_pool_size parameter to50M. If the problem persists, then increase the parameter again inincrements of 10M until you stop seeing the error.

• Inadequate Sizing
  The first thing is determining if the ORA-04031 error is a resultof lack of contiguous space in the library cache by verifying thefollowing from V$SHARED_POOL_RESERVED:
  

  REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is> SHARED_POOL_RESERVED_MIN_ALLOC

  If this is the case, consider loweringSHARED_POOL_RESERVED_MIN_ALLOC to allow the database putting moreobjects into the shared pool reserved space and then increase theSHARED_POOL_SIZE if the problem is not resolved.
  
  
  Note: a bug was discoverd whereLAST_FAILURE_SIZE can be wrong in cases where multiple pools areused. The value in LAST_FAILURE_SIZE can be a sum of failure sizesacross all pools. This is filed in unpublished Bug:3669074 and hasbeen fixed as of 9.2.0.7, 10.1.0.4, and 10.2.x.
• Fragmentation
  If this is not the case, then you must determine if the ORA-04031was a result of fragmentation in the library cache or in the sharedpool reserved space by following this rule:
  

  REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is< SHARED_POOL_RESERVED_MIN_ALLOC.

  To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOCto lower the number of objects being cached into the shared poolreserved space and increase SHARED_POOL_RESERVED_SIZE andSHARED_POOL_SIZE to increase the available memory in the sharedpool reserved space.

Another consideration: pre-9i, changing OPTIMIZER_MAX_PERMUTATIONSto 2000 can reduce shared pool space pressure.

The above checks are still applicable with ORA-04031 in 10g andbeyond with auto-tuning functionality. However, explicitlyadjusting the setting for SHARED_POOL_SIZE only changes theauto-tuned "minimum" for the Shared Pool. It is a "best practice"to set an explicit setting but to ensure it is low enough toaccommodate memory needs in other places if the auto-tuner has toshrink the Shared Pool to handle workload.

3. Resolving error ORA-04031

• Oracle BUGs
  
  Oracle recommends to apply the latest patchset available for yourplatform. Most of the ORA-4031 errors related to BUGs can beavoided by applying these patchsets. See Document 4031.1 with the latest reports of bugs andpatches.
  
  If you run out of memory while compiling a java code (withinloadjava or deployejb), you should see an error:
  
  
  A SQL exception occurred while compiling: :ORA-04031: unable to allocate bytes of shared memory ("sharedpool","unknown object","joxlod: init h", "JOX:ioc_allocate_pal")
  
  The solution is to shut down the database and set JAVA_POOL_SIZE toa larger value. The mention of "shared pool" in the error messageis a misleading reference to running out of memory in the "SharedGlobal Area". It does not mean you should increase yourSHARED_POOL_SIZE. Instead, you must increase your JAVA_POOL_SIZE,restart your server, and try again.
  
  The cause of this problem has been identified in:
  unpublished Bug:2736601 - CORRECT ORA-4031 MESSAGE TO FLAG JAVAPOOL.
  
  
  
• Small shared poolsize
  
  In many cases, a small shared pool can be the cause of theORA-04031 error.
  
  The following information will help you to adjust the size of theshared pool:
  
  • Library Cache Hit Ratio
    The hit ratio helps to measure the usage of the shared pool basedon how many times a SQL/PLSQL statement needed to be parsed insteadof being reused. The following SQL statement help you to calculatethe library cache hit ratio:
    
    SELECT SUM(pins) "EXECUTIONS",
           SUM(reloads)"CACHE MISSES WHILE EXECUTING"
    FROM v$librarycache;
    
    If the ratio of misses to executions is more than 1%, then try toreduce the library cache misses by increasing the shared poolsize.
    
  • Shared Pool SizeCalculation
    Scripts used to be available for calculating a "best" size for theShared Pool. Problems arose over time due to changes to internalmemory structures and, in some cases, those older scripts couldaccount for certain memory areas twice presenting you withpercentages greater than 100%. There are some adjusted scriptsavailable at:
    Document 430473.1- ORA-4031 Common Analysis/Diagnostic Scripts [Video]
    
    However, with the introduction of the memory advisors (with 9.2x)and auto-tuning (with 10g Release 2), these estimation scripts arenot as useful. See also:
    Document 276103.1 - Performance Tuning Using Advisors and ManageabilityFeatures: AWR, ASH, ADDM and Sql Tuning Advisor
    
• Shared PoolFragmentation:
  
  Every time a SQL or PL/SQL statement needs to be executed the parserepresentation is loaded in the library cache requiring a specificamount of free contiguous space. The first resource where thedatabase scans is the free memory available in the shared pool.Once the free memory is exhausted, the database looks for reusingan already allocated piece not in use. If a chunk with the exactsize is not available, the scan continues looking for space basedon the following criteria:
  
  • The chunk size is larger than the required size
  • The space is contiguous
  • The chunk is available (not in use)
  
  Then that chunk is split and the remaining free space is added tothe appropriate free space list. When the database is operating inthis way for a certain period of time the shared pool structurewill be fragmented.
  
  When the shared pool is suffering fragmentation ORA-04031 errors(when the database cannot find a contiguous piece of free memory)may occur. Also as a concequence, the allocation of a piece of freespace takes more time an the performance may be affected (the"chunk allocation" is protected by a single latch called "sharedpool latch" which is held during the whole operation). However,ORA-4031 errors don't always affect the performance of thedatabase.
  
  If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors area result of dynamic SQL fragmenting the shared pool. This can becaused by:
  
  • Not sharing SQL
  • Making unnecessary parse calls (soft)
  • Setting SESSION_CACHED_CURSORS too high
  • Not using bind variables
  
  To reduce fragmentation you will need to address one or more of thecauses described before. In general to reduce fragmentation youmust analyze how the application is using the shared pool andmaximize the use of sharable cursors.
  
  Please refer to Document 62143.1, which describes these options in greater detail. Thisnote contains as well further detail on how the shared pool works.The following views will help you to identify non-sharable versionsof SQL/PLSQL text in the shared pool:
  
  • V$SQLAREA View
    This view keeps information of every SQL statement and PL/SQL blockexecuted in the database. The following SQL can show you statementswith literal values or candidates to include bind variables:
    
    SELECT SUBSTR(sql_text,1,40) "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: The number "30" in the having sectionof the statement can be adjusted as needed to get more detailedinformation.
  • X$KSMLRU View
    
    There is a fixed table called X$KSMLRU that tracks allocations inthe shared pool that cause other objects in the shared pool to beaged out. This fixed table can be used to identify what is causingthe large allocation.
    
    If many objects are being periodically flushed from the shared poolthen this will cause response time problems and will likely causelibrary cache latch contention problems when the objects arereloaded into the shared pool.
    
    One unusual thing about the X$KSMLRU fixed table is that thecontents of the fixed table are erased whenever someone selectsfrom the fixed table. This is done since the fixed table storesonly the largest allocations that have occurred. The values arereset after being selected so that subsequent large allocations canbe noted even if they were not quite as large as others thatoccurred previously. Because of this resetting, the output ofselecting from this table should be carefully kept since it cannotbe retrieved back after the query is issued.
    
    To monitor this fixed table just run the following:
    
    SELECT *
    FROM X$KSMLRU
    WHERE ksmlrsiz > 0;
    
    This view can only be queried by connected as the SYS.
  • X$KSMSP View (Similar to HeapdumpInformation)
    
    Using this view you will be able to find out how the free space iscurrently allocated, which will be helpful to undrestand the levelof fragmentation of the shared pool. As it was described before,the first place to find a chunck big enough for the cursorallocation is the free list. The following SQL shows the chunksavailable in the free list:
    
    SELECT '0 (<140)' bucket, ksmchcls,ksmchidx, 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, ksmchidx, 10*TRUNC(ksmchsiz/10)
    UNION ALL
    SELECT '1 (140-267)' bucket, ksmchcls,ksmchidx,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, ksmchidx, 20*TRUNC(ksmchsiz/20)
    UNION ALL
    SELECT '2 (268-523)' bucket, ksmchcls, ksmchidx,50*TRUNC(ksmchsiz/50),
           COUNT(*),MAX(ksmchsiz),
           TRUNC(AVG(ksmchsiz))"AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
    FROM x$ksmsp
    WHERE ksmchsiz BTEWEEN 268 AND 523
    AND ksmchcls='free'
    GROUP BY ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50)
    UNION ALL
    SELECT '3-5 (524-4107)' bucket, ksmchcls, ksmchidx,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, ksmchidx, 500*TRUNC(ksmchsiz/500)
    UNION ALL
    SELECT '6+ (4108+)' bucket, ksmchcls, ksmchidx,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, ksmchidx, 1000*TRUNC(ksmchsiz/1000);
    
    
    Note: The information available in this viewis the same that is generated as part of a HEAPDUMP level 2.
    
    Be aware of Bug:4715420 stating that selecting from X$KSMSP is asking onesession to hold the shared pool latches in turn for a LONG periodof time and should be avoided on live systems. Selecting fromX$KSMSP on a production system is a very bad idea.
    
    If the result of the above query shows that must of the spaceavailable is on the top part of the list (meaning available only invery small chuncks). It is very likely that the error is due to aheavy fragmentation.
    
    You can also use this view as follows to review overall memoryusage in the SGA:
    
    SQL> SELECT ksmchcls CLASS,COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
    TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k'"AVG SIZE"
    FROM X$KSMSP GROUP BY ksmchcls;
    
    CLASS           NUM        SIZAVG SIZE
    -------- ---------- ---------- ------------
    R-free           12    8059200      655.86k
    R-freea          24        960         .04k
    free            331  151736448      447.67k
    freeabl        4768    7514504        1.54k
    perm              2   30765848   15,022.39k
    recr           3577    3248864         .89k
    
    
    1. if free memory (SIZ) is low (less than 5mb or so) you may needto increase the shared_pool_size andshared_pool_reserved_size.
    2. if perm continually grows then it is possible you are seeingsystem memory leak.
    3. if freeabl and recr are always huge, this indicates that youhave lots of cursor info stored that is not releasing.
    4. if free is huge but you are still getting 4031 errors, (you cancorrelate that with the reloads and invalids causingfragmentation).

4. ORA-04031 error and Large Pool

The Large pool is an optional memory area that can be configuredto provide large memory allocations for one of the followingoperations:

• session memory for the multi-threaded server and the Oracle XAinterface.
• The memory ( Buffers ) for Oracle backup and restore operationsand for I/O server processes.
• Parallel Execution messaging buffers.

The Large pool does not have a LRU list. It is different fromreserved space in the shared pool, which uses the same LRU list asother memory allocated from the shared pool. Chunks of memory arenever aged out of the large pool, memory has to be explicitlyallocated and freed by each session. If there is no free memoryleft when a request is made then an ORA-4031 will be signalledsimilar to this:

ORA-04031: unable to allocate XXXX bytes ofshared memory ("large pool","unknown object","sessionheap","frame")

Few things can be checked when this error occurs:

1. Check V$SGASTAT and see how much memory is used and free usingthe following SQL statement:
   
   SELECT pool, name, bytes
   FROM v$sgastat
   WHERE pool = 'large pool';
2. You can also take a heapdump level 32 to dump the large poolheap and check free chunks sizes.

Memory is allocated from the large pool in chunks ofLARGE_POOL_MIN_ALLOC bytes to help avoid fragmentation. Any requestto allocate a chunk size less LARGE_POOL_MIN_ALLOC will beallocated with size of LARGE_POOL_MIN_ALLOC. In general you may seemore memory usage when using Large Pool compared to SharedPool.

Usually to resolve an ORA-4031 in the large pool theLARGE_POOL_SIZE size must be increased.

5. ORA-04031 and SHARED POOL FLUSHING

There are several techniques to increase cursor sharability sothat shared pool fragmentation is reduce as well as likeability ofORA-4031 errors. The best way is by modifying the application touse bind variables. Another workaround when the application cannotbe modified is using CURSOR_SHARING to a value different of EXACT(Be aware that this may cause changes in execution plan, so it isadvisable to test the application first). When none of the abovetechniques can be used and fragmentation is considearble heavy inthe system, flushing the shared pool might help alliviating thefragmentation. However some considerations must be taken intoaccount:

• Flushing the shared pool will cause that all the cursor thatare not in use are removed from the library cache. Therefore justafter the shared pool flusing is issued, most of the SQL and PL/SQLcursors will have to be hard parsed. This will increase the CPUusage of the system and will also increase the latch activity.
• When applications don't use bind variables and have heavypossibilities of many users doing frequen similar operations (likein OLTP systems) it is common that soon after the flush is issuedthe fragmentation is back in place. So be advice that flushing theshared pool is not always the solution for a bad application.
• For large shared pool flushing the shared pool may cause a haltof the system, specially when the instance is very active. It isrecommended to flush the shared pool during off-peak hours.

6. Advanced analysis to ORA-04031 error

If none of the techniques provided cannot resolve the occurenceof ORA-04031 errors, additional tracing may be needed to get asnapshot of the shared pool when the problem is in place.

Modify the init.ora paramater to add the following events to get atrace file with additional information about the problem:

event = "4031 trace name errorstack level3"
event = "4031 trace name HEAPDUMP level 2"

 

Note: These parameters will take not effectunless the instance is bounced.

Starting with 9.2.0.5, instead of requesting heapdump level 1,2, 3or 32 you can use level those same levels plus (536870912). Thiswill generate the 5 largest subheaps AND the 5 largest heap areaswithin each of those. If the problem is reproducible, the event canbe set at session level using the following statement before theexecution of the faulty SQL statement:

SQL> ALTER SESSION SET EVENTS '4031trace name errorstack level 3';
SQL> ALTER SESSION SET EVENTS '4031 trace name heapdump level536870914';

The trace file(s) should be sent to Oracle Support fortroubleshooting.

Important Note: In Oracle 9.2.0.5 and higherreleases a trace file is generated BY DEFAULT every time anORA-4031 error occurs, and can be located in the USER_DUMP_DESTdirectory (or ADR with 11g). If your database version is one ofthese, you don't need to follow the steps described before togenerate additional tracing.

References

NOTE:1012046.6- How to Calculate Your Shared Pool Size


NOTE:316138.1 - ORA-4031 / Continuous Growth of 'miscellaneous'in v$sgastat when STATISTICS_LEVEL is set to TYPICAL or ALL
NOTE:367392.1 - ORA-4031 with calls to ksfd_alloc_sgabuffer,ksfd_alloc_contig_buffer, ksfd_get_contig_buffer
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error[Video]
NOTE:61623.1 - Resolving Shared Pool Fragmentation InOracle7
NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool andTuning Library Cache Latch Contention
NOTE:4031.1 - OERR: ORA-4031 "unable to allocate %s bytes ofshared memory ("%s","%s","%s")"