Troubleshooting and Di…

跳至内容

9876

 

ID

…

 

Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID396940.1]

---

修改时间:2013-3-11类型:FAQ状态:PUBLISHED优先级:2

In thisDocument

Purpose Common BugsQuestions and Answers How is memory allocated and deallocated in the SGA pools? What are Subpools? What is the Reserved Area? What are the pools in the SGA used for? What is an ORA-04031 Error? Is my Reserved Area sized properly? Is there a way to find a "right" size for the Shared Pool? How much free memory is available in my SGA? What is managed automatically through 10g ASMM and/or 11gAMM? How many Subpools will I have by default? How do I control the number of subpools used? Are all ORA-04031 errors reported in the alert log? How can we see a breakdown of the data in the "miscellaneous"structure in V$SGASTAT? What database parameters are relevant to ORA-04031problems? What should we look at in an RDA to help diagnose a 4031error? What is relevant in the default 4031 trace file? What is relevant in the Statspack/AWR report for a 4031error? How can we determine if there are application issues causing theproblem? Not Using Bind Variables Multiple child cursors High parse ratios Is it possible to find objects in the Library Cache that could becausing the problem?References

---

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.5.0 to 11.2.0.4[Release 8.1.5 to 11.2]
Information in this document applies to any platform.

Purpose

This article is intended to

•   help the reader understand causes of theORA-4031
•   gather the diagnosticsneeded to narrow down the errors
•   answer some of the commonquestions 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 releaselevel.


NOTE:  References below to script namesare canned scripts that can be found in Note 430473.1 ORA-4031 Common Analysis/DiagnosticScripts.   Look in the section ofthe article titled 'Software Requirements/Prerequisites'. You candownload the zip file clicking on the 'Click here'link.    Thescripts are updated over time, so please verify you have the latestversions of the scripts from time to time.

Questions andAnswers

 

How is memory allocated and deallocated in the SGApools?

The SGA is comprised of fixed areas like the Log Buffers and theBuffer Cache(s) as well as memorypools (Shared Pool, Large Pool, Java Pool, and in 10g, the StreamsPool). Memory allocated to the variouspools are divided in heaps that can be composed of one or many subheap memory structures. 

This is similarto many segments inside a tablespace. Thetablespace will be the heap and the segments are the subheaps. Theextents within the segment are like the various subheaps that canexist inside "parent"subheaps.   The goal in theshared memory areas is the same as atablespace--avoid fragmentation.  To do this weallocate the chunk of memory that best fits therequest for memory, coalescing adjacent free space as needed anddetecting memory that can be flushed and reused.

Inside these pools, we use free listbuckets.   They are structures offree lists and each list correspond to a specific size. Oracle doesa binary search on the free list sizes to find the appropriate freelist.  The first bucket that is greater or equalto the requested size will be returned.  Atstartup of the database, there are various sized chunks of memorycreated in each pool.  We will continue to walkthe free list until we find a bucket which points to a large enoughextent of memory.  The Shared Pool will utilize aLeast Recently Used (LRU) algorithm to "age out" memory structuresthat have not been reused over time.

To get a better idea, see this information from a heapdump traceshowing 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 maximumsize is 40 bytes and the average is 40 bytes. Bucket 2 is a freelist of memory chunks with sizes between 40 and 48 bytes. When achunk of space is freed/deallocated, it is added to the bucketwhose size is less than or equal to the chunksize.  If you find smaller chunks of memoryavailable in the Shared Pool vs. a mixture of large chunks andsmall chunks, this can indicate the application is causingfragmentation and there isa increasing chance thata future large memoryrequests will fail with an ORA-04031 error. See How can we determine if there are Application issues causing theproblem? for more on tracing inefficient application code.

The Shared Pool and LargePool divide their shared memory areas intosubpools (startingwith 9i).  Each subpool will haveFree List Buckets containing pointers to memory chunks within thesubpool.  The other pools are treated as one largememory area with a single Free List as described above.

When a memory chunk isallocated inside the memory pool, it will be associated with amemory type.  The chunk will be allocated asPERMANENT, FREEABLE, orRECREATABLE.   These memorychunks are then associated with a memory structure or elementinside the pool.  For example, "KGLSheap".  These memory structures/elements are notalways tracked/commented in the data dictionary (specifically thosein the PERMANENT type).

Chunk types:

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

Free chunks - These chunks are free and available for reuse shoulda 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 onan 
LRU list when they areunpinned.   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 differentways.   Some chunks areallocated 
and will remain in use for the "life" of theinstance.   Some "permanent"chunks are allocated but can 
be used over and over again internally as they areavailable. 

@  Commented permanent chunks -With an event set, permanent chunks are commented as othertypes 
@  of memory chunks for debuggingpurposes.

What are Subpools?

In Oracle 9i and later versions, the SharedPool can be divided intosubpools.  Each subpool is a "mini" shared pool,having its own set of Free Lists, memory structure entries, and LRUlist.  This was a scalability change made to theShared Pool/Large Pool to increase the throughput ofthese pools in that now each subpool is protectedby a Pool child latch. This means there is nolonger contention in the Shared/Large Pool for a single latch as inearlier versions.  The reserved area for theShared Pool is divided equally throughout the subpools aswell.

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

For example,
ORA-04031: unable to allocate 4192 bytes of shared memory ("sharedpool","SELECT * 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 onesubpool can get over-utilized. Once the subpool is selected, thesearch for memory chunks can fail even though another subpool mighthave adequate memory available. Starting with 10g, wedo have functionality allowing the search to"switch" to another subpool if a memory request is not met in theselected subpool but that is not possible for allmemory structures/elements.

@  NOTE: There is a small subset of functionality whichwill use memory chunks across multiple subpools.
@  In other words, stripingmemory used across multiple subpools.
@
@  There is very littledocumented on these and generally a request for memory will use around-robin
@  approach to find the memorychunk it needs from a "random" subpool.

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

An end-user has no visibility into subpools.  Theyare hidden below the implementation of the Shared/LargePools.   NOTE: If the Shared Poolis using subpools, there will automatically besubpools created in the Large Pool as longas LARGE_POOL_SIZE>0.

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

What is the Reserved Area?

A cache miss on the data dictionary cache or library cache ismore expensive than a miss on the buffer cache. For this reason, the Shared Pool should be sized to ensure thatfrequently used data is cached.  If there is notenough free space in the Shared Pool, then Oracle must search forand free enough memory to satisfy this request. This operationcould conceivably hold latch resources fordetectable periods of time, causing minor disruption to otherconcurrent attempts at memory allocation. Missed space requests forlarge size allocations can thus be very costly since it may causemany 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 ReservedArea) inside the Shared Pool. This memory can be used to satisifylarge contiguous allocation requests when space is not available inthe general shared pool list.
These large allocation requests are typically for operations suchas PL/SQL and trigger compilation or for temporary space whileloading Java objects.
After the memory allocated from the Reserved Pool is freed, itreturns to the Reserved Pool.

The minimum size of allocations that can be placed in Reserved poolare controlled via hidden parameter_shared_pool_reserved_min_alloc. The default setting is 4400 byteson 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 thereserved list if a chunk of memory of sufficient size is not foundon the general shared pool free list.

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

The Reserved pool size can be set to a fixed size by specifyingshared_pool_reserved_size or as a percentage with hidden parameter_shared_pool_reserved_pct.

By default, 5% of the Shared Pool is set aside asthe Reserved Pool to handle allocations of memoryhigher than defined by the hidden parameter_shared_pool_reserved_pct.  Issue

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm in('_shared_pool_reserved_pct','_shared_pool_reserved_min_alloc');

to see the current settingin your environment.  In some applicationenvironments, 5% is too small. If an ORA-04031 problem indicates a very largememory request failure, increase the size of the Reserved Area bymanually setting SHARED_POOL_RESERVED_SIZE or if usingSGA_TARGET>0, change the hidden parameter,_shared_pool_reserved_pct to 10 or 15 (see example below) to ensurethat when the Shared Pool grows or shrinks automatically, theReserved Area will change as well. 

The viewV$SHARED_POOL_RESERVED can be used to determine failed requestsizes and shared pool reserved areautilization.   Document: 430473.1 ORA-4031 CommonAnalysis/Diagnostic Scripts contains a script for querying thisview.

Warning:
If you decide to use theSHARED_POOL_RESERVED_SIZE parameter and SGA_TARGET>0, you mayexperience ORA-4031 situations on large memory requests because theMMAN process may be increasing the size of the Shared Pool overtime, while the Reserved Area remainsconstant.   Thus the ReservedArea may start out at 10% of the Shared Pool size, but over time itmay end up 2% or 3% (much smaller than expected in relation to thenew Shared Pool size).

The Reserved Area handles bigger memoryallocations in an attemptto decrease the chancesof  fragmentation in the SharedPool over time.

SQL> alter system set"_shared_pool_reserved_pct"=10 scope=spfile

or add this in the pfile

"_shared_pool_reserved_pct"=10

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

   1.  From theunreserved part of the shared pool. 

   2.  From thereserved pool. If there is not enough space in the unreserved partof the Shared Pool, then Oracle checks whether the reserved poolhas enough space. 

   3.  If thereis not enough space in the unreserved and reserved parts of theShared Pool, then Oracle attempts to free enough memory for theallocation.  It then retries the unreserved andreserved parts of the Shared Pool.  Thesemini-flushes will clean out RECREATABLE/FREEABLE memory chunks fromthe LRU list that have not been reused for a while.

What are the pools in the SGA usedfor?

The Shared Pool portion ofthe SGA contains the library cache, the dictionary cache, buffersfor parallel execution messages, and controlstructures.  The library cache includes the sharedSQL areas, private SQL areas (in the case of a shared serverconfiguration), PL/SQL procedures and packages, and controlstructures such as locks and library cachehandles.   We allocate memoryfrom the shared pool when a new SQL statement is parsed, to storein the shared SQL area. The size of this memory depends on thecomplexity of the statement.  Ideally, the Shared Pool should be usedfor caching shared SQL and to avoid theperformance overhead caused by shrinking the shared SQL cache.

Many features ofOracle like Recovery Manager (RMAN), parallelprocessing/IO slave processing, and Shared Server are designed toutilize large shared memory chunks .  Thesefeatures will put unnecessary stress on the Shared Pool andtherefore we recommend you define a Large Poolusing LARGE_POOL_SIZE or by using SGA_TARGET to help reduce memorystress in the Shared Pool in these scenarios.

The Java Pool memory isused for memory allocationsassociated with all session-specific Java code anddata within the JVM.  Java pool memory is used indifferent ways, depending on what mode the Oracle server is runningin.

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

The Shared Pool employsa LRU algorithm similar to what is found in theBuffer Cache.  Therefore, tuning the Shared Poolis more complex than other pools.  Most of thetime, if an ORA-04031 errors occur in one of theother memory pools, this indicates that the pool is too small andyou must increase the size of the problem pool to stop these errorsin the future. 

The defaultsettings for these other pools are usually sufficient, but tomanually adjust these pools, you can alter theparameters  LARGE_POOL_SIZE, STREAMS_POOL_SIZE,and JAVA_POOL_SIZE.  Using SGA_TARGET these poolsare automatically adjusted as needed by the MMAN process.

What is an ORA-04031 Error?

The memory pool in the SGAare comprised of memory chunks in various sizes. When the databasestarts is started, you have a large chunk of memory allocated inthe various pools and tracked in free list hash buckets. Over time,as memory is allocated and deallocated, the memory chunks are movedaround into different free list buckets inside the pool accordingto their size. An ORA-04031 error occurs in any of the memory poolsin the SGA when Oracle cannot find a memory chunk large enough tosatisfy an internal allocation request on behalf of a user'soperation.

The Shared Pool is managed differently than theother memory pools.  The Shared Pool storesinformation related to the dictionary and librarycache.  However, these memory areas are managedusing free lists and a Least Recently Used (LRU)algorithm.  The ORA-04031 issignaled on the Shared Pool after searching all the free lists,aging all objects possible from the LRU list, and scanning the freelist multiple times.  This means the ORA-04031 isvery difficult to predict.  There can be manycontributing factors to the ORA-04031 and the trace informationprovide at the time of the error is associated with the "victimsession" in the memory condition and not the cause. The allocationcode is complicated, but a simplified version of the allocationalgorithm is sketched below:

scan regular free list formatch, 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 freelist 
   do LRU operations andrepeat search
    if (chunkfound)
      check chunk size and perhaps truncate
      all done
    if (chunk isnot found) 
   do LRU operations andrepeat

NOTE:  There are internalchecks to limit the number of times these searchesrepeat prior to reportingORA-04031 error.

The sum of the free space, which one may obtain through v$sgastator x$ksmsp, is not important. What is important is the size of thelargest chunk that can be freed or merged after some LRUoperations.  From a heapdump trace we can see freelist buckets and information about the chunks of memory in eachbucket.

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 maximumsize is 40 bytes and the average is 40 bytes. Bucket 2 includesmemory chunks with sizes between 40 and 48 bytes. The average size in this case is 40 bytes and the maximum size is40 bytes.  Finding out whatcaused fragmentation in amemory pool is not always feasible. Sometimes theproblem is an Oracle functionality issue, but in a large percentageof the cases, inefficient application coding canbe the root issue.

The 4031 error can occur in the Large Pool, Java Pool, Streams Pool(new to 10g), or the Shared Pool.  The errormessage will indicate which pool had the problem. If the error indicates a problem in a pool other than the SharedPool, this usually indicates the problem pool is configured toosmall for the application environment.  Increasethe size of the problem pool by 15% and monitor for continuedproblems.  If using the 10g, Automatic SharedMemory Management (ASMM) functionality, the MMAN process willattempt to shrink and grow different components in the SGA asmemory is needed over time. You may need to increase the settingfor SGA_TARGET to allow MMAN more memory to manage behind thescenes if you experience ORA-04031 errors in the Large Pool,Streams Pool, or Java Pool.

The Shared Pool is little more complicated to tune. Forexample

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

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

NOTE:  TheShared Pool is used in an ASM environment aswell.  There have been reports of ORA-04031 on10.1. x ASM instances because the default size can be too small toaccommodate the diskgroup management activities. In these cases,set the SHARED_POOL_SIZE parameter to 50M and increase the settingin increments of 10M if the problems persist.

Note 146599.1 Diagnosing and Resolving Error ORA-04031

Is my Reserved Area sizedproperly?

An ORA-04031 error referencing a large failed requests indicatesthe Reserved Area is toofragmented.   Youcan investigate memory usage in the reserved area using the scriptfrom Note 430473.1

ReservedAnalysis.sql

Request Misses = 0 can mean the Reserved Area is toobig.   Request Misses alwaysincreasing but Request Failures not increasing can mean theReserved Area is too small.  In this case flushesin the Shared Pool satisfied the memoryneeds.   Request Misses andRequest Failures always increasing can mean the Reserved Area istoo small and flushes in the Shared Pool are not helping (likelygot an ORA-04031).

You can also investigate the efficiency of thesize of your Reserved Area.  The goal is tohave the "Hit %" stay as closeto 100 aspossible.  NOTE:  Failures in the Reserved Area do not alwaysequate to ORA-04031 errors.  We performmini-flushes to try to find matching memory requests and in manycases we will find the requested memory and avert the errormessage.   If you increase thesize of the Reserved Area, you can increase the chances of takingneeded memory from the SharedPool.   We recommend you increasethe Shared Pool and the Reserved Area sizes by the same amount.

col requests for 999,999,999
col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE"
col last_miss_size for 999,999,999 head "LAST MISS|SIZE "
col pct for 999 head "HIT|% "
col request_failures for 999,999,999,999 head "FAILURES"
select requests,
decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0))PCT, request_failures, last_miss_size, last_failure_size
from v$shared_pool_reserved;

The V$SHARED_POOL_RESERVED view can reportwrong data on Oracle versions priorto 10.2.  Per Bug 3669074, the workaround is to use this query instead of theexisting V$SHARED_POOL_RESERVED view.

select p.inst_id, p.free_space, p.avg_free_size,p.free_count,
  p.max_free_size, p.used_space, p.avg_used_size,p.used_count, p.max_used_size,
  s.requests, s.request_misses, s.last_miss_size,s.max_miss_size,
  s.request_failures, s.last_failure_size,s.aborted_request_threshold,
  s.aborted_requests, s.last_aborted_size
  from (select avg(x$ksmspr.inst_id)inst_id, 
  sum(decode(ksmchcls,'R-free',ksmchsiz,0))free_space,
  avg(decode(ksmchcls,'R-free',ksmchsiz,0))avg_free_size,
  sum(decode(ksmchcls,'R-free',1,0))free_count,
  max(decode(ksmchcls,'R-free',ksmchsiz,0))max_free_size,
  sum(decode(ksmchcls,'R-free',0,ksmchsiz))used_space,
  avg(decode(ksmchcls,'R-free',0,ksmchsiz))avg_used_size,  
  sum(decode(ksmchcls,'R-free',0,1))used_count,
  max(decode(ksmchcls,'R-free',0,ksmchsiz))max_used_size from x$ksmspr
  where ksmchcom not like '%reserved sto%')p,
    (selectsum(kghlurcn) requests, sum(kghlurmi)request_misses, 
    max(kghlurmz)last_miss_size, max(kghlurmx)max_miss_size, 
    sum(kghlunfu)request_failures, max(kghlunfs) last_failure_size,
   max(kghlumxa) aborted_request_threshold, sum(kghlumer)aborted_requests,
   max(kghlumes) last_aborted_size from x$kghlu) s;

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

You can configure the Shared Pool manually using theSHARED_POOL_SIZE parameter or have the pool auto-tuned usingSGA_TARGET (10g and higher).  Part of the memoryallocated for the Shared Pool is overheadmemory (based on settings for some internalparameters).  Prior to 10g, this overhead memorywas "on top" of the SHARED_POOL_SIZE parameter setting, but notreflected by the parameterSHARED_POOL_SIZE.  This appearsto be a calculation error when you run a query on V$SGASTAT priorto 10G.

Example,

SHARED_POOL_SIZE=64M
Overhead=12M

SQL> Select sum(bytes) "Total Mem" from v$sgastat wherepool='shared pool';

  Total Mem
-----------
   79691776

With 10g, this overhead memory is now included inside theSHARED_POOL_SIZE setting.  Somecustomers run into memory issues in the Shared Pool after a move to10g if they do not account for this overheadmemory.   From the example above,if the SHARED_POOL_SIZE is manually set to 64M and the overheadremains unchanged, this means the usable Shared Pool memory is only54525952 bytes.

NOTE: Scripts like in the Metalinkarticle 105813.1 SCRIPT TO SUGGEST MINIMUM SHAREDPOOL SIZE.   are useful in olderreleases of the database, but do not work consistently with Oracle9.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 viewV$SGASTAT. The view is broken down into memory structure tableentries like 'library cache', 'KGLS heap', 'CCursor'. Prior to 10g,there were only a handful of table entries tracked in this view sosmall memory structures were lumped together in the table entry'miscellaneous'.

The X$KSMSP view shows the breakdown of memory in theSGA.  You can run this query tobuild trend information on memory usage in theSGA.   Remember, the 'free' classin this query is not specific to the Shared Pool, but is across theSGA.  NOTE: It is not recommendedto run queries on X$KSMSP when the DB is underload.  Performance of the database willbe impacted, especially today with very largeSGAs.  Bug 14020215 was filed for ORA-600 errorsand unplanned outages running queries directly onX$KSMSP.  There is a view, X$KSMSP_NWEX, in laterversions of 11g that is safer to investigate memoryusage.  However, we STRONGLY recommend younot run these queries unless given specific instructions fromOracle Support to do so.

SQL> SELECT KSMCHCLSCLASS, 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       SIZ       AVG SIZE
-------- ---------- ---------- ------------
R-free          12   8059200     655.86k<                                                   processing
perm             2  30765848   15,022.39k<                                                 system
recr          3577    3248864        .89k<                                                 processing

**SAFER APPROACH ON SOME VERSIONS OF 11g**



select DSIDX_KSMNWEX "DSIDX",CURDUR_KSMNWEX "DURATION",
   count(*) "GRANULES",  substr('sga heap(' ||DSIDX_KSMNWEX || ',
            ' || (CURDUR_KSMNWEX - 1) || ')',1,20) "HEAP NAME"
from X$KSMSP_NWEX
group by DSIDX_KSMNWEX, CURDUR_KSMNWEX
order by DSIDX_KSMNWEX, CURDUR_KSMNWEX;

Watch for trends using these guidelines:

a) if 'free' memory (column SIZ) is low (less than 5mb or so)you may need to increase the shared_pool_size andshared_pool_reserved_size.  You should expect'free' memory to increase and decrease overtime.   Seeing trends where'free' memory decreases consistently is not necessarily a problem,but seeing consistent spikes up and down could be a problem.
b) if 'freeable' or 'perm' memory (columnSIZ) continually grows then it is possible you areseeing a memory bug.
c) if 'freeabl' and 'recr' memory classes (columnSIZ)are always huge, this indicates that you have a lot of cursorinfo stored that is not releasing.
d) if 'free' memory  (column SIZ) is huge but youare still getting 4031 errors, the problem islikely  reloads and invalidationsin the library cache causing fragmentation.

NOTE: Please be aware that there are bugs filed on HP indicatingrunning queries on x$ksmsp can hang the system on 10g. I haven'tseen reports of the problem on 9.2.x or on otherplatforms.  

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

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

•    SharedPool 
•    LargePool 
•    Java Pool
•    Buffer Cache (the defaultone managed by db_cache_size)
•    Streams Pool (new to 10gRelease 2)

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

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

The SGA_TARGET can be changed dynamically up to the setting forSGA_MAX_SIZE. NOTE:   There are limitations onthis for some platforms depending on how LOCK_SGA works with memoryat 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 theshared pool(SHARED_POOL_SIZE=1G).  

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

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

   LogBuffers            209,715,200 
   Keep Buffer Cache268,435,456
   4K BufferCache    536,870,912
+ SharedPool         1,073,741,824 (manually assigning this value means this isthe Shared Pool
                                                           cannot shrink beyond this)
------------------------------------ 
Total                     2,088,763,392

There are benefits to manually setting aminimum size for the auto-tuned components of the SGA, but it doeshave an impact on the amount of memory that MMAN has access to whenneeding to grow and shrink variouscomponents.   We DO recommendsetting default/explicit settings for the auto-tuned components inthe SGA.  To Gauge how ASMM is working, issue thescript from Note 430473.1

SGAComponents.sql (for 10.2.x)
SGAComponents11g.sql (for 11g)

A rule of thumb for setting up ASMM is

SGA_TARGET = 256M * # of CPUs

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

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

Warning:  There have been anumber of issues filed on 10.2.x with ASMM andORA-4031.   Many customers do notset minimum sizes for the various auto-tuned pools instead relyingon sga_target and MMAN to move memory around as isneeded.   Internal tests and fromdiscussions with development, it is better to find a minimumsetting for these pool and manually set that in the spfile or initparameter file.   As a startingpoint, review data in v$sga_dynamic_components andmanually assign values to the pools at 70 or 75% of thecurrent_size.   If thereare indications of failed attempts to shrink theshared pool below that number over time,then decrease the default setting by another 10%and monitor that the new value is a better minimumsetting.   This will help withdecreasing chances of seeing excessive pinging of memory betweenthe buffer cache and the various pools.

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

NOTE:  With 11g, IMMEDIATE memory requests tothe pools in the SGA can be moved around in the SGA even whenMEMORY_TARGET and SGA_TARGET are explicitly set to0.  See Note 1269139.1

@INTERNAL ONLY:
@If it becomes necessary to gather trace dataassociated with memory allocation by ASMM,
@ you can use
@
@ SQL> ALTER SYSTEM SET"_memory_management_tracing"=7;
@
@ This will trace dynamic memory allocations. Itmay well cause the alert log
@ to grow quickly get the diagnostic data for afew hours or a typical day at most.
@ Then reset the event (to 0).
@
@ SQL> ALTER SYSTEM SET"_memory_management_tracing"=0;
@
@ Generally this should only be used wheninstructed to do so by development. 

How many Subpools will I have by default?

The number of subpools iscalculated using a simple algorithm.  First, asubpool must be at least 128MB in 9ireleases and at least256MB in 10greleases.  Second, there can beone subpool for every four CPUs on the system, up to 7 subpools.The number of subpools can be explicitly controlled using theinit.ora parameter _kghdsidx_count. There is no parameter toexplicitly control the size of eachsubpool. 

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

Because 128MB (and even256MB on 10g) subpools can be small in manyapplication environments, the memory per subpool will likely needto be increased.  There is no parameter to changethe minimum size of the subpool; the only recourse is to decreasethe number of subpools for a given shared pool size or increase theshared pool size so the size of the subpoolsincrease.  Please remember that increasing thesize of the shared pool does not necessarily increase the size ofthe subpool, because the number of subpools can increase if thereare many CPUs on the system.

Change as of 10.2.0.3 andhigher:  Per bug 4994956, the 10g andhigher algorithm was increased to 512M persubpool. 

How do I control the number of subpools used?

To see the number ofsubpools used currently issue 

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '%kghdsidx%';

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

SQL> alter system set"_kghdsidx_count"=1scope=spfile;

or add this in the pfile

"_kghdsidx_count"=1

NOTE: Subpools are created at startup when the SGA iscreated.  In both examples above, the database hasto be restarted to change the number of subpoolscreated.   Any change to the_kghdsidx_count will change the number of subpools in the LargePool as well.

Warning: Reducing the number of subpools mayhave a noticable impact on performance, especially
on RAC configurations, highly concurrent systems, or databaseinstance with very large pools.
Altering this parameter will affect the Shared pool, Shared poolreserved and Large pool.
Reducing the number of subpools could result in increased latchcontention in the pool area.

When setting the number of subpools manually with _kghdsidx_count,recommend making the changes
incrementally to monitor performance impact and minimize anydrastic impact.

Conversely, increasing number of subpools without increasingoverall pool size could lead
to space issues since subpools could be undersized.

Are all ORA-04031 errors reported in the alertlog?

No.  Some errors only show up at the clientworkstation.  Prior to 11g, any ORA-4031 errorthat occurs on a regular user process will not be documented in thealert log.  There can also be cases where anORA-600 or ORA-7445 error is reported in the alert log but theinternal error was a side-effect of a 4031 memoryissue.    Theassociated trace for the internal error may contain diagnostic datausually included with a default ORA-4031 trace.

NOTE:  With 11g, bug 9209518 indicates that we dogenerate incident traces for ORA-4031 even when a miss for memoryhappens and we satisfy the memory request doing internal LRUoperations.  The exception to this could be casesaffected by flood control settings when ORA-4031 errors happenfrequently.

You can monitor if users havebeen seeing ORA-04031 using this code

From website http://www.ixora.com.au/

connect / as sysdba
-------------------------------------------------------------------------------
--
-- Script: shared_pool_lru_stats.sql
-- Purpose: to check the shared pool lru stats
-- For: 8.0 and higher
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- NOTE: Per Bug 3352753, this may not work with 9i and higher /added kghlushrpool to Steve's code for 9i/10g. Reports are coming in of issues with this query on 11g.
-------------------------------------------------------------------------------

>column kghluops heading "PINS AND|RELEASES"
column kghlunfu heading "ORA-4031|ERRORS"
column kghlunfs heading "LAST ERROR|SIZE"
column kghlushrpool heading"SUBPOOL"  

select
kghlushrpool,
kghlurcr,
kghlutrn,
kghlufsh,
kghluops,
kghlunfu,
kghlunfs
from
sys.x$kghlu
where
inst_id = userenv('Instance')

We recommend using this code instead for 10g and higher:

col free_space format 999,999,999,999 head "Reserved|FreeSpace"
col max_free_size format 999,999,999,999 head "Reserved|Max"
col avg_free_size format 999,999,999,999 head "Reserved|Avg"
col used_space format 999,999,999,999 head "Reserved|Used"
col requests format 999,999,999,999 head "Total|Requests"
col request_misses format 999,999,999,999 head"Reserved|Area|Misses"
col last_miss_size format 999,999,999,999 head "Size of|LastMiss"
col request_failures format 9,999 head "Shared|Pool|Miss"
col last_failure_size format 999,999,999,999 head"Failed|Size"

select request_failures, last_failure_size, free_space,max_free_size, avg_free_size
from v$shared_pool_reserved
/

select used_space, requests, request_misses, last_miss_size
from v$shared_pool_reserved
/

"Shared Pool Miss" (or "Reserved Area Misses") will be incrementedwhen a request for memory fails.  It does notnecessarily indicate an ORA-4031 error.  LRUoperations may have come up with the memory internally, but seeingaggressive increases in "Shared Pool Misses" or "Reserved AreaMisses" could indicate that ORA-4031 errors are happening (whetherthey are tracked in the alert log or not).

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 youwill see a very large value for "miscellaneous". Until Oracle 10g Release 2, the basic design of the SGA structureinternally remainedunchanged.  There were table entries for various memory"comments" in the data dictionary where we keep statistics on thelargest memory "comments" orstructures.   The smallerstructures are lumped together in the memory "comment" calledmiscellaneous because we tracked only ahandful ofstructures.   If you run a scriptlike SGAStat.sql from Note: 430473.1.  This script will report backthe biggest allocation areas in the view V$SGASTAT for the SharedPool.   You can adjust the scriptto look at any of the Pools in the SGA.

Per bug 3663344, there were occasional inconsistencies in thestatistics reported in V$SGASTAT due to memorystructures growing and shrinking overtime.   Once amemory structure reached an internally controlled size, we movedthe data about the memory structure from thegeneral purpose table entry, "miscellaneous", to a specific memorystructure table entry.   Theproblem was usually seen with negative numbersin some memory structures in v$sgastat or attimes you would see an unusuallylarge value in "miscellaneous".

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

alter system set events '4031 trace nameHEAPDUMP level 536870914';

NOTE:  Setting this event at the instance levelwill generate large files and if the 4031 error occurs frequently,you will get many trace files.  This can impactperformance and hang (and in some cases crash adatabase).  Turn this event off using

alter system set events '4031 trace name HEAPDUMPoff';

and at the next occurrences of the 4031 problem you will get abreakdown of the memory in the SGA and also the breakdown of memoryused in the top five subheaps in the SGA.  In thisscenario, you would expect one or more of the largest subheapslisted 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 thanexpected entries within 'miscellaneous'.

To get an immediate memory dump use these steps

alter system set events 'immediate trace nameheapdump level 536870914';
or

sqlplus /nolog
connect / as sysdba 
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace

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

If the problem is actually associated with permanent memorystructures (tracked under the 'miscellaneous' table entry), thereis not a way to get information on these memory areasunless you set the event 10235 level65536.  This event should only be set underdirection from Oracle Support.

@INTERNALONLY:  
@ To set the 10235, it is necessary to add
@
@ event = '10235 trace name context forever, level65536'
@
@ to the pfile used to startup the database or toadd this to the spfile using
@
@ ALTER SYSTEM SET
@   EVENT='10235 trace name context forever, level 65536'scope=spfile;
@
@ The database must be restarted in both cases asthis event causes additional 
@ comment information to betracked in the Shared Pool forpermanent memory
@ structures. The level 65536 wasadded with 9.2.0.5 and higher and
@ has minimalimpact to database performance. Other levelsettings for this event canGREATLY impact
@ performance and should onlybe used when instructed to do so inanalysis for a bug. See bug 3293155
@ for more on level 65536.

What database parameters are relevant to ORA-04031problems?

• CURSOR_SHARING

Literalreplacement is a feature where Oracle replaces literal values inSQL statements to reduce the application Shared Pool footprint anddecrease "hard" parsing.  Theliteral values are replaced with bind variables and if two or moresessions are executing the same SQL statement, they can both usethe same cursor with the bind variable instead of creating twounsharable cursors.

For example, two users connected as SCOTT issuedthe SQL statements "select ename from emp where empno = 20" and"select ename from emp where empno =100".  Ifcursor_sharing is set to FORCE, Oracle will create one cursor witha bind variable so the statement becomes equivalent to "selectename from emp where empno = :b1".  The two userswill share the same cursor object instead of creating two separateLibrary Cache parent objects and their corresponding childobjects.

The parameter has three modes:

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

Invalidations arecaused by either executing DDL against the objects, gatheringstats, or granting/revoking privileges.  Youshould see associated "library cache pin" waitsalso.

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

The implementationof SIMILAR is not optimal.  Even though cursorswith literal will have the best plans, the match criteria is basedon the values of the literals.  The implementationcreates a mini-hash table based on literal values, and if thevalues are the same, the child cursors areshared.  This behavior can lead to anapplication's Shared Pool footprint equivalent to running withCURSOR_SHARING=EXACT.   Insteadof creating lots of individual cursors with their own children,SIMILAR creates lots of child cursors under the same parent cursorobject.  If you see hard parses at 50/sec orhigher (Snapshot/AWR), CURSOR_SHARING=EXACT can be helpful.

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

• CURSOR_SPACE_FOR_TIME

If this parameteris set, Oracle does not unpin the library cache object at the endof execute.  This means that the active amount ofmemory pinned within the library cache increases as more cursorsare opened and executed, reducing the amount of memory that can beaged out of the Shared Pool.  This parameter mustbe used carefully and with knowledge about the total applicationfootprint within the Shared Pool.  Setting of thisparameter without this knowledge can lead to ORA-04031 errors. Cursors that are on the Session Cached Cursorlist do not have their SQL area heaps pinned.

Some sites use this parameter because it avoidsthe library cache pin and unpin code path.  Thisis not a recommended parameter to set forperformance.  It is very difficult to know whetherit is safe to set this parameter.

NOTE:  This parameter can help with datapumpperformance in 10g.   Ifinvestigating ORA-04031 errors in the "sql area" area in the SharedPool, look forCURSOR_SPACE_FOR_TIME=true.  This can cause the allocations for "sql area" to grow to take upmost of the Shared Pool in 10.1.x.

• DB_CACHE_SIZE

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

• DB_nK_CACHE_SIZE

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

• DB_KEEP_CACHE_SIZE

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

• DB_RECYCLE_CACHE_SIZE

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

• JAVA_POOL_SIZE

If usingSGA_TARGET, this will default to 0. Any hard-coded setting for thisparameter when using SGA_TARGET will act as a minimum size for MMANwhen it attempts to shrink the JavaPool. 

If you are notusing SGA_TARGET, the default size for this memory area is usuallysufficient unless your environment utilizes a lotof JVM objects.

@NOTE: In some cases, an ORA-04031 error will occur like
@
@ ORA-04031: unable to allocate ... bytes ofshared memory ("shared pool",
@ "unknown object", "joxlod: inith",  "JOX: ioc_allocate_pal")
@
@ The third argument is JOXLOD, this indicates aproblem with the Java Pool, even though the
@ pool listed in the error is'shared pool'. This reference to JOXLOD indicates the Java Poolis
@ too small.  However additional analysis is needed to be surethat Java Pool is the problem.

If you need to seemore detail on how the memory is allocated in the Java Pool, youcan issue 

> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdumplevel 128';

 

• LARGE_POOL_SIZE

The Large Pool isintended to offload larger memoryallocations from the Shared Pool related to SharedServer (UGA), Parallel Processing (Buffer allocations), and RMANbackup operations, and sequential file IO (e.g. IO slaveactivity).  If you are not using these specificfunctionality areas, you can set the Large Pool to0.  The Large Pool does not use a Least RecentlyUsed (LRU) algorithm, so until a session releases larger memoryallocations, they remain in the Large Pool.

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

> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdumplevel 32';

If usingSGA_TARGET, the parameter will show as 0, but you can hard-code aminimum size and MMAN will notattempt to shrink the Large Poolbelow that setting. 

• LOG_BUFFER

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

• OPEN_CURSORS

Thisparameter sets the upper bound for the number ofcursor that a session can have open.  Normally,cursors are opened through an OCI call or through a PL/SQL call toopen a cursor.

In versions of Oracle prior to9.2.0.5, OPEN_CURSORS was used as a cache forPL/SQL cursors.  When PL/SQL closes a cursorPL/SQL has opened, it sees if it can cache the cursor using one ofthe cursors allocated through OPEN_CURSORS.  Thecursor is not really closed, but may be closed and replaced byanother cached cursor or a cursor the application explicitlyopens.  If an application opened very few cursorsusing OCI, it could still have many open cursors for PL/SQL'scursor cache if the application uses PL/SQL and the PL/SQL openedand closed many different cursors.

If a user explicitly opened a cursor and the session could not opena new cursor (the number of open cursors was equal toOPEN_CURSORS), one of the cached cursor would be closed so theexplicit 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 processeswill impact the size of the shared pool starting in 9.2.x. Memorystructures are located in the Shared Pool with 9.2.x to storedynamic parameter setting information persession/process.  NOTE:  Thememory structure can take as much as 20 bytesper parameter for32-bit databases and 32 bytesper parameter for 64-bit databases. At 10.2.x,there are over 1300 dynamic parameters, so this can add up quicklywith a lot of users on the database.  You canreview the high water mark for Sessions and Processes in theV$RESOURCE_LIMIT view.  If the hard-coded valuesfor these parameters are much higher than the high water markinformation, consider decreasing the parameter settings to free upsome memory in the Shared Pool for other uses.

• SESSION_CACHED_CURSORS 

When a cursor isclosed, Oracle divorces all association between the session and thelibrary cache state. If no other session has the same cursoropened, the library cache object and its heaps are unpinned andavailable for an LRU operation. The parameterSESSION_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_cursorsstatistic; it is a separate list. Instead of being really closed,Oracle places the cursor on a session-private LRU list and keepsthe cursor available for a subsequent parse. If the user executes anew statement, it will first search the Session Cached Cursor listand, if found, uses it.

This parameter was originally conceived for Oracle FORMSapplications. To parallelize FORMS development, customers oftendeveloped separate forms and navigated from form to form. The FORMSbehavior is to close all cursors from the previous form beforenavigating to the next form. This parameter allowed Oracle to"cache" closed cursors and reused them if the application opens acursor for the same statement.

Setting this parameter to a high value increases the amount ofLibrary Cache memory (monitored by reviewing the viewV$SGASTAT).

In 9.2.0.5 onwards, this parameter has also been overloaded tocontrol the number of cursors PL/SQL caches using cursors allocatedthrough the setting for OPEN_CURSORS.

Reference:
Note 270097.1 ORA-4031 and Very Large Library Cache in Oracle9.2 with Session_cached_cursors set. Library Cache Pin/Lock Pile Uphangs the application
Note 274496.1 ORA-7445 and ORA-4031 in 9.2.0.5 and 10g ifSESSION_CACHED_CURSORS is used

• SGA_TARGET

If this parameteris set, the MMAN process will attempt to grow and shrink auto-tunedmemory components.  Interestingly, on10.2, if you specify in thespfile an explicit setting forSGA_TARGET higher than the explicit value for SGA_MAX_SIZE the nextstartup will ignore the prior setting for SGA_MAX_SIZE and set itequal to the new SGA_TARGET setting.  This is notthe behavior at 11g.

• SHARED_POOL_SIZE

If usingSGA_TARGET, this will default to 0, but a hard-coded setting forthis parameter will act as a minimum size for MMAN when it attemptsto shrink the Shared Pool.   With9i and 10g, more SGA fixed memory structures have been moved to theShared Pool.   This means thatwhen upgrading from Oracle7 and Oracle8/Oracle8i, you must performadditional tuning analysis onthe 9i or 10g memory needs forthe Shared Pool.   With 9i andhigher, Oracle also implemented a new subpoolfunctionality.   This can requireadditional tuning analysis as the application(s)will utilize memory differently.  In some casestoo many subpools inside the Shared Pool, can cause one of thesubpools to be over-utilized and lead to ORA-4031 problems.

Reference:

Note 270935.1 Shared pool sizing in 10g

If you need to seemore detail on how the memory is allocated in the Shared Pool, youcan issue

> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdumplevel 2';

NOTE:  Running the heapdump trace is notrecommended during peak activity on thedatabase.   The tracing willaffect performance.

 

• SHARED_POOL_RESERVED_SIZE

This parameterdefaults to 5% of the SHARED_POOL_SIZE setting. When usingSGA_TARGET, this will be adjusted as the Shared Pool componentgrows and shrinksautomatically.   If you areseeing consistent ORA-4031 errors that indicate memory requestfailures larger than 4000 bytes, the 5% default value may not besufficient in your applicationenvironment.   You can change thehidden parameter, _shared_pool_reserved_pct, to10.  This will cause the Reserved Area to utilize10% of the Shared Pool.  For example,

SQL> alter system set"_shared_pool_reserved_pct"=10 scope=spfile;

or add in the init file

"_shared_pool_reserved_pct"=10

• STATISTICS_LEVEL

There areadditional memory structures in 10g related to Statistics tracking.This parameter controls all major statistics collections oradvisories in the database and sets the statistics collection levelfor the database. The parameter can be set to BASIC, TYPICAL, orALL.

The defaultsetting of TYPICAL will put strain on the Shared Pool unless youtune the Shared Pool to accommodate the ongoinganalysis activity in the database.  In someperformance tuning cases, it is necessary to set STATISTICS_LEVELto ALL. This will use more memory in the Shared Pool than the othersettings, so using ALL can cause ORA-4031 problems if the SharedPool is not tuned to handle the additional memory needs.

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

• STREAMS_POOL_SIZE

This is new memorypool in 10g.  It is intended to alleviate stresson memory structures in the Shared Pool related to Streamsoperations.  Review the size set for the parameterin the RDA.

If usingSGA_TARGET on 10g Release 2, the parameter will be auto-tuned andwill show up as 0.  You can hard-code a minimumsize with 10g and Release 2 and MMAN will notattempt to shrink the Streams Pool below thatsetting.

If you need to seemore detail on how the memory is allocated in the Java Pool, youcan issue

> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdumplevel 64';

 

Also review any settings that would indicate the use of SharedServer (MTS_SERVERS, MTS_DISPATCHERS, etc).  Ifthese parameters indicate that Shared Server isconfigured, you should not see memory structuresin the Shared Pool related to Shared Server 

NOTE:  Some of the parameters listed above areonly applicable if usingSGA_TARGET.  Be sure toinvestigate exactly how much memory MMAN can work with to grow andshrink the auto-tuned memory components.

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

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

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

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

Under 'RDBMS', 'Database Parameters', review the appropriateparameters as described above. With the latest versions of the RDAreport, 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 markinformation for 'processes', 'sessions'. If the settings forPROCESSES and SESSIONS is much higher than high water mark numbers,decreasing these settings could help decrease some permanent memorystructure allocations. In RAC environments, the parameter settingsfor 'ges*' parameters can also be important. There are someRAC/ORA-04031 bugs related to the 'ges%' parameters.

What is relevant in the default 4031 tracefile?

With 9.2.0.5 and higher, a trace file is generated at the timeof an ORA-04031 error (controlled by_4031_dump_bitvec).   On9.2.x, the default: is 6639615 = 0x654fff , whichmeans: 
    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 dumpsexcept: subheaps & top sga heap w/contents  

and there are additional initialization parameters added aswell:

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

NOTE: Setting either of these to 0 means all errors generate atrace file.  If the ORA-04031 errors happen oftenin a short period of time, you can hang the database with too manytrace files generated.
 
_4031_max_dumps (default 100) - sets the limit onthe number of dumps perprocess.  0 turnsoff 4031 dumps.
_4031_sga_max_dumps (default 10) - sets the limiton the number of SGA dumps per instance.  0 turnsoff SGA dumps.

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

*** SESSION ID:(242.24755) 2006-08-2908:55:15.765
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=08/29/2006 08:55:14
Allocation request for: optdef :apanlg   <<<< request for memeory
                                             structure(related to 
                                             inlistand the CBO)
Heap: 44b5c89b8, size:96   <<<******************************************************
******************************************************
HEAP DUMP heap name="sga heap" desc=380000030
extent sz=0xfe0 alt=200 het=32767 rec=9 flg=-126 opc=0
parent=0 owner=0 nex=0 xsz=0x1
******************************************************
HEAP DUMP heap name="sql area" desc=44b5c89b8 <                                                     in "sql area"
extent sz=0x1040 alt=32767 het=32 rec=0 flg=2 opc=2
parent=380000030 owner=44b5c8898 nex=0 xsz=0x1
Subheap has 840329704 bytes of memory allocated

Scroll down the trace file until just after the Stack Tracelisting,

----- End of Call Stack Trace -----
===============================
Memory Utilization of Subpool1    <===============================
Allocation Name Size
_________________________ __________
"free memory "124944864    <<<< this was "free memory" at timeof error
"miscellaneous "57893552   <<<< prior to 10gR2, this is a general purpose
                                 holder for a lot of smaller memory areas
"sim memory hea " 2319640
"PL/SQL PPCODE " 0
"KQR L SO " 56320
"type object de " 0
"trigger source " 0
"errors " 0
"PX subheap " 147016
"trigger defini " 0
"trigger inform " 0
"PLS non-lib hp " 2088
"KGLS heap " 215352
"FileOpenBlock " 7517528
"KQR M SO " 39976
"PL/SQL SOURCE " 0
"PL/SQL DIANA " 99968
"joxlod: in phe " 0
"db_block_hash_buckets " 9978352
"joxs heap init " 4240
"MTTR advisory " 697248
"fixed allocation callback" 552
"dictionary cache " 3229952
"KQR L PO " 245784
"KQR M PO " 319096
"parameters " 0
"partitioning d " 0
"library cache " 18615496
"table definiti " 0
"sql area " 901605416  <                              of memory already allocated here
"pl/sql source " 0
"transaction co " 0
"KGK heap " 7000
"KQR S SO " 14360
"event statistics per sess" 12499760
"joxlod: in ehe " 357736
"temporary tabl " 0
"PL/SQL MPCODE " 39392

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

LIBRARY CACHESTATISTICS:  (emphasis added on keyareas)
namespace gets hit ratio pins hit ratio reloadsinvalids
--------------  ------------------ --------- --------- ---------- ----------
CRSR 4265150 0.977 4961141500.999 155148 46115
TABL/PRCD/TYPE 40860748 0.99980409664 0.994 190813 0
BODY/TYBD 52028 0.996 55986 0.9203084 0
TRGR 468975 0.998 468975 0.99876 0
INDX 54546 0.919 65318 0.867 00
CLST 122885 0.992 166510 0.989 00
OBJE  0 0.000 0 0.000 00

In the Library Cache Statistics information, look for 'hitratio' percentages that would indicate fragmentationproblems.  The goal is to keep the 'hit ratio' asclose to 100% as possible.   Alsoreview the reloads and invalids information.  Lotsof invalids and reloads means more flushing of memory in thelibrary cache and can be a sign of application inefficiencies andfragmentation. 

On 10gR2, the V$SGASTAT view contains moredetail than prior releases.  Heapdump traces andqueries on X$KSMSP can impactperformance.   An initialapproach on 10gR2 is to run the SGAStat.sql query (from Note: 430473.1) every 30 minutes or so for several hours duringpeak activity.  Comparing the results of thememory entries will help identify where allocations aregrowing.

If explicitly setting the Heapdump event

alter system set events '4031 trace nameHEAPDUMP level 536870914';

you will see a more detailed view of memory

SUBHEAP 1: desc=3800092e0
******************************************************
HEAP DUMP heap name="KSFD SGA I/O b" desc=3800092e0
extent sz=0x4258 alt=32767 het=32767 rec=9 flg=3 opc=0
. . .
******************************************************
SUBHEAP 2: desc=3a1b57a10
******************************************************
HEAP DUMP heap name="PX subheap" desc=3a1b57a10
extent sz=0xff50 alt=32767 het=32767 rec=9 flg=2 opc=0
parent=380000030 owner=0 nex=0 xsz=0xff38
EXTENT 0 addr=3a4342860
Chunk 3a4342870 sz= 64904 free " "
Chunk 3a43525f8 sz= 104 freeable "PX msg batch st"
Chunk 3a4352660 sz= 104 freeable "PX msg batch st"
Chunk 3a43526c8 sz= 104 freeable "PX msg batch st"
Chunk 3a4352730 sz= 104 freeable "PX msg batch st"
Total heap size =65320      
Bucket 0 size=40
Bucket 1 size=104
Bucket 2 size=528
Bucket 3 size=600
Bucket 4 size=1112
Bucket 5 size=1120
Chunk 3a4342870 sz= 64904 free " "
Total free space =64904      
PERMANENT CHUNKS:
Permanent space = 0
******************************************************

In some cases, it is necessary to dump additional diagnosticinformation on a subheap.   Forexample,

    <>>

Chunk 3a0ba0480 sz= 4184 freeable "CURSOR STATS "ds=3a1a6c0d8
. . .
ds 3a1a6c0d8 sz= 246856 ct= 59
39e642190 sz= 4184
39c08c728 sz= 4184

SQL> ORADEBUG SETMYPID
SQL> ORADEBUG DUMP HEAPDUMP_ADDR 1 15596962008 (decimal value for 3a1a6c0d8)

   <>>

HEAP DUMP heap name="CURSOR STATS" desc=3a1a6c0d8
extent sz=0x1040 alt=32767 het=32767 rec=9 flg=3 opc=0
parent=380000030 owner=0 nex=0 xsz=0x1040
EXTENT 0 addr=39e6421a8
Chunk 39e6421b8 sz= 4144 free " "
EXTENT 1 addr=39c08c740
Chunk 39c08c750 sz= 80 freeable "kks pstat "
. . .
Chunk 39c08c860 sz= 336 freeable "kks cstat "

@ Search inWebIV using the name of the largest allocation tosee if the problem points to a known bug.
@ Search in TAO for clues in theOracle code about functionality associated with the largestallocations 
@ 
@ It can also be necessary to run a library cachedump if the problem seems to point to
@ fragmentation issues
@
@ ALTER SYSTEM SET EVENTS IMMEDIATE TRACE NAMELIBRARY_CACHE LEVEL 11;
@
@ NOTE: Use level 10 in pre-9.2.xreleases

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

When investigating the 4031 problem using the Statspack/AWRreports, 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

Database    DBId     Instance   InstNum  StartupTime   Release   RAC
~~~~~~~~ ----------- ------------ -------- -------------------------- ---
         1460166532 cqlwh              1 28-Nov-05 20:18 10.2.0.1.0 NO

Host Name: cqlstldb06.ceque Num CPUs: 4 Phys Memory (MB):3,992

and

Load Profile
~~~~~~~~~~~~


             PerSecond     Per Transaction
             --------------- ---------------
    Parses:          132.46          31.70
Hardparses:            0.73            0.17

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
             Library Hit %:99.90    SoftParse %: 99.45
        Execute to Parse %:42.77    Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 97.19 % Non-Parse CPU: 93.91

Shared PoolStatistics    Begin   End
                          ------ ------ 
          MemoryUsage %: 95.54  95.50 
  % SQL withexecutions>1:  71.11  67.96