70后，北漂一族，混迹于江湖之中，痛并快乐着！爱拼才会赢！

shared pool问题（ora-4031相关）

上一篇 / 下一篇 2009-12-07 16:22:37 / 个人分类：oracle

数据库出了ora-4031错误，原因是碎片过多。看了相关文档，总结如下：

无论何时，当oracle尝试在SHARED POOL中去申请一个大的连续的内存失败的时候，ORACLE首先会刷新池中所有当前没有用到的对象，并把返回的空闲内存的CHUNKS进行合并。如果仍然没有一个足够大的单个CHUNK来满足这个申请的话，就会返回ORA-04031错误。
注意：ORA-04031错误也有可能发生在ASM实例上

1. Instance parameters related with the Shared Pool

Before continuing, understanding the following instance parameters will be essential:

SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes and can accept a numerical values or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000"

注意：SHARED_POOL_SIZE的值不是通过1024计算出来的，而是通过除以1000计算出来的

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_SIZE，这个参数制定了保留池的大小，它是为那些比较大的连续内存申请准备的。
SHARED_POOL_RESERVED_MIN_ALLOC参数定义了使用SHARED_POOL和SHARED_RESERVED_POOL的分界线，大于这个参数的内存申请就会认为是大的申请请求，就会到SHARED_RESERVED_POOL中申请，否则就会到SHARED_POOL中去申请。
SHARED_POOL_RESERVED_POOL_SIZE的大小，经验值是SHARED_POOL_SIZE的10％。

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:

按照这里的意思，大的内存申请应该还是先到SHARED_POOL中去申请，如果SHARED_POOL中没有合适的CHUNK的时候，不会去释放不用的内存并合并出一个大的CHUNK，而是直接转而向SHARED_RESERVED_POOL来进行申请。

下面的查询可以得到当前的分界线参数SHARED_POOL_RESERVED_MIN_ALLOC的大小。
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;

注意，这个值在8I后就变成一个隐含参数了

在10G中，如果使用了ASMM的话，则当SHARED_POOL不够的时候，ORACLE会自动调整SHARED_POOL的大小来适应新的内存请求，很大程度上避免了ORA-04031的发生。

2. Diagnosing error ORA-04031:

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. NOTE: There have been many reports where the default size for shared_pool_size on an ASM instance is too small. If you experience ORA-04031 error on your ASM instance, increase the shared_pool_size parameter to 50M. If the problem persists, then increase the parameter again in increments of 10M until you stop seeing the error.
如果ASM实例中发现ORA-04031错误，则可以把SHARED_POOL_SIZE设置到50M，如果还发生错误，则每次增加10M上去，直到错误不发生为止。
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 from V$SHARED_POOL_RESERVED:

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

如果上面这个公式成立，则说明最后请求的大小都没有达到分界点的值就出现了错误，所以SHARED_RESERVED_POOL是没有被正确使用的，因此应该减小SHARED_POOL_RESERVED_MIN_ALLOC的值，使得这些相对比较大的内存申请到SHARED_RESERVED_POOL中去申请，如果问题还没有解决，则增加SHARED_POOL_SIZE。

NOTE: A bug was discoverd where LAST_FAILURE_SIZE can be wrong in cases where multiple pools are used. The value in LAST_FAILURE_SIZE can be a sum of failure sizes across all pools. This is fixed as of 9.2.0.7, 10.1.0.4, and 10.2.x.

注意：在很多平台上都有一个BUG，就是如果系统中使用了多个池，那么这多个池的失败可能都会算到SHARED_POOL的LAST_FAILURE_SIZE的头上去

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.

如果上面这个公式成立，则说明可能是因为保留池中的碎片太多了，这个时候可以考虑增加SHARED_POOL_RESERVED_MIN_ALLOC的大小，使得保留池中只保留相对较大的对象，而不是保留比较小的对象，如果这样还不解决问题，则可以考虑同时增加SHARED_POOL_SIZE和SHARED_RESERVED_POOL_SIZE的大小。

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.

Another consideration: - Pre-9i, changing OPTIMIZER_MAX_PERMUTATIONS to 2000 can reduce shared pool space pressure
在9I中，设置OPTIMIZER_MAX_PERMUTATIONS参数为2000可以减缓SHARED POOL的压力（网上查了下，因为9I中这个参数已经被设置为默认2000了，10G中都成隐含参数了，而且也是设置为2000的）。

optimizer_search_limit和OPTIMIZER_MAX_PERMUTATIONS参数说明：

通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的连接组合。例如：有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合，因此如果optimizer_search_limit等于5（默认值），则优化器将评估所有的120种可能。optimizer_search_limit参数也控制着调用带星号的连接提示的阀值。当查询中的表的数目比optimizer_search_limit小时，带星号的提示将被优先考虑。

初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限，且依赖于初始参数optimizer_search_limit。optimizer_max_permutations的默认值是80,000。
参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限：除非（表或组合数目）超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值，否则优化器将生成所有可能的连接组合。一旦优化器停止评估表的连接组合，它将选择成本最低的组合。

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 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
Bug 3910149 KGLHDDEP PROBLEM IN RAC
Slow SGA memory leak in internal permanent space (KGL handle). Backports are available on various platforms and release levels Restart problem node at intervals
(flushing shared pool doesn't
clear permanent structures. 9207, 10105, 10201
Note 263791.1 Several number of BUGs related
to ORA-4031 errors were fixed
in the 9.2.0.5 patchset N/A 9205

ORA-4031 when compiling Java code:

If you run out of memory while compiling a java code (within loadjava or deployejb), you should see an error:
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:

很多情况下，过小的SHARED_POOL大小是产生ORA-04031的原因，下面的信息将帮助我们来调整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 reused. The following SQL statement help you to calculate the library cache hit ratio:

SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
这个公式可以计算出CACHE HIT RATIO，如果命中率低于99％，那将需要增加SHARED_POOL的大小
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
To calculate the size of the shared pool that best fits to your current workload, please refer to:
Note 1012046.6: HOW TO CALCULATE YOUR SHARED POOL SIZE.

Shared Pool Fragmentation:
共享池的分裂：

每个SQL或者PL/SQL语句被执行的时候，LIBRARY CACHE中的解析器都需要请求一块特定的空闲连续的内存空间，首先数据库会来扫描共享池中的空闲内存，一旦空闲内存耗尽，数据库将会查找那些已经分配但是没有在使用中的内存并进行重用。如果没有合适的完全匹配的CHUNK大小，则扫描会按照下面的规则继续进行：
如果当前的CHUNK大小大于请求的大小
空间是连续的
CHUNK没有被使用
这个时候，CHUNK将会分裂，而剩下的空闲空间将会被加入到适当的空闲列表中去。如果数据库这样操作了一段时间后，共享池中就会有很多碎片了。

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.

如果共享池是因为碎片过多而导致的ORA-04031错误（也就是说数据库不能找到连续的共享内存片的时候）。另外，如果申请空闲内存消耗了比较长的时间，将会影响数据库的性能，因为CHUNK申请的整个过程中是通过一个单一的叫做SHARED POOL LATCH来实现内存保护的。
如果共享池足够大还产生这样的错误，则可能是以下的原因：

没有共享SQL、执行过多不必要的PARSE CALLS、没有使用绑定变量。

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:
下面这个sql将会帮助你找到不能共享池中不被共享的SQL/PLSQL

V$SQLAREA View
This view keeps information of every SQL statement and PL/SQL block executed in the database. The following SQL can show you statements with literal values or candidates to include bind variables:
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

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

X$KSMLRU View
There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation. 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.

这个表可以用来跟踪内存申请导致共享池中其他SQL被交换出去的问题。

这个鸟表与众不同的是当你查询这个表的时候，这个表中的内容将会被清除掉，一次性的。所以查询的时候要仔细考虑是否要关闭当前查询的窗口。

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)
Using this view you will be able to find out how the free space is currently allocated, which will be helpful to undrestand the level of fragmentation of the shared pool. As it was described before, the first place to find a chunck big enough for the cursor allocation is the free list. The following SQL shows the chunks available in the free list:

X$KSMSP有点象HEAPDUMP的信息。
使用这个视图可以得到当前分配了多少的空闲空间，这个对查找共享池的碎片等级很有帮助。

select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);

注意：这个视图产生的信息和2级的HEAPDUMP是相同的，不要频繁的做这个查询，因为可能会导致共享池的内存问题，而且在10G的HP平台上，会把数据库搞挂掉。
如果上面的查询返回有很多的可访问的空间位于列表的顶端（表示有很多非常小的可使用的CHUNK），那就很可能是碎片过多导致的错误。

也可以使用下面的查询来汇总SGA中的内存使用的情况：

SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;

CLASS NUM 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

如果FREE MEMORY小于5M，则需要增加共享池和保留池。
如果PERM持续不断的增长，则需要关注系统内存是否足够
如果FREEABL和RECR总是很大，则表明有很多没有释放的CURSOR
如果FREE很大但是还是有4031错误，则应该关注共享池的碎片

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 :
大池没有LRU列表，大池的CHUNK内存也永远不会被交换出去，内存被每个申请的SESSION自己释放

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';
可以使用上面的SQL来查看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.

也可以使用32级的HEAPDUMP来查看LARGE_POOL的HEAP和空闲CHUNKS的信息。LARGE_POOL_MIN_ALLOC参数用来避免LARGE_POOL的碎片，如果一个会话请求的CHUNK SIZE小于LARGE_POOL_MIN_ALLOC参数的大小，则实际会分配LARGE_POOL_MIN_ALLOC大小的内存，所以就是使用内存的部分牺牲来避免碎片的发生。

5. ORA-04031 and SHARED POOL FLUSHING

也可以通过设置CURSOR_SHARING参数来减少共享池碎片，从而避免ORA-04031错误，但是可能会引起执行计划的改变，并且这个烂东西远没有传说的那么神奇，BUG多多。
也可以通过刷新共享池来减少碎片，刷新共享池会导致所有不在使用中的CURSOR被从LIBRARY CACHE中交换出去，因此，刷新完了后，将会引起大量的SQL硬解析，这将消耗大量的CPU并增加LATCH的使用。而且刷新很大的共享池的话，可能会导致系统短暂的停顿，尤其是系统繁忙的时候。

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.
Starting with 9.2.0.5, instead of requesting heapdump level 1,2 , 3 or 32 you can use level those same levels plus (536870912).
This will generate the 5 largest subheaps AND the 5 largest heap areas within each of those.

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 536870915';

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.
如果以上的办法还都不能解决问题，则可以DUMP内存结构来查看问题发生的原因，不过在10G的版本中，发生ORA-04031错误时候的TRACE文件跟DUMP出来的信息是完全相同的，因此只要查询TRACE文件就可以定位问题了。

SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 536870915';