ORA-04031错误
ORA-4031 错误故障排除与诊断[视频] (文档 ID 2016002.1)、
Troubleshooting and Diagnosing ORA-4031 Error [Video] (文档 ID 396940.1)
文档内容
| 用途 |
| 常见 Bug |
| 问题和答案 |
| 在 SGA 池中内存是如何分配和释放的？ |
| 什么是子池（Subpools）？ |
| 什么是保留区域? |
| SGA 中的各池的作用是什么？ |
| ORA-4031 错误是什么？ |
| 我的保留区域大小是否合适？ |
| 是否有办法找到一个共享池的“正确”大小？ |
| 我的 SGA 中有多少空闲内存可用？ |
| 10g 的 ASMM 和/或 11gAMM 自动化地管理了什么？ |
| 我默认有多少子池？ |
| 我应当如何控制使用的子池的数量？ |
| 是否所有的 ora-4031 错误都记录在 alert log 里？ |
| 我们应当如何查看 V$SGASTAT 中的"miscellaneous"结构的分解数据？ |
| 什么数据库参数与 ORA-04031 问题有关？ |
| 我们应该查看 RDA 中的哪些信息来帮助诊断 4031 错误？ |
| 在默认的 4031 追踪文件中有哪些信息是相关的？ |
| 在 Statspack/AWR 报告中有哪些信息是与 4031 错误相关的？ |
| 我们如何确定是否有应用的问题导致了这个错误？ |
| 没有使用绑定变量 |
| 多个子游标 |
| 高解析率 |
| 参考 |
适用于:
Oracle Database - Enterprise Edition - 版本 8.1.5.0 和更高版本本文档所含信息适用于所有平台
用途
这篇文档用于
- 帮助读者理解 ora-4031 错误的原因
- 收集需要的诊断信息来缩小错误的范围
- 回答一些关于 ora-4031 的常见问题
上次更新于 2010年6月
视频 - FAQ for ORA-4031 Errors (08:00)
如果你想要进一步探索这个错误，请加入社区讨论 ' Diagnosing and Resolving ORA-4031 errors '。这里你可以提出问题，从其他人那里获取帮助，并且分享你对这篇文档的经验。
常见 Bug
Note: 4031.1 提供了可按照版本查询的最新的 Bug 信息。
注意：下面所引用的脚本名字是封装的脚本，并且可以在 Note 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts 中找到。查看文章中标记为 'Software Requirements/Prerequisites' 的部分。您可以点击 'Click here' 链接下载 zip 文件。这些脚本随时更新，请每次都确认你下载到最新版本的脚本。
问题和答案
在 SGA 池中内存是如何分配和释放的？
SGA 是由固定的区域组成的，例如 Log Buffers 和 Buffer Cache(s)，以及内存池（Shared Pool，Large Pool，Java Pool，从 10g 开始还有 Streams Pool）。这些不同的池中分配的内存被划分为 heap，这些 heap 又可以由一个或者多个 subheap 内存结构所组成。
这类似于一个表空间中的很多段。表空间是 heap 而段是 subheap。段中的 extent 类似于在“父” subheap 中的各种 subheap。在共享内存区域中的目标与表空间是相同的 - 避免碎片。为了实现这点，我们分配的内存块要能够最好的匹配请求的内存，在需要时合并邻近的空余空间，并且探测可以释放和重用的内存。
在这些池中,我们使用空闲列表 buckets。他们是空闲列表的结构体，并且每一个列表对应一个特定的大小。Oracle 以二进制搜索空闲列表的大小，以找到合适的空闲列表。返回大于或者等于要求的大小的第一个 bucket。数据库启动时，在每个池中创建了不同大小的内存块。我们会继续遍历空闲列表，直到我们找到一个 bucket 指向一个足够大的内存区间。共享池会利用 Least Recently Used (LRU)算法来“老化”长久未用的内存结构。
要更好的理解这个问题，请看如下的 heapdump 信息，这是一个空闲列表 bucket 的总结：
Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
这里显示 bucket1 有 443 个内存块（chunk），其中最大的是 40byte，平均是 40byte。Bucket 2 这个空闲列表中内存块的大小是 40 到 48byte 之间。当一块空间被释放时，它会被添加到小于或等于块大小的 bucket 里。如果你发现共享池中的可用内存越来越小，而不是有大块和小块内存的混合，这可能意味着应用导致了碎片，而且更有可能的，在未来，某一个大的内存请求会因 ora-4031 错误而失败。 参考我们如何确定是否有应用的问题导致了这个错误？, 其中解释了如何追踪低效的代码。
共享池和大池划分他们的内存区域到子池中（从 9i 开始）。每一个子池会有空闲列表 Bucket，包含指向这个子池内部的内存块的指针。其它的池则没有子池，只有一个大的内存区域，和一个上述的空闲列表。
当一个内存块被分配到内存池中时，它会被赋予一个内存类型，包括 PERMANENT，FREEABLE 或者 RECREATABLE。这些内存块之后会被赋予一种池中的内存结构或者元素。例如"KGLS heap"。这些内存结构或元素并不总是会在数据字典中被追踪或者注释（特别是 PERMANENT 类型的那些）。
块的类型：
Normal (freeable) chunks – 这些块所分配的方式使得当内存处理结束时，用户可以显式的释放内存块。
Free chunks – 这些块是空闲的，当有小于或等于块大小的请求进入池中时，就可以重用。
Recreatable chunks – 这是一种特殊形式的"freeable"内存。当它们没有被 pin 的时候，这些块放在 LRU 列表里。如果需要内存，我们会到 LRU 列表内释放一段时间没有使用的“Recreatable”内存。
Permanent chunks – 这些块以不同的方式分配。一些块分配后会在实例的整个生命周期中存在。一些块分配后只要可用，会被内部反反复复的使用。
通过设置事件，可以把 permanent 块标注为其它类型的内存块来进行调试。
什么是子池（Subpools）？
在 Oracle 9i 和之后的版本，共享池可以被划分为子池。每个子池是一个小号的共享池，有它自己的空闲列表，内存结构条目，和LRU列表。这是一个对共享池和大池的可扩展性的改变，现在每一个子池都由一个 child latch 来保护，因此可以增加这些池的吞吐量。这意味着不再有之前版本的对于共享池和大池的单独 latch 的竞争。共享池中的保留区域也被平均的划分到每个子池中。
当你遇到 ora-04031 时，trace 会显示错误发生在哪个子池中。
例如：
ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","SELECT /*+ FIRST_ROWS */ * F...","sql area (6,0)","kafco : qkacol"):4031:375:2008:ocicon.c
这个例子中，错误发生在第六个子池中。
使用子池的缺点是，有些情况下，个别子池被过分利用了。一旦子池选定，即使其他子池有合适的可用内存，内存块的搜索也可能失败。从 10g 开始，我们确实有这样的功能，允许当内存请求在选定的子池中无法满足时，“交换”到其他子池进行搜索，但这功能不可能对所有的内存结构和元素都起作用。
注意：有一小部分功能会跨子池的利用内存块。换句话说，就是跨越多子池的条带化使用内存。
这极少有文档记录，一般来说，内存请求会以轮转的方式，从一个“随机”的子池中找到它需要的内存块。
不平衡的使用子池会导致 ORA-04031。常见的是在"session param values"内存结构上的内存分配失败。在 9i 及更高版本，需要为每个配置了的 process 分配内存来存储动态参数。在启动时，会选中一个子池来管理所有的"session param values"条目。如果 PROCESSES 参数设置的非常高，并且又没有非常高的并发连接，这可能导致在子池中不必要的永久内存分配，并且可能引发 ORA-04031 问题。一般来说，拥有多个共享池 latch 所带来的性能上的改善要超过由于过度利用子池而可能带来的问题。
终端用户是看不到子池的。他们被隐藏在共享池和大池的使用下。注意：如果共享池使用子池，只要 LARGE_POOL_SIZE>0，就会自动的在大池中创建子池。
参考（这个问题看上去影响了很多 Bug）：
Bug 4184298 - Subpool imbalance for "session parameters"
什么是保留区域?
数据字典缓存或者库缓存上未命中的代价要比 Buffer Cache 上的未命中更高。由于这个原因，共享池应该设计的足够大，能够保证经常用的数据都被缓存起来。如果在共享池里没有足够的空余空间，那么 Oracle 必须搜索和释放足够的内存来满足这个请求。这个操作会毫无疑问的占有 latch 资源比较长的时间，导致对其它并发的内存分配的尝试造成影响。大的空间分配请求失败的代价可能是非常昂贵的，因为他可能导致很多小一些的内存片被从 Shared Pool 内存中清理出去，来为大的空间请求让出位置。
默认的，Oracle 在共享池中配置一个小的保留池（或保留区域）。这块内存可以在普通的共享池列表中的空间不可用时，被用来满足大的连续的分配请求。
典型的大的分配请求操作有，PL/SQL，触发器编译，或者加载 Java 对象。当保留池中分配的内存被释放后，它会交还给保留池。
可以放置在保留池中的内存分配的最小大小通过隐含参数 _shared_pool_reserved_min_alloc 来控制。在近期版本上默认设置是 4400byte，可以被最低设置到 4000byte。
如果从普通共享池空闲列表上无法找到足够大小的内存块，那么大于 _shared_pool_reserved_min_alloc 的内存分配请求可以从保留区域的列表中分配。
如果 ORA-4031 错误消息中指出的失败大小或者 V$SHARED_POOL_RESERVED 中的 LAST_FAILURE_SIZE 列的大小大于 _shared_pool_reserved_min_alloc，这意味着需要增大保留区域来适应这些大的内存请求。
保留池大小可以通过指定 shared_pool_reserved_size 来设置为一个固定的大小，也可以指定隐含参数 _shared_pool_reserved_pct 来指定一个比例。如果同时设置了两个参数，以隐藏参数 _shared_pool_reserved_pct 为准。
在一些应用环境，5% 太小了。如果一个 ORA-4031 问题指向一个很大的内存请求失败，那么通过手动设置 SHARED_POOL_RESERVED_SIZE 来增大保留区域大小，或者如果使用了 SGA_TARGET>0，改变隐含参数 _shared_pool_reserved_pct 到 10 或者 15（参考下例）来确保当共享池增长或者收缩时，保留区域也会同样变化。
视图 V$SHARED_POOL_RESERVED 可以被用来确定失败的请求大小，和共享池保留区域利用率。Document: 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts 包含一个脚本来查询这个视图。
警告：
如果你决定使用 SHARED_POOL_RESERVED_SIZE 参数并且 SGA_TARGET>0，你可能会在大的内存请求上遇到 ORA-4031 的情况，因为 MMAN 进程可能会一直增大共享池大小，而保留区域大小是恒定的。因此，保留区域在开始时可能是共享池的 10%，但是随时间变化，可能最终变成 2% 或者 3%（相比新的共享池大小，可能比期望的小很多）。
保留区域处理大些的内存请求，试图减少共享池随时间的推移产生碎片的几率。
或者在 pfile 中增加
对于大的分配，Oracle 尝试按照如下顺序在共享池中分配空间
1. 从共享池的非保留区域。
2. 从保留池。如果在共享池的非保留区域没有足够的空间，那么 Oracle 检查是否在保留区域有足够的空间。
3. 如果在共享池的非保留区域和保留区域都没有足够的空间，那么 Oracle 尝试去释放足够的空间来分配。接下来重试共享池中的非保留区域和保留区域。这些小规模的清理会从 LRU 列表中清除一段时间没用过的 RECREATABLE/FREEABLE 的内存块。
SGA 中的各池的作用是什么？
SGA 中的共享池部分包含了库缓存，数据字典缓存，并行执行消息的 Buffer 和控制结构。库缓存包括共享 SQL 区域，私有 SQL 区域（如果是共享服务器配置的话），PL/SQL 过程和包，以及控制结构比如 LOCK 和库缓存句柄。当 SQL 语句被解析时，我们从共享池中分配内存，来存储到共享的 SQL area。内存的大小依赖于语句的复杂性。理想情况下，共享池应被用来缓存共享 SQL，并且避免由于收缩共享 SQL 缓存导致的性能开销。
Oracle 的很多特性，比如 Recovery Manager (RMAN)，并行处理 I/O slave 进程，和共享服务器的设计中都会用到大的共享内存块。这些特性会给共享池带来不必要的压力，因此我们推荐使用 LARGE_POOL_SIZE 定义一个大池，或者使用 SGA_TARGE 来减少共享池在这些场景下的内存压力。
JAVA Pool 是被用来分配内存给 JVM 中和会话相关的 Java 代码和数据。取决于 Oracle 服务器的运行模式，Java Pool 的内存以多种不同方式利用。
如果使用 Streams 功能，你可以配置 Steams Pool 来管理此功能需要的内存分配。
共享池使用一个与 Buffer Cache 类似的 LRU 算法。因此，调节共享池比其它池更加复杂。大多数时候，如果 ora-4031 发生在其它的内存池，这意味着池太小，你必须增大出问题的池来避免问题在将来继续发生。
通常来说，这些池的默认设置是足够的，但是要手动的调节这些池，你可以修改参数 LARGE_POOL_SIZE，STREAMS_POOL_SIZE，和 JAVA_POOL_SIZE。如果使用 SGA_TARGET，MMAN 进程会根据需要自动调节这些池。
ORA-4031 错误是什么？
SGA 中的内存池由不同大小的内存块组成。当数据库实例启动时，大量的内存块分配到不同的池中并且由空闲列表哈希 bucket 追踪。随着时间推移，由于内存块被分配和回收，内存块会根据它们的大小在池中的不同空闲列表 Bucket 中移动。当 Oracle 不能找到一个足够大的内存块来满足用户操作所带来的内部分配请求的时候，ORA-4031 错误就可能在 SGA 的任何一个池中出现。
共享池的管理与其它内存池不同。共享池存储数据字典和库缓存相关的信息。然而，这些内存区域使用空闲列表和最少最近使用（LRU）算法管理。在搜索所有的空闲列表，老化 LRU 列表上所有可能的对象，并且扫描空闲列表多次之后，共享池上才会发出 ORA-4031 错误。这意味着 ORA-4031 很难预测。可能有很多的因素一起导致了 ORA-4031，错误发生时的 trace 信息是有关于当时内存条件下“受害者会话”，而不是原因。分配的算法非常复杂，下面是一个内存分配算法的简化版本：
scan regular free list for match, if not found
large request, scan reserved list
if (chunk found)
check chunk size and perhaps truncate
if (chunk is not found)
scan regular free list
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
small request, scan regular free list
do LRU operations and repeat search
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
注意：有内部检查来限制报出 ORA-4031 错误前所进行搜索的次数。
空闲空间的总数可以从 v$sgastat 中获得，但这是不重要的。重要的是，通过 LRU 操作可以释放或者融合的最大的块的大小。从 heapdump 我们可以看到空闲列表 Bucket 和每个 Bucket 中内存块的信息。
Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
这里显示 bucket1 有 443 个内存块（chunk），其中最大的是 40byte，平均是 40byte。Bucket 2 包含的内存块的大小是 40 到 48byte 之间。在这个例子中平均大小是 40byte，最大是 40byte。找到在内存池中什么导致了碎片并不总是可行的。有时问题是Oracle 功能导致的，但是很大一部分的情况中，低效的应用代码才是根本原因。
ORA-4031 错误可以发生在 Large Pool，Java Pool，Streams Pool （10g 新增），或者 Shared Pool 中。错误信息会指出哪个池出了问题。如果错误指出问题不在共享池中，这通常意味着对应用环境来说，出问题的池配置的太小了。将出问题的池增大 15%，然后观察后续的问题。如果使用 10g ，Automatic Shared Memory Management （ASMM）功能，MMAN进程会随着时间推移，尝试根据内存需要收缩或者增长 SGA 中不同的组件。如果你在 Large Pool，Streams Pool 或者 Java Pool 中遇到了 ORA-4031 错误， 那么您可能需要增长 SGA_TARGET，使 MMAN 可以在背后管理更多的内存。
共享池的调节要更复杂一些。例如
ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","SELECT /*+ FIRST_ROWS */ * F...","sql area","kafco : qkacol"):4031:375:2008:ocicon.c
在这个例子中，问题发生在共享池。错误消息也包含内存请求失败的大小的信息。在我们的例子中，请求 SQL Area 中 4192byte 时失败。
注意：在 ASM 环境中也用到共享池。10.1.x ASM 实例上有 ORA-4031 的问题，原因是默认值太小了，不能适应磁盘组管理活动的需要。在这个案例中，应设置 SHARED_POOL_SIZE 到 50M，如果问题仍然存在，逐次增加 10M。
Note 146599.1 Diagnosing and Resolving Error ORA-04031
我的保留区域大小是否合适？
ora-4031 错误中引用了大的失败请求意味着保留区域过于破碎了。你可以用 Note 430473.1 中的脚本调查保留区域的内存使用。
Request Misses = 0 可能意味着保留区域太大了。Request Misses 总是增长，但是 Request Failures 不增长可能意味着保留区域太小.这种情况下清理 Shared Pool 可满足内存需要。Request Misses 和 Request Failures 总是增长意味着保留区域太小并且清理 Shared Pool 不会有帮助（很可能遇到 ORA-04031）。
你也可以调查你的保留区域的大小的效率。目标是让"Hit %"尽可能的靠近 100。 注意：保留区域中的失败并不总是等同于 ORA-4031 错误。我们执行小规模的清理来尝试匹配内存请求，在很多情况下，我们会找到请求的内存并且避免错误。如果你增大保留区域的大小，那么你可能将有用的内存从共享池中拿走。我们推荐你同步的增大共享池和保留区域的大小。
col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "
col last_miss_size for 999,999,999 head "LAST MISS|SIZE "
col pct for 999 head "HIT|% "
col request_failures for 999,999,999,999 head "FAILURES"
select requests,
decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_size
from v$shared_pool_reserved;
是否有办法找到一个共享池的“正确”大小？
你可以使用参数 SHARED_POOL_SIZE 手动的配置共享池，或者 SGA_TARGET（10g 及以上）自动调节。为共享池分配的部分内存是附加内存开销（基于一些内部参数的设置）。10g 之前，附加的内存开销在 SHARED_POOL_SIZE 参数设定“之上”，但是不被参数 SHARED_POOL_SIZE 所反映。当你在 10g 之前查询 V$SGASTAT时，这看上去是一个计算错误。
SHARED_POOL_SIZE=64M
Overhead=12M
SQL> Select sum(bytes) "Total Mem" from v$sgastat where pool='shared pool';
Total Mem
-----------
79691776
在 10g 上，附加内存开销现在被包括在 SHARED_POOL_SIZE 设定中了。由于没有考虑附加内存开销，一些客户升级到 10g 后遇到了内存问题。在上例中，如果 SHARED_POOL_SIZE 手动设置到 64M 并且附加开销保持不变，这意味着可用的共享池内存只有 54525952bytes。
注意：像 105813.1 SCRIPT TO SUGGEST MINIMUM SHARED POOL SIZE 文章中这样的脚本，在旧版本的数据库中是有用的，但在 Oracle9.2.x 或者更高的版本并不是总能工作。有关 10g 中共享池大小调节的其它问题，参考
Note 270935.1 Shared pool sizing in 10g
Note 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts
我的 SGA 中有多少空闲内存可用？
你可以从视图 V$SGASTAT 中看到共享池的空闲内存。视图被分解成内存结构表条目，例如 'library cache'，'KGLS heap'，'CCursor'。10g 之前，这个视图只能追踪少量的表条目，因此一些小的内存结构被集中到一起，归入 'miscellaneous' 条目。
10g 的 ASMM 和/或 11gAMM 自动化地管理了什么？
在 10g 上，当 SGA_TARGET>0，会打开自动共享内存管理（Automatic Shared Memory Management）功能。ASMM 会为 SGA 中的这些组件管理“最好”的大小
- Shared Pool
- Large Pool
- Java Pool
- Buffer Cache （db_cache_size所管理的默认的部分）
- Streams Pool （10g Release 2 新增）
内存的移动是以”Granule”为单位进行的。你可以通过通过查询 V$SGAINFO 找到数据库的 granule 大小。Note 947152.1 指出了使用大 granule 时的问题。默认的非常大的 SGA 会使用非常大的 granule。
其它的 Buffer Cache（通过参数 DB_nK_CACHE_SIZE，DB_KEEP_CACHE_SIZE，DB_RECYCLE_CACHE_SIZE 管理），Log Buffer 和 Fixed SGA areas 不会被 MMAN 自动调节。然而，这些设置确实会影响 SGA_TARGET 设置中 MMAN 实际可用的内存。
SGA_TARGET 可以在 SGA_MAX_SIZE 设置的范围内动态调节。注意：对于一些平台有一些限制，取决于在这些平台的内存中 LOCK_SGA 如何工作。
案例学习：
配置 SGA_TARGET 到 4G。
并且配置
DB_KEEP_CACHE_SIZE=256M
LOG_BUFFER=200M
DB_4K_CACHE_SIZE=512M.
并且手动设置 SHARED POOL 的最小值（SHARED_POOL_SIZE=1G）。
这对 MMAN 有什么影响？这意味着 MMAN 无法使用整个 4G 内存对自动调节内存组件进行管理。MMAN 只能访问 2,206,203,904 bytes。
据上述配置，如下的内存必须是 SGA 的一部分
Log Buffers 209,715,200
Keep Buffer Cache 268,435,456
4K Buffer Cache 536,870,912
+ Shared Pool 1,073,741,824 （手动指定这个值意味着共享池不能收缩到更小）
------------------------------------
Total 2,088,763,392
手动的为 SGA 中的自动调节组件设置一个最小值是有一些益处的，但是它对 MMAN 根据需要增长和收缩不同的组件时所能访问的内存数量也有影响。我们推荐对 SGA 中的自动调节组件进行默认/显式的设置。要评估 ASMM 如何工作，运行 Note 430473.1 中的脚本
SGAComponents11g.sql (for 11g)
设置 ASMM 的一个经验法则是
SGA_TARGET = 256M * # of CPUs
这个对你的应用内存使用等等做了很多的假设，而且更适当的是使用上面列出的查询对受管理的各池的大小进行监控。
1. 观察趋势并找到上面脚本中的不同的池的正确的最小值。
2. 在你的初始化文件中，使用这些值来作为池参数大小的初始设置。
3. SGA_TARGET 设为高于固定设置和初始设置总和的 25%。
4. SGA_MAX_SIZE设为高于SGA_TARGET 25%。注意：这一步不是适用于所有环境的。SGA_MAX_SIZE 的内存在 Oracle 启动时分配。大多数客户都难以理顺 Oracle 内存的分配，但却不能在需要时自动处理。一些客户发现，当有业务流程周期性的需求更多的 SGA 内存时，将 SGA_TARGET 提升到 SGA_MAX_SIZE，比把这些内存给应用程序日常使用更有益处。
警告：在 10.2.x 上，有很多与 ASMM 和 ORA-4031 相关的问题报告。很多客户并不为自动调节池设置最小值，而是依赖于 SGA_TARGET 和 MMAN 去根据需要移动内存。内部的测试以及和开发部门的讨论表明，最好是为这些池找到一个最小值，并且手动的设置到 spfile 或者 init 参数文件中去。 作为起始点，查看 v$sga_dynamic_components 中的数据，手动的给这些池赋值到当前值的 70 到 75%。如果随着时间推移，有收缩 share pool 到那个值之下失败的迹象，那么将默认设置减少 10%，监控新值是否是一个更好的最小设置。这会帮助减少 Buffer Cache 和不同的池之间出现过量的钉住内存的情况。
从 11g 开始，自动调节也会管理 PGA_AGGREGATE_TARGET。 11g 自动调节（AMM）通过 MEMORY_TARGET>0 来配置。Note 443746.1 指出了 11g 自动调节上的改变。
注意：在 11g 中，即使当 MEMORY_TARGET 和 SGA_TARGET 被显示的设置为 0 时，对 SGA 中池的 IMMEDIATE 内存请求也可以在 SGA 中移动。参考 Note 1269139.1。
我默认有多少子池？
子池的数量计算有一个简单的算法。首先，在 9i 中一个子池至少有个 128MB，在 10g 中至少 256MB。第二，系统中每 4 个 CPU 可以有一个子池，最多 7 个子池。子池的数量可以用初始化参数 _kghdsidx_count 明确的控制。没有参数可以明确控制每个子池的大小。
如果有人在 9i 上配置一个 12 CPU 的系统，300MB 的共享池，Oracle 会创建 2 个子池，每个 150MB。如果共享池大小增大到 500MB，Oracle 会创建 3 个子池，每个 166MB。
因为 128MB（甚至 10g 上的 256MB）子池在很多应用环境中可能偏小，每个子池中的内存会倾向于需要增大。没有参数可以改变子池的最小值。唯一的办法是在一个固定的共享池大小基础上减少子池的数量，或者增大共享池使得子池大小增长。 请注意增长共享池大小并不一定增长子池的大小，因为如果系统上有很多 CPU，子池的数量也会增长。
10.2.0.3 及以上的变化：根据 bug 4994956，10g 或更高版本上，算法是将每个子池增长到 512M。
我应当如何控制使用的子池的数量？
变量 _kghdsidx_count 控制了使用的子池的数量。将这个参数设置为 1 会使共享池的行为回退到 8.1.7 版本。即，一个子池。
或者把这行加入 pfile
注意：子池的创建是在启动过程中 SGA 创建时发生的。上述的 2 个例子中，数据库都必须重启来改变子池的数量。对 _kghdsidx_count 的改变也会改变大池中的子池数量。
当通过 _kghdsidx_count 手动的设置子池的数量的时候，推荐的做法是逐渐的进行改动，监控性能影响，以避免任何剧烈的影响。
相反的的，增加子池的数量而不增加池的整体大小的话，由于子池大小变小，可能导致空间问题。
是否所有的 ora-4031 错误都记录在 alert log 里？
不。一些错误只在客户端显示。在 11g 之前，在常规用户进程上发生的任何 ora-4031 错误都不会被记录在 alert log 中。也有一些情况，alert log 中报出了 ora-600 和 ora-7445 错误，但是这些内部错误实际是一个 ora-4031 内存问题的副作用。这些内部错误的日志通常包含在 ora-4031 trace 中。
注意：在 11g 中，bug 9209518 指出甚至当内存请求未得到满足，但后来我们通过执行内部 LRU 操作满足了这个内存请求的情况下，我们也会生成 ora-4031 的 incident trace。例外情况是，当 ora-4031 错误发生的非常频繁，有些 trace 文件可能因为防止泛滥的设置而没有产生。
我们应当如何查看 V$SGASTAT 中的"miscellaneous"结构的分解数据？
当查询 v$sgastat 时，有些情况下你会看到一个非常大的"miscellaneous"数值。直到 Oracle 10g Release 2，SGA 内部结构的基本设计仍然没有变化。在数据字典里，有一些对不同内存“注释”的条目表，我们在最大的内存“注释”或者结构上保留统计数据。因为我们只追踪某一些结构，所以小一些的结构被归纳到叫做 miscellaneous 的内存“注释”上。如果你运行 Note: 430473.1 中的 SGAStat.sql 脚本，这个脚本会报告出 V$SGASTAT 视图中有关共享池的最大的分配区域。你可以调整这个脚本来查看 SGA 的任何池。
根据 Bug 3663344，由于内存结构随着时间变化增长和收缩，在 V$SGASTAT 的统计数据中，偶尔有些不一致。一旦一个内存结构达到一个内部的控制大小，我们就会从一个通用性的条目"miscellaneous"，将有关的内存结构的数据移动到一个特别的内存结构条目。常见的问题是在 v$sgastat 的某些内存结构上会看到负值，或者有时会看到一个不寻常的大的"miscellaneous"。
要挖掘"miscellaneous"区域的内存的唯一方法是获取一个 heapdump trace。你可以运行这个命令
注意：在实例级别设置这个事件会生成大文件，如果 4031 错误发生频繁，会产生很多文件。这可能影响性能或者导致挂起（在一些案例中导致数据库崩溃）。使用如下命令关闭这个事件：
alter system set events '4031 trace name HEAPDUMP off';
在 4031 错误再次发生时，会获得一个 SGA 中内存的分解和 SGA 中最大的 5 个子堆的内存分解。这个场景下，你会看到在 trace 中列出的一个或者更多的最大的子堆是在 'miscellaneous' 中的。不幸的是，没有办法看到 'miscellaneous' 所有的分解，但我们只需要关注 'miscellaneous' 中高于预期的条目。
使用如下步骤来即时获取内存 dump
alter system set events 'immediate trace name heapdump level 536870914';
or
sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace
关闭 SQL*Plus 会话，根据上面 'oradebug tracefile_name' 命令的输出找到 heapdump trace 文件。
如果问题实际上关联到永久内存结构（在 'miscellaneous' 条目下查看），那么除非你设置事件 10235 level 65536，否则没有其他方式获取这些内存区域的详细信息。这个事件应在 Oracle Support 的指导下设置。
什么数据库参数与 ORA-04031 问题有关？
- CURSOR_SHARING
在 Literal replacement（字面值取代）特性中，Oracle 通过取代 SQL 中的字面值来减少应用在共享池中占用的空间，并且降低硬解析。字面值被绑定变量取代，如果两个或者更多的会话执行相同的 SQL 语句，他们可以用带有绑定变量的相同的游标，而不是创建两个不可共享的游标。
例如，两个用户以 SCOTT 连接，并运行 SQL 语句"select ename from emp where empno = 20" 和 "select ename from emp where empno =100"。如果 cursor_sharing 设置成 FORCE，Oracle 会创建一个带绑定变量的游标，于是语句就等同于"select ename from emp where empno = :b1"。这两个用户会共享相同的游标对象而不是创建两个独立的库缓存父对象和它们对应的子对象。
这个参数有三个模式：
- EXACT：不尝试进行 Literal replacement。
- FORCE：所有的字面值都会被取代。语句会被共享，而不考虑字面值对执行计划的影响。
- SIMILAR：所有的字面值都会被取代，但是，语句仅仅当游标有相同计划时才共享。
对对象执行 DDL，收集统计信息，或者赋予或收回权限都会导致失效（Invalidations）。你也会观察到关联的“library cache pin”等待。
参考：
Note 287059.1 Library Cache Pin/Lock Pile Up hangs the application
Note 34579.1 WAITEVENT "library cache pin" Reference
Note 115656.1 WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK
使用 SIMILAR 不是最优的。虽然带有字面值的游标有最好的执行计划，匹配的标准是基于字面值上的。这个用法会基于文字值创建一个小的哈希表，如果值是相同的，子游标会共享。这个行为会导致应用的共享池空间占用等同于使用 CURSOR_SHARING=EXACT 运行的结果。不再是创建很多独立的游标及它们自己的子游标，SIMILAR 在相同的父游标对象下创建很多子游标。如果你发现硬解析达到50/秒或更高（Snapshot/AWR），CURSOR_SHARING=EXACT 会比 SIMILAR 更有帮助的。
要评估这个参数当前设置的有效性，参考 Note 208918.1。
- CURSOR_SPACE_FOR_TIME
如果设置了这个参数，Oracle 不会在执行结束后 unpin 库缓存对象。这意味着随着越来越多的游标打开和执行，在库缓存中pin住的活跃的内存的数量在增加，可以被从共享池中老化出去的内存的数量在减少。这个参数要小心的使用，而且要知道应用在共享池中总共占有的空间。对此不了解的情况下设置这个参数会导致 ORA-04031。在 Session Cached Cursor 列表中的游标的 SQL area 堆并不被 pin 住。
一些站点使用这个参数，因为它避免了库缓存 pin 和 unpin 的代码执行。这并不是一个推荐的改善性能的参数。非常难以判断设置这个参数是否安全。
注意：在 10g 上这个参数可以帮助改善数据泵的性能。如果调查共享池中“sql area”区域发生的 ORA-04031 错误，查看是否 CURSOR_SPACE_FOR_TIME=true。这可能导致在 10.1.x 版本上"sql area"的分配增长并占用大部分的共享池。
- DB_CACHE_SIZE
在 RDA 中检查这个参数设置的大小。如果使用 SGA_TARGET，它的默认设置为 0。如果在使用 SGA_TARGET 时对这个参数进行的设定，那么它会作为 MMAN 在收缩 Buffer Cache 时的最小值。
- DB_nK_CACHE_SIZE
这个 SGA 内存组件是不能自动调节的，但是这项内存设定会影响 MMAN 实际可管理的内存。
- DB_KEEP_CACHE_SIZE
这个 SGA 内存组件是不能自动调节的，但是这项内存设定会影响 MMAN 实际可管理的内存。
- DB_RECYCLE_CACHE_SIZE
这个 SGA 内存组件是不能自动调节的，但是这项内存设定会影响 MMAN 实际可管理的内存。
- JAVA_POOL_SIZE
如果使用 SGA_TARGET，它会默认设置为0。在使用 SGA_TARGET 时对这个参数进行的设定会作为 MMAN 尝试收缩 Java Pool 时的最小值。
如果你不使用 SGA_TARGET，这个内存区域的默认值通常是足够的，除非你的环境利用了大量的 JVM 对象。
如果你需要了解 JAVA Pool 中内存分配的更多细节，可以运行
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 128';
- LARGE_POOL_SIZE
Large Pool 是用来缓解共享池中较大的内存分配的负担的，包括共享服务器（UGA），并行处理（Buffer allocations），和 RMAN 备份操作，和顺序文件 IO（例如，IO slave activity）。如果你不使用这些特别的功能，你可以设置 Large Pool 为 0。 Large Pool 不使用 Least Recently Used（LRU）算法，所以在会话释放大的内存之前，它们都会保留在 Large Pool 里。
如果你需要了解 Large Pool 中内存分配的更多细节，可以运行
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 32';
如果使用 SGA_TARGET，这个参数会显示为 0，但是你也可以显式设置一个最小值，MMAN 不会尝试收缩 Large Pool 到这个设置之下。
- LOG_BUFFER
这个 SGA 内存组件是不能自动调节的，但是这项内存设定会影响 MMAN 实际可管理的内存。
- OPEN_CURSORS
这个参数设置了一个 session 可以打开的游标数量的上限。一般来说，游标是通过 OCI 调用或者 PL/SQL 调用来打开的。
在 Oracle 9.2.0.5 之前的版本，OPEN_CURSORS 作为 PL/SQL 游标的缓存。当 PL/SQL 关闭了一个 PL/SQL 所打开的游标，它会检查是否可以使用 OPEN_CURSORS 分配的游标来缓存这个游标。这个游标并没有真正的关闭，但是可能被另外一个缓存的游标或者应用明确打开的游标所关闭和取代。如果应用使用 OCI 打开非常少的游标，但使用 PL/SQL 打开和关闭很多不同语句的游标，那么它可能仍然会有很多打开的游标。
如果一个用户显式的打开一个游标，虽然此会话已打开游标的数量达到了 OPEN_CURSORS；此时一个缓存的游标会被关闭，而显式打开仍然会成功。
参考：
Note 76684.1 Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
Note 208857.1 SCRIPT - to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters
- PROCESSES / SESSIONS
从 9.2.x 开始，这些进程会影响共享池的大小。在 9.2.x 中共享池会分配内存结构来存储每个进程或会话的动态参数设定。注意：在 32bit 数据库中，每个参数可以占用的内存结构最大为 20bytes，64bit 数据库中，每个参数是 32bytes。在 10.2.x，有超过 1300 个动态参数，所以当数据库中有很多用户时，所占用的内存会显著增加。你可以从 V$RESOURCE_LIMIT 中检查 Sessions 和 Processes 的高水位线。如果这些参数的设定值远高于高水位线信息，考虑减少参数设定来从共享池中释放一些内存。
- SESSION_CACHED_CURSORS
当游标关闭时，Oracle 将会话与库缓存状态的所有联系分离开。如果没有其它的会话打开了相同的游标，库缓存对象和它的堆就会被 unpin, 并且可以做 LRU 操作。参数 SESSION_CACHED_CURSORS 控制“软”关闭的游标数量，就像缓存的 PL/SQL 游标一样。
SESSION_CACHED_CURSORS 游标不是 open_cursors 统计数据的一部分；它是一个独立列表。不是真正的关闭，而是 Oracle 将游标放在一个会话私有的 LRU 列表里，使游标可以在下次解析时可用。如果用户执行一个新的语句，它首先搜索 Session Cached Cursor 列表，如果找到，就利用它。
这个参数最初的设想是用于 Oracle FORMS 应用。为了并行化 FORMS 开发，客户经常开发独立的 form 并且在 form 之间切换。FORMS 行为是要在切换到新的 form 之前关闭前面 form 的所有的游标。如果应用打开的游标是针对同一个语句的，这个参数允许 Oracle 来”缓存”关闭的游标并且重用他们。
参数设置的高的话会增加库缓存内存的数量。（通过视图 V$SGASTAT 来监控）
在 9.2.0.5 以上, 这个参数也被重载用于控制 PL/SQL 缓存的游标的数量。之前 PL/SQL 缓存游标数量是由 OPEN_CURSORS 控制的。
参考：
Note 270097.1 ORA-4031 and Very Large Library Cache in Oracle 9.2 with Session_cached_cursors set. Library Cache Pin/Lock Pile Up hangs the application
Note 274496.1 ORA-7445 and ORA-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used
- SGA_TARGET
如果设置了这个参数，MMAN 进程会尝试增长和收缩自动调节的内存组件。有趣的是，在 10.2 如果你在 spfile 中明确的设置 SGA_TARGET 高于 SGA_MAX_SIZE 设置值，那么下一次启动会忽略之前设置的 SGA_MAX_SIZE，设置它等于新的 SGA_TARGET 值。这与 11g 中的行为不同。
- SHARED_POOL_SIZE
如果使用 SGA_TARGET，这个参数默认是 0，但是 MMAN 尝试收缩共享池时，如果这个参数有一个显式的设置，那么这个设置会作为最小值。在 9i 和 10g 中，更多的 SGA 固定内存结构被移到了共享池中。这意味着当从 Oracle 7，Oracle 8，Oracle 8i 升级上来时，你必须为 9i 和 10g 共享池所需的内存执行额外的调优分析。在 9i 及更高的版本，Oracle 还使用了一个新的子池特性。由于应用会以不同方式利用内存，这可能需要额外的调优分析。在一些情况下，共享池中出现过多的子池，可能导致一个子池过度利用并带来 ora-4031 错误。
参考：
Note 270935.1 Shared pool sizing in 10g
如果你需要查看共享池中内存如何分配的更多细节，你可以运行
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 2';
注意：不推荐在业务高峰运行 heapdump trace，这会影响性能。
- SHARED_POOL_RESERVED_SIZE
这个参数默认 是SHARED_POOL_SIZE的5%。当使用 SGA_TARGET 时，这个会随着共享池组件的自动增长和减少进行调节。如果你看到有持续发生的 ORA-4031 错误指出内存请求失败大于 4000byte，那么这个 5% 的默认值可能对你的应用环境来说就不够了。你可以修改隐藏参数 _shared_pool_reserved_pct 到 10。这使得保留区域利用共享池的 10%。例如
或者添加到 init 文件
"_shared_pool_reserved_pct"=10
- STATISTICS_LEVEL
关于统计数据的追踪，在 10g 有额外的内存结构。这个参数控制数据库中所有的主要的统计数据收集和建议，并且设置数据库的统计数据收集级别。这个参数可以设置成 BASIC，TYPICAL 或者 ALL。
默认设置是 TYPICAL，它会给共享池增大压力，除非你调节共享池来适应数据库中进行的分析动作。在一些性能调节案例中，必须设置 STATISTICS_LEVEL 为 ALL。相对其它的设置，这会使用共享池中更多的内存，所以如果共享池没有调节到能处理额外内存需求的话，使用ALL可能导致 ORA-4031 错误。
在 9i 和 10g 中已知的一些 Bug 中，绕开方案是设置 STATISTICS_LEVEL 为 BASIC。这会使用最少的共享池内存，但是你放弃了自我调节的功能（内存，建议器，对象统计数据管理，等等）。
- STREAMS_POOL_SIZE
这是 10g 中的新内存池。设计它的目的是要减轻有关的 Streams 操作对共享池带来的内存压力。通过 RDA 检查这个参数的大小设置。
如果在 10gR2 中使用 SGA_TARGET，这个参数会被自动调节，并且会呈现为 0。你可以在 10gR2 中给它显式设置一个最小值，MMAN 不会尝试收缩 Streams 池到小于这个设置。
如果你需要了解 Streams Pool 中内存分配的更多细节，可以运行
SQL> connect / as sysdba
SQL> alter session set events 'immediate trace name heapdump level 64';
另外也检查一下跟共享服务器使用有关的设置（MTS_SERVERS，MTS_DISPATCHERS 等等)。如果这些参数显示已经配置了共享服务器，你不应该看到在共享池中有跟共享服务器有关的内存结构。
注意：上面列出来的参数中的一部分仅当使用 SGA_TARGET 时才有效。请一定要调查一下 MMAN 可以用来自动增长或者收缩的内存组件的可用内存实际有多少。
我们应该查看 RDA 中的哪些信息来帮助诊断 4031 错误？
检查 RDA 报告是因为在一个报告中包含了很多“拼图”。除非问题是简单的因为内存组件未配置或者太小，我们经常需要其它诊断信息来找到根本原因：
在'Overview'，'System Information'中，检查这个服务器上CPU的数量。CPU 的数量能够帮助确认共享池中使用的子池的数量。例如
在'Overview'，'Database Information'中，查看版本信息（V$VERSION）
在'RDBMS'，'Database Parameters'中，查看如上所述的适当的参数。使用最新版本的 RDA 报告，隐藏参数的设置也被包含在这个部分里。你应该查看如下设置'_PX_use_large_pool'，'_kghdsidx_count'，'_large_pool_min_alloc'，'_library_cache_advice'，'_shared_pool_reserved_pct'，'_shared_pool_reserved_min_alloc'，'_4031_dump_bitvec'，'_4031_max_dumps'，'_4031_dump_interval'，'_4031_sga_dump_interval' ，'_4031_sga_max_dumps'。
在'RDBMS'，'V$RESOURCE_LIMIT'中，查看'processes'，'sessions'的高水位线信息。如果 PROCESSES 和 SESSIONS 的设置远高于高水位线信息。降低这些设置可以帮助减少一些永久内存结构分配。在 RAC 环境中，'ges*'参数的设置也非常重要。有一些 RAC/ORA-04031 Bug 是关于'ges*'参数的。
在默认的 4031 追踪文件中有哪些信息是相关的？
在 9.2.0.5 或者更高版本中，当发生 ora-4031 错误时，会生成一个 trace 文件（通过 _4031_dump_bitvec 控制）。在 9.2.x，默认是 6639615 = 0x654fff , 含义是：
0x0004fff: 除了subheaps & top sga heap w/ contents 之外，启用所有的 dump
0x0050000: 进程 dump 之间间隔 5 分钟
0x0600000: 在 sga heap dumps 之间间隔 60 分钟 (6 x 10)
在 10g，默认是 20479 = 0x004fff
0x0004fff: 除了 subheaps & top sga heap w/ contents 之外，启用所有的 dump
还额外添加了其它初始化参数：
_4031_dump_interval (default 300) - 在 4031 诊断 dump 之间的最小时间间隔，单位是秒。
_4031_sga_dump_interval (default 3600) - SGA heap dump 之间的最小时间间隔。
注意：这些参数中任意一个设置为 0 意味着所有的错误都会生成一个 trace 文件。如果 ORA-04031 错误在一个短时间内频繁发生，则可能由于太多的 trace 文件生成，导致数据库挂起。
_4031_max_dumps (default 100) - 对每个进程能够生成的 dump 的数量设限。0 会关闭 4031dump。
_4031_sga_max_dumps (default 10) - 对每个实例能够生成的 SGA dump 的数量设限。0 会关闭 SGA dump。
确保检查头信息并且确认日期信息与错误报告一致。在 trace 文件的头信息之后，你会看到类似如下的信息。
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=08/29/2006 08:55:14
Allocation request for: optdef : apanlg <<<< request for memeory
structure (related to
inlist and the CBO)
Heap: 44b5c89b8, size: 96 <<<******************************************************
******************************************************
HEAP DUMP heap name="sga heap" desc=380000030
extent sz=0xfe0 alt=200 het=32767 rec=9 flg=-126 opc=0
parent=0 owner=0 nex=0 xsz=0x1
******************************************************
HEAP DUMP heap name="sql area" desc=44b5c89b8 < 在"sql area"
extent sz=0x1040 alt=32767 het=32 rec=0 flg=2 opc=2
parent=380000030 owner=44b5c8898 nex=0 xsz=0x1
Subheap has 840329704 bytes of memory allocated
向下滚动 trace 文件，直到 Stack Trace 列表结束之后，
===============================
Memory Utilization of Subpool 1 <===============================
Allocation Name Size
_________________________ __________
"free memory " 124944864 <<<< 这是发生错误时的 "free memory"
"miscellaneous " 57893552 <<<< 在 10gR2 之前，这是很多小内存区域的统一占位符。
"sim memory hea " 2319640
"PL/SQL PPCODE " 0
"KQR L SO " 56320
"type object de " 0
"trigger source " 0
"errors " 0
"PX subheap " 147016
"trigger defini " 0
"trigger inform " 0
"PLS non-lib hp " 2088
"KGLS heap " 215352
"FileOpenBlock " 7517528
"KQR M SO " 39976
"PL/SQL SOURCE " 0
"PL/SQL DIANA " 99968
"joxlod: in phe " 0
"db_block_hash_buckets " 9978352
"joxs heap init " 4240
"MTTR advisory " 697248
"fixed allocation callback" 552
"dictionary cache " 3229952
"KQR L PO " 245784
"KQR M PO " 319096
"parameters " 0
"partitioning d " 0
"library cache " 18615496
"table definiti " 0
"sql area " 901605416 < 这里已经分配的内存。
"pl/sql source " 0
"transaction co " 0
"KGK heap " 7000
"KQR S SO " 14360
"event statistics per sess" 12499760
"joxlod: in ehe " 357736
"temporary tabl " 0
"PL/SQL MPCODE " 39392
在 trace 文件中再向下翻一点，我们看到错误发生时的 Library Cache 信息
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 4265150 0.977 496114150 0.999 155148 46115
TABL/PRCD/TYPE 40860748 0.999 80409664 0.994 190813 0
BODY/TYBD 52028 0.996 55986 0.920 3084 0
TRGR 468975 0.998 468975 0.998 76 0
INDX 54546 0.919 65318 0.867 0 0
CLST 122885 0.992 166510 0.989 0 0
OBJE 0 0.000 0 0.000 0 0
在 Library Cache 统计信息里，找到'hit ratio'百分比，这指示了碎片问题。目标是使得'hit ratio'尽可能的接近 100%。另外要查看 reloads 和 invalids 信息。reloads 和 invalids 很多意味着库缓存中发生了很多内存清理，可能意味着应用低效和碎片化。
在 10gR2 中，V$SGASTAT 视图比以前版本包含更多的细节。作为 10gR2 上的开始方案，可以在高峰期，每 30分钟左右运行 SGAStat.sql（来自 Note: 430473.1）中的查询，运行几个小时。比较内存条目的结果可以帮助找到哪里的分配增长了。
如果显式的设置 Heapdump 事件
你会看到对内存更加详细的描述
******************************************************
HEAP DUMP heap name="KSFD SGA I/O b" desc=3800092e0
extent sz=0x4258 alt=32767 het=32767 rec=9 flg=3 opc=0
. . .
******************************************************
SUBHEAP 2: desc=3a1b57a10
******************************************************
HEAP DUMP heap name="PX subheap" desc=3a1b57a10
extent sz=0xff50 alt=32767 het=32767 rec=9 flg=2 opc=0
parent=380000030 owner=0 nex=0 xsz=0xff38
EXTENT 0 addr=3a4342860
Chunk 3a4342870 sz= 64904 free " "
Chunk 3a43525f8 sz= 104 freeable "PX msg batch st"
Chunk 3a4352660 sz= 104 freeable "PX msg batch st"
Chunk 3a43526c8 sz= 104 freeable "PX msg batch st"
Chunk 3a4352730 sz= 104 freeable "PX msg batch st"
Total heap size = 65320 <free lists: ="" bucket="" 0="" size="40
Bucket 1 size=104
Bucket 2 size=528
Bucket 3 size=600
Bucket 4 size=1112
Bucket 5 size=1120
Chunk 3a4342870 sz= 64904 free " "
Total free space = 64904 <unpinned recreatable="" chunks="" (lru="" first): ="" permanent="" chunks: Permanent space = 0
******************************************************
在一些情况中，必须 dump 出 subheap 里面的额外信息。例如
Chunk 3a0ba0480 sz= 4184 freeable "CURSOR STATS " ds=3a1a6c0d8
. . .
ds 3a1a6c0d8 sz= 246856 ct= 59
39e642190 sz= 4184
39c08c728 sz= 4184
SQL> ORADEBUG SETMYPID
SQL> ORADEBUG DUMP HEAPDUMP_ADDR 1 15596962008 (decimal value for 3a1a6c0d8)
<>>
HEAP DUMP heap name="CURSOR STATS" desc=3a1a6c0d8
extent sz=0x1040 alt=32767 het=32767 rec=9 flg=3 opc=0
parent=380000030 owner=0 nex=0 xsz=0x1040
EXTENT 0 addr=39e6421a8
Chunk 39e6421b8 sz= 4144 free " "
EXTENT 1 addr=39c08c740
Chunk 39c08c750 sz= 80 freeable "kks pstat "
. . .
Chunk 39c08c860 sz= 336 freeable "kks cstat "
在 Statspack/AWR 报告中有哪些信息是与 4031 错误相关的？
当使用 Statspack/AWR 报告调查 4031 错误时，集中在如下几点
- time period
- v$librarycache statistics
- parse vs execute and parse/sec (hard vs soft parses)
- Waits section
- Parameter section
- Latch contention
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1460166532 cqlwh 1 28-Nov-05 20:18 10.2.0.1.0 NO
Host Name: cqlstldb06.ceque Num CPUs: 4 Phys Memory (MB): 3,992
以及
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Parses: 132.46 31.70
Hard parses: 0.73 0.17
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Library Hit %: 99.90 Soft Parse %: 99.45
Execute to Parse %: 42.77 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 97.19 % Non-Parse CPU: 93.91
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.54 95.50
% SQL with executions>1: 71.11 67.96 <% Memory for SQL w/exec>1: 64.80 64.01
要进一步的调查 Library Cache 问题,可以查看错误发生前后时段的一系列的 Statspack 报告。在 10g 中，你可以在 Snapshots 报告中找到 Library Cache 信息。点击'Performance'页面，翻到最下面。点击'Snapshots'链接，点击’ID’链接，选择一个你的数据库生成的 Snapshot。选择‘Report’页面，你会看到以 HTML 格式显示的类似于如下的信息。
我们如何确定是否有应用的问题导致了这个错误？
应用代码中常见的问题是
- 没有使用绑定变量 - 多个子游标 - 高解析率这种情况下你应该考虑的问题是：
1. 问题会在重启数据库后多久发生？
2. 你是否运行了 Statspack 报告或者 10g 上的 AWR？是否看到很高的硬解析数量？
3. 应用使用了绑定变量还是字面值？
4. 收集统计信息的频率如何？当你收集统计信息时，对于有新的统计数据的表，它们在库缓存中关联的对象会失效。
5. 库缓存中是否有 high version counts 的迹象？
只有当你看到不寻常的高数值的时候才是一个问题。有 high version counts 的游标可能意味着在游标共享上存在问题。游标出现大的 shareable_mem 并不一定是有问题，但记得共享池的分配被限制在大约 4k。更大的分配会给保留区域带来压力，并且对 heap manager 发出多个请求（导致内部 latch 上的竞争）。
可以通过查询调查每一种可能性。
参考：
Note 232443.1 How to Identify Resource Intensive SQL for Tuning
没有使用绑定变量
下面是一个内部测试的例子，没有使用绑定变量。这个测试代码在2到3分钟之内都没有完成。
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
简单的将测试代码改为只用一个绑定变量，性能奇迹般的改变了，只用了2到3秒就完成了
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
不使用绑定变量会导致共享池/库缓存的过度使用。
当你发现问题与高硬解析数量有关时，可以这样调查。
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 3,092,800.46 2,563.60
Logical reads: 314,615.77 260.78
Block changes: 18,384.93 15.24
Physical reads: 7,497.42 6.21
Physical writes: 1,698.45 1.41
User calls: 4,922.77 4.08
Parses: 8,245.52 6.83
Hard parses: 141.85 0.12 <sorts: 10,794.93 ="" 8.95 ="" logons: ="" 2.72 ="" 0.00 Executes: 22,421.54 18.59
Transactions: 1,206.43
% Blocks changed per Read: 5.84 Recursive Call %: 92.53
Rollback per transaction %: 2.17 Rows per Sort: 5.23
从数据库的角度，你可以使用 CURSOR_SHARING=SIMILAR|FORCE，字面值会在后台被绑定变量所取代。FORCE 和 SIMILAR 几乎是相同的，但是 SIMILAR 还会考虑优化器计划。CURSOR_SHARING 还有其它的性能影响，所以你需要在改变这个参数之前对环境进行测试。CURSOR_SHARING 是一个动态参数，但是在共享池中的作用不是立刻生效的。你可以 flush 这个池或者重启数据库，这样可以开始更好的利用内存。
多个子游标
每个子游标都会在共享池中分配空间。在一些案例中，过去的已知的 Bug，创建了过多的子游标，分配的内存超过了期望的行为。
select sa.sql_text,sa.version_count,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.address
and sa.version_count > 50
order by sa.version_count ;
(Version 8,9)
select sa.sql_text,sa.version_count ,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.kglhdpar
and sa.version_count > 50
order by sa.version_count ;
这个查询会显示拥有超过 50 个的子游标，标记为‘Y’的列指出了生成子游标的原因。输出中标记为‘N’的列可以忽略。
警告：
Note 403616.1 Many Child Cursors create in 10.2.0.3 on Windows and Linux
注意：V$SQL_SHARED_CURSOR视图中包含’Y’和’N’列。
Note 430473.1 提供一个脚本更好的解析了这个视图的信息。
高解析率
重要的是找出什么导致了高解析调用：
- 使用动态 PL/SQL
- 在高负载阶段执行 DDL 语句。每次 DDL 语句执行，都会导致所有引用了这个对象的语句失效。下次执行引用了这个对象的 sql 语句时，则不得不重新解析并加载到共享池中。
会导致这种情形的典型操作是：
- Grant/revoke command
- Alter view
- Alter package | procedure
- Analyze table |index
- DBMS_STATS
- Truncate table
- Alter index
- Alter table move
如果一个 ora-04031 错误与高解析率有关，你也会看到 library cache latch 上的 latch 竞争，在 Statspack 或者 AWR 报告中的库缓存统计信息中，也会看到很多的 invalidations 和 reloads。
Note 1012047.6 How To Pin Objects in Your Shared Pool
Note 69925.1 PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL
参考
BUG:3352753 - ORA-600 [KCBLIBR_1] FOLLOWED IMMEDIATELY BY ORA-4031
NOTE:274496.1 - ora-7445 and ora-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used
NOTE:403616.1 - Many Child Cursors create in 10.2.0.3 on Windows and Linux
BUG:3663344 - V$SGASTAT SHOWS ENORMOUS VALUE FOR 'TRANSACTION CO' MEMORY AREA
NOTE:947152.1 - How To Determine Granule Size
NOTE:270097.1 - ORA-4031 and Very Large Library Cache in Oracle 9.2 with Session_cached_cursors set.
NOTE:115656.1 - Legacy: Wait Scenarios Regarding 'library cache pin' and 'library cache load lock'
NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
NOTE:232443.1 - How to Identify Resource Intensive SQL ("TOP SQL")
NOTE:146599.1 - Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video]
NOTE:1012047.6 - How To Pin Objects in the Shared Pool
NOTE:270935.1 - Shared pool sizing
NOTE:778.1 - Troubleshooting Video Issues in MOS
NOTE:4031.1 - OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")"
NOTE:287059.1 - Library Cache Pin/Lock Pile Up in Pre-10g versions
NOTE:76684.1 - Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
NOTE:34579.1 - WAITEVENT: "library cache pin" Reference Note
NOTE:443746.1 - Automatic Memory Management (AMM) on 11g
NOTE:1269139.1 - SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0)
NOTE:459694.1 - Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes
NOTE:377143.1 - How to Check what Automatic Statistics Collections are Scheduled on Oracle 10g
BUG:4994956 - NUMBER OF CONFIGURED SHARED POOL SUBPOOLS IS NOT CORRECT
NOTE:1381442.1 - How to use the ORA-4031 Troubleshooting Tool on MOS
NOTE:69925.1 - PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL
NOTE:1355030.1 - How To Troubleshoot ORA-4031's and Shared Pool Issues With Procwatcher
NOTE:208857.1 - SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters Based on Usage
NOTE:208918.1 - SCRIPT - to Gauge the Impact of the SESSION_CACHED_CURSORS Parameter
ORA-04031案例一则 http://www.laoxiong.net/an-ora-04031-case.html
ORA-04031这个错误，几乎每一个专业的DBA都遇到过。这是一个相当严重的错误，Oracle进程在向SGA申请内存时，如果申请失败，则会报这个错误。大部分情况下是在向SGA中的shared pool申请内存时失败，而少有向large pool等池中申请内存失败。比如下面的报错：
- Wed Apr 27 16:00:25 2011
- Errors in file /oracle/app/oracle/admin/zxin/bdump/zxin1_ora_2052294.trc:
- ORA-04031: unable to allocate 4128 bytes of shared memory
- ("shared pool","unknown object","sga heap(3,0)","kgllk hash table")
这里很清楚地表示出来，是在向shared pool申请内存时失败。
shared pool内存申请（分配）失败，通常有如下的几种可能：
- shared pool过小，比如在SGA Manual Management方式下，shared pool设置过小。比如一套数千连接的大系统，shared pool只设置了几百M。这种情况下，要解决问题很解单，增加shared pool的大小即可。
- 应用没有使用绑定变量，硬解析非常多，导致shared pool内存碎片严重，分配大块内存时不能获得连续的内存空间。硬解析多的一个变种是虽然使用了绑定变量，但是由于某种原因，Cursor不能共享，导致Child Cursor非常多。实际上，如果shared pool较大（比如数GB大小），这种问题还是很少出现的，并且出现也通常出现在申请大块内存时。这种情况如果使用alter system flush shared_pool可以暂时缓解问题。但是这条命令又通常不适用于shared pool较大而且比较繁忙的系统。使用绑定变量
- Cache的cursor很多，同时cursor_space_for_time这一参数设置为TRUE，可能会使shared pool碎片化严重，导致不能分配到大块的连续内存。
- Oracle的BUG导致内存泄露，比如在一些版本中查询v$segment_statistics这样的视图导致内存泄露，使shared pool内存耗光。同样的情形还有类似于“obj stat memory”,"gcs resources","ges resources"等。通常这类内存为perm类型（permanet），这类内存通常是在分配时就确定了固定的用途，不能用于其他用途，因此极容易产生碎片。
- Oracle从9i开始，根据shared pool的大小将shared pool分为多个子池（subpool)，每个子池有独立的free list，同时在分配时单独管理（有其独立 的shared pool latch）。Oracle的BUG或者说是内存分配策略缺陷导致某一类shared pool的内存分配只在一个子池(subpool)中，即多个子池的使用极不均衡，导致向那个使用得最多的子池申请内存时失败。报错信息中的"sga heap(3,0)"即指明是在第3个子池申请内存时失败。本文案例中的ORA-04031错误其产生的原因可以归结为Oracle对shared pool的分配/使用策略问题。
- 操作系统内存不足，这只会出现在shared pool的使用还没有达到最大值时才会出现，并且在操作系统都有swap的情况下，只有部分操作系统才可能有这种情况，比如在HP-UX下，reserved 内存过多导致swap满。
- 其他原因，多数是因为BUG。请参考下面提及的MOS参考文档。
本文中的案例，其数据库是运行在AIX 5.3系统中的10.2.0.4 RAC，RAC节点数为2。数据库是从9i升级到10g，而目前处于正式升级前的测试阶段。数据库报的ORA-04031错误信息如本文前面所示（其中的数据库名称已经做了处理）。
在继续讲解案例之前，不得不提到MOS上的几篇关于ORA-04031错误的文档：
- Master Note for Diagnosing ORA-4031 [ID 1088239.1]
- Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] [ID 146599.1]
- Interpreting the automatically generated ORA-4031 diagnostic trace. [ID 809560.1]
- Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID 396940.1]
- ORA-4031 Common Analysis/Diagnostic Scripts [Video] [ID 430473.1]
其实分析ORA-04031错误，通常有以下几个要点：
- 判断错误发生所有的内存区域，是shared pool,large pool还是streams pool等。这个很容易从错误信息中判断出来，本文主要描述shared pool的ORA-04031错误，这也是最常见的。
- 检查Shared Pool的总大小以及free memory的大小。如果free memory看上去挺多，以subpool为单位检查是否存在是由于碎片导致没有足够的连续内存以供分配，特别是关注报错信息中提及的子池。
- 如果Shared Pool相较于系统规模来说足够大（通常数GB都已经是很大的了），检查Shared Pool中有没有占用非常多的内存类型或内存组件，如果有，是什么样的类型的内存，在各个子池之间是否分布均匀。如果有异常占用较多的内存类型，根据此类型在MOS上搜寻是否是会有相应的BUG引起，或者分析这种类型的内存消耗较多的原因。比如如果是sql area很大，检查是不是硬解析特别多，或者是不是child cursor特别多引起。
- 基于以上分析的数据，来判断shared pool内存分配失败的原因。
上面的步骤写得比较粗略，关于分析和解决ORA-04031问题，这里也有一篇不错的文章：Simplified Approach to Resolve ORA-4031
这里关键的是分析Shared Pool的内存数据。ORA-04031错误发生后如果有条件可以马上连接到数据库中查询相应的x$表和v$视图得到相应的数据，否则只能通过ORA-4031错误发生时产生的trace文件。_4031_dump_bitvec这个隐含参数用于控制发生ORA-04031错误时对SGA的dump行为，而trace文件的分析就不像使用SQL那样简单了。
下面再来详细地分析案例：
从错误信息来看，很显然，是向shared pool的第3个subpool申请内存时出错。
以下的数据是shared pool的数据：
- SQL> select sum(bytes)/1024/1024 mb from v$sgastat where pool='shared pool';
- MB
- ----------
- 4096.53062
- SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ)
- 2 SIZ,
- 3 To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')|
- 4 |'k' "AVG SIZE"
- 5 FROM X$KSMSP GROUP BY KSMCHCLS;
- CLASS NUM SIZ AVG SIZE
- -------- ---------- ---------- ------------
- R-freea 512 24576 .05k
- freeabl 807395 1643969848 1.99k
- recr 530728 662065240 1.22k
- R-free 256 214910976 819.82k
- free 43063 100605496 2.28k
- perm 140 1673368632 11,672.49k
虽然free的数量不是太多，但是freeable的数量还是很多的。
下面是各个子池更详细的数据：
- SQL> SELECT KSMCHIDX,KSMCHDUR, KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ)
- 2 SIZ,
- 3 To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')|
- 4 |'k' "AVG SIZE"
- 5 FROM X$KSMSP GROUP BY KSMCHIDX,KSMCHDUR, KSMCHCLS
- 6 order by 1,2,3;
- KSMCHIDX KSMCHDUR CLASS NUM SIZ AVG SIZE
- ---------- ---------- -------- ---------- ---------- ------------
- 1 1 R-free 27 22666392 819.82k
- R-freea 54 2592 .05k
- free 26 14024 .53k
- perm 32 430299448 13,131.70k
- 2 R-free 12 10073952 819.82k
- R-freea 24 1152 .05k
- free 10531 24519112 2.27k
- freeabl 44922 32457736 .71k
- recr 163177 134273584 .80k
- 3 R-free 9 7555464 819.82k
- R-freea 18 864 .05k
- free 1678 4555704 2.65k
- freeabl 79815 102514024 1.25k
- recr 32689 36368096 1.09k
- 4 R-free 20 16789920 819.82k
- R-freea 40 1920 .05k
- free 2182 5810056 2.60k
- freeabl 66235 254656184 3.75k
- recr 16245 58284480 3.50k
- 2 1 R-free 25 20987400 819.82k
- R-freea 50 2400 .05k
- free 23 20016 .85k
- perm 35 398418384 11,116.58k
- 2 R-free 4 3357984 819.82k
- R-freea 8 384 .05k
- free 5137 6604176 1.26k
- freeabl 40377 12140944 .29k
- recr 54942 45005024 .80k
- 3 R-free 9 7555464 819.82k
- R-freea 18 864 .05k
- free 1477 5524568 3.65k
- freeabl 79548 101879808 1.25k
- recr 32380 36033448 1.09k
- 4 R-free 21 17629416 819.82k
- R-freea 42 2016 .05k
- free 2540 7092424 2.73k
- freeabl 70133 270332800 3.76k
- recr 15924 57263032 3.51k
- 3 1 R-free 26 21826896 819.82k
- R-freea 52 2496 .05k
- free 26 20520 .77k
- perm 33 414355416 12,261.94k
- 2 R-free 4 3357984 819.82k
- R-freea 8 384 .05k
- free 4693 7053032 1.47k
- freeabl 49723 14339800 .28k
- recr 52771 42357312 .78k
- 3 R-free 11 9234456 819.82k
- R-freea 22 1056 .05k
- free 3594 9280904 2.52k
- freeabl 95823 121934488 1.24k
- recr 39643 44097504 1.09k
- 4 R-free 25 20987400 819.82k
- R-freea 50 2400 .05k
- free 2822 7291680 2.52k
- freeabl 84443 323149712 3.74k
- recr 19148 67997008 3.47k
- 4 1 R-free 27 22666392 819.82k
- R-freea 54 2592 .05k
- free 26 18088 .68k
- perm 40 430295384 10,505.26k
- 2 R-free 6 5036976 819.82k
- R-freea 12 576 .05k
- free 4818 11473920 2.33k
- freeabl 46089 39963224 .85k
- recr 54061 44188072 .80k
- 3 R-free 9 7555464 819.82k
- R-freea 18 864 .05k
- free 1427 4109504 2.81k
- freeabl 80026 102379080 1.25k
- recr 33217 36949240 1.09k
- 4 R-free 21 17629416 819.82k
- R-freea 42 2016 .05k
- free 1993 6228464 3.05k
- freeabl 70581 269087136 3.72k
- recr 16614 59372656 3.49k
KSMCHDUR是什么意思？在9i里面这个列的值通常为1。实际上，Oracle从9i开始，将shared pool划分为多个sub pool。而在10g以上的版本中（具体开始的版本号已经不记得），每个sub pool又分了4个更小的池，我们暂且称之为mini heap。每个mini heap有其自己的free list。KSMCHDUR这一列即表示mini heap的编号。"heap(3,0)"中的0是指第1个mini heap。
在上面的数据中，可以看到这个子池的第1个mini heap的free已经很少，只有10来K。另外，我们可以观察到，perm类型的内存块只存在于每个sub pool的第1个min heap中。这个是一个重点，在后面会有解释。
这里本应该有通过查询v$sgastat得到shared pool的各个组件占用的内存分布，只是写BLOG时找不到了....但是我们可以在trace文件中找到数据，下面只列出sub pool 3的数据：
- ==============================
- Memory Utilization of Subpool 3
- ================================
- Allocation Name Size
- _________________________ __________
- "free memory " 81466568
- "miscellaneous " 0
- "dpslut_kfdsg " 512
- "trace buffer " 737280
- "trace_knlasg " 504
- "gcs res hash bucket " 1048576
- "gcs res latch table " 12288
- "evaluation con " 0
- "sql area " 344545752
- "UNDO STAT INFO " 59904
- "txncallback " 141744
- "transaction " 2103264
- "ges resource pools " 3968
- "sessions " 4526488
- "dlo fib struct " 128032
- "KJCTS process batching st" 240
- "row cache " 3272
- "KCB where statistics arra" 25888
- "KCB buffer wait statistic" 32000
- "KCB incremental ckpt entr" 512
- "invalid low rba queue " 1024
- "table definiti " 108704
- "temporary tabl " 4136
- "KCL instance cache transf" 131072
- "resumable " 2720
- "KESTB existence bitvec se" 128
- "type object de " 392448
- "enqueue_hash " 318960
- "KSXR pending consumption " 20192
- "KTI SGA freeable small po" 0
- "trigger defini " 885472
- "trigger source " 99264
- "trigger inform " 960
- "KTCN: Obj Invalidation Se" 2336
- "kmgsb circular statistics" 108800
- "kgl lock hash table state" 45360
- "kglsim size of pinned mem" 8024
- "kelr system metrics table" 280
- "kzctxgjsi ksuseclid memor" 117360
- "kzctxgjci ksuseclid memo" 0
- "CCursor " 95912048
- "ksr message pool free que" 188960
- "ksb ci process list (each" 144
- "ksunfy: nodes of hierarch" 320
- "ksuloi: long op free list" 256
- "kwqmncal: allocate buffer" 4048
- "ksim group query request " 0
- "ksuxds ksuseclid memory " 0
- "call " 87304
- "dictionary cache " 0
- "KSXR pending reply queue " 255488
- "hng: All sessions data fo" 0
- "ksfv subheap descriptor " 184
- "gcs resources " 169082312
- "gcs affinity " 8320
- "gcs opaque in " 12312
- "PCursor " 50743128
- "ges resource " 539376
- "fdrec_kffsg " 24
- "work area tab " 80640
- "kglsim main lru count " 38400
- "plwpil:wa " 4264
- "grptab_kfgsg " 2464
- "AW SGA " 40
- "KEWS sesstat seg tbl " 8
- "kebm slave descriptors " 1456
- "kglsim hash table bkts " 1048576
- "KSXR global channels " 1288
- "ges enqueues " 17333720
- "PLS chunk " 352
- "KSQ event description " 1440
- "KESTB existence bitvec " 4096
- "gcs shadows " 101246344
- "qmtb_init_data " 224
- "Core dump directory " 264
- "sort segment handle " 7480
- "SERVICE NAME ENTRY " 48
- "PQ/BizCard " 1536
- "qtree_kwqbspse " 40
- "latch descriptor table " 1576
- "recovery domain " 29856
- "parameters " 30056
- "SHARED SERVERS INFO " 240
- "qtree_kwqbsgn " 40
- "post agent " 0
- "pspool_kfsg " 80
- "plwsppwp:wa " 0
- "PL/SQL DIANA " 14050624
- "segmented arrays " 2072
- "Checkpoint queue " 4097024
- "sim lru segments " 2560
- "sim segment hits " 2560
- "sim state object " 40
- "partitioning d " 199616
- "ASH buffers " 8388608
- "message pool freequeue " 276336
- "PL/SQL MPCODE " 4499360
- "PL/SQL PPCODE " 3984944
- "procs: ksunfy " 1512000
- "primem_kfmdsg " 1032
- "SYSTEM PARAMETERS " 76624
- "object queue hash buckets" 262656
- "object queue hash table d" 7552
- "object level stats hash t" 512
- "object stat dummy statprv" 144
- "sim cache sizes " 320
- "logout storm management " 24000
- "pl/sql source " 21256
- "sys event stats " 199136
- "parameter handle " 67896
- "Parameter Handle " 1656
- "channel handle " 828672
- "API blockers array " 64
- "PARAMETER TABLE " 2048
- "PARAMETER ENTRY " 8
- "LGWR post requested array" 24
- "bloom filter " 3104
- "param hash values " 5984
- "sql area:PLSQL " 11477776
- "PX subheap desc " 256
- "repository " 213544
- "sql area:KOKA " 16192
- "archive_lag_target " 9624
- "state objects " 640
- "latch nowait fails or sle" 116832
- "sched job slv " 5952
- "pso tbs: ksunfy " 390000
- "dummy " 269928
- "Sort Segment " 37440
- "Cursor Stats " 6095760
- "Banner Storage " 2048
- "quiescing session " 3872
- "API data buffer " 16
- "buffer handles " 1020000
- "prmtzdini tz region " 408320
- "sga node map " 16
- "savepoints " 0
- "Managed Standby Proc Arra" 24576
- "OS proc request holder " 4664
- "db_files " 416576
- "PX server msg stats " 2288
- "KQR M PO " 283376
- "kks stats " 40
- "parameter table block " 483168
- "KSFV SGA " 824
- "plugin datafile array " 36016
- "plwda:PLW_STR_NEW_RVAL " 24
- "plwspv:PLW_STR_NEW_VAL " 16
- "KGKP sga " 32
- "BRANCH TABLE SEGMENTED AR" 70176
- "mvobj part des " 306544
- "parameter value memory " 216
- "multiblock re " 98496
- "parameter text value " 1080
- "parallel_max_servers " 8192
- "KGLS heap " 13290800
- "KGSKI sga " 80
- "resize request state obje" 368000
- "MTTR advisory " 1462832
- "monitoring co " 12480
- "rules engine aggregate st" 1416
- "krbmror " 36400
- "joxs heap " 136
- "krbmrsr " 152
- "ksfqpar " 4008
- "SGA - SWRF DrvMet Runtime" 2656
- "SGA - SWRF Metrics ksuTim" 72
- "SGA - SWRF RawMet Runtime" 1408
- "SGA - SWRF Metrics WCTime" 32
- "SQL Memory Manager Base W" 13400
- "change notification regis" 4096
- "simulator latch/bucket st" 59392
- "Prefetch history buffer " 2832
- "change notification obj m" 4096
- "KQR ENQ " 16512
- "kksss " 16464
- "API data buffer length " 0
- "kokcd " 0
- "kohsg " 8
- "Sequence Background Insta" 88
- "ksfqpn " 416
- "KGLS SP " 4704
- "knstsg " 48
- "latch classes " 352
- "system default language h" 568
- "name-service entry " 2592
- "API data buffer array " 0
- "kzull " 4096
- "kzulu " 392
- "kfgsga " 104
- "library cache " 46604712
- "kcrrny " 25320
- "spfile cleanup structure " 16760
- "xssinfo " 5952
- "buffer_pool_desc_array " 3384
- "row cache child latch " 3360
- "rm request queue link " 5320
- "SCHEDULING POLICY TABLE " 160
- "namhsh_kfdsg " 4104
- "Closed Thread SCN Bitvec " 8448
- "Client ID trace settings " 3872
- "osp allocation " 21104
- "os statistics " 9192
- "plwppwp:PLW_STR_NEW_LEN_V" 16
- "plwgc: plwgc_garbage_clea" 0
- "plwiiw: kglpql warnings " 0
- "object queue " 808080
- "obj stat memo " 599184
- "obj htab chun " 122960
- "object level " 111888
- "XCT XGA " 0
- "SGA - SWRF Metric Eidbuf " 900840
- "Processor group descripto" 64
- "Prefetch client count per" 32
- "X$SKGXPIA " 2680
- "simulator hash buckets " 2101248
- "State object subpools " 896
- "API data buffer length ma" 0
- "AWR Table Info (KEW layer" 872
- "character set memory " 4856
- "sim segment num bufs " 1280
- "character set object " 129728
- "session idle latches " 2560
- "qesmmaInitialize: " 112
- "returns from remote ops " 49152
- "name-service " 4080
- "SGA - SWRF Metric CHBs " 10912
- "listener addresses " 32
- "db_block_hash_buckets " 67108864
- "KSI resource types " 2704
- "kglsim object batch " 4196304
- "trigger condition node " 72
- "ksws service events " 18560
- "Heap0: KGL " 11642128
- "fixed allocation callback" 392
- "kqlpWrntoStr:value " 0
- "KEWS statistic name " 424
- "KEWS statistic maps " 1096
- "KCL partition table " 131072
- "kebm slave message " 88
- "kcbl state objects " 12800
- "free rm request queue lin" 0
- "xsoqsehift " 3104
- "DBWR event stats array " 192
- "kgllk hash table " 659456
- "event descriptor table " 192
- "kpssnfy: kpsssgct " 32
- "kpscad: kpscscon " 1952
- "dbwriter coalesce buffer " 3158016
- "kglsim hash table " 8208
- "gcs resource freelist dyn" 256
- "gcs shadow locks dyn seg " 256
- "kks stats latch " 160
- "KTC latch cleanup " 576
- "ges enqueue max. usage pe" 64
- "ges lmd process descripto" 2760
- "KTU latch cleanup " 2496
- "kscdnfyinithead " 16
- "X$KSVIT table " 512
- "kqlpaac:value-1 " 64
- "KCL buffer header " 192064
- "kxfpdp pointers " 28800
- "kodosgi kopfdo " 104
- "kglsim latches " 136
- "TXN TABLE SEGMENTED ARRAY" 54784
- "KJCT remote i " 1640
- "KKJ SHRD WRQS " 288
- "KJC dest ctx " 3560
- "kwrsnfy: kwrs " 1624
- "kwqmn:tskdata " 0
- "KKKI consumer " 4136
- "dbwr suspend/resume ptr a" 16
- "dbwr actual working sets " 64
- "KGSKI schedule " 0
- "temp lob duration state o" 3296
- "ges regular msg buffers " 3078008
- "jsksncb: 9 " 28672
- "Transportable DB Converte" 2552
- "KTU lat struct " 800
- "kks stats hds " 256
- "KSFD SGA I/O b " 4190248
- "HTTP fixed headers " 72
- "UNDO INFO SEGMENTED ARRAY" 649856
- "ges process hash table " 132000
- "jsksncb-latch " 1280
- "kfkid hrec " 24
- "KTCCC OBJECT " 0
- "KTPR HIST TB " 2808
- "KTF MAPPINGS " 12288
- "kksss-heap " 35136
- "kglsim heap " 3431232
- "event statistics per sess" 7665280
- "eventlist to post commits" 16
从上面的数据可以看到，第3个sub pool中，占用较多的内存是gcs resources、gcs shadows以及sql area。但是没有明显的异常。
下面是第3个sub pool中第1个mini-heap中free memory的更详细数据：
- SQL> break on ksmchidx on ksmchdur
- SQL> select
- 2 ksmchidx,ksmchdur,
- 3 case
- 4 when ksmchsiz < 1672 then trunc((ksmchsiz-32)/8)
- 5 when ksmchsiz < 4120 then trunc((ksmchsiz+7928)/48)
- 6 when ksmchsiz < 8216 then 250
- 7 when ksmchsiz < 16408 then 251
- 8 when ksmchsiz < 32792 then 252
- 9 when ksmchsiz < 65560 then 253
- 10 when ksmchsiz >= 65560 then 253
- 11 end bucket,
- 12 sum(ksmchsiz) free_space,
- 13 count(*) free_chunks,
- 14 trunc(avg(ksmchsiz)) average_size,
- 15 max(ksmchsiz) biggest
- 16 from
- 17 sys.x$ksmsp
- 18 where
- 19 inst_id = userenv('Instance') and
- 20 ksmchcls = 'free'
- 21 group by
- 22 case
- 23 when ksmchsiz < 1672 then trunc((ksmchsiz-32)/8)
- 24 when ksmchsiz < 4120 then trunc((ksmchsiz+7928)/48)
- 25 when ksmchsiz < 8216 then 250
- 26 when ksmchsiz < 16408 then 251
- 27 when ksmchsiz < 32792 then 252
- 28 when ksmchsiz < 65560 then 253
- 29 when ksmchsiz >= 65560 then 253
- 30 end ,
- 31 ksmchidx, ksmchdur
- 32 order by ksmchidx , ksmchdur
- 33 /
- KSMCHIDX KSMCHDUR BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
- ---------- ---------- ---------- ---------- ----------- ------------ ----------
- 3 1 5 72 1 72 72
- 13 136 1 136 136
- 27 248 1 248 248
- 48 416 1 416 416
- 56 1920 4 480 480
- 66 1680 3 560 560
- 68 4608 8 576 576
- 164 1344 1 1344 1344
- 180 1472 1 1472 1472
- 188 1536 1 1536 1536
- 190 1552 1 1552 1552
- 199 1624 1 1624 1624
- 204 1880 1 1880 1880
- 207 2032 1 2032 2032
可以看到，最大的free memory块才2032字节，而报错中提到的申请的内存大小为4128字节。由于在第3个sub pool的第1个mini heap中没有4128字节的连续free memory，所以导致内存申请失败。
那么这里的问题是，为什么这个mini heap中的free memory那么少？正如前面提及，为什么这个mini heap中的已经使用的类型全是perm类型？这个问题的答案就在于"DURATION"。Oracle在启用了SGA自动管理的模式下，为了便于在shared pool与buffer cache或其他内存之间动态调整大小，规定了在每一个mini heap中分配内存按照duration来进行。这里duration可以理解为内存块的持久时间。perm类型的内存块，就是分配后不能释放，只能用于相同组件的重用。比如gcs resources这种组件的内存是perm类型，这种内存被分配后，不能释放给sql area使用，也不能给gcs shadows使用，只能给其他的gcs resource使用。按DURATION分配内存时，perm类型的内存就只能从每个sub pool的第1个mini heap中分配。而其他类型的内存通常在sub pool的第2-4个mini heap中分配。由于perm类型的内存不能释放，也不能被其他组件的内存重用，所以里面的内存会越用越少，如果没有了free memory怎么办？前面说到，这种模式主要是工作在SGA自动管理模式下，如果free memory没有了，就会从SGA中的其他部分，比如buffer cache中取得memory chunk，加入到缺少内存的mini heap中。正常情况下这种机制没有问题。
完全使用SGA自动管理有一个缺陷就是，如果应用系统绑定变量做得不好，或者由于BUG，child cursor过多，导致shared pool会变得很大，甚至超过10G，严重的比buffer cache还大，另一方面，在buffer cache和shared pool之间频繁地调整大小，可能会导致严重的解析问题和其他性能问题。针对这个问题，通常有2种解决办法：一种就是关闭SGA自动管理，即将SGA_TARGET设置为0，以9i的方式来设置shared_pool_size,db_cache_size这些参数，来手动管理SGA；第二种就是sga_target仍然大于0，即自动管理SGA，但是通过设置shared_pool_size，db_cache_size等参数限制这些内存组件的最小大小，而只留给系统极少的自动调整空间。
而出现问题的这套系统，正是使用了第二种方式，开启了SGA自动调整，但是留给自动调整的空间极少。SGA_TARGET为35G，buffer_cache_size为30G，shared_pool_size为4G，再加上large_pool等组件，几乎没有什么可自动调整的余地。这种方式下，就存在了问题。下面来做一个按时间的分析：
- 时间T1，数据库启动，shared pool只消耗了极少量的内存。
- 时间T2至时间T3，Oracle进程请求shared pool内存，Oracle会向操作系统以指定的粒度为单位（比如16MB）请求物理内存，加入到所请求内存所在的mini heap中。直至shared pool的大小达到shared pool最大容许的大小。这个容许大小由各参数计算而来。比如说SGA_TARGET为10G，其他组件的参数设置后最小值为8G，shared_pool_size的值为1G，但是shared pool的最大容许大小为2G。这个时候，每个sub pool的mini heap的大小已经固定。在到达shared pool最大容许大小这一阶段，可能会从buffer cache等组件中占用。
- 时间T4，Oracle进程请求shared pool内存，这个时候只能从free list或age out内存块后获取内存，对于sub pool的第1个mini heap，只能从free list中获取，因为这个mini heap中的已用内存全是perm，是不能age out的。
- 时间T5，Oracle进程请求shared sub pool中第1个mini heap的内存，但是free list中已经没有内存。所以报ORA-04031错误。
在上面的时间点T5那里，如果SGA有较大的自动调整空间，比如说完全没有限制，即buffer_cache_size等参数很少或为0，这样在请求第1个mini heap中的内存时，完全可以从buffer cache中占用，这样的后果是使shared pool越来越大。
而本文案例的ORA-04031，正是由于SGA自动管理，而自动调整的余地又太小，最终使sub pool的第1个mini heap空间用光。当然我们可以分析为什么会用光，这个就显得更为复杂，这跟数据量、应用系统都有很大的关系。而系统中第1次出现ORA-04031错误的进程，是一个job进程，而此后大部分出现的错误均是job进程，能检查job代码，发现在做大量的表的大量数据的UPDATE操作，这可能是引起gcs shadows和gcs resources大量内存使用的原因。在一套RAC数据库中，gcs和ges相关的perm内存占用可能会比较大。
那么除了调整应用，应该怎么样解决这样问题？这里的解决方法是增加shared_pool_size参数到6G，同时将sga_target设置为0，再重启。
而另一种可能的办法是将参数“_enable_shared_pool_durations"设置为FALSE。这一参数为FALSE，将会使shared pool内存分配时，不再使某一类型的内存(比如perm)必须要求在一个固定的mini heap中。而实际上，sga_target设置为0之后，这一个参数自动会设为FALSE（由于这一参数是静态参数，所以修改了sga_target之后需要重启才会使这个隐含参数改变），所以建议的解决办法是设置sga_target参数，而不建议修改隐含参数。当然还有一种办法是完全让Oracle自动管理SGA，将buffer_cache_size和shared_pool_size等参数设置为0，但是正如前面所说，这种方法有比较大的缺陷。
Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] (文档 ID 146599.1)
In this Document
Purpose
Troubleshooting Steps
1. Instance parameters related with the Shared Pool
2. Diagnosing error ORA-04031
3. Resolving error ORA-04031
4. ORA-04031 error and Large Pool
5. ORA-04031 and SHARED POOL FLUSHING
6. Advanced analysis to ORA-04031 error
Community Discussion
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Oracle Advanced Benefits - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 24-Feb-2010***
PURPOSE
The purpose of this document is to provide an easy to use, step by step guide to resolving ORA-04031 errors.
In case of conflicting information between note and video presentation, information contained in the note takes precedence over information that may be recorded or shown on video.
Video - Diagnosing and Resolving 4031 errors (10:00)
Note:
If you would like to explore this topic further, please join the Community discussion ' Diagnosing and Resolving ORA-4031 errors' where you can ask questions, get help from others, and share your experiences with this specific article.TROUBLESHOOTING STEPS
When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned. NOTE: These errors can occur on an ASM instance as well. The default SHARED_POOL_SIZE should be sufficient in most environments, but can be increased if you are experiencing ORA-04031 errors.
The message that you will get when this error appears is the following:04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".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_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_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:
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;
Note: This parameter was obsoleted with 8i. The parameter can still be modified via the underscore parameter _SHARED_POOL_RESERVED_MIN_ALLOC.10g Note: In Oracle 10g a new feature called "Automatic Shared Memory Management" allows the DBA to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool.
In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure.
Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Shared Memory Management is enabled when the parameter SGA_TARGET is greater than zero and the current setting can be obtained quering the V$SGA_DYNAMIC_COMPONENTS and V$SGA_RESIZE_OPS views.
It is not possible restrict the size of auto-tuned components in the SGA if using SGA_TARGET. If you want to exercise more control on shared pool size, please do not use SGA_TARGET parameter. Using explicit settings for the auto-tuned components is recommended as minimum sizes to keep auto-tuning from being too aggressive moving memory in the SGA.
Please refer to the 10g Administration Manual for further reference.
11g Note: In Oracle 11g a new feature called "Automatic Memory Mmanagement" allows the DBA to configure the database to manage shared memory areas as well as PGA memory.
In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure and/or PGA. During times of heavy workload, PGA may automatically increase using free memory from the SGA structures.
Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Memory Management is enabled when the parameter MEMORY_TARGET is greater than zero and the current setting can be obtained quering the V$MEMORY_DYNAMIC_COMPONENTS and V$MEMORY_RESIZE_OPS views.
It is not possible restrict the size of auto-tuned components in the SGA if using MEMORY_TARGET. If you want to exercise more control on the components in the SGA or PGA do not use MEMORY_TARGET or SGA_TARGET. SGA_TARGET and explicit settings for the auto-tuned components are recommended as minimum sizes to keep auto-tuning from being too aggressive moving memory for the database workload.
Please refer to the 11g Administration Manual for further reference.2. Diagnosing error ORA-04031
Note: most common ORA-4031 occurrences are related to the SHARED POOL SIZE, therefore the diagnostic steps provided in this article will mostly address issues related to the shared pool. For other areas like large_pool or java_pool where the memory allocation algorith is simpler, normally the error is caused by an undersized structure.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.
- 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_ALLOCQuery to determine if you should consider increasing the reserved area is:
select count (*)
from V$SHARED_POOL_RESERVED
where
REQUEST_FAILURES > 0
and
LAST_FAILURE_SIZE > ;
IF the failing size as indicated by the ORA-4031 error messages or LAST_FAILURE_SIZE column of V$SHARED_POOL_RESERVED is >
_shared_pool_reserved_min_alloc that is an indication the Reserved area needs to be increased to accommodate these large space requests.
To resolve this consider increasing SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE by the same amount.
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 filed in unpublished Bug:3669074 and has been fixed as of 9.2.0.7, 10.1.0.4, and 10.2.x.- 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.Query to determine if the reserved area setting for the shared pool minimum allocation needs to be lowered is:
select count (*)
from V$SHARED_POOL_RESERVED
where
REQUEST_FAILURES > 0
and
LAST_FAILURE_SIZE is < ;
If the majority of allocations that are causing LRU actions are in the 4100 - 4400 byte range, lowering the value for the
_shared_pool_reserved_min_alloc can help.
Setting _shared_pool_reserved_min_alloc=4100 increases the probability the shared pool can satisfy the allocation request.
The default value for this parameter is 4400, and is in units of bytes. This change should
be made in conjunction with increasing the reserved pool size and the shared pool size.
Due to the min allocation change, the shared pool will satisfy more allocations from the reserved pool than before the
change. Usually, the reserved pool size should be increased from 5% of the shared pool to somewhere in the 15% - 20% range.
The shared_pool_size should be changed accordingly.
Do not set _shared_pool_reserved_min_alloc below 4100 bytes.
Another consideration: pre-9i, changing OPTIMIZER_MAX_PERMUTATIONS to 2000 can reduce shared pool space pressure.
The above checks are still applicable with ORA-04031 in 10g and beyond with auto-tuning functionality. However, explicitly adjusting the setting for SHARED_POOL_SIZE only changes the auto-tuned "minimum" for the Shared Pool. It is a "best practice" to set an explicit setting but to ensure it is low enough to accommodate memory needs in other places if the auto-tuner has to shrink the Shared Pool to handle workload.
3. Resolving error ORA-04031
- Oracle BUGs
Oracle recommends to apply the latest patchset available for your platform. Most of the ORA-4031 errors related to BUGs can be avoided by applying these patchsets. See Document 4031.1 with the latest reports of bugs and patches.
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.
The cause of this problem has been identified in:
unpublished Bug:2736601 - CORRECT ORA-4031 MESSAGE TO FLAG JAVA POOL.
- 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:
- 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;
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
Scripts used to be available for calculating a "best" size for the Shared Pool. Problems arose over time due to changes to internal memory structures and, in some cases, those older scripts could account for certain memory areas twice presenting you with percentages greater than 100%. There are some adjusted scripts available at:
Document 430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
However, with the introduction of the memory advisors (with 9.2x) and auto-tuning (with 10g Release 2), these estimation scripts are not as useful. See also:
Document 276103.1 - Performance Tuning Using Advisors and Manageability Features: AWR, ASH, ADDM and Sql Tuning Advisor- Shared Pool Fragmentation:
Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
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.
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic SQL fragmenting the shared pool. This can be caused by:
- Not sharing SQL
- Making unnecessary parse calls (soft)
- Setting SESSION_CACHED_CURSORS too high
- Not using bind variables
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 Document 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:
- 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.
One unusual thing about the X$KSMLRU fixed table is that the contents of the fixed table are erased whenever someone selects from the fixed table. This is done since the fixed table stores only the largest allocations that have occurred. The values are reset after being selected so that subsequent large allocations can be noted even if they were not quite as large as others that occurred previously. Because of this resetting, the output of selecting from this table should be carefully kept since it cannot be retrieved back after the query is issued.
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:
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);
Note: The information available in this view is the same that is generated as part of a HEAPDUMP level 2.
Be aware of Bug:4715420 stating that selecting from X$KSMSP is asking one session to hold the shared pool latches in turn for a LONG period of time and should be avoided on live systems. Selecting from X$KSMSP on a production system is a very bad idea.
If the result of the above query shows that must of the space available is on the top part of the list (meaning available only in very small chuncks). It is very likely that the error is due to a heavy fragmentation.
You can also use this view as follows to review overall memory usage in the 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
R-freea 24 960 .04k
free 331 151736448 447.67k
freeabl 4768 7514504 1.54k
perm 2 30765848 15,022.39k
recr 3577 3248864 .89k
- if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size.
- if perm continually grows then it is possible you are seeing system memory leak.
- if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing.
- if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation).
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:
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:
- 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';- 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.
5. ORA-04031 and SHARED POOL FLUSHING
There are several techniques to increase cursor sharability so that shared pool fragmentation is reduce as well as likeability of ORA-4031 errors. The best way is by modifying the application to use bind variables. Another workaround when the application cannot be modified is using CURSOR_SHARING to a value different of EXACT (Be aware that this may cause changes in execution plan, so it is advisable to test the application first). When none of the above techniques can be used and fragmentation is considearble heavy in the system, flushing the shared pool might help alliviating the fragmentation. However some considerations must be taken into account:
- Flushing the shared pool will cause that all the cursor that are not in use are removed from the library cache. Therefore just after the shared pool flusing is issued, most of the SQL and PL/SQL cursors will have to be hard parsed. This will increase the CPU usage of the system and will also increase the latch activity.
- When applications don't use bind variables and have heavy possibilities of many users doing frequen similar operations (like in OLTP systems) it is common that soon after the flush is issued the fragmentation is back in place. So be advice that flushing the shared pool is not always the solution for a bad application.
- For large shared pool flushing the shared pool may cause a halt of the system, specially when the instance is very active. It is recommended to flush the shared pool during off-peak hours.
6. Advanced analysis to ORA-04031 error
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 2"
Note: These parameters 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 536870914';
The trace file(s) should be sent to Oracle Support for troubleshooting.Important Note: In Oracle 9.2.0.5 and higher 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 (or ADR with 11g). If your database version is one of these, you don't need to follow the steps described before to generate additional tracing.
Community Discussion
For further discussion or questions about topics in this article, please visit 'Diagnosing and Resolving ORA-4031 errors'.
REFERENCES
NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool
NOTE:4031.1 - OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")"
NOTE:1012046.6 - How to Calculate Your Shared Pool Size
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error [Video]
NOTE:316138.1 - ORA-4031 / Continuous Growth of 'miscellaneous' in v$sgastat when STATISTICS_LEVEL is set to TYPICAL or ALL
NOTE:367392.1 - ORA-4031 with calls to ksfd_alloc_sgabuffer, ksfd_alloc_contig_buffer, ksfd_get_contig_buffer
How To Determine Granule Size (文档 ID 947152.1)
In this Document
| Goal |
| Solution |
| What is a memory granule |
| How is the granule size determined |
| How to find the Granule size |
| Why is granule size important |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.1.0.2 [Release 9.2 to 12.1]Oracle Database - Standard Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.
GOAL
The goal of this document is to establish the manner in which Oracle sets the granule size for memory management, and the significance of granule sizing.
SOLUTION
What is a memory granule
When a database instance starts up, the amount of memory allocated is determined by the allocations requested in the parameter file (init file or spfile). This memory is allocated in units called granules. All memory pool sizes will be allocated in multiples of the granule size.
How is the granule size determined
The granule size is determined based on the amount of memory requested at the instance startup. It is based on the SGA_MAX_SIZE. If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET for the purpose of sizing the granule. Once set, the granule size does not change for the life of the instance.
The granule sizes at the time of writing, are:
| RDBMS | SGA_MAX_SIZE (or memory_max_target) | GRANULE SIZE |
|---|---|---|
| 9.2 | <= 128MB | 4MB |
| > 128MB | 16MB | |
| 10.2 | <= 1GB | 4MB |
| > 1GB | 16MB | |
| 11gR1 | <= 1GB | 4MB |
| >1Gb <= 4GB | 16MB | |
| >4Gb <= 16GB | 64MB | |
| >16Gb <= 64GB | 256MB | |
| > 64GB | 512MB | |
| 11gR2 (and 11gR1 with patch 8813366 applied *) and later | <= 1Gb | 4Mb |
| >1Gb <= 8Gb | 16Mb | |
| >8Gb <= 16Gb | 32Mb | |
| >16Gb <= 32Gb | 64Mb | |
| >32Gb <= 64Gb | 128Mb | |
| >64Gb <= 128Gb | 256Mb | |
| > 128Gb | 512Mb |
* The granule size changes in Unpublished Bug 8813366 can be backported to 11gR1.
How to find the Granule size
You can check the granule size that is currently set for your database instance by running the following SQL statement as SYSDBA
SQL> select bytes from v$sgainfo where name like 'Granule Size';
There is a 16MB granule size maximum on 32-bit platforms. This applies even if the granule size is manually overridden.
Why is granule size important
The SGA memory components are sized as multiples of granules.
The components are:
- shared pool
- buffer cache (plus different size buffer caches)
- redo log buffer
- java pool
- streams pool
- large pool
There can be no component of size less than one granule. The minimum of some components can be greater than one granule (and rounded up to the nearest granule boundary). For example the Buffer Cache minimum will be 4MB*num_cpus, and can exceed 1 granule.
If you set a value in the spfile that is not a multiple of the granule size, the actual size allocated will be rounded up to the nearest granule. This can become important in large SGA's.
For example, if your SGA in 11GR1 is 70G, and you set the java_pool_size to 150M in the spfile, the actual allocation for the java_pool_size will be rounded up to 512M.
The significance of this granule sizing is the following:
The SGA (actually shared, streams and large pool) gets divided in subpools, a maximum of 7 depending on the number of processors and the SGA size.
Typically, 16 processors (cores) will have 4 subpools, 24 processors will have 6 subpools, and 25 or more processors will have 7 subpools. The number of subpools is derived by an internal algorithm.
In addition, in 10g and 11g, the shared pool and streams pool subpools are further divided into 4 'durations' ("instance", "session", "cursor", and "execution").
(It is possible the number of durations may change in 12g.)
So with over 24 processors, there would be 28 subpools in the shared pool and likely another 28 in the streams pool, each with a minimum of 1 granule.
If you add to that the granules for the other SGA pools, the memory usage could be over 60 granules even before any memory component exceeds 1 granule in size.
If the derived granule size is 256MB, the resulting memory requirement becomes over 15 GB just to start up the instance. This scenario can cause an ORA-4031 during or soon after startup.
Oracle Support can usually offer solutions to this by manually reducing the granule size or by reducing the processor count used in the subpool algorithm.
The patch for unpublished Bug 8813366 reduces the granule sizing to help offset this error.
Another place where granule sizes are taken into consideration, is with Automatic Shared Memory Management (ASMM) in 10g, and Automatic Memory Management (AMM) in 11g.
As memory pressures rise on the Shared Pool, instead of a ORA-4031, the memory auto-tuner in ASMM (or AMM) will go to the Buffer Cache and transfer memory to the Shared Pool to fill the required need. This memory transfer is also done in granules. So with large SGA sizes, it is possible that a transfer of memory will not occur unless there is 256M or 512M of memory available to be transferred. If at least one granule is not available, an ORA-4031 will occur.
REFERENCES
NOTE:260171.1 - How SGA_MAX_SIZE Parameter WorksNOTE:266702.1 - About Dynamic SGA Sizing
NOTE:455179.1 - How To Determine The Default Number Of Subpools Allocated During Startup
About Me
...............................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
