简单分析ORA-4031错误及解决方法

作者简介:种英明,ORACLE数据库工程师,就职于北京海天起点南京分公司,拥有多年数据库开发,管理,维护经验,获有ORACLE OCP和MySQL OCP证书,擅长ORACLE安装升级,故障处理以及sql优化,了解SQL Server,Sybase等多种数据库技术。

介绍

我们大家在数据库维护当中,偶尔会遇到ORA-4031这种错误,ORA-4031很容易导致数据库崩溃。当遇到这种错误时,应尽快采取措施,避免数据库核心进程因分配不到共享池内存而导致数据库宕掉。

成因

ORA-04031错误说明如下:

由上面的信息可以知道,4031错误原因是在共享池中需要的内存超过能够分配的内存大小。Oerr错误信息比较少,解释的并不详细。

共享池是由一个或多个堆(Heap)组成,堆是由区(Extent)组成,区是由块(Chunk)组成。有的Chunk可以包含子堆,子堆又划分区,区又划分chunk。如果不存在子池情况下,共享池就一个Heap。

chunk是最小的分配单位,大小不一致,用来存储执行计划等信息。

chunk有4种状态: 

1.free:空闲的chunk,由 free list 链表管理。

2.recr:recreatable,可重建的chunk,例如共享SQL语句 

3.freeable:可释放的chunk。 

4.perm:permanent,永久的chunk,实例运行期间这种chunk不会被覆盖。Oracle内部内存结构,x$表等信息。

如果是保留池chunk的状态,则多一个前缀”R_”。

chunk查找过程:

进程如果要获取一个chunk,首先在free list中查找合适大小的空闲chunk,如果空闲chunk比请求的大小还要大,则分割这个空闲的chunk,剩余的空间按照大小放置到对应的bucket中。

如果free list没有找到空闲的chunk,则开始找lru list链表,lru都是可重建的

chunk,如果也没有找到,则出现ORA-4031错误。ALERT错误日志如下:

但是alert日志里面提供的字节信息不准确,这是一种特殊的情况。举个例子,如果从子堆里无法获取200字节的chunk,这时需要从上一级的Heap中获取一个4096字节的chunk,但这时数据库共享池已经无法分配出空间,Alert日志会报”ORA-04031: 无法分配 200 字节的共享内存”,但实际数据库是因为无法分配4096字节的空间。

Oerr给出错误原因,一旦出现4031错误,一般都是下面三个原因,导致共享池无法收集更多的内存:

  1. 共享池内存太小。
  2. 共享池碎片太多。
  3. 共享池对象太多。

问题排查方法

检查共享池最多的组件

查看占用shared pool空间最大的组件:

当出现4031错误时,一般最常见的组件是SQL,cursor之类的,这类问题一般是由于硬解析造成的。

如果是硬解析过多,执行下面的查询:

判断共享池大小是否合适

刚开始建库时,建议设置成SGA的15%。

数据库运行一段时间之后,可以通过查询V$SHARED_POOL_ADVICE视图来判断共享池当前大小是否合适。

也可以通过查询x$kghlu表来判断:

共享池中的chunk是通过LRU链表来管理的,LRU链分为瞬时LRU, 周期LRU。当一个chunk第一次被使用,会放在瞬时LRU,第二次使用到,移至周期LRU。sql语句如下:

kghlutrn 瞬时LRU链中chunk数量

kghlurcr 周期LRU链中chunk数量

一般来说,瞬时LRU链中chunk数量,超过周期LRU 3倍,说明共享池太大了。

KGHLUFSH 是Flush Chunk的数量,主要是合并相邻的chunk

KGHLUOPS 是pins 和释放chunk的数量,也就是使用的chunk数量

Flush Chunk的数量和使用的chunk数量的比例大于1/20 ,也就是说chunk平均使用不到20次就Flush,这说明共享池太小了。

保留池:

首先说一下保留池,保留池的大小有个参数就是SHARED_POOL_RESERVED_SIZE缺省是SHARED_POOL_SIZE的5%

有一个隐藏参数_SHARED_POOL_RESERVED_MIN_ALLOC=4400,超过这个大小才被认为是大对象,只有符合申请>=4400字节的chunk才会去保留池上找空闲chunk.

查看保留池是否够用:

注意free_space剩余空间大小,如果request_failures这个参数大于零或持续增加,就说明保留池太小。

判断碎片是否太多

通过查询x$ksmsp表判断共享池的free memory情况:

再查询ksmchcls 为recr,freeabl类型的chunk,如果小的chunk剩余过多,那就是碎片引起的。在一个繁忙的数据库系统中,查询x$ksmsp表时候会比较慢,甚至会影响业务的运行。保证随时kill掉这个会话。

也可以通过dump共享池查看使用情况:

判断对象是否过多

根据x$kglob视图查询什么对象正在耗用内存:

可以根据这个语句判断对象是否太多,不过这个值要和数据库正常运行期间的值做对比才有意义。

其他

mos提供一个用于分析和解决ORA-4031错误的脚本:srdc_db_ora4031sp.sql,可以用来参考,文档 ID 1909791.1

解决方法

1. 如果共享池参数值太小,只能考虑调大SHARED_POOL_SIZE,SHARED_POOL_

RESERVED_SIZE参数值。

2. 硬解析过多的解决方法,就是共享SQL,比如说统一书写风格,使用绑定变量等,这个是开发人员编写程序之前就应该确定的。我们只能找出相关的sql,让开发人员修改代码,减少硬解析。尽量不要设置cursor_sharing=’force’强制使用绑定变量,这个可能会造成执行计划不准确,带来许多其他问题。

3. 共享池碎片有很多种原因,硬解析过多,参考上面的解决方法;oracle9i开始引入子池的,虽然可以减少Shared Pool Latch争用,会很容易导致共享池碎片,如果可以的话可以设置_kghdsidx_count参数为1,只用一个共享池。

4.KEEP方法:

针对那些经常加载的包,存储过程,函数等大对象,防止在载入内存时,没有足够大的内存,导致4031错误,因此需要把这些对象KEEP到内存。这个需要根据共享池大小,对象的数量和业务情况进行综合判断。

举例:

查询共享池中大于1M,加载次数大于100次,而且没被KEEP在内存中的对象:

将对象keep在共享池:

5.杀掉一部分会话

可以根据v$session视图machine字段,也就是服务器来区分,确定需要杀掉哪些用户会话,但是最好先杀掉长时间inactive的用户会话。这个方法很快能够解决问题,避免实例宕掉,但只是临时的解决办法。

6.刷新共享池

除了keep在共享池中的对象,会将shared pool中的其他对象清空,这个只是临时的解决办法,尽量不要采用。

7.如果遇到数据库的bug,需要打对应的补丁,具体可以参考文档 ID 4031.1。

未经允许不得转载:Oracle一体机用户组 » 简单分析ORA-4031错误及解决方法

相关推荐