Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID 396940.1]
In this Document
Purpose
Common Bugs
Questions 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?
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-04031 problems?
What should we look at in an RDA to help diagnose a 4031 error?
What is relevant in the default 4031 trace file?
What is relevant in the Statspack/AWR report for a 4031 error?
How can we determine if there are application issues causing the problem?
Not Using Bind Variables
Multiple child cursors
High parse ratios
Is it possible to find objects in the Library Cache that could be causing the problem?
References
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 11.1.0.7 - Release: 8.1.5 to 11.1
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.1.5.0 to 11.1.0.8
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) [Trouble seeing this video?]
Common Bugs
BUG REPORTED FIXED NOTES DETAILS
Bug 2934402 9.2.x 10.x No Backports Feasible The variable component of the 'show sga' command will show more memory than expected when SGA_MAX_SIZE is much larger than the SGA size required and you set parameters like PROCESSES, DB_FILES, OPEN_CURSORS to a high value. These cause additional memory to be allocated in the SGA as a reserve for future memory needs. Reference Note 270935.1
Bug 3490108 (Bug 4171368) 9.2.x 10.2.x No Backports to 9i at this point Seeing ORA-4031 on 'BAMIMA: Bam Buffer', however lots of free memory on hand in the shared pool. The problem appears to stem from very small chunks used in the Shared Pool (Reserved Area specifically)
Bug 4992466 (Bug 3046725) 9.2.x 10.2.x No Backports Feasible Depending on application work loads, some components in a RAC environment can temporarily consume a lot of SGA memory for "ges enqueues" and this can fragment the shared pool sufficiently to cause ORA-04031 type errors. Workaround: Pre-reserve plenty of space for GES resources in the shared pools to prevent the fragmentation effect of a sudden jump in requirements. This can be achieved by setting the hidden parameters _lm_locks / _lm_ress
Bug 3090397 9.2.0.3 9.2.0.5 Backports Available In situations when using ASYNC or SYNC=PARALLEL, the LGWR trace file can indicate ORA-04031 errors and indicate large memory request failures. Workaround: Use ARCH transport mode instead.
Bug 3854318 9.2.0.5 10.2.x Backports Not Feasible The problem shows up as heavy 'perm' allocations in the 'sql area' in a heapdump trace. The root issue is allocations related to ansi-join processing and query transformation routines that allocate memory and do not get cleaned up properly.
Bug 4375655 9.2.0.5 9.2.0.8 and 10g
A large in-list can consume excessive memory if there is a zero length string in the inlist. Do not use '' in an inlist.
Bug 5246688 / (Bug 4184298) 10.1.0.3 10.1.0.5 / 10.2.0.1 Backports to 10.1.x only If the system is configured with multiple shared pool subpools, and if many sessions are active, it is possible for an allocation imbalance to occur between the subpools. It extreme cases, this could lead to ORA-04031 due to a large number of "session parameters" allocations using up space from one subpool. Workaround: increase the size of the Shared Pool or revert back to one large Shared Pool instead of using subpools.
Bug 4367986
** NEW 10.1.0.3 10.2.0.4 / 11.x Backports to 10.2.x on some platforms Parallel execution cursors are not shared when bind peeking is used. Excessive child cursors created can lead to fragmentation and ORA-4031. Watch for nonshared code showing BIND_PEEKED_PQ_MISMATCH as the problem.
Bug 3513427 10.1.0.3 / 10.1.0.2 10.1.0.3 / 10.2.0.1 Backports to 10.1.0.2 / 9.2.0.5 When an ORA-4031 occurs SGA heapdumps can occur every minute or two. This can lead to contention for the shared pool latch. The dumps are only meant to occur every "_4031_sga_dump_interval" seconds (default 3600) but the code uses "_4031_max_dumps" as the interval instead (default 100). Workaround: Avoid the ORA-4031 situation or set "_4031_max_dumps"=3600 so that dumps only occur every 3600 seconds.
Bug 4733833/ Bug 5473945 10.1.0.3 / 10.2.x 10.2.0.3 / 11.x Backports Not Available Full outer join queries can be transformed into union all code with left outer joins and anti joins. The transformation causes multiple entries in the Library Cache and high parse counts and can cause additional fragmentation leading to ORA-04031 errors.
Bug 4467058 10.2.x 11 Backports Feasible on 10g With ORA-04031 dumping events set, DBWR and LGWR can generate spurious dumps. Reference Note 367392.1 Workaround: Turning off 4031 dump events.
Bug 4237613 10.2.x 10.2.0.3, 11g Backports Feasible on 10.2.x Increases to 'ASM extent pointer array' in V$SGASTAT over time, while no decreases are accounted for. This appears to be a leak, but is simply an accounting problem in v$SGASTAT. Some customers are reporting a side-effect of error indicators from GRID due to this overstatement of memory in the Shared Pool.
Bug 5045507 / (Bug 4507532) 10.2.0.1 10.2.0.2 Backports not available Seeing aggressive growth of entries in V$SGASTAT for "KGH: NOACCESS". See Note 461160.1 for more on this problem.
Bug 5918642 10.2.0.3 10.2.0.4 Backports available on some platforms With very large Buffer Cache, cache statistics can cause excessive latch contention. The latch issues can lead to ORA-4031 errors in some cases. Workaround is to set DB_CACHE_ADVICE=OFF. However, this will impact ASMM management in the SGA.
Bug 5618049 10.2.0.2 10.2.0.4 and 11 Some Backports on 10.2.x 4031 during partitioning ddl's and there are a number of allocations with the comment "mvobj part des". This can show up with automated jobs that perform operations on partitions as well.
Bug 5552515 10.2.0.2 11.x Backports can be requested Commiting DML on table with MView log causes an increase in heap stats for the memory structure "ktcmvcb". No workaround.
Bug 6868080 10.2.0.2 10.2.0.5 / 11.1.0.7 / 11.2 Some one-off patches available NUMA option can overflow memory allocations into one subpool and cause an imbalanced availability of free memory and ORA-4031. Look for "NUMA pool X" allocations.
Bug 5573238 10.2.0.2 10.2.0.4 / 11.1.0.6 / 11.2 Some one-off patches available Fragmentation problems from "obj stat memo" allocations
Bug 5548510 10.2.0.2 10.2.0.4 and 11.x Backports Available CBO leak. The allocation in the SGA for fix control will grow with each new session. You will see this running: select * from v$sgastat where name like 'qksbg%'.
Bug 5705795 10.2.0.3 10.2.0.4 / 11.x Introduced in 10.2.0.3 and backport feasible This problem is introduced in 10.2.0.3 on Windows 32bit and Linux 32bit only. 10.2.0.3 on other platforms include the fix for this bug# and so are not affected. SQL using bind variables with different bind sizes can lead to a large number of child cursors being created leading to excess shared pool usage and latch contention.
Bug 6271680 / 7258873 10.2.0.4 11.2 Some one-off patches available Free memory reported in the default 4031 trace can reflect large negative values in cases where the memory exceeds 2G.
Bug 6981690 10.2.0.4 11.2 Some one-off patches available Excessive growth in memory for cursors not shared because of PQ_SLAVE_MISMATCH. This happens with Parallel activity on databases mounted and not open in RAC and/or Standby environments.
Bug 7340448 10.2.0.4 no fix yet
Excessive Shared Pool memory growth with command CTAS and REGEXP_LIKE expression
Bug 6868080 11.1.0.6 11.1.0.7 / 11.2 Some one-off patches available NUMA option can lead to ORA-4031 and ORA-600 [17137]. Workaround is to disable NUMA - "_enable_NUMA_optimization"=false
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.
Representation of Memory Pools
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’s 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.
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.
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.
By default, Oracle configures a small Reserved Pool (or Reserved Area) inside the Shared Pool. This memory can be used 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.
5% of the Shared Pool is set aside as the Reserved Pool to handle allocations of memory higher 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 = '_shared_pool_reserved_pct';
to see the current setting in your environment. 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.
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.
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 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 from the other memory pools. The Shared Pool stores information related to the dictionary and library cache. However, these memory areas are managed using free lists and a Least Recently Used (LRU) algorithm. The ORA-04031 is signaled on the Shared Pool after searching all the free lists, aging all objects possible from the LRU list, and scanning the free list multiple times. This means the ORA-04031 is very difficult to predict. There can be many contributing factors to the ORA-04031 and the trace information provide at the time of the error is associated with the "victim session" in the memory condition and not the cause. The allocation code is complicated, but a simplified version of the allocation algorithm is sketched below:
scan regular free list for match, if not found
large request, scan reserved list
if (chunk found)
check chunk size and perhaps truncate
if (chunk is not found)
scan regular free list
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
small request, scan regular free list
do LRU operations and repeat search
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
NOTE: There are internal checks to limit the number of times these searches repeat prior to reporting ORA-04031 error.
The sum of the free space, which one may obtain through v$sgastat or x$ksmsp, is not important. What is important is the size of the largest chunk that can be freed or merged after some LRU operations. From a heapdump trace we can see free list buckets and information about the chunks of memory in each bucket.
Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
This shows that bucket 1 has 443 chunks of memory where the maximum size is 40 bytes and the average is 40 bytes. Bucket 2 includes memory chunks with sizes between 40 and 48 bytes. The average size in this case is 40 bytes and the maximum size is 40 bytes. Finding out what caused fragmentation in a memory pool is not always feasible. Sometimes the problem is an Oracle functionality issue, but in a large percentage of the cases, inefficient application coding can be the root issue.
The 4031 error can occur in the Large Pool, Java Pool, Streams Pool (new to 10g), or the Shared Pool. The error message will indicate which pool had the problem. If the error indicates a problem in a pool other than the Shared Pool, this usually indicates the problem pool is configured too small for the application environment. Increase the size of the problem pool in increments of 50MB or 100MB and monitor for continued problems. If using the 10g, Automatic Shared Memory Management (ASMM) functionality, the MMAN process will attempt to shrink and grow different components in the SGA as memory is needed over time. You may need to increase the setting for SGA_TARGET to allow MMAN more memory to manage behind the scenes if you experience ORA-04031 errors in the Large Pool, Streams Pool, or Java Pool.
The Shared Pool is little more complicated to tune. For example
ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","SELECT /*+ FIRST_ROWS */ * F...","sql area","kafco : qkacol"):4031:375:2008:ocicon.c
In this case, the problem occurred in the Shared Pool. The error message also includes information on the size of the memory request that failed. In our example, the failure was on a request for 4192 bytes in the SQL Area.
NOTE: The Shared Pool is used in an ASM environment as well. There have been reports of ORA-04031 on 10.1. x ASM instances because the default size can be too small to accommodate the diskgroup management activities. In these cases, set the SHARED_POOL_SIZE parameter to 50M and increase the setting in increments of 10M if the problems persist.
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
ReservedAnalysis.sql
Request Misses = 0 can mean the Reserved Area is too big. Request Misses always increasing but Request Failures not increasing can mean the Reserved Area is too small. In this case flushes in the Shared Pool satisfied the memory needs. Request Misses and Request Failures always increasing can mean the Reserved Area is too small and flushes in the Shared Pool are not helping (likely got an ORA-04031).
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.
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 report wrong data on Oracle versions prior to 10.2. Per Bug 3669074, the workaround is to use this query instead of the existing 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,
(select sum(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 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.
Example,
SHARED_POOL_SIZE=64M
Overhead=12M
SQL> Select sum(bytes) "Total Mem" from v$sgastat where pool='shared pool';
Total Mem
-----------
79691776
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'.
The X$KSMSP view shows the breakdown of memory in the SGA. You can run this query to build trend information on memory usage in the SGA. Remember, the 'free' class in this query is not specific to the Shared Pool, but is across the SGA. NOTE: It is not recommended to run queries on X$KSMSP when the DB is under load. Performance of the database will be impacted.
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 SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 12 8059200 655.86k <<= Reserved List
R-freea 24 960 .04k <<= Reserved List
free 331 151736448 447.67k <<= Free Memory
freeabl 4768 7514504 1.54k <<= Memory for user/system
processing
perm 2 30765848 15,022.39k <<= Memory allocated to the
system
recr 3577 3248864 .89k <<= Memory for user/system
processing
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 and shared_pool_reserved_size. You should expect 'free' memory to increase and decrease over time. 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 (column SIZ) continually grows then it is possible you are seeing a memory bug.
c) if 'freeabl' and 'recr' memory classes (column SIZ)are always huge, this indicates that you have a lot of cursor info stored that is not releasing.
d) if 'free' memory (column SIZ) is huge but you are still getting 4031 errors, the problem is likely reloads and invalidations in the library cache causing fragmentation.
NOTE: Please be aware that there are bugs filed on HP indicating running queries on x$ksmsp can hang the system on 10g. I haven't seen reports of the problem on 9.2.x or on other platforms.
What is managed automatically through 10g ASMM?
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)
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. Officially 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
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 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.
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.
UNOFFICIAL GUIDELINE: Bugs and internal tests show that subpools around 500M will work efficiently for 10.2.x and in future versions perhaps we will see higher default sizes in the algorithm
How do I control the number of subpools used?
To see the number of subpools 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 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.
SQL> alter system set "_kghdsidx_count"=1 scope=spfile;
or add this in the pfile
"_kghdsidx_count"=1
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. They may or may not be reported to the DBA as the user may reissue the command or set of application steps and not see the error again. 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.
You can monitor if users have been 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 / added kghlushrpool to Steve's code for 9i/10g
-------------------------------------------------------------------------------
>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')
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
alter system set events '4031 trace name HEAPDUMP level 536870914';
NOTE: Setting this event at the instance level will generate large files and if the 4031 error occurs frequently, you will get many trace files. This can impact performance and hang (and in some cases crash a database). Turn this event off using
alter system set events '4031 trace name HEAPDUMP off';
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
alter system set events 'immediate trace name heapdump 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 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.
@ NOTE: In some cases, an ORA-04031 error will occur like
@
If you need to see more detail on how the memory is allocated in the Java Pool, you can issue
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 128';
* 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
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 32';
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
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 2';
NOTE: Running the heapdump trace is not recommended during peak activity on the database. The tracing will affect performance.
* 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,
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 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
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 64';
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,
RDA - CPU Information
Under 'Overview', 'Database Information', review the release level information (V$VERSION)
RDA - 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_time', '_4031_sga_dump_time', '_4031_sga_max_dumps'.
RDA - Database Parameters
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.
RDA - V$RESOURCE_LIMIT Information
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_time (default 300) - minimum amount of time between 4031 diagnostic dumps, in units of seconds.
_4031_sga_dump_time (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
*** SESSION ID:(242.24755) 2006-08-29 08: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
inlist and the CBO)
Heap: 44b5c89b8, size: 96 <<<< the requested memory allocation was 96 bytes
******************************************************
******************************************************
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 <<< optdef: apanlg is subheap
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 Trace listing,
----- End of Call Stack Trace -----
===============================
Memory Utilization of Subpool 1 <<< you will see multiple breakdowns if more than one subpool is used
===============================
Allocation Name Size
_________________________ __________
"free memory " 124944864 <<<< this was "free memory" at time of 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 <<< failed memory request inside this structure--lot
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 Library Cache information at the time of the error:
LIBRARY CACHE STATISTICS: (emphasis added on key areas)
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 4265150 0.977 496114150 0.999 155148 46115
TABL/PRCD/TYPE 40860748 0.999 80409664 0.994 190813 0
BODY/TYBD 52028 0.996 55986 0.920 3084 0
TRGR 468975 0.998 468975 0.998 76 0
INDX 54546 0.919 65318 0.867 0 0
CLST 122885 0.992 166510 0.989 0 0
OBJE 0 0.000 0 0.000 0 0
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. Heapdump traces and queries on X$KSMSP can impact performance. 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
alter system set events '4031 trace name HEAPDUMP 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 <<< total available memory
FREE LISTS:
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 <<< total free memory
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
Permanent space = 0
******************************************************
In some cases, it is necessary to dump additional diagnostic information on a subheap. For example,
<<< from Heapdump trace >>>
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)
<<< portion of subheap trace >>>
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 "
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
Database DB Id Instance Inst Num Startup Time 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
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Parses: 132.46 31.70
Hard parses: 0.73 0.17
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Library Hit %: 99.90 Soft Parse %: 99.45
Execute to Parse %: 42.77 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 97.19 % Non-Parse CPU: 93.91
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.54 95.50
% SQL with executions>1: 71.11 67.96 <<< >80% best for these
% Memory for SQL w/exec>1: 64.80 64.01
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:
AWR - Shared Pool Advisory
AWR - Library Cache Information
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
From Note 430473.1, run script SQLStats.sql
Only a problem if you see unusually high values. Cursor with a high version_count may indicate a problem with cursor sharing. Cursors with large shareable_mem may not necessarily be a problem but recall that the shared pool allocations are limited to approximately 4k. Larger allocations put stress on the Reserved Area and make multiple requests of the heap manager (causing contention on internal latches.
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
alter system flush shared_pool;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
Simply changing the test code to use a bind variable improved the performance dramatically and completed in 2 or 3 seconds
alter system flush shared_pool;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
Not using bind variables will cause over usage of memory in the Shared Pool/Library Cache. You can run this query:
SELECT substr(sql_text,1,90) "SQL",count(*) "SQL Copies",
sum(executions) "TotExecs", sum(sharable_mem) "TotMemory"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,90) HAVING count(*) > 30
ORDER BY 2;
Note: Change the substr() length value to narrow down non-shared code in your environment. This example shows 90, but the length may need to be higher or lower depending on complexity of the code used in the application. Smaller non-shared code is actually a bigger contributor to heavy fragmentation, so you may want to use 40 or 50.
For a queries like
select sal from emp where empno=7782;
select sal from emp where empno=7900;
You could use a length of 32 and see that TotExecs is equal to or very close to COUNT(*).
or to get a more complete listing of literal SQL in your Library Cache, run the following code (from <>):
set serveroutput on
set lines 120
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor my_statement is
select address from v$sql
group by address;
cursor getsqlcode is
select substr(sql_text,1,60)
from v$sql
where address = b_myadr;
cursor kglcur is
select kglhdadr from x$kglcursor
where kglhdpar = b_myadr
and kglhdpar != kglhdadr
and kglobt09 = 0;
cursor isthisliteral is
select kkscbndt
from x$kksbv
where kglhdadr = b_myadr1;
begin
dbms_output.enable(10000000);
open my_statement;
loop
Fetch my_statement into b_myadr;
open kglcur;
fetch kglcur into b_myadr1;
if kglcur%FOUND Then
open isthisliteral;
fetch isthisliteral into b_anybind;
if isthisliteral%NOTFOUND Then
open getsqlcode;
fetch getsqlcode into qstring;
dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
close getsqlcode;
end if;
close isthisliteral;
end if;
close kglcur;
Exit When my_statement%NOTFOUND;
End loop;
close my_statement;
end;
/
You want to investigate this problem when you see issues with high hard parse counts.
<< from Statspack report>>
Load Profile
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Redo size: 3,092,800.46 2,563.60
Logical reads: 314,615.77 260.78
Block changes: 18,384.93 15.24
Physical reads: 7,497.42 6.21
Physical writes: 1,698.45 1.41
User calls: 4,922.77 4.08
Parses: 8,245.52 6.83
Hard parses: 141.85 0.12 <<< 142 hard parses/sec
Sorts: 10,794.93 8.95
Logons: 2.72 0.00
Executes: 22,421.54 18.59
Transactions: 1,206.43
% Blocks changed per Read: 5.84 Recursive Call %: 92.53
Rollback per transaction %: 2.17 Rows per Sort: 5.23
From the database side, you could use CURSOR_SHARING=SIMILAR|FORCE and the literals will be replaced with bind variables behind the scenes. FORCE and SIMILAR are nearly the same, but SIMILAR will take into account the optimizer plan as well. There are other performance impacts with CURSOR_SHARING settings, so you need to test your environment before changing this parameter. CURSOR_SHARING is a dynamic parameter, but usage changes in the Shared Pool are not immediate. If you can flush the pool or restart the database, this jump start the better use of memory.
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.
(Version 10g)
select sa.sql_text,sa.version_count,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.address
and sa.version_count > 50
order by sa.version_count ;
(Version 8,9)
select sa.sql_text,sa.version_count ,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.kglhdpar
and sa.version_count > 50
order by sa.version_count ;
This query will show cursors with more than 50 children and the column showing 'Y' will indicate the reason for the generation of the child cursors. The columns with a value of 'N' can be ignored in the output.
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.
Is it possible to find objects in the Library Cache that could be causing the problem?
Yes, but it is important to gather this information as close the the errors as possible as data in the Library Cache changes over time. You can execute a query on X$KSMLRU to see what objects are causing reloads in the library cache.
Start with
Select namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratio, reloads, invalidations from v$librarycache;
Further investigation is warranted if you see evidence of high reloads and invalidations.
Select KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ from x$ksmlru where KSMLRSIZ > 5000;
NOTE:
COLUMN DESCRIPTION
KSMLRCOM allocation comment that describes the type of allocation.
KSMLRSIZ amount of contiguous memory being allocated. Values over around 5K start
to be a problem, values over 10K are a serious problem, and values over
20K are very serious problems. Anything less then 5K should not be a
problem
KSMLRNUM number of objects that were flushed from the shared pool in order allocate
the memory
KSMLRHON the name of the object being loaded into the shared pool if the object is
a PL/SQL object or a cursor.
Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool using dbms_shared_pool.keep procedure. Remember, the content of this view is recycled every time the object is queried, either spool the content to a file or store in a temporary table.
For example,
create table LRU_TMP as select * from x$ksmlru;
and on regular intervals issue
insert into LRU_TMP select * from x$ksmlru;
Use the LRU_TMP table for analysis. You can use a query like
select USERNAME, KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ, sql_text
from v$sqlarea a, lru_tmp k, v$session s
where ksmlrsiz > 3000
and a.address=s.sql_address and a.hash_value = s.sql_hash_value
and saddr=ksmlrses;
to see more information on candidate code in the library cache.
To look for candidate objects in the Library Cache for pinning, use Statpack/AWR or code like
col stored_objects format a40
col sq_executions format 999,999,999,999
select /*+ ordered use_hash(d) use_hash(c) */
o.kglnaown||'.'||o.kglnaobj stored_object,
+3sum(c.kglhdexc) sql_executions
from sys.x$kglob o, sys.x$kglrd d, sys.x$kglcursor c
where
o.inst_id = userenv('Instance') and
d.inst_id = userenv('Instance') and
c.inst_id = userenv('Instance') and
o.kglobtyp in (7, 8, 9, 11, 12) and
d.kglhdcdr = o.kglhdadr and
c.kglhdpar = d.kglrdhdl
group by o.kglnaown, o.kglnaobj
having sum(c.kglhdexc) > 0;
Note 1012047.6 How To Pin Objects in Your Shared Pool
Note 69925.1 PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL
References
NOTE:368475.1 - How To Extend Maintenance Windows For GATHER_STATS_JOB for More Than 8 Hours?
NOTE:377143.1 - How to check what automatic statistics collection is scheduled on 10g
NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
NOTE:554521.1 - Quick Fix For ORA-4031 - What to Look For and What to Do!
*********************************************************************************************************************
Quick Fix For ORA-4031 - What to Look For and What to Do! [ID 554521.1]
Modified 25-JUN-2009 Type HOWTO Status MODERATED
In this Document
Goal
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.5 to 10.2.0.4
Information in this document applies to any platform.
5/RDBMS.RDBMS (9.2.0.5.0 to 10.2.0.3.0)
Goal
A quick fix guide to solving the ORA-4031 error.
Solution
STEP ONE:
------------------------------
When you first receive the ORA-4031 error retrieve the following:
1. Trace files that are generated in the dump directory. They have "Begin 4031 diagnostic" at the top of the file.
2. OS
3. Physical Memory
(All of the above are in the RDA for the most part).
STEP TWO:
------------------------------
In the trace file, go immediately to the SubPool lists and look at the free memory (this is what it looks like):
===============================
Memory Utilization of Subpool 1
===============================
Allocation Name Size
_________________________ __________
"free memory " 38445192
etc... etc.. etc...
So TAKE NOTE, we have roughly 38MB of free memory.
Scroll down and compare that 38MB to any other allocations in that list, LOOK for anything that sticks out (large in size). Usually in 10g it's the sql area or library cache.
"library cache " 378259720
So in this case, all other allocations are small (1-25MB or so), but the library cache is nearly 378MB.
So this tells us that there may be a possible CURSOR SHARING issue. Why? Because there are so many cursors being created and thus ballooning the library cache. (See Fix 1)
STEP THREE:
--------------------------------
If there are no specific pools that stick out, the cause is one of two things, either:
1) there is too little memory associated with the shared_pool (See Fix 2),
or 2) the free memory is fragmented (free shows something large like 100MB or larger). (See Fix3)
STEP FOUR:
--------------------------------
If there are multiple subpools and they look ok except for 1 or 2 of the pools. EXAMPLE: Subpool 1 shows free memory to be 200MB, but three and four only show 23MB free. There is a misallocation going on and it may be best to reduce the pools to a lesser value and/or set it to one. (SEE FIX 4)
FIXES:
--------------------------------
1. If not using Oracle Application Server - set cursor_sharing=FORCE; and review Note 62143.1 to find literal SQL, hit ratios, sql with high version counts for pinning, and high memory usage statements
2. Increase the value of shared_pool by 20% (rule of thumb)
3. Re-write code to use bind variables instead of literals, set cursor sharing to force, and review Note 62143.1 to find literal SQL, hit ratios, sql with high version counts for pinning, and high memory usage statements
4. Include in the init file: _kghdsidx_count=1
This is just a quick fix guide, if the solution is not contained herein, please review the FAQ guide located for a more in-depth and complete review: Note 396940.1 Troubleshooting and Diagnosing ORA-4031 Error:
References
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error
NOTE:62143.1 - Understanding and Tuning the Shared Pool
***********************************************************************************************************************
ORA-4031 On Using Auto SGA [ID 556140.1]
Modified 03-MAR-2010 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6
Information in this document applies to any platform.
Symptoms
Checked for relevance on 04-MAR-2010
- Getting ORA-4031 that is reported in the shared pool on very large allocation requests.
- Using auto SGA.
- Shared pool and SGA are set to appropriate values.
- Opening the ORA-4031 trace file, you can see a lot of space allocated to "Free Space" field.
- Running the following queries will return values less than 10
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 = '_shared_pool_reserved_pct';
Parameter Session Value Instance Value
------------------------------------------------------------------------------
_shared_pool_reserved_pct 5 5
Cause
The issue is Shared Pool fragmentation.
This fragmentation is caused because the shared pool reserved size is set to less than 10% of the shared pool size (5% in the above example). It is recommended that shared pool reserved size is 10% of the shared pool size to avoid fragmentation.
Modifying the shared pool in Auto-SGA is done only using a hidden parameter as below.
NOTE: With auto-tuning, the Reserved Area can grow and shrink just as the Shared Pool will. The setting for the hidden parameter helps the auto-tuner refrain from aggressive shrinks on the Reserved Area.
Solution
SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile
or set it in init.ora
"_shared_pool_reserved_pct"=10
And restart the instance.
<<End_of_Article>> FOLDER:ST.Server.Manageability.MemoryMgmt TOPIC:ORA-4031 Shared Pool DOCUMENT-ID:556140.1 ALIAS: SOURCE:AWIZ 6734715.994 DOCUMENT-TYPE:PROBLEM ZCXTECH TITLE:ORA-4031 On Using Auto SGA IMPACT:LOW SKILL-LEVEL:NOVICE STATUS:MODERATED DISTRIBUTION:EXTERNAL ZCXPUBLIC ZCXCURRENT AUTHOR:MWAGIH.EG ERROR:ORA-4031 KEYWORD:FRAGMENTATION KEYWORD:FREE~SPACE KEYWORD:SHARED~POOL PRODID-5 COMPONENT:RDBMS.RDBMS MINVER:10.1.0.2 MAXVER:11.1.0.6 PORTID-0 FDRSEG-1219 FDRSEG-1220 FDRSEG-773 FDRSEG-465
**********************************************************************************************************************
**********************************************************************************************************************
Full Export From 10.2.0.1 Aborts With EXP-56 ORA-932 (Inconsistent Datatypes) EXP-0 [ID 339938.1]
Modified 30-MAR-2009 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
Oracle Server - Personal Edition - Version: 10.1.0.2 to 10.2.0.3
Oracle Server - Standard Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.
Symptoms
A full database export from a Oracle10g database aborts with:
...
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
EXP-00000: Export terminated unsuccessfully
If export was started with SYS schema, a table level export may also fail with:
...
Current user changed to TEST
. . exporting table DOC_ARCHIVE 16 rows exported
Current user changed to SYS
EXP-00011: SYS.; does not exist
Export terminated successfully with warnings.
Cause
One possible cause (note there might be others):
Script $ORACLE_HOME/rdbms/admin/catmeta.sql has been run recently.
There are several invalid SYS.KU$_% views in the dictionary:
-- invalid objects:
SET lines 120 pages 2000
COL status FOR a9
COL object_type FOR a20;
COL owner.object FOR a50
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
--------- ---------- --------------- --------------------------------
INVALID 7105 PACKAGE BODY SYS.DBMS_METADATA
INVALID 6683 VIEW SYS.KU$_10_1_COMMENT_VIEW
INVALID 6788 VIEW SYS.KU$_10_1_IND_STATS_VIEW
INVALID 6778 VIEW SYS.KU$_10_1_PIND_STATS_VIEW
INVALID 6752 VIEW SYS.KU$_10_1_PTAB_STATS_VIEW
INVALID 6770 VIEW SYS.KU$_10_1_SPIND_STATS_VIEW
INVALID 6748 VIEW SYS.KU$_10_1_TAB_ONLY_STATS_VIEW
... (etc)
A query in SQL*Plus on sys.ku$_xmlschema_view also fails with ORA-932:
SET lines 200 pages 2000
COL url FOR a60 WRA
SELECT url, local, stripped_val
FROM sys.ku$_xmlschema_view;
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
Solution
For reasons having to do with compatibility, the XDB objects cannot be created by the catproc.sql script. The script catproc.sql therefore calls the catmeta.sql script, which contains fake object views for XDB objects.
The real object views are defined in the catmetx.sql script (this script is invoked by catxdbv.sql which is invoked by catqm.sql).
Solution #1
Run following scripts while connected as SYS user:
> sqlplus /nolog
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> exit
Afterwards, re-run the export.
or:
Solution #2
Run the export with the Export DataPump client. E.g.:
> expdp system/manager directory=my_dir \
dumpfile=expdp_full.dmp logfile=expdp_full.log full=y
References
BUG:4668539 - (BUG)ORA-932 WHILE EXPORT
BUG:4685068 - EXPORT IS FAILING WITH ERROR ORA-00932 ON 10.2
**************************************************************************************************************************
In this Document
Purpose
Common Bugs
Questions 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?
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-04031 problems?
What should we look at in an RDA to help diagnose a 4031 error?
What is relevant in the default 4031 trace file?
What is relevant in the Statspack/AWR report for a 4031 error?
How can we determine if there are application issues causing the problem?
Not Using Bind Variables
Multiple child cursors
High parse ratios
Is it possible to find objects in the Library Cache that could be causing the problem?
References
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 11.1.0.7 - Release: 8.1.5 to 11.1
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.1.5.0 to 11.1.0.8
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) [Trouble seeing this video?]
Common Bugs
BUG REPORTED FIXED NOTES DETAILS
Bug 2934402 9.2.x 10.x No Backports Feasible The variable component of the 'show sga' command will show more memory than expected when SGA_MAX_SIZE is much larger than the SGA size required and you set parameters like PROCESSES, DB_FILES, OPEN_CURSORS to a high value. These cause additional memory to be allocated in the SGA as a reserve for future memory needs. Reference Note 270935.1
Bug 3490108 (Bug 4171368) 9.2.x 10.2.x No Backports to 9i at this point Seeing ORA-4031 on 'BAMIMA: Bam Buffer', however lots of free memory on hand in the shared pool. The problem appears to stem from very small chunks used in the Shared Pool (Reserved Area specifically)
Bug 4992466 (Bug 3046725) 9.2.x 10.2.x No Backports Feasible Depending on application work loads, some components in a RAC environment can temporarily consume a lot of SGA memory for "ges enqueues" and this can fragment the shared pool sufficiently to cause ORA-04031 type errors. Workaround: Pre-reserve plenty of space for GES resources in the shared pools to prevent the fragmentation effect of a sudden jump in requirements. This can be achieved by setting the hidden parameters _lm_locks / _lm_ress
Bug 3090397 9.2.0.3 9.2.0.5 Backports Available In situations when using ASYNC or SYNC=PARALLEL, the LGWR trace file can indicate ORA-04031 errors and indicate large memory request failures. Workaround: Use ARCH transport mode instead.
Bug 3854318 9.2.0.5 10.2.x Backports Not Feasible The problem shows up as heavy 'perm' allocations in the 'sql area' in a heapdump trace. The root issue is allocations related to ansi-join processing and query transformation routines that allocate memory and do not get cleaned up properly.
Bug 4375655 9.2.0.5 9.2.0.8 and 10g
A large in-list can consume excessive memory if there is a zero length string in the inlist. Do not use '' in an inlist.
Bug 5246688 / (Bug 4184298) 10.1.0.3 10.1.0.5 / 10.2.0.1 Backports to 10.1.x only If the system is configured with multiple shared pool subpools, and if many sessions are active, it is possible for an allocation imbalance to occur between the subpools. It extreme cases, this could lead to ORA-04031 due to a large number of "session parameters" allocations using up space from one subpool. Workaround: increase the size of the Shared Pool or revert back to one large Shared Pool instead of using subpools.
Bug 4367986
** NEW 10.1.0.3 10.2.0.4 / 11.x Backports to 10.2.x on some platforms Parallel execution cursors are not shared when bind peeking is used. Excessive child cursors created can lead to fragmentation and ORA-4031. Watch for nonshared code showing BIND_PEEKED_PQ_MISMATCH as the problem.
Bug 3513427 10.1.0.3 / 10.1.0.2 10.1.0.3 / 10.2.0.1 Backports to 10.1.0.2 / 9.2.0.5 When an ORA-4031 occurs SGA heapdumps can occur every minute or two. This can lead to contention for the shared pool latch. The dumps are only meant to occur every "_4031_sga_dump_interval" seconds (default 3600) but the code uses "_4031_max_dumps" as the interval instead (default 100). Workaround: Avoid the ORA-4031 situation or set "_4031_max_dumps"=3600 so that dumps only occur every 3600 seconds.
Bug 4733833/ Bug 5473945 10.1.0.3 / 10.2.x 10.2.0.3 / 11.x Backports Not Available Full outer join queries can be transformed into union all code with left outer joins and anti joins. The transformation causes multiple entries in the Library Cache and high parse counts and can cause additional fragmentation leading to ORA-04031 errors.
Bug 4467058 10.2.x 11 Backports Feasible on 10g With ORA-04031 dumping events set, DBWR and LGWR can generate spurious dumps. Reference Note 367392.1 Workaround: Turning off 4031 dump events.
Bug 4237613 10.2.x 10.2.0.3, 11g Backports Feasible on 10.2.x Increases to 'ASM extent pointer array' in V$SGASTAT over time, while no decreases are accounted for. This appears to be a leak, but is simply an accounting problem in v$SGASTAT. Some customers are reporting a side-effect of error indicators from GRID due to this overstatement of memory in the Shared Pool.
Bug 5045507 / (Bug 4507532) 10.2.0.1 10.2.0.2 Backports not available Seeing aggressive growth of entries in V$SGASTAT for "KGH: NOACCESS". See Note 461160.1 for more on this problem.
Bug 5918642 10.2.0.3 10.2.0.4 Backports available on some platforms With very large Buffer Cache, cache statistics can cause excessive latch contention. The latch issues can lead to ORA-4031 errors in some cases. Workaround is to set DB_CACHE_ADVICE=OFF. However, this will impact ASMM management in the SGA.
Bug 5618049 10.2.0.2 10.2.0.4 and 11 Some Backports on 10.2.x 4031 during partitioning ddl's and there are a number of allocations with the comment "mvobj part des". This can show up with automated jobs that perform operations on partitions as well.
Bug 5552515 10.2.0.2 11.x Backports can be requested Commiting DML on table with MView log causes an increase in heap stats for the memory structure "ktcmvcb". No workaround.
Bug 6868080 10.2.0.2 10.2.0.5 / 11.1.0.7 / 11.2 Some one-off patches available NUMA option can overflow memory allocations into one subpool and cause an imbalanced availability of free memory and ORA-4031. Look for "NUMA pool X" allocations.
Bug 5573238 10.2.0.2 10.2.0.4 / 11.1.0.6 / 11.2 Some one-off patches available Fragmentation problems from "obj stat memo" allocations
Bug 5548510 10.2.0.2 10.2.0.4 and 11.x Backports Available CBO leak. The allocation in the SGA for fix control will grow with each new session. You will see this running: select * from v$sgastat where name like 'qksbg%'.
Bug 5705795 10.2.0.3 10.2.0.4 / 11.x Introduced in 10.2.0.3 and backport feasible This problem is introduced in 10.2.0.3 on Windows 32bit and Linux 32bit only. 10.2.0.3 on other platforms include the fix for this bug# and so are not affected. SQL using bind variables with different bind sizes can lead to a large number of child cursors being created leading to excess shared pool usage and latch contention.
Bug 6271680 / 7258873 10.2.0.4 11.2 Some one-off patches available Free memory reported in the default 4031 trace can reflect large negative values in cases where the memory exceeds 2G.
Bug 6981690 10.2.0.4 11.2 Some one-off patches available Excessive growth in memory for cursors not shared because of PQ_SLAVE_MISMATCH. This happens with Parallel activity on databases mounted and not open in RAC and/or Standby environments.
Bug 7340448 10.2.0.4 no fix yet
Excessive Shared Pool memory growth with command CTAS and REGEXP_LIKE expression
Bug 6868080 11.1.0.6 11.1.0.7 / 11.2 Some one-off patches available NUMA option can lead to ORA-4031 and ORA-600 [17137]. Workaround is to disable NUMA - "_enable_NUMA_optimization"=false
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.
Representation of Memory Pools
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’s 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.
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.
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.
By default, Oracle configures a small Reserved Pool (or Reserved Area) inside the Shared Pool. This memory can be used 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.
5% of the Shared Pool is set aside as the Reserved Pool to handle allocations of memory higher 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 = '_shared_pool_reserved_pct';
to see the current setting in your environment. 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.
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.
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 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 from the other memory pools. The Shared Pool stores information related to the dictionary and library cache. However, these memory areas are managed using free lists and a Least Recently Used (LRU) algorithm. The ORA-04031 is signaled on the Shared Pool after searching all the free lists, aging all objects possible from the LRU list, and scanning the free list multiple times. This means the ORA-04031 is very difficult to predict. There can be many contributing factors to the ORA-04031 and the trace information provide at the time of the error is associated with the "victim session" in the memory condition and not the cause. The allocation code is complicated, but a simplified version of the allocation algorithm is sketched below:
scan regular free list for match, if not found
large request, scan reserved list
if (chunk found)
check chunk size and perhaps truncate
if (chunk is not found)
scan regular free list
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
small request, scan regular free list
do LRU operations and repeat search
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
NOTE: There are internal checks to limit the number of times these searches repeat prior to reporting ORA-04031 error.
The sum of the free space, which one may obtain through v$sgastat or x$ksmsp, is not important. What is important is the size of the largest chunk that can be freed or merged after some LRU operations. From a heapdump trace we can see free list buckets and information about the chunks of memory in each bucket.
Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
This shows that bucket 1 has 443 chunks of memory where the maximum size is 40 bytes and the average is 40 bytes. Bucket 2 includes memory chunks with sizes between 40 and 48 bytes. The average size in this case is 40 bytes and the maximum size is 40 bytes. Finding out what caused fragmentation in a memory pool is not always feasible. Sometimes the problem is an Oracle functionality issue, but in a large percentage of the cases, inefficient application coding can be the root issue.
The 4031 error can occur in the Large Pool, Java Pool, Streams Pool (new to 10g), or the Shared Pool. The error message will indicate which pool had the problem. If the error indicates a problem in a pool other than the Shared Pool, this usually indicates the problem pool is configured too small for the application environment. Increase the size of the problem pool in increments of 50MB or 100MB and monitor for continued problems. If using the 10g, Automatic Shared Memory Management (ASMM) functionality, the MMAN process will attempt to shrink and grow different components in the SGA as memory is needed over time. You may need to increase the setting for SGA_TARGET to allow MMAN more memory to manage behind the scenes if you experience ORA-04031 errors in the Large Pool, Streams Pool, or Java Pool.
The Shared Pool is little more complicated to tune. For example
ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","SELECT /*+ FIRST_ROWS */ * F...","sql area","kafco : qkacol"):4031:375:2008:ocicon.c
In this case, the problem occurred in the Shared Pool. The error message also includes information on the size of the memory request that failed. In our example, the failure was on a request for 4192 bytes in the SQL Area.
NOTE: The Shared Pool is used in an ASM environment as well. There have been reports of ORA-04031 on 10.1. x ASM instances because the default size can be too small to accommodate the diskgroup management activities. In these cases, set the SHARED_POOL_SIZE parameter to 50M and increase the setting in increments of 10M if the problems persist.
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
ReservedAnalysis.sql
Request Misses = 0 can mean the Reserved Area is too big. Request Misses always increasing but Request Failures not increasing can mean the Reserved Area is too small. In this case flushes in the Shared Pool satisfied the memory needs. Request Misses and Request Failures always increasing can mean the Reserved Area is too small and flushes in the Shared Pool are not helping (likely got an ORA-04031).
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.
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 report wrong data on Oracle versions prior to 10.2. Per Bug 3669074, the workaround is to use this query instead of the existing 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,
(select sum(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 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.
Example,
SHARED_POOL_SIZE=64M
Overhead=12M
SQL> Select sum(bytes) "Total Mem" from v$sgastat where pool='shared pool';
Total Mem
-----------
79691776
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'.
The X$KSMSP view shows the breakdown of memory in the SGA. You can run this query to build trend information on memory usage in the SGA. Remember, the 'free' class in this query is not specific to the Shared Pool, but is across the SGA. NOTE: It is not recommended to run queries on X$KSMSP when the DB is under load. Performance of the database will be impacted.
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 SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 12 8059200 655.86k <<= Reserved List
R-freea 24 960 .04k <<= Reserved List
free 331 151736448 447.67k <<= Free Memory
freeabl 4768 7514504 1.54k <<= Memory for user/system
processing
perm 2 30765848 15,022.39k <<= Memory allocated to the
system
recr 3577 3248864 .89k <<= Memory for user/system
processing
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 and shared_pool_reserved_size. You should expect 'free' memory to increase and decrease over time. 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 (column SIZ) continually grows then it is possible you are seeing a memory bug.
c) if 'freeabl' and 'recr' memory classes (column SIZ)are always huge, this indicates that you have a lot of cursor info stored that is not releasing.
d) if 'free' memory (column SIZ) is huge but you are still getting 4031 errors, the problem is likely reloads and invalidations in the library cache causing fragmentation.
NOTE: Please be aware that there are bugs filed on HP indicating running queries on x$ksmsp can hang the system on 10g. I haven't seen reports of the problem on 9.2.x or on other platforms.
What is managed automatically through 10g ASMM?
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)
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. Officially 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
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 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.
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.
UNOFFICIAL GUIDELINE: Bugs and internal tests show that subpools around 500M will work efficiently for 10.2.x and in future versions perhaps we will see higher default sizes in the algorithm
How do I control the number of subpools used?
To see the number of subpools 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 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.
SQL> alter system set "_kghdsidx_count"=1 scope=spfile;
or add this in the pfile
"_kghdsidx_count"=1
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. They may or may not be reported to the DBA as the user may reissue the command or set of application steps and not see the error again. 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.
You can monitor if users have been 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 / added kghlushrpool to Steve's code for 9i/10g
-------------------------------------------------------------------------------
>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')
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
alter system set events '4031 trace name HEAPDUMP level 536870914';
NOTE: Setting this event at the instance level will generate large files and if the 4031 error occurs frequently, you will get many trace files. This can impact performance and hang (and in some cases crash a database). Turn this event off using
alter system set events '4031 trace name HEAPDUMP off';
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
alter system set events 'immediate trace name heapdump 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 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.
@ NOTE: In some cases, an ORA-04031 error will occur like
@
If you need to see more detail on how the memory is allocated in the Java Pool, you can issue
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 128';
* 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
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 32';
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
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 2';
NOTE: Running the heapdump trace is not recommended during peak activity on the database. The tracing will affect performance.
* 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,
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 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
> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 64';
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,
RDA - CPU Information
Under 'Overview', 'Database Information', review the release level information (V$VERSION)
RDA - 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_time', '_4031_sga_dump_time', '_4031_sga_max_dumps'.
RDA - Database Parameters
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.
RDA - V$RESOURCE_LIMIT Information
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_time (default 300) - minimum amount of time between 4031 diagnostic dumps, in units of seconds.
_4031_sga_dump_time (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
*** SESSION ID:(242.24755) 2006-08-29 08: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
inlist and the CBO)
Heap: 44b5c89b8, size: 96 <<<< the requested memory allocation was 96 bytes
******************************************************
******************************************************
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 <<< optdef: apanlg is subheap
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 Trace listing,
----- End of Call Stack Trace -----
===============================
Memory Utilization of Subpool 1 <<< you will see multiple breakdowns if more than one subpool is used
===============================
Allocation Name Size
_________________________ __________
"free memory " 124944864 <<<< this was "free memory" at time of 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 <<< failed memory request inside this structure--lot
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 Library Cache information at the time of the error:
LIBRARY CACHE STATISTICS: (emphasis added on key areas)
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 4265150 0.977 496114150 0.999 155148 46115
TABL/PRCD/TYPE 40860748 0.999 80409664 0.994 190813 0
BODY/TYBD 52028 0.996 55986 0.920 3084 0
TRGR 468975 0.998 468975 0.998 76 0
INDX 54546 0.919 65318 0.867 0 0
CLST 122885 0.992 166510 0.989 0 0
OBJE 0 0.000 0 0.000 0 0
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. Heapdump traces and queries on X$KSMSP can impact performance. 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
alter system set events '4031 trace name HEAPDUMP 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 <<< total available memory
FREE LISTS:
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 <<< total free memory
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
Permanent space = 0
******************************************************
In some cases, it is necessary to dump additional diagnostic information on a subheap. For example,
<<< from Heapdump trace >>>
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)
<<< portion of subheap trace >>>
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 "
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
Database DB Id Instance Inst Num Startup Time 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
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Parses: 132.46 31.70
Hard parses: 0.73 0.17
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Library Hit %: 99.90 Soft Parse %: 99.45
Execute to Parse %: 42.77 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 97.19 % Non-Parse CPU: 93.91
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.54 95.50
% SQL with executions>1: 71.11 67.96 <<< >80% best for these
% Memory for SQL w/exec>1: 64.80 64.01
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:
AWR - Shared Pool Advisory
AWR - Library Cache Information
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
From Note 430473.1, run script SQLStats.sql
Only a problem if you see unusually high values. Cursor with a high version_count may indicate a problem with cursor sharing. Cursors with large shareable_mem may not necessarily be a problem but recall that the shared pool allocations are limited to approximately 4k. Larger allocations put stress on the Reserved Area and make multiple requests of the heap manager (causing contention on internal latches.
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
alter system flush shared_pool;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
Simply changing the test code to use a bind variable improved the performance dramatically and completed in 2 or 3 seconds
alter system flush shared_pool;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
Not using bind variables will cause over usage of memory in the Shared Pool/Library Cache. You can run this query:
SELECT substr(sql_text,1,90) "SQL",count(*) "SQL Copies",
sum(executions) "TotExecs", sum(sharable_mem) "TotMemory"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,90) HAVING count(*) > 30
ORDER BY 2;
Note: Change the substr() length value to narrow down non-shared code in your environment. This example shows 90, but the length may need to be higher or lower depending on complexity of the code used in the application. Smaller non-shared code is actually a bigger contributor to heavy fragmentation, so you may want to use 40 or 50.
For a queries like
select sal from emp where empno=7782;
select sal from emp where empno=7900;
You could use a length of 32 and see that TotExecs is equal to or very close to COUNT(*).
or to get a more complete listing of literal SQL in your Library Cache, run the following code (from <>):
set serveroutput on
set lines 120
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor my_statement is
select address from v$sql
group by address;
cursor getsqlcode is
select substr(sql_text,1,60)
from v$sql
where address = b_myadr;
cursor kglcur is
select kglhdadr from x$kglcursor
where kglhdpar = b_myadr
and kglhdpar != kglhdadr
and kglobt09 = 0;
cursor isthisliteral is
select kkscbndt
from x$kksbv
where kglhdadr = b_myadr1;
begin
dbms_output.enable(10000000);
open my_statement;
loop
Fetch my_statement into b_myadr;
open kglcur;
fetch kglcur into b_myadr1;
if kglcur%FOUND Then
open isthisliteral;
fetch isthisliteral into b_anybind;
if isthisliteral%NOTFOUND Then
open getsqlcode;
fetch getsqlcode into qstring;
dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
close getsqlcode;
end if;
close isthisliteral;
end if;
close kglcur;
Exit When my_statement%NOTFOUND;
End loop;
close my_statement;
end;
/
You want to investigate this problem when you see issues with high hard parse counts.
<< from Statspack report>>
Load Profile
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Redo size: 3,092,800.46 2,563.60
Logical reads: 314,615.77 260.78
Block changes: 18,384.93 15.24
Physical reads: 7,497.42 6.21
Physical writes: 1,698.45 1.41
User calls: 4,922.77 4.08
Parses: 8,245.52 6.83
Hard parses: 141.85 0.12 <<< 142 hard parses/sec
Sorts: 10,794.93 8.95
Logons: 2.72 0.00
Executes: 22,421.54 18.59
Transactions: 1,206.43
% Blocks changed per Read: 5.84 Recursive Call %: 92.53
Rollback per transaction %: 2.17 Rows per Sort: 5.23
From the database side, you could use CURSOR_SHARING=SIMILAR|FORCE and the literals will be replaced with bind variables behind the scenes. FORCE and SIMILAR are nearly the same, but SIMILAR will take into account the optimizer plan as well. There are other performance impacts with CURSOR_SHARING settings, so you need to test your environment before changing this parameter. CURSOR_SHARING is a dynamic parameter, but usage changes in the Shared Pool are not immediate. If you can flush the pool or restart the database, this jump start the better use of memory.
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.
(Version 10g)
select sa.sql_text,sa.version_count,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.address
and sa.version_count > 50
order by sa.version_count ;
(Version 8,9)
select sa.sql_text,sa.version_count ,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.kglhdpar
and sa.version_count > 50
order by sa.version_count ;
This query will show cursors with more than 50 children and the column showing 'Y' will indicate the reason for the generation of the child cursors. The columns with a value of 'N' can be ignored in the output.
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.
Is it possible to find objects in the Library Cache that could be causing the problem?
Yes, but it is important to gather this information as close the the errors as possible as data in the Library Cache changes over time. You can execute a query on X$KSMLRU to see what objects are causing reloads in the library cache.
Start with
Select namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratio, reloads, invalidations from v$librarycache;
Further investigation is warranted if you see evidence of high reloads and invalidations.
Select KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ from x$ksmlru where KSMLRSIZ > 5000;
NOTE:
COLUMN DESCRIPTION
KSMLRCOM allocation comment that describes the type of allocation.
KSMLRSIZ amount of contiguous memory being allocated. Values over around 5K start
to be a problem, values over 10K are a serious problem, and values over
20K are very serious problems. Anything less then 5K should not be a
problem
KSMLRNUM number of objects that were flushed from the shared pool in order allocate
the memory
KSMLRHON the name of the object being loaded into the shared pool if the object is
a PL/SQL object or a cursor.
Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool using dbms_shared_pool.keep procedure. Remember, the content of this view is recycled every time the object is queried, either spool the content to a file or store in a temporary table.
For example,
create table LRU_TMP as select * from x$ksmlru;
and on regular intervals issue
insert into LRU_TMP select * from x$ksmlru;
Use the LRU_TMP table for analysis. You can use a query like
select USERNAME, KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ, sql_text
from v$sqlarea a, lru_tmp k, v$session s
where ksmlrsiz > 3000
and a.address=s.sql_address and a.hash_value = s.sql_hash_value
and saddr=ksmlrses;
to see more information on candidate code in the library cache.
To look for candidate objects in the Library Cache for pinning, use Statpack/AWR or code like
col stored_objects format a40
col sq_executions format 999,999,999,999
select /*+ ordered use_hash(d) use_hash(c) */
o.kglnaown||'.'||o.kglnaobj stored_object,
+3sum(c.kglhdexc) sql_executions
from sys.x$kglob o, sys.x$kglrd d, sys.x$kglcursor c
where
o.inst_id = userenv('Instance') and
d.inst_id = userenv('Instance') and
c.inst_id = userenv('Instance') and
o.kglobtyp in (7, 8, 9, 11, 12) and
d.kglhdcdr = o.kglhdadr and
c.kglhdpar = d.kglrdhdl
group by o.kglnaown, o.kglnaobj
having sum(c.kglhdexc) > 0;
Note 1012047.6 How To Pin Objects in Your Shared Pool
Note 69925.1 PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL
References
NOTE:368475.1 - How To Extend Maintenance Windows For GATHER_STATS_JOB for More Than 8 Hours?
NOTE:377143.1 - How to check what automatic statistics collection is scheduled on 10g
NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
NOTE:554521.1 - Quick Fix For ORA-4031 - What to Look For and What to Do!
*********************************************************************************************************************
Quick Fix For ORA-4031 - What to Look For and What to Do! [ID 554521.1]
Modified 25-JUN-2009 Type HOWTO Status MODERATED
In this Document
Goal
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.5 to 10.2.0.4
Information in this document applies to any platform.
5/RDBMS.RDBMS (9.2.0.5.0 to 10.2.0.3.0)
Goal
A quick fix guide to solving the ORA-4031 error.
Solution
STEP ONE:
------------------------------
When you first receive the ORA-4031 error retrieve the following:
1. Trace files that are generated in the dump directory. They have "Begin 4031 diagnostic" at the top of the file.
2. OS
3. Physical Memory
(All of the above are in the RDA for the most part).
STEP TWO:
------------------------------
In the trace file, go immediately to the SubPool lists and look at the free memory (this is what it looks like):
===============================
Memory Utilization of Subpool 1
===============================
Allocation Name Size
_________________________ __________
"free memory " 38445192
etc... etc.. etc...
So TAKE NOTE, we have roughly 38MB of free memory.
Scroll down and compare that 38MB to any other allocations in that list, LOOK for anything that sticks out (large in size). Usually in 10g it's the sql area or library cache.
"library cache " 378259720
So in this case, all other allocations are small (1-25MB or so), but the library cache is nearly 378MB.
So this tells us that there may be a possible CURSOR SHARING issue. Why? Because there are so many cursors being created and thus ballooning the library cache. (See Fix 1)
STEP THREE:
--------------------------------
If there are no specific pools that stick out, the cause is one of two things, either:
1) there is too little memory associated with the shared_pool (See Fix 2),
or 2) the free memory is fragmented (free shows something large like 100MB or larger). (See Fix3)
STEP FOUR:
--------------------------------
If there are multiple subpools and they look ok except for 1 or 2 of the pools. EXAMPLE: Subpool 1 shows free memory to be 200MB, but three and four only show 23MB free. There is a misallocation going on and it may be best to reduce the pools to a lesser value and/or set it to one. (SEE FIX 4)
FIXES:
--------------------------------
1. If not using Oracle Application Server - set cursor_sharing=FORCE; and review Note 62143.1 to find literal SQL, hit ratios, sql with high version counts for pinning, and high memory usage statements
2. Increase the value of shared_pool by 20% (rule of thumb)
3. Re-write code to use bind variables instead of literals, set cursor sharing to force, and review Note 62143.1 to find literal SQL, hit ratios, sql with high version counts for pinning, and high memory usage statements
4. Include in the init file: _kghdsidx_count=1
This is just a quick fix guide, if the solution is not contained herein, please review the FAQ guide located for a more in-depth and complete review: Note 396940.1 Troubleshooting and Diagnosing ORA-4031 Error:
References
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error
NOTE:62143.1 - Understanding and Tuning the Shared Pool
***********************************************************************************************************************
ORA-4031 On Using Auto SGA [ID 556140.1]
Modified 03-MAR-2010 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6
Information in this document applies to any platform.
Symptoms
Checked for relevance on 04-MAR-2010
- Getting ORA-4031 that is reported in the shared pool on very large allocation requests.
- Using auto SGA.
- Shared pool and SGA are set to appropriate values.
- Opening the ORA-4031 trace file, you can see a lot of space allocated to "Free Space" field.
- Running the following queries will return values less than 10
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 = '_shared_pool_reserved_pct';
Parameter Session Value Instance Value
------------------------------------------------------------------------------
_shared_pool_reserved_pct 5 5
Cause
The issue is Shared Pool fragmentation.
This fragmentation is caused because the shared pool reserved size is set to less than 10% of the shared pool size (5% in the above example). It is recommended that shared pool reserved size is 10% of the shared pool size to avoid fragmentation.
Modifying the shared pool in Auto-SGA is done only using a hidden parameter as below.
NOTE: With auto-tuning, the Reserved Area can grow and shrink just as the Shared Pool will. The setting for the hidden parameter helps the auto-tuner refrain from aggressive shrinks on the Reserved Area.
Solution
SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile
or set it in init.ora
"_shared_pool_reserved_pct"=10
And restart the instance.
<<End_of_Article>> FOLDER:ST.Server.Manageability.MemoryMgmt TOPIC:ORA-4031 Shared Pool DOCUMENT-ID:556140.1 ALIAS: SOURCE:AWIZ 6734715.994 DOCUMENT-TYPE:PROBLEM ZCXTECH TITLE:ORA-4031 On Using Auto SGA IMPACT:LOW SKILL-LEVEL:NOVICE STATUS:MODERATED DISTRIBUTION:EXTERNAL ZCXPUBLIC ZCXCURRENT AUTHOR:MWAGIH.EG ERROR:ORA-4031 KEYWORD:FRAGMENTATION KEYWORD:FREE~SPACE KEYWORD:SHARED~POOL PRODID-5 COMPONENT:RDBMS.RDBMS MINVER:10.1.0.2 MAXVER:11.1.0.6 PORTID-0 FDRSEG-1219 FDRSEG-1220 FDRSEG-773 FDRSEG-465
**********************************************************************************************************************
**********************************************************************************************************************
Full Export From 10.2.0.1 Aborts With EXP-56 ORA-932 (Inconsistent Datatypes) EXP-0 [ID 339938.1]
Modified 30-MAR-2009 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
Oracle Server - Personal Edition - Version: 10.1.0.2 to 10.2.0.3
Oracle Server - Standard Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.
Symptoms
A full database export from a Oracle10g database aborts with:
...
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
EXP-00000: Export terminated unsuccessfully
If export was started with SYS schema, a table level export may also fail with:
...
Current user changed to TEST
. . exporting table DOC_ARCHIVE 16 rows exported
Current user changed to SYS
EXP-00011: SYS.; does not exist
Export terminated successfully with warnings.
Cause
One possible cause (note there might be others):
Script $ORACLE_HOME/rdbms/admin/catmeta.sql has been run recently.
There are several invalid SYS.KU$_% views in the dictionary:
-- invalid objects:
SET lines 120 pages 2000
COL status FOR a9
COL object_type FOR a20;
COL owner.object FOR a50
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
--------- ---------- --------------- --------------------------------
INVALID 7105 PACKAGE BODY SYS.DBMS_METADATA
INVALID 6683 VIEW SYS.KU$_10_1_COMMENT_VIEW
INVALID 6788 VIEW SYS.KU$_10_1_IND_STATS_VIEW
INVALID 6778 VIEW SYS.KU$_10_1_PIND_STATS_VIEW
INVALID 6752 VIEW SYS.KU$_10_1_PTAB_STATS_VIEW
INVALID 6770 VIEW SYS.KU$_10_1_SPIND_STATS_VIEW
INVALID 6748 VIEW SYS.KU$_10_1_TAB_ONLY_STATS_VIEW
... (etc)
A query in SQL*Plus on sys.ku$_xmlschema_view also fails with ORA-932:
SET lines 200 pages 2000
COL url FOR a60 WRA
SELECT url, local, stripped_val
FROM sys.ku$_xmlschema_view;
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
Solution
For reasons having to do with compatibility, the XDB objects cannot be created by the catproc.sql script. The script catproc.sql therefore calls the catmeta.sql script, which contains fake object views for XDB objects.
The real object views are defined in the catmetx.sql script (this script is invoked by catxdbv.sql which is invoked by catqm.sql).
Solution #1
Run following scripts while connected as SYS user:
> sqlplus /nolog
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> exit
Afterwards, re-run the export.
or:
Solution #2
Run the export with the Export DataPump client. E.g.:
> expdp system/manager directory=my_dir \
dumpfile=expdp_full.dmp logfile=expdp_full.log full=y
References
BUG:4668539 - (BUG)ORA-932 WHILE EXPORT
BUG:4685068 - EXPORT IS FAILING WITH ERROR ORA-00932 ON 10.2
**************************************************************************************************************************
No comments:
Post a Comment