ORA04031故障分析处理

ORA-04031故障分析处理当任何试图分配一个大的连续的共享池中的内存失败，Oracle首先会刷新掉共享池中所有目前不在使用的对象，然后使得内存块被合并在一起如果内存块仍然不够满足需求，那么就会出现ORA-04031错误这些错误同样可能发生在ASM的实例中默认的共享池的大小基本能够满足大部分的环境，但是如果遇到ORA-04031错误的时候可能就需要增大当遇到这个错误的时候，往往会出现如下的消息显示：04031,00000,"unabletoallocate%sbytesofsharedmemory("%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查到：selectnam.ksppinmNAME,val.KSPPSTVLVALUEfromx$ksppinam,x$ksppsvvalwherenam.indx=val.indxandnam.ksppinmlike'%shared%'orderby1;对于Oracle10g，具有一个新特性自动内存管理，允许dba保留用于分配给Oracle内存区域使用的共享内存通常来说，当Oracle需要分配一个大对象到共享池中却无法找到一段连续可用的内存空间时候，Oracle10g将会从其他SGA结构中使用自由内存来自动增大共享池的大小自从10g数据库中内存空间分配被Oracle自动管理之后，出现ORA-04013错误的可能性大大降低了，当SGA_TARGET参数大于0的时候，就启用了自动内存管理特性,当前SGA的设置可以查询视图v$sga_dynamic_components。

现在可以开始诊断ORA-04013错误了大部分的ORA-4031错误都和共享池大小有关，因此我们诊断这个错误一般都由共享池开始当然，large_pool和java_pool的内存分配机制比较的简单，一般错误都是由于不够大引起的由于设置共享池大小不合理或者是共享池碎片太多都会导致Oracle无法找到一个足够大的内存段来容纳数据对象从而产生ORA-04013错误已经有很多的例子说明对于ASM实例，默认的共享池大小是远远不够的，如果对于一个ASM实例出现这个错误，首先应该查看共享池大小，如果太小了，增大到不出现错误为止不适当的大小：首先决定出现ORA-04031错误的的原因是在librarycache中缺乏连续的内存空间，可以通过查询视图v$SHARED_POOL_RESERVED，满足如下的条件：REQUEST_FAILURES>0并且LAST_FAILURE_SIZE

但是如果当使用了多个池的时候,LAST_FAILURE_SIZE会显示错误，会显示出所有池失败大小的总和，这个是个bug(3669074),在Oracle的9.2.0.7,10.1.0.4和10.2.x版本中被修正共享池碎片：如果是由于共享池碎片引起的ORA-04031错误，那么需要判断是由于librarycache的碎片导致的还是共享池保留空间中的碎片太多导致的，可以用下面的规则来判定：REQUEST_FAILURES>0并且LAST_FAILURE_SIZE>SHARED_POOL_RESERVED_MIN_ALLOC要解决这个问题，增大SHARED_POOL_RESERVED_MIN_ALLOC来减少对象被缓存到共享池保留空间中的数量并且增大SHARED_POOL_RESERVED_SIZE和SHARED_POOL_SIZE来提高共享池保留空间中有更多可用的内存现在看看如何来解决ORA-04031的错误OracleBUGs：Oracle强烈建议安装最新的数据库补丁，大部分的ORA-04031错误都和BUGs相关，安装最新的补丁可以避免这些错误例如Bug1397603,Bug1640583,Bug2104071,Bug3910149当编译Java代码的时候出现ORA-04031错误：当编译java代码(包括loadjava或者deployjb),内存消耗完全之后也会出现这个错误：ORA-04031:unabletoallocatebytesofsharedmemory("sharedpool","unknownobject","joxlod:inith","JOX:ioc_allocate_pal")针对这种错误的解决方案是关闭数据库，并且设置更大的JAVA_POOL_SIZE大小，错误中提及的共享池很容易误导认为是SGA内存消耗完全，此时不能去增大共享池大小，而应该是增大JAVA池的大小，然后重启数据库继续尝试。

过于小的共享池：在大多的情况下，共享池过小能导致ORA-04031错误下面两方面的信息可以帮助我们来调整共享池的大小：LibraryCacheHitRatio：命中率可以帮助估算共享池的使用率，通过一个SQL/PLSQL语句需要被分析而不是被重复使用的次数，下面的SQL语句可以用来计算librarycache命中率：SELECTSUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHEMISSESWHILEEXECUTING"FROMV$LIBRARYCACHE;如果执行的未命中率多于1%，那么就很有必要增大共享池大小了共享池的碎片：每次一个SQL或者PL/SQL需要被分析意味着载入librarycache中的时候需要一定数量的连续自由空间，当自由空间被消耗尽，数据库会寻找重新使用一个已经分配过的但却又不是正在使用的空间如果需要空间大小不确定，寻找连续自由空间将基于下列的标准：a) 块大小大于需要的大小b) 块空间是连续的c) 块当前没有在使用中那么这个块将被分割，并且剩余的自由空间被加入到正确的自由空间列表中当数据库这么运行一段时期之后，共享池结构就被碎片化了当共享池中碎片情况比较严重的时候，就会引起的ORA-04031错误，因此，以后每次分配自由空间的时间将会花费更多的时间(由共享池的latch来控制)，导致整个数据库性能下降。

如果共享池本身已经设置的足够大，那么出现的大部分ORA-04031错误都是由于共享池中的动态SQL碎片引起的a) 没有共享的SQLb) 执行了没有必要的软分析调用c) 没有使用绑定变量为了减少碎片的产生，那就必须注意前面引起碎片的三点原因，通常我们必须去分析应用是如何来使用共享池的以便能够最大化使用共享游标下面的几个视图可以帮助更好的找出在共享池中没有共享的SQL/PLSQL：V$SQLAREA视图：这个视图保存了每个SQL和PL/SQL执行的块，下面的SQL能够帮助找到有问题的语句：SELECTsubstr(sql_text,1,40)"SQL",count(*),sum(executions)"TotExecs"FROMv$sqlareaWHEREexecutions<5GROUPBYsubstr(sql_text,1,40)HAVINGcount(*)>30ORDERBY2;注：上面的30可以根据需要随便调整，这里只是个例子X$KSMLRU视图：这个视图记录所有在共享池中由于分配自由空间而导致其他对象被刷新出共享池的信息通常用来判定什么导致了大的自由空间分配如果很多对象周期性的被刷新出共享池，那么会导致响应时间问题，例如libaraycachelatch的竞争问题。

尤其需要注意的是当存储了最大的自由空间分配之后，任何时候查询这个视图都会删除这个视图中的内容，即使下一个分配的大的自由空间没有以前的大，这些被查询的值都会被重置，因此，查询这个表的结果应该被小心的保存下来监控这个视图只需要运行下面的SQL：(SYS用户)SELECT*FROMX$KSMLRUWHEREksmlrsiz>0;X$KSMSP视图：这个视图能够帮助找出当前的自由空间是如何分配的，能够更好的理解共享池碎片的程度下面这个SQL能够找到所有在freelist上的可用块：select'0(<140)'BUCKET,KSMCHCLS,KSMCHIDX,10*trunc(KSMCHSIZ/10)"From",count(*)"Count",max(KSMCHSIZ)"Biggest",trunc(avg(KSMCHSIZ))"AvgSize",trunc(sum(KSMCHSIZ))"Total"fromx$ksmspwhereKSMCHSIZ<140andKSMCHCLS='free'groupbyKSMCHCLS,KSMCHIDX,10*trunc(KSMCHSIZ/10)UNIONALLselect'1(140-267)'BUCKET,KSMCHCLS,KSMCHIDX,20*trunc(KSMCHSIZ/20),count(*),max(KSMCHSIZ),trunc(avg(KSMCHSIZ))"AvgSize",trunc(sum(KSMCHSIZ))"Total"fromx$ksmspwhereKSMCHSIZbetween140and267andKSMCHCLS='free'groupbyKSMCHCLS,KSMCHIDX,20*trunc(KSMCHSIZ/20)UNIONALLselect'2(268-523)'BUCKET,KSMCHCLS,KSMCHIDX,50*trunc(KSMCHSIZ/50),count(*),max(KSMCHSIZ),trunc(avg(KSMCHSIZ))"AvgSize",trunc(sum(KSMCHSIZ))"Total"fromx$ksmspwhereKSMCHSIZbetween268and523andKSMCHCLS='free'groupbyKSMCHCLS,KSMCHIDX,50*trunc(KSMCHSIZ/50)UNIONALLselect'3-5(524-4107)'BUCKET,KSMCHCLS,KSMCHIDX,500*trunc(KSMCHSIZ/500),count(*),max(KSMCHSIZ),trunc(avg(KSMCHSIZ))"AvgSize",trunc(sum(KSMCHSIZ))"Total"fromx$ksmspwhereKSMCHSIZbetween524and4107andKSMCHCLS='free'groupbyKSMCHCLS,KSMCHIDX,500*trunc(KSMCHSIZ/500)UNIONALLselect'6+(4108+)'BUCKET,KSMCHCLS,KSMCHIDX,1000*trunc(KSMCHSIZ/1000),count(*),max(KSMCHSIZ),trunc(avg(KSMCHSIZ))"AvgSize",trunc(sum(KSMCHSIZ))"Total"fromx$ksmspwhereKSMCHSIZ>=4108andKSMCHCLS='free'groupbyKSMCHCLS,KSMCHIDX,1000*trunc(KSMCHSIZ/1000);注：这个视图查询到的信息是使用HEAPDUMPlevel2产生的信息的一部分。

注意不要太频繁的运行这个SQL,容易导致共享池的其他内存问题出现如果上述查询的结果显示大部分的可用空间在列表上，那么产生ORA-04031的错误很可能就是由于共享池严重的碎片引起的这个视图还可以用来查看在SGA中的全部内存使用情况：SELECTKSMCHCLSCLASS,COUNT(KSMCHCLS)NUM,SUM(KSMCHSIZ)SIZ,To_char(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k'"AVGSIZE"FROMX$KSMSPGROUPBYKSMCHCLS;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错误和大池(LargePool)大池是一个可选的内存区域，能够为下列操作提供大内存分配：a) 针对多线程服务器和OracleXA接口的会话内存b) 针对Oracle备份和恢复操作所需要的内存c) 并行执行的信息缓存大池没有LRU列表，它和共享池中的保留空间不太一样，大池中的内存块永远不会刷新出去，针对每一个会话明确的分配和释放内存如果大池中没有自由内存空间，而又有请求，那么就会出现类似下面的ORA-04031错误ORA-04031:unabletoallocateXXXXbytesofsharedmemory("largepool","unknownobject","sessionheap","frame")当出现上述错误的时候，可以查看如下几个方面：a) 检查V$SGASTAT看看多少的内存被使用和空闲：SELECTpool,name,bytesFROMv$sgastatwherepool='largepool';b) 使用heapdumplevel32来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="4031tracenameerrorstacklevel3"event="4031tracenameHEAPDUMPlevel3"这两个参数需要重启实例后才可以生效如果问题是可以重现的，这个事件可以被在会话级别上设置：SQL>altersessionsetevents'4031tracenameerrorstacklevel3';SQL>altersessionsetevents'4031tracenameHEAPDUMPlevel536870915';。