ORA-04031的诊断和解决
【IT168 专稿】本文目的：提供一个使用简单，一步步的指导去解决ORA-04013错误。
当任何试图分配一个大的连续的共享池中的内存失败，Oracle首先会刷新掉共享池中所有目前不在使用的对象，然后使得内存块被合并在一起。如果内存块仍然不够满足需求，那么就会出现ORA- 04031错误。这些错误同样可能发生在ASM的实例中。默认的共享池的大小基本能够满足大部分的环境，但是如果遇到ORA-04031错误的时候可能就需要增大。
当遇到这个错误的时候，往往会出现如下的消息显示：
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\", \"% s\",\"%s\")"
准备工作：
要解决问题，首先需要了解一下跟共享池相关的实例参数：
a) SHARED_POOL_SIZE – 该参数指定共享池的大小，以字节为单位。
b) SHARED_POOL_RESERVED_SIZE – 该参数指定为大的连续的共享池内存请求预留的共享池的大小。该参数连 同 SHARED_POOL_RESERVED_MIN_ALLOC参数，可以用于避免消除由于共享池碎片太多导致迫使Oracle搜寻空闲的共享池空间而产生ORA-04031错误。
c) SHARED_POOL_RESERVED_MIN_ALLOC – 该参数用于控制预留内存的分配。大于该参数的内存分配可以使用预留内存的列表。其默认值适合绝大多数的系统，如果增加这个参数值，那么Oracle将允许分配越少的预留内存列表而更多的请求共享池列表。该参数在Oracle8i和之后的版本都是隐含参数，但是可以通过执行如下SQL查到：
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;
对于Oracle10g，具有自动内存管理的新特性，允许DBA保留用于分配给Oracle内存区域使用的共享内存。通常来说，当Oracle需要分配一个大对象到共享池中却无法找到一段连续可用的内存空间时，Oracle10g将会从其他SGA结构中使用自由内存来自动增大共享池的大小。自从10g数据库中内存空间分配被Oracle自动管理之后，出现ORA-04013错误的可能性大大降低了，当SGA_TARGET参数大于0的时候，就启用了自动内存管理特性，当前SGA的设置可以查询视图v$sga_dynamic_components。
诊断工作：
大部分的ORA-4031错误都和共享池大小有关，因此我们诊断这个错误一般都由共享池开始。当然，large_pool和java_pool的内存分配机制比较的简单，一般错误都是由于不够大引起的。由于设置共享池大小不合理或者是共享池碎片太多都会导致Oracle无法找到一个足够大的内存段来容纳数据对象从而产生ORA-04013错误。已经有很多的例子说明对于ASM实例，默认的共享池大小是远远不够的，如果 对于一个ASM实例出现这个错误，首先应该查看共享池大小，如果太小了，增大到不出现错误为止。
不适当的大小：首先决定出现ORA-04031错误的的原因是在library cache中缺乏连续的内存空间，可以通过查询视图 v$SHARED_POOL_RESERVED，满足如下的条件：
REQUEST_FAILURES > 0并且LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
REQUEST_FAILURES=0 并且LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
如果出现上述情况，可以认为是由于设置了较小的SHARED_POOL_RESERVED_MIN_ALLOC使得数据库把更多的对象放到共享池保留空间中，增大共享池大小可以解决这种问题的出现。
但是如果当使用了多个池的时候, LAST_FAILURE_SIZE会显示错误,会显示出所有池失败大小的总和,这个是个bug（3669074）,在Oracle的9.2.0.7,10.1.0.4和10.2.x版本中被修正。
共享池碎片：
如果是由于共享池碎片引起的ORA-04031错误，那么需要判断是由于library cache的碎片导致的还是共享池保留空间中的碎片太多导致的，可以用下面的规则来判定：
REQUEST_FAILURES>0 并且LAST_FAILURE_SIZE>SHARED_POOL_RESERVED_MIN_ALLOC
要解决这个问题，增大SHARED_POOL_RESERVED_MIN_ALLOC来减少对象被缓存到共享池保留空间中的数量并且增大SHARED_POOL_RESERVED_SIZE 和SHARED_POOL_SIZE来提高共享池保留空间中有更多可用的内存。
解决方法：
Oracle BUGs：Oracle强烈建议安装最新的数据库补丁，大部分的ORA-04031错误都和BUGs相关，安装最新的补丁可以避免这些错误。例如Bug 1397603, Bug 1640583,Bug 2104071,Bug 3910149。
当编译Java代码的时候出现ORA-04031错误：当编译java代码（包括loadjava或者deployjb）,内存消耗完全之后也会出现这个错误：
ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
针对这种错误的解决方案是关闭数据库，并且设置更大的JAVA_POOL_SIZE大小，错误中提及的共享池很容易误导认为是SGA内存消耗完全，此时不能去增大共享池大小，而应该是增大JAVA池的大小，然后重启数据库继续尝试。
过于小的共享池： 在大多的情况下，共享池过小能导致ORA-04031错误。下面两方面的信息可以帮助我们来调整共享池的大小：
Library Cache Hit Ratio：命中率可以帮助估算共享池的使用率，通过一个SQL/PLSQL语句需要被分析而不是被重复使用的次数，下面的SQL语句可以用来计算library cache命中率：
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
如果执行的未命中率多于1%，那么就很有必要增大共享池大小了。
共享池的碎片：
每次一个SQL或者PL/SQL需要被分析意味着载入library cache中的时候需要一定数量的连续自由空间，当自由 空间被消耗尽，数据库会寻找重新使用一个已经分配过的但却又不是正在使用的空间。如果需要空间大小不确定，寻找连续自由空间将基于下列的标准：
a) 块大小大于需要的大小
b) 块空间是连续的
c) 块当前没有在使用中那么这个块将被分割，并且剩余的自由空间被加入到正确的自由空间列表中。当数据库这么运行一段时期之后，共享池结构就被碎片化了。当共享池中碎片情况比较严重的时候，就会引起的ORA-04031错误，因此，以后每次分配自由空间的时间将会花费更多的时间（由共享池的latch来控制），导致整个数据库性能下降。
如果共享池本身已经设置的足够大，那么出现的大部分ORA-04031错误都是由于共享池中的动态SQL碎片引起的。
a) 没有共享的SQL
b) 执行了没有必要的软分析调用
c) 没有使用绑定变量
为了减少碎片的产生，那就必须注意前面引起碎片的三点原因，通常我们必须去分析应用是如何来使用共享池的以便能够最大化使用共享游标。
下面的几个视图可以帮助更好的找出在共享池中没有共享的SQL/PLSQL：
V$SQLAREA视图：
这个视图保存了每个SQL和PL/SQL执行的块，下面的SQL能够帮助找到有问题的语句：
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;
注：上面的30可以根据需要随便调整，这里只是个例子。
X$KSMLRU视图：
这个视图记录所有在共享池中由于分配自由空间而导致其他对象被刷新出共享池的信息。通常用来判定什么导致了大的自由空间分配。如果很多对象周期性的被刷新出共享池，那么会导致响应时间问题，例如libaray cache latch的竞争问题。
尤其需要注意的是当存储了最大的自由空间分配之后，任何时候查询这个视图都会删除这个视图中的内容，即使下一个分配的大的自由空间没有以前的大，这些被查询的值都会被重置，因此，查询这个表的结果应该被小心的保存下来。
监控这个视图只需要运行下面的SQL：(SYS用户)
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
X$KSMSP视图 ：
这个视图能够帮助找出当前的自由空间是如何分配的，能够更好的理解共享池碎片的程度。下面这个SQL能够找到所有在 free list上的可用块：
select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ 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*trun(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);
注：这个视图查询到的信息是使用HEAPDUMP level 2产生的信息的一部分。注意不要太频繁的运行这个SQL，容易导致共享池的其他内存问题出现。
如果上述查询的结果显示大部分的可用空间在列表上，那么产生ORA-04031的错误很可能就是由于共享池严重的碎片引起的。
这个视图还可以用来查看在SGA中的全部内存使用情况：
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;
KSMCHCLS值的说明：
R-free ：保留列表
R-freea：保留列表
Free：自由内存
freeabl ： 用户使用的内存/系统处理
perm ：分配给系统的内存
recr ：用户使用的内存/系统处理
a) 如果自由内存的大小过小（少于5M左右），那么应该增大shared_pool_size和 shared_pool_reserved_size的大小。
b) 如果perm连续增长，那么可能是出现了内存泄露。
c) 如果freeabl和recr一直都很大，那么意味着有很多的没有释放的游标信息存储。
d) 如果free非常大但是仍然出现ORA-04031错误，那么可以和共享池碎片联系起来处理。
ORA-04031错误和大池（Large Pool）
大池是一个可选的内存区域，能够为下列操作提供大内存分配：
a) 针对多线程服务器和OracleXA接口的会话内存。
b) 针对Oracle备份和恢复操作所需要的内存。
c) 并行执行的信息缓存。
大池没有LRU列表，它和共享池中的保留空间不太一样，大池中的内存块永远不会刷新出去针对每一个会话明确的分配和释放内存。
如果大池中没有自由内存空间，而又有请求，那么就会出现类似下面的ORA-04031错误。
ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame")
当出现上述错误的时候，可以查看如下几个方面：
a)检查V$SGASTAT看看多少的内存被使用和空闲：
SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';
b) 使用heapdump level 32来dump出大池heap，确定自由的块大小。
从大池中分配的内存如果是LARGE_POOL_MIN_ALLOC的块数倍可以避免产生碎片，任何请求要求分配的块大小如果小于 LARGE_POOL_MIN_ALLOC，那么将分配LARGE_POOL_MIN_ALLOC大小的内存。通常来说如果大池出现ORA-04031错误，增大LARGE_POOL_SIZE的大小都有助于消除这个错误。
针对ORA-04031错误的一些事件诊断方法：
如果上述的一些方法都无法解决出现的ORA-04031错误，那么就需要额外的分析来获得共享池的一个快照。修改init.ora参数，增加这么一个诊断事件来获得额外的问题信息：
event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 3"
这两个参数需要重启实例后才可以生效。
如果问题是可以重现的，这个事件可以被在会话级别上设置：
SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 536870915';
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-711695/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16381228/viewspace-711695/
4737
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
