oracle tips : DIAGNOSING AND RESOLVING ORA-04031 ERROR

===========================================================

DIAGNOSING AND RESOLVING ORA-04031 ERROR

===========================================================

作者: jametong(http://jametong.itpub.net)
发表于: 2005.04.26 07:41
分类: oracle refs
出处: http://jametong.itpub.net/post/5042/27713
---------------------------------------------------------------

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

Contents:

1. Instance parameters related with the Shared Pool
2. Diagnosing ORA-04031 errors
3. Resolving error ORA-04031

    * Known Oracle BUGs
    * ORA-4031 when compiling Java code
    * Small shared pool size
          o Library Cache Hit Ratio
          o Shared Pool Size Calculation
    * Shared Pool Fragmentation
          o V$SQLAREA View
          o X$KSMLRU View
          o X$KSMSP View (Similar to Heapdump Information

4. ORA-04031 error and Large Pool
5. ORA-04031 and Flushing the SHARED POOL
6. Advanced analysis to ORA-04031 error

DIAGNOSING AND RESOLVING ORA-04031 ERROR

When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned.

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

1. Instance parameters related with the Shared Pool

SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes and can accept

SHARED_POOL_RESERVED_SIZE - It specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid the occurrence of this error from situations where shared pool fragmentation forces Oracle to search for and free
chunks of unused pool to satisfy the current request.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. Since the operating system memory may constraint the size of the shared pool, in general, you should set this parameter to 10% of the SHARED_POOL_SIZE parameter.
SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parameter controls allocation of reserved memory. Memory allocation larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle server will allow fewer allocations from the reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and higer, but it can be found by executing the following SQL statement:

select nam.ksppinm NAME,
val.KSPPSTVL VALUE
from x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx
and nam.ksppinm like '%shared%'
order by 1;

10g Note: In Oracle 10g a new feature called "automatic memory management" allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool.
In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure.
Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Memory Management is enabled when the parameter SGA_TARGET is greater than zero and the current setting can be obtained quering the v$sga_dynamic_components view.

Please refer to the 10g Administration Manual for further reference.

2. Diagnosing error ORA-04031:

Note: Most common ORA-4031 occurrences are related to the SHARED POOL SIZE, therefore the diagnostic steps provided in this article will mostly address issues related to the shared pool. For other areas like large_pool or java_pool where the memory allocation algorith is simpler, normally the error is caused by an undersized structure.

ORA-04031 error can be due to either an inadequeate sizing of the SHARED POOL size or due to heavy fragmentation leading the database to not finding large enough chuncks of memory.

Inadequate Sizing: The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache by verifying the following:

REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
SHARED_POOL_RESERVED_MIN_ALLOC
or
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

If this is the case, consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to
allow the database putting more objects into the shared pool reserved space and then increase the
SHARED_POOL_SIZE if the problem is not resolved.

Fragmentation: If this is not the case, then you must determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by following this rule:

REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
SHARED_POOL_RESERVED_MIN_ALLOC.

To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower
the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the
available memory in the shared pool reserved space.

3. Resolving error ORA-04031:

Oracle BUGs

Oracle recommends to apply the latest patchser available for your platform. Most of the ORA-4031 errors related to BUGs can be avoided by applying these patchsets. The following table summarize the most common BUGs related with this error, possible workaround and the patchset that fixes the problem.

BUG	Description	Workaround	Fixed
<Bug:1397603>	ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles.	_db_handles_cached = 0	8172, 901
<Bug:1640583>	ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access	Not available	8171, 901
Bug:1318267 Not Public	INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031	_SQLEXEC_PROGRESSION_COST=0	8171, 8200
Bug:1193003 Not Public	Cursors may not be shared in 8.1 when they should be	Not available	8162, 8170, 901
<Bug:2104071>	ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS)	None-> This is known to affect the XML parser.	8174, 9013, 9201
<Note:263791.1>	Several number of BUGs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset		9205

ORA-4031 when compiling Java code:

A SQL exception occurred while compiling: : ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")

The solution is to shut down the database and set JAVA_POOL_SIZE to a larger value. The mention of "shared pool" in the error message is a misleading reference to running out of memory in the "Shared Global Area". It does not mean you should increase your SHARED_POOL_SIZE. Instead, you must increase your JAVA_POOL_SIZE, restart your server, and try again.

See <Bug:2736601>

Small shared pool size

In many cases, a small shared pool can be the cause of the ORA-04031 error.

The following information will help you to adjust the size of the shared
pool:

Library Cache Hit Ratio
The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce
the library cache misses by increasing the shared pool size.

Shared Pool Size Calculation

<Note:1012046.6>:
HOW TO CALCULATE YOUR SHARED POOL SIZE.

Shared Pool Fragmentation:

Every time a SQL or PL/SQL statement needs to be executed the
parse representation is loaded in the library cache requiring a specific
amount of free contiguous space. The first resource where the database
scans is the free memory available in the shared pool. Once the free memory
is exhausted, the database looks for reusing an already allocated piece
not in use. If a chunk with the exact size is not available, the scan continues
looking for space based on the following criteria:

- The chuck size is larger than the required size

- The space is contiguous

- The chuck is available (not in use)

Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.

When the shared pool is suffering fragmentation ORA-04031
errors (when the database cannot find a contiguous piece of free memory) may occur.
Also as a concequence , the allocation of a
piece of free space takes more time an the performance may be affected
(the "chunk allocation" is protected by a single latch called
"shared pool latch" which is held during the whole operation). However, ORA-4031
errors don't always affect the performance of the database.

See <Note:61623.1>:
for a detailed discussion on shared pool fragmentation.

If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a
result of dynamic sql fragmenting the shared pool. This can
be caused by:

o Not sharing SQL

o Making unnecessary parse
calls (soft)

o Not using bind variables

To reduce fragmentation you will need to address one or more of the
causes described before. In general to reduce fragmentation you must analyze
how the application is using the shared pool and maximize the use of sharable cursors.

Please refer to <Note:62143.1>,
which describes these options in greater detail. This note contains as well
further detail on how the shared pool works.

The following views will help you to identify non-sharable versions
of SQL/PLSQL text in the shared pool:

V$SQLAREA View

SELECT substr(sql_text,1,40) "SQL",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30

ORDER BY 2;

Note: The number "30" in the having section of the statement can be
adjusted as needed to get more detailed information.

X$KSMLRU View

If many objects are being periodically flushed from the shared pool
then this will cause response time problems and will likely cause library
cache latch contention problems when the objects are reloaded into the
shared pool.

One unusual thing about the x$ksmlru fixed table is that the contents
of the fixed table are erased whenever someone selects from the fixed table.
This is done since the fixed table stores only the largest allocations
that have occurred. The values are reset after being selected so that subsequent
large allocations can be noted even if they were not quite as large as
others that occurred previously. Because of this resetting, the output
of selecting from this table should be carefully kept since it cannot be
retrieved back after the query is issued.

To monitor this fixed table just run the following:

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

This view can only be queried by connected as the SYS.

X$KSMSP View (Similar to Heapdump Information)

select '0 (<140)' BUCKET, KSMCHCLS, 10*trunc(KSMCHSIZ/10) "From",

count(*) "Count" , max(KSMCHSIZ) "Biggest",

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"

from x$ksmsp

where KSMCHSIZ<140

and KSMCHCLS='free'

group by KSMCHCLS, 10*trunc(KSMCHSIZ/10)

UNION ALL

select '1 (140-267)' BUCKET, KSMCHCLS, 20*trunc(KSMCHSIZ/20) ,

count(*) , max(KSMCHSIZ) ,

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"

from x$ksmsp

where KSMCHSIZ between 140 and 267

and KSMCHCLS='free'

group by KSMCHCLS, 20*trunc(KSMCHSIZ/20)

UNION ALL

select '2 (268-523)' BUCKET, KSMCHCLS, 50*trunc(KSMCHSIZ/50) ,

count(*) , max(KSMCHSIZ) ,

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"

from x$ksmsp

where KSMCHSIZ between 268 and 523

and KSMCHCLS='free'

group by KSMCHCLS, 50*trunc(KSMCHSIZ/50)

UNION ALL

select '3-5 (524-4107)' BUCKET, KSMCHCLS, 500*trunc(KSMCHSIZ/500) ,

count(*) , max(KSMCHSIZ) ,

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"

from x$ksmsp

where KSMCHSIZ between 524 and 4107

and KSMCHCLS='free'

group by KSMCHCLS, 500*trunc(KSMCHSIZ/500)

UNION ALL

select '6+ (4108+)' BUCKET, KSMCHCLS, 1000*trunc(KSMCHSIZ/1000) ,

count(*) , max(KSMCHSIZ) ,

trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"

from x$ksmsp

where KSMCHSIZ >= 4108

and KSMCHCLS='free'

group by KSMCHCLS, 1000*trunc(KSMCHSIZ/1000);

Note: The information available in this view is the same that is generated as part of a HEAPDUMP level 2.

If the result of the above query shows that must of the space available is on the top part of the list (meaning available only in very small chuncks). It is very likely that the error is due to a heavy fragmentation.

4. ORA-04031 error and Large Pool

The Large pool is an optional memory area that can be configured to provide large memory allocations for one of the following operations :
session memory for the multi-threaded server and the Oracle XA interface.

The memory ( Buffers ) for Oracle backup and restore operations and for I/O server processes.

Parallel Execution messaging buffers.

The Large pool does not have a LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.
Chunks of memory are never aged out of the large pool,memory has to be explicitly allocated and freed by each session.
If there is no free memory left when a request is made then an ORA-4031 will be signalled similar to this :
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
Few things can be checked when this error occurs:
1- Check V$SGASTAT and see how much memory is used and free using the following SQL statement:

SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';

2- You can also take a heapdump level 32 to dump the large pool heap and check free chunks sizes.

Memory is allocated from the large pool in chunks of LARGE_POOL_MIN_ALLOC bytes to help avoid fragmentation. Any request to allocate a chunk size less LARGE_POOL_MIN_ALLOC will be allocated with size of LARGE_POOL_MIN_ALLOC. In general you may see more memory usage when using Large Pool compared to Shared Pool.
Usually to resolve an ORA-4031 in the large pool the LARGE_POOL_SIZE size must be increased.

5. ORA-04031 and SHARED POOL FLUSHING

There are several technics to increase cursor sharability so that shared pool fragmentation is reduce as well as likeability of ORA-4031 errors. The best way is by modifying the application to use bind variables. Another workaround when the application cannot be modified is using CURSOR_SHARING to a value different of FORCE (Be aware that this may cause changes in execution plan, so it is advisable to test the application first). When none of the above techniques can be used and fragmentation is considearble heavy in the system, flushing the shared pool might help alliviating the fragmentation. However some considerations must be taken into account:

Flushing the shared pool will cause that all the cursor that are not in use are removed from the library cache. Therefore just after the shared pool flusing is issued, most of the SQL and PL/SQL cursors will have to be hard parsed. This will increase the CPU usage of the system and will also increase the latch activity.

When applications don't use bind variables and have heavy possibilities of many users doing frequen similar operations (like in OLTP systems) it is common that soon after the flush is issued the fragmentation is back in place. So be advice that flushing the shared pool is not always the solution for a bad application.

For large shared pool flushing the shared pool may cause a halt of the system, specially when the instance is very active. It is recommended to flush the shared pool during off-peak hours.

6. Advanced analysis to ORA-04031error

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

Modify the init.ora paramater to add the following events to get a trace file with additional information about the problem:
event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 3"

Note: This parameter will take not effect unless the instance is bounced.

If the problem is reproducible, the event can be set at session level using the following statement before the execution of the faulty SQL statement:
SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 3';

This trace file should be sent to Oracle Support for troubleshooting.

Important Note: In Oracle 9.2.0.5 and Oracle 10g releases a trace file is generated BY DEFAULT every time an ORA-4031 error occurs, and can be located in the user_dump_dest directory. If your database version is one of these, you don't need to follow the steps described before to generate additional tracing.

RELATED DOCUMENTS

<Note:1012046.6> How to Calculate Your Shared Pool Size
<Note:62143.1> Understanding and Tuning the Shared Pool
<Note:1012049.6> Tuning Library Cache Latch Contention
<Note:61623.1> Resolving Shared Pool Fragmentation