典型案例：深入剖析 ORA-04031 的前世今生
李磊
云和恩墨技术专家
每一个接触过 Oracle 数据库的人想必听到 Ora-04031 都会有一种捶胸顿足的感觉，至少在两年前的我是这样子的。都说 Ora-04031 和 Ora-01555 等是 Oracle 的经典错误，之所以成为经典，可能就是因为它们会经常出现，却又不是那么好解决的缘故吧。今天我就跟大家分享一个我工作当中的4031案例，解读一下4031的前世今生，希望通过今天晚上的交流，当我们再次遇见4031错误时不再像之前那么恐惧。
本次跟大家分享的这个案例是去年我在某电力公司驻场的时候，某天下午刚回到住处就收到手机报警短信，说 pmdb1 库有4031报错（因为该客户使用了 zabbix 监控，所以手机可以收到数据库的所有警告和错误），于此同时开发人员打来电话说应用日志里面有4031错误，我立马赶回办公室，处理这个棘手的4031错误。
在继续下面的内容之前我先介绍一下本次案例中这套数据库的情况。这是一个运行在 AIX 上的一套双节点 RAC，数据库的版本为11.2.0.3.0，库的数据数据量不是很大，在80G左右，但是是一个非常重要的业务系统的中间库。
以我这几年的工作经验来说，当我们遇到 Oracle 报错时，通常都会从数据库的 alert 日志看起，结合与错误相关的 trace 文件以及发生错误时的一些系统状态、数据库状态等获取相关信息，综合上面的信息来判断该报错。本次对4031错误的分析也是一样。哪么我首先来查看了数据库的 alert 日志，果不其然，日志里面有大量的4031错误，记录如下所示：
经历过 Ora-04031 错误的 DBA 都知道，如果数据库遇到了4031错误，可能会导致很多会话都会抛出4031错误，严重的会导致整个数据库不能运行任何 SQL 语句，即使是一条非常简单的语句，更有甚者还会导致数据宕机。
而一提到数据库宕机相信可能会让很多 DBA 都胆战心惊，这里我先用一句话简单的总结一下4031错误的原因，让大家对4031先有一个初步的认识。当客户端的 SQL 操作被传送到oracle端，oracleserver 进程在处理客户端 SQL 请求时，首先需要向 SGA（注意，我这里说的是 SGA 而不是 share pool）申请内存，当 SGA 不能满足 Oracle server 进程请求内存的需求时就会发生4031错误。
我们知道 SGA 中有很多 pool，例如 share pool，java pool，large pool，stream pool 等，所以前面我所强调的“向 SGA 申请内存”，意思就是说4031错误会在上面的任何一个 pool 中发生。
回到上面的 alert 日志中关于4031报错的信息，这里它告诉了我们两个非常重要的信息，一个是发生错误的 trace 文件；本次案例中4031错误的 trace 文件为 /u01/oracle/app/oracle/admin/pmdb1 /bdump/pmdb1_ora_42338038.trc，另外一个是发生错误的信息；本案例中发生4031错误的信息为：ORA-04031: unable to allocate3896 bytes of shared memory ("shared pool","select /*+leading(CM_BUSI_M...","sga heap(2,0)","kglsim objectbatch")
4031错误的trace文件中记录了更加详细的关于4031错误的信息，而且里面也记录了发生4031错误时内存的状态信息。OK，哪我们就打开 /u01/oracle/app/oracle/admin/pmdb1/bdump/pmdb1_ora_42338038.trc 来看看。第一部分就是该数据库的环境信息：
通过 trace 文件也可以判断这是在运行在 AIX 上的一个 RAC 环境，数据库的版本为11.2.0.3.0，数据库的实例名为 pmdb1，发生错误的进程号为 42338038。
trace 文件的第二部分就是 4031 Diagnostic Information：
这里记录了关于4031错误更加详细的信息，但是关键信息和 alert 日志中记录的信息是一样的。ORA-04031: unable toallocate 3896 bytes of shared memory ("shared pool", "select /*+leading(CM_BUSI_M...", "sga heap(2,0)", "kglsim objectbatch")
好了，既然 ORA-04031:unable to allocate 3896 bytes of shared memory ("shared pool","select/*+ leading(CM_BUSI_M...","sga heap(2,0)","kglsim objectbatch") 是4031错误非常关键的信息，那我们就从开始看懂这个关键的报错信息开始。
“ORA-04031: unable to allocate 3896bytes of shared memory”，报错中提到了不能分配3896个 bytes 的共享内存，那么是从那个 pool 中分配呢？
OK，让我们往后面看，("sharedpool","select /*+ leading(CM_BUSI_M...","sgaheap(2,0)","kglsim object batch")，很清楚，这里说是从shared pool 中分配内存。
既然这里提到了 shared pool，这里就稍微的讲一下 shared pool 的组成，请看下图（该图来源于 concept）：
通过上图可以看出 shared pool 可以划分为以下几个部分：
（1） Library Cache
Library Cache 由 Shared Sql Area 和 Private Sql Area 组成。其中 Library cache 中主要存储的信息有解析后的 SQL 语句，SQL 执行计划和解析和编译后的 PL/SQL 代码单元；而Private SQL Area 只有在使用共享的服务连接方式并且配置 Large Pool 的时候才会在Library Cache 中分配，Private SQL Area 由 Persistent Area 和 Runtime Area 组成，它主要记录了会话相关的进程信息。
（2） Data Dictionary Cache
Data Dictionary Cache 主要缓存了数据字典的相关信息
（3） Server Result Cache
Server Result Cache 中主要缓存了部分的查询结果，这里请大家思考一下它和 buffer cache 的缓存有什么不同呢？
（4） Reserve pool
Reserve pool 是指 Oracle 在默认情况下，shared pool 中会配置一个保留区域，这个保留区域就是 reserved pool，它用做当在普通的 shared pool 列表中的空间不可用时来满足 large request 的内存分配请求。当一个内存请求大于隐含参数_shared_pool_reserved_min_alloc (默认：4400，可以修改) 时就是一个 large request，反之当内存请求小于 _shared_pool_reserved_min_alloc (默认：4400，可以修改)时就是一个small request. 另外关于 Reserved pool 还有两个参数需要关注一下，一个是shared_pool_reserved_size，另外一个是隐含参数 _shared_pool_reserved_pct (默认：5%)，通过 shared_pool_reserved_size 我们可以为 reserved pool 指定一个大小，也可以通过_shared_pool_reserved_pct 来为 shared pool 指定一个比例，如果这两个参数同时设置了，那么就会以 _shared_pool_reserved_pct 为准
（5） Other
上面 sharedpool 的这几个组成部分其中 Library Cache 也是结构是比较复杂的，关于 Library Cache 有三张非常经典的图，如下图所示：
图一：Hash Table
图二：Library CacheObject Handles
图三：Library CacheObject
这三张图在我们 enmo 的微信公众号中另外一位大神做了非常详细和明白的解释，大家可以参考【细致入微：Oracle 中执行计划在 Shared Pool 中的存储位置探秘】
http://dwz.cn/3PinUl，我在这里就不再赘述了。
下面我向大家分享另外一个知识点，在 Sahred pool 中是如何寻找可用的内存呢？下面这段伪代码是一个简化版的 sharedpool 中分配内存的过程：
解释一下上面的这段伪代码。当在 shared pool 中请求内存时，首先会搜索 shared pool 的 free list 中是否有足够的空间，如果有则使用，如果没有则判断此次内存请求是一次 large 请求还是一次 small 请求，若是 large 请求，则在 reserved pool 中查找是否有可用的空间，如果找到了可用的内存 (chunck) 则做size检查，并对内存 (chunck) 做截断操作，截取所需的内存大小使用，如果在 reserved pool 中依然没有找到可用的内存 (chunck)，则会再次到 shared pool 中去查找是否有可用的内存 (chunck)，如果找到了可用的内存 (chunck) 则做 size 检查，并对内存 (chunck) 做截断操作，截取所需的内存大小使用，如果依然没有找到，则对 reserved pool 中的对象做 LRU 算法操作，age out 一些 reserved pool 中的对象，来满足本次的内存 (chunck) 请求操作，如果还是没有找到可用的内存 (chunck)，则重复 LRU 算法的 age out 操作，直到找到可用内存 (chunck)；若是 small 请求，则在 sharedpool 的 free list 中查找是否有可用内存 (chunck)，如果找到了可用的内存 (chunck) 则做 size 检查，并对内存 (chunck) 做截断操作，截取所需的内存大小使用，如果没有找到，则对 sharedpool 中的对象做 LRU 算法的 age out 操作，并再次查找是否有可用的内存 (chunck)，如果找到了可用的内存 (chunck) 则做 size 检查，并对内存 (chunck) 做截断操作，截取所需的内存大小使用，如果没有找到则重复 LRU 算法的 age out 操作，直到找到可用内存 (chunck)。
为了看起来更加清楚，上面的过程我用一个流程图做示意：
通过上面这些我们知道了在 shared pool 中请求内存的过程。
有了上面这些基础知识，就可以对本案例的的报错信息可以进步一的解读为在 shared pool请求3968个 bytes 的内存，而且本次内存请求是一个 small request，不会在 reserved pool 中分配。
回到本次案例的4031报错信息中来，继续进一步解析本案例的报错：“("sharedpool", "select /*+ leading(CM_BUSI_M...", "sgaheap(2,0)", "kglsim object batch")”. “shared pool”已经解释过了，那么来看错误的下一个信息 “select /*+ leading(CM_BUSI_M...”. 这个就是导致4031错误的 SQL 语句，在 /u01/oracle/app/oracle/admin/pmdb1/bdump/pmdb1_ora_42338038.trc 文件会有这个语句的记录，来看看 trace 文件中关于这条语句的记录：
上面就是trc文件对于造成4031错误的 sql 语句的记录，这里记录了 library 句柄，sql 语句的id，hash 值，持有的 lock 和 pin 的类型，语句状态等等信息，而且下面还记录了该语句 child 的相关信息，因为 sql 语句的多 childcursor 问题也会引起4031错误，因此我们有时也需要关注下这里的信息。
从上面的 trace 文件中，可以看出本案例中该语句的 child 数为6，是一个相对比较小的值了。
继续解读本次案例的报错信息“("sharedpool","select /*+ leading (CM_BUSI_M...", "sgaheap(2,0)", "kglsim object batch")”，其中 sga heap(2,0)，这个给我透露了什么信息呢？要理解它，我们需要先看看共享池中 subpool 的演变过程：
Oracle 从9i开始为了提高 Oracle 的并发性，减少竞争，Oracle 将 shared pool 划分为多个 subpool，每一个 subpool 都拥有完全相同的内存结构和管理方式，以及自己的 free list，内存结构条目和 LRU list，这也是为什么有时我们在查看 heapdump 文件的时候会有多个 Free list 和 LRU list。
本次案例中有7个 shared pool（从/u01/oracle/app/oracle/admin/pmdb1 /bdump/pmdb1_ora_42338038.trc 中的 emory Utilization of Subpool 部分可以获取，内容比较长，我在这里就不贴了），这也是最多的 subpool 数量。在9i中每个 subpool 至少需要4个CPU（logical），128M内存，10g中每个 subpool 至少需要4个 CPU，256M 内存，11g中每个 subpool 至少需要4个 CPU，512M内存。当然了，subpool 的数量也是通过隐含参数_kghdsidx_count 来控制的，每次 instance 实例启动的时候，Oracle 根据 CPU 的个数和 MEMORY 的大小来初始化 _kghdsidx_count 的值。而 Oracle 从10g开始将每一个 subpool 又划分成4个更小的 pool，姑且记为 sub subpool 吧(这个叫法只是我这样叫，官方的叫法我没有查到，知道这个意思就行了)
如上图中，内部表 x$ksmsp 的 KSMCHIDX 就是 subpool 的编号（其编号为：1-7），KSMCHDUR 就是 subsubpool 的编号（其编号为：1-4），上面的 sgaheap(2,0) 就是告诉我们在2号 subpool 的第1（0-3）个 sub subpool，那么进一步的我们本次案例的报错信息可以解读为在 shared pool 的第2个 subpool 的第一个 sub subpool 中分配 3896 bytes 的内存失败。
接着继续解读本次案例的报错信息“("sharedpool", "select /*+ leading(CM_BUSI_M...", "sgaheap(2,0)", "kglsim object batch")”，下面我们关注的信息就是 “kglsimobject batch” 这个信息了。
我们说当一个内存块被分配到内存池中时，它会被赋予一个内存类型，shared pool 中有四种类型，分别为 free，freeabl，perm 和 recr。其中 free 是在 Free list 上可用的内存(chunck)，freeabl 是指挂载在 recr 下面的内存，他的分配的方式使得当内存处理结束时，用户可以显式的释放内存块这些内存（chunck），perm 是指在整个实例的整个生命周期中都存在，且不可让用户使用的内存（chunck），recr 是指挂载在 LRUlist 上的内存（chunck）。之后 shared pool 中的内存会被赋予一种池中的内存结构或者元素，例如"SQLA heap"。而上面的kglsim objectbatch就是 shared pool 中的一种内存结构或者称为元素。
那么本次案例中的4031报错信息就可以解读为在 shared pool 的第2个 subpool 中的第1个 sub subpool 中请求 3896bytes 的 kglsim object batch 的内存结构时失败，进而导致了4031错误。
既然通过上面的分析我们知道了本次案例是在 heap (2,0) 上分配内存失败，那么我们从 trc文件来看看第二个 subpool 内存情况（下面的内容有省略，只保留了 trc 文件中很少的一部分相关内容）：
通过查看 trc，可以看出占用内存比较高的内存结构为：SQLA，KGLHD，KKSSP，KGLH0，gcs resources，gcsshadows 等。注意：这些值所谓高的都只是一个相对值，并不是绝对的。
上面 trc 文件中的结果可能看起来比较晦涩难读，那么我们通过 sql 语句来查看一下 shared pool 中不同的内存结构占用空间的情况（只截取了我们关心的部分）：
从上面的结果可以看出每个 subpool 中的 perm 类型的内存基本上都是在第 一个 sub subpool 中分配的,其他的 sub subpool 中的 perm 类型的内存结构都非常小,而且是一个固定的值 (80bytes)，这就是说每个 heap (x,0) 中 perm 类型的内存结构占据了很大一部分的空间。
再来查询一下本案例中每个 sub pool 的空闲内存的情况（截取了部分结果）：
从结果可以看出，2号 subpool 的1号 subsubpool 中（ 即 heap(2,0)）最大的空闲可用的内存为2944个字节，因此要在其上分配3896个字节的内存必然会失败。如果我们细心点就会发现1号 subpool 的4号 subsubpool 中可用的最大空闲内存为15937536个字节，这就是说 heap(1,3) 是可以满足我们本次的内存请求，那么它为什么不在 heap(1,3) 中分配呢？这是因为使用子池会存在一个缺点：在有些情况下，个别子池被过分利用了。一旦子池选定，即使其他子池有合适的可用内存，内存块的搜索也可能失败。从 10g 开始，我们确实有这样的功能，允许当内存请求在选定的子池中无法满足时，“交换”到其他子池进行搜索，但这功能不可能对所有的内存结构和元素都起作用。注意：有一小部分功能会跨子池的利用内存块。换句话说，就是跨越多子池的条带化使用内存。这极少有文档记录，一般来说，内存请求会以轮转的方式，从一个“随机”的子池中找到它需要的内存块。
结合不同内存结构的内存在 shared pool 中的分配情况，以及上面提到的知识点，我们可以理解为什么我们经常会碰到 heap(x,0)中的 4031 错误,而很少碰见 heap(x,1-3) 中的 4031 报错，于此同时这也导致了 subpool 的不均衡使用。
根据上面的分析结果，我们可以确定本案例中造成4031错误的原因就是 subpool 的不均衡使用导致的。
针对本案例的解决4031错误的措施：因为本案例中的数据库版本为11.2.0.3.0的，也使用了 AMM，但是经过查询后得知，shared pool 和 data buffer cache pool 均设置了一个比较大的初始化值，这导致实际上可以动态分配的内存其实很少。因此可以将 shared_pool 的值在原来的基础上适当的调大些，并且禁用 AMM 或者 ASMM；另外可以通过设置 _enable_shared_pool_durations＝false 来改变 subpool 中内存分配的方式，或者完全使用 AMM。
到这里，我们本案例的4031错误基本上已经阐述清楚了。
下面我简单的总结一下发生4031错误的原因以及应对措施：
（1） 极高的硬解析，例如没有使用绑定变量。如果硬解析高的话，经过一段时间 shared pool 的碎片率会变得非常的高，在申请稍微大一点的 shared pool 内存时，虽然总体来说空闲的空间还有一些，但是并没有连续的较大空间可用，这就会造成4031错误。遇见这种情况我们就应该尽量减少硬解析的数量，例如使用绑定变量，使用 cursor_sharing＝FORCE 参数（不建议使用）。
（2） open_cursor 设置的过大。open_cursor 如果设置的过大，导致 library cache 中很多对象都处于 pin 状态，而不能释放，那么当申请 shared pool 内存时，通过 LRU 依然不能找到可用空间，就会导致4031错误。遇见这种情况我们可以适当减少 open_cursor 的值。
（3） shared pool 确实太小。有时候我们的 shared pool 相对于数据库的压力来讲确实 shared pool，这个时候就需要增加 shared pool（SGA）的大小。
（4） subpool 的不均衡使用。Subpool 的不均衡使用是使用 subpool 一个缺点之一，对于这种情况我们可以使用设置隐含参数 _enable_shared_pool_durations＝false 来改变shared pool 的 subpool 内存结构的分配方式，或者完全使用 AMM.
（5） Oracle Bug，Oracle 的与4031相关的 BUG 有很多，这个我们可以多关注 MOS 上的相关知识。
好了，关于4031的错误我就跟大家分享到这里。
最后，我提出一个问题希望大家有时间思考一下。我们知道 SGA 中 data buffer cache 是通过标准化的 buffer 来管理的，而 shared pool 是通过 heap 的方式来管理，这也是造成为什么与 shared pool 相关的很多东西都不是很好理解，比如说4031错误，那么我的问题是：为什么 Oracle 不用管理 data buffer cache 的方式来管理 shared pool，而是通过 heap 的方式来管理呢？
搜索盖国强（Eygle）微信号：eeygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
近期文章