简答题
7. 如何解决ORAL-04030和ORA-04031错误?
【正确答案】ORA-04030和ORA-04031都是典型的内存分配错误，下面分别讲解。
(1)ORA-04030错误 ORA-04030报错形如“ORA-04030 'out of process memory when trying to allocate%s bytes(%s,%s)'”，该错误意味着Oracle Server进程无法从操作系统分配更多内存。该内存由PGA组成，其内容取决于服务器配置。对于专用的服务器进程，内存包含堆栈以及用于保存用户会话数据、游标信息和排序区的UGA。在多线程服务器(共享服务器)中，UGA被分配在SGA中，所以在这种配置下UGA不是造成ORA-04030错误的原因。因此，ORA-04030表示进程需要更多内存(堆栈、UGA或PGA)来执行其任务。
ORA-04030错误可能是进程本身导致的，例如进程需要过多的内存，或者一些其他原因导致操作系统内存被耗尽，例如，SGA分配太大或系统虚拟内存(物理内存+交换空间)中要容纳的进程过多。许多操作系统会对单个进程能够获取的内存量加以限制，以便自我保护。在大部分情况下，发生ORA-04030错误的进程并非总是内存损耗的元凶，错误的发生仅仅是因为此进程无法取得所需的内存造成的。
MOS文档(ID 1548826.1和199746.1)对该错误有非常详细的说明。如果发生了ORA-04030错误，那么会在告警日志中记录详细信息。若发生ORA-04030错误，则可以从以下几个方面去排查该错误：
1)是否仍然有足够的可用内存?
主要使用操作系统特定的工具(top、topas、vmstat、swapon -s)来检查内存使用情况。如果有足够的内存可用，那么就需要检查操作系统是否存在强制限制。如果内存已被耗尽，那么就需要找出内存被用到了哪些地方。
2)是否发置了操作系统限制?
如果仍有足够的内存可用，那么有可能是进程需要使用的内存量是不被允许的。使用命令“ulimit -a”查看操作系统限制。尤其对于“data seg size”选项，应该设置为unlimited。在RAC或GRID环境中，由于数据库监听是通过CRS进行启动，所以监听继承了root用户的ulimit限制。如果在root的ulimit限制中data(kbytes)的限制为1310KB，那么表示每个通过监听连接的进程能分配的内存资源不能超过1310KB。
3)是否设置了Oracle限制?
查询参数PGA_AGGREGATE_TARGET的大小，该参数限制一个实例可以分配的PGA总量。以下查询用于查找分配给所有会话的PGA区的内存总量：
4)哪个进程需要的内存过多?
一些操作会需要大量的进程内存，例如，大型的PL/SQL表或大量的排序操作。在这些情况下，在出现错误ORA-04030之前，进程将会运行一段时间，所以，可以在这段时间内找出内存分配的位置和原因。可以使用以下查询来查找所有Oracle进程的PGA和UGA大小：
通常，从操作系统的角度来确认进程内存使用情况，是一个好办法。毕竟，使用过多内存的不一定是Oracle Servet。进程。
5)如何收集有关进程实际正在执行的任务的信息?
可以做heapdump，然后分析dump结果：
或者使用event，如下：
设置系统级事件来产生ORA-04030错误时的trace文件，设置evem如下：
得到报错的dump文件后，关闭生成event：
(2)ORA-04031错误 SGA中的内存池由不同大小的内存块组成。当数据库实例启动时，大量的内存块被分配到不同的池中并且由空闲列表哈希Bucket追踪。随着时间推移，由于内存块被分配和回收，内存块会根据它们的大小在池中的不同空闲列表Bucket中移动。
当Oracle不能找到一个足够大的内存块来满足用户操作所带来的内部分配请求的时候，ORA-04031错误就可能在SGA的任何一个池中[Large Pool、Java Pool、Streams Pool(10g新增)、Shared Pool]出现。ORA-04031错误信息会指出哪个池出了问题。如果错误指出问题不在共享池中，那么这通常意味着对应用环境来说，出问题的池配置的太小了。可以将出问题的池增大，然后继续观察后续的问题。如果使用Oracle 10g的ASMM功能，那么MMAN进程会随着时间推移，尝试根据内存需要收缩或者增大SGA中不同组件的大小。如果在Large Pool、Streams Pool或者Java Pool中遇到了ORA-04031错误，那么可以通过增大SGA_TARGET，使MMAN可以管理更多的内存。
MOS文档(ID 2016002.1和146599.1)对ORA-04031有非常详细的说明。
在Oracle 9i和之后的版本，共享池可以被划分为子池。每个子池是一个小号的共享池，有它自己的空闲列表，内存结构条目和LRU列表。这是一个对共享池和大池的可扩展性的改变，现在每一个子池都由一个Child Latch来保护，因此可以增加这些池的吞吐量。这意味着不再有之前版本的对于共享池和大池的单独Latch的竞争。共享池中的保留区域也被平均地划分到每个子池中。
在Oracle 9.2.0.5或者更高版本中，当发生ORA-04031错误时，会生成一个trace文件(通过_4031_dump_bitvec参数控制)。ORA-04031的报错形式如下：
在这个例子中，问题发生在共享池。错误消息也包含内存请求失败的大小的信息。在这里，请求SQL Area中4192bytes时失败，并且发生在第6个子池中。
子池的个数跟以下3个方面相关：
1)CPU的个数系统中每4个CPU可以分配一个子池，最多分配7个子池，由参数CPU_COUNT控制。
2)参数SHARED_POOL_SIZE大小若ASMM或AMM没有启用(即手动管理内存，SGA_TARGET和MEMORY_TARGET都没有设置)，则在子池的内存分配原则见表。
若启用了ASMM或AMM，参数SHARED_POOL_SIZE被显式地设置了值，则使用上表的分配原则。如果没有显式地设置该参数的值，那么SHARED_POOL_SIZE的值取SGA_TARGET的50%。如果使用的是AMM，则SGA_TARGET的值取MEMORY_TARGET的60%。
3)隐含参数“_KGHDSIDX_COUNT”的大小，默认为1，表示1个子池。设置的SQL命令为：“ALTER SYSTEM SET"_KGHDSIDX_COUNT"=2 SCOPE=SPFILE;”。
在以上规则中，若设置了隐含参数“_KGHDSIDX_COUNT”为大于1的值，则以该参数为准，否则取1)和2)中的最小值。例如，如果在Oracle 11g中，有16个CPU，MEMORY_TARGET为4.2G，SGA_TARGET和SHARED_POOL_SIZE的值都为0，那么16个CPU可以分配4个子池，SHARED_POOL_SIZE的值为1.26G(4.2×0.6×0.5)，可以分配2个子池，每个子池为630MB。由于没有设置隐含参数“KGHDSIDX_COUNT”，所以，该系统的子池个数为MIN(4,2)=2。
子池的创建是在启动过程中SGA创建时发生的，所以修改隐含参数“_KGHDSIDX_COUNT”、SHARED_POOL_SIZE和CPU_COUNT之后，需要重新启动数据库，子池的数量才能变动。
具体的子池分配、内存情况及剩余内存情况可以使用如下的SQL语句查询：
通过以下查询可以详细列举不同子池的Free内存块情况：
需要注意的是，在ASM环境中也用到了共享池。在Oracle10gR1版本的ASM实例上有ORA-04031的问题，原因是默认值太小了，不能适应磁盘组管理活动的需要。
当试图在共享池中分配大块连续内存而失败时，Oracle会首先从池中清理当前不用的对象从而使得空闲内存碎片(chunk：内存块)得以合并。如果这样仍然没有足够大的单个chunk来满足分配需要，那么会产生ORA-04031报错。有许多ORA-04031错误直接原因都是由于共享池的大小或调整不当造成的。
在日常维护上可以查询视图V$SHARED_POOL_RESERVED，当REQUEST_FAILURES＞0时，则说明Shared Pool在内存分配上存在一定的问题。
如果问题可以重现，那么可在执行有问题的SQL语句前，在会话级别对事件进行设置：
从Oracle 9.2.0.5版本开始，除了在请求heapdump时使用1、2、3或32等级，还可以使用相同等级并加值536870912，这样将会在此等级上再进一步显示5个最大的subheaps同时每个subheap下显示相关5个最大的heap areas。
下面给出分析ORA-04031产生的TRACE文件的一般步骤：
1)首先需要确认报错的是SGA的哪个池、哪个子池、需要分配哪部分内存、需要分配多大内存等问题。
大部分信息可以从报错信息直接获取到。检查告警日志并查看错误是否记录，但是，不是所有的ORA-04031错误都会记录在告警日志中。如果错误被记录，请检查是SGA的哪部分收到此错误，共享池，大池，Java池或Streams池。找到发生ORA-04031错误时的trace文件。
2)在trace文件中搜索关键字“Memory Utilization of Subpool”(子池可以直接搜索Subpool)和granule size(粒度大小)，确认子池的个数及粒度的大小等参数。
3)找到各个Subpool的使用情况。
从Subpool部分分析哪个组件用的内存最多，free memory还剩多少内存?可能有的子池的free memory已经为0了。可以从视图V$SGASTAT来检查是否有组件表现出非正常增长，查询SQL可以为“SELECT*FROM V$SGASTAT A WHERE A.NAME='obj star memo';”。如果V$SGASTAT中没有记录，那么也可以查询：DBA_HIST_SGASTAT视图。
①如果用的最多的是SQLA(SQLAREA)，那么很可能就是没有使用绑定变量。
②如果是不常见的组件(例如，obj stat memo)，那么很可能就是BUG。如果是BUG，那么可以拿关键字去MOS上进行搜索，基本上可以找到相关内容。
在产生的TRACE文件中还需要关注“LIBRARY CACHE STATISTICS”，它代表库缓存的信息，如下：
在Library Cache统计信息里，找到“hit ratio”百分比，这指示了碎片问题。目标是使得“hit ratio”尽可能地接近100%。另外要查看reloads和invalids信息。reloads和invalids很多意味着库缓存中发生了很多内存清理，可能意味着应用低效和碎片化。
在AWR中，可以查看“Load Profile”、“Shared Pool Advisory”和“Library Cache Activity”等部分进行分别分析。
可以通过如下的SQL语句分析ORA-04031出现的次数和需要分配的字节数：
(1)ORA-04030错误 ORA-04030报错形如“ORA-04030 'out of process memory when trying to allocate%s bytes(%s,%s)'”，该错误意味着Oracle Server进程无法从操作系统分配更多内存。该内存由PGA组成，其内容取决于服务器配置。对于专用的服务器进程，内存包含堆栈以及用于保存用户会话数据、游标信息和排序区的UGA。在多线程服务器(共享服务器)中，UGA被分配在SGA中，所以在这种配置下UGA不是造成ORA-04030错误的原因。因此，ORA-04030表示进程需要更多内存(堆栈、UGA或PGA)来执行其任务。
ORA-04030错误可能是进程本身导致的，例如进程需要过多的内存，或者一些其他原因导致操作系统内存被耗尽，例如，SGA分配太大或系统虚拟内存(物理内存+交换空间)中要容纳的进程过多。许多操作系统会对单个进程能够获取的内存量加以限制，以便自我保护。在大部分情况下，发生ORA-04030错误的进程并非总是内存损耗的元凶，错误的发生仅仅是因为此进程无法取得所需的内存造成的。
MOS文档(ID 1548826.1和199746.1)对该错误有非常详细的说明。如果发生了ORA-04030错误，那么会在告警日志中记录详细信息。若发生ORA-04030错误，则可以从以下几个方面去排查该错误：
1)是否仍然有足够的可用内存?
主要使用操作系统特定的工具(top、topas、vmstat、swapon -s)来检查内存使用情况。如果有足够的内存可用，那么就需要检查操作系统是否存在强制限制。如果内存已被耗尽，那么就需要找出内存被用到了哪些地方。
2)是否发置了操作系统限制?
如果仍有足够的内存可用，那么有可能是进程需要使用的内存量是不被允许的。使用命令“ulimit -a”查看操作系统限制。尤其对于“data seg size”选项，应该设置为unlimited。在RAC或GRID环境中，由于数据库监听是通过CRS进行启动，所以监听继承了root用户的ulimit限制。如果在root的ulimit限制中data(kbytes)的限制为1310KB，那么表示每个通过监听连接的进程能分配的内存资源不能超过1310KB。
3)是否设置了Oracle限制?
查询参数PGA_AGGREGATE_TARGET的大小，该参数限制一个实例可以分配的PGA总量。以下查询用于查找分配给所有会话的PGA区的内存总量：
4)哪个进程需要的内存过多?
一些操作会需要大量的进程内存，例如，大型的PL/SQL表或大量的排序操作。在这些情况下，在出现错误ORA-04030之前，进程将会运行一段时间，所以，可以在这段时间内找出内存分配的位置和原因。可以使用以下查询来查找所有Oracle进程的PGA和UGA大小：
通常，从操作系统的角度来确认进程内存使用情况，是一个好办法。毕竟，使用过多内存的不一定是Oracle Servet。进程。
5)如何收集有关进程实际正在执行的任务的信息?
可以做heapdump，然后分析dump结果：
或者使用event，如下：
设置系统级事件来产生ORA-04030错误时的trace文件，设置evem如下：
得到报错的dump文件后，关闭生成event：
(2)ORA-04031错误 SGA中的内存池由不同大小的内存块组成。当数据库实例启动时，大量的内存块被分配到不同的池中并且由空闲列表哈希Bucket追踪。随着时间推移，由于内存块被分配和回收，内存块会根据它们的大小在池中的不同空闲列表Bucket中移动。
当Oracle不能找到一个足够大的内存块来满足用户操作所带来的内部分配请求的时候，ORA-04031错误就可能在SGA的任何一个池中[Large Pool、Java Pool、Streams Pool(10g新增)、Shared Pool]出现。ORA-04031错误信息会指出哪个池出了问题。如果错误指出问题不在共享池中，那么这通常意味着对应用环境来说，出问题的池配置的太小了。可以将出问题的池增大，然后继续观察后续的问题。如果使用Oracle 10g的ASMM功能，那么MMAN进程会随着时间推移，尝试根据内存需要收缩或者增大SGA中不同组件的大小。如果在Large Pool、Streams Pool或者Java Pool中遇到了ORA-04031错误，那么可以通过增大SGA_TARGET，使MMAN可以管理更多的内存。
MOS文档(ID 2016002.1和146599.1)对ORA-04031有非常详细的说明。
在Oracle 9i和之后的版本，共享池可以被划分为子池。每个子池是一个小号的共享池，有它自己的空闲列表，内存结构条目和LRU列表。这是一个对共享池和大池的可扩展性的改变，现在每一个子池都由一个Child Latch来保护，因此可以增加这些池的吞吐量。这意味着不再有之前版本的对于共享池和大池的单独Latch的竞争。共享池中的保留区域也被平均地划分到每个子池中。
在Oracle 9.2.0.5或者更高版本中，当发生ORA-04031错误时，会生成一个trace文件(通过_4031_dump_bitvec参数控制)。ORA-04031的报错形式如下：
在这个例子中，问题发生在共享池。错误消息也包含内存请求失败的大小的信息。在这里，请求SQL Area中4192bytes时失败，并且发生在第6个子池中。
子池的个数跟以下3个方面相关：
1)CPU的个数系统中每4个CPU可以分配一个子池，最多分配7个子池，由参数CPU_COUNT控制。
2)参数SHARED_POOL_SIZE大小若ASMM或AMM没有启用(即手动管理内存，SGA_TARGET和MEMORY_TARGET都没有设置)，则在子池的内存分配原则见表。
若启用了ASMM或AMM，参数SHARED_POOL_SIZE被显式地设置了值，则使用上表的分配原则。如果没有显式地设置该参数的值，那么SHARED_POOL_SIZE的值取SGA_TARGET的50%。如果使用的是AMM，则SGA_TARGET的值取MEMORY_TARGET的60%。
3)隐含参数“_KGHDSIDX_COUNT”的大小，默认为1，表示1个子池。设置的SQL命令为：“ALTER SYSTEM SET"_KGHDSIDX_COUNT"=2 SCOPE=SPFILE;”。
在以上规则中，若设置了隐含参数“_KGHDSIDX_COUNT”为大于1的值，则以该参数为准，否则取1)和2)中的最小值。例如，如果在Oracle 11g中，有16个CPU，MEMORY_TARGET为4.2G，SGA_TARGET和SHARED_POOL_SIZE的值都为0，那么16个CPU可以分配4个子池，SHARED_POOL_SIZE的值为1.26G(4.2×0.6×0.5)，可以分配2个子池，每个子池为630MB。由于没有设置隐含参数“KGHDSIDX_COUNT”，所以，该系统的子池个数为MIN(4,2)=2。
子池的创建是在启动过程中SGA创建时发生的，所以修改隐含参数“_KGHDSIDX_COUNT”、SHARED_POOL_SIZE和CPU_COUNT之后，需要重新启动数据库，子池的数量才能变动。
具体的子池分配、内存情况及剩余内存情况可以使用如下的SQL语句查询：
通过以下查询可以详细列举不同子池的Free内存块情况：
需要注意的是，在ASM环境中也用到了共享池。在Oracle10gR1版本的ASM实例上有ORA-04031的问题，原因是默认值太小了，不能适应磁盘组管理活动的需要。
当试图在共享池中分配大块连续内存而失败时，Oracle会首先从池中清理当前不用的对象从而使得空闲内存碎片(chunk：内存块)得以合并。如果这样仍然没有足够大的单个chunk来满足分配需要，那么会产生ORA-04031报错。有许多ORA-04031错误直接原因都是由于共享池的大小或调整不当造成的。
在日常维护上可以查询视图V$SHARED_POOL_RESERVED，当REQUEST_FAILURES＞0时，则说明Shared Pool在内存分配上存在一定的问题。
如果问题可以重现，那么可在执行有问题的SQL语句前，在会话级别对事件进行设置：
从Oracle 9.2.0.5版本开始，除了在请求heapdump时使用1、2、3或32等级，还可以使用相同等级并加值536870912，这样将会在此等级上再进一步显示5个最大的subheaps同时每个subheap下显示相关5个最大的heap areas。
下面给出分析ORA-04031产生的TRACE文件的一般步骤：
1)首先需要确认报错的是SGA的哪个池、哪个子池、需要分配哪部分内存、需要分配多大内存等问题。
大部分信息可以从报错信息直接获取到。检查告警日志并查看错误是否记录，但是，不是所有的ORA-04031错误都会记录在告警日志中。如果错误被记录，请检查是SGA的哪部分收到此错误，共享池，大池，Java池或Streams池。找到发生ORA-04031错误时的trace文件。
2)在trace文件中搜索关键字“Memory Utilization of Subpool”(子池可以直接搜索Subpool)和granule size(粒度大小)，确认子池的个数及粒度的大小等参数。
3)找到各个Subpool的使用情况。
从Subpool部分分析哪个组件用的内存最多，free memory还剩多少内存?可能有的子池的free memory已经为0了。可以从视图V$SGASTAT来检查是否有组件表现出非正常增长，查询SQL可以为“SELECT*FROM V$SGASTAT A WHERE A.NAME='obj star memo';”。如果V$SGASTAT中没有记录，那么也可以查询：DBA_HIST_SGASTAT视图。
①如果用的最多的是SQLA(SQLAREA)，那么很可能就是没有使用绑定变量。
②如果是不常见的组件(例如，obj stat memo)，那么很可能就是BUG。如果是BUG，那么可以拿关键字去MOS上进行搜索，基本上可以找到相关内容。
在产生的TRACE文件中还需要关注“LIBRARY CACHE STATISTICS”，它代表库缓存的信息，如下：
在Library Cache统计信息里，找到“hit ratio”百分比，这指示了碎片问题。目标是使得“hit ratio”尽可能地接近100%。另外要查看reloads和invalids信息。reloads和invalids很多意味着库缓存中发生了很多内存清理，可能意味着应用低效和碎片化。
在AWR中，可以查看“Load Profile”、“Shared Pool Advisory”和“Library Cache Activity”等部分进行分别分析。
可以通过如下的SQL语句分析ORA-04031出现的次数和需要分配的字节数：
【答案解析】