【DB笔试面试528】在Oracle中，如何解决ORA-04030和ORA-04031错误？

♣

题目部分

在Oracle中，如何解决ORA-04030和ORA-04031错误？

♣

答案部分

ORA-04030和ORA-04031都是典型的内存分配错误，下面分别讲解。

（一）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错误，则可以从以下几个方面去排查该错误：

①　是否仍然有足够的可用内存？

主要使用操作系统特定的工具（top、topas、vmstat、swapon -s）来检查内存使用情况。如果有足够的内存可用，那么就需要检查操作系统是否存在强制限制。如果内存已被耗尽，那么就需要找出内存被用到了哪些地方。

②　是否设置了操作系统限制？

如果仍有足够的内存可用，那么有可能是进程需要使用的内存量是不被允许的。使用命令“ulimit -a”查看操作系统限制。尤其对于“data seg size”选项，应该设置为unlimited。在RAC或GRID环境中，由于数据库监听是通过CRS进行启动，所以监听继承了root用户的ulimit限制。如果在root的ulimit限制中data(kbytes)的限制为1310kb，那么表示每个通过监听连接的进程能分配的内存资源不能超过1310kb。

③　是否设置了Oracle限制？

查询参数PGA_AGGREGATE_TARGET的大小，该参数限制一个实例可以分配的PGA总量。以下查询用于查找分配给所有会话的PGA区的内存总量：

SELECT SUM(VALUE) / 1024 / 1024  PGA_SIZE_MB
  FROM V$SESSTAT S, V$STATNAME N
 WHERE N.STATISTIC# = S.STATISTIC#
   AND NAME = 'session pga memory';

④　哪个进程需要的内存过多？

一些操作会需要大量的进程内存，例如，大型的PL/SQL表或大量的排序操作。在这些情况下，在出现错误ORA-04030之前，进程将会运行一段时间，所以，可以在这段时间内找出内存分配的位置和原因。可以使用以下查询来查找所有Oracle进程的PGA和UGA大小：

SELECT SID, NAME, VALUE
  FROM V$STATNAME N, V$SESSTAT S
 WHERE N.STATISTIC# = S.STATISTIC#
   AND NAME LIKE 'session%memory%'
 ORDER BY 3 ASC;

通常，从操作系统的角度来确认进程内存使用情况，是一个好办法。毕竟，使用过多内存的不一定是Oracle Server进程。

⑤　如何收集有关进程实际正在执行的任务的信息？

l可以做heapdump，然后分析dump结果：

SQL> select PID from v$process p, v$session s where p.addr=s.paddr and sid=<SID>;
SQL> oradebug setorapid <PID>
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3
SQL> oradebug dump heapdump 536870917
SQL> oradebug tracefile_name (shows the path and filename information)
SQL> oradebug close_trace

或者使用event，如下所示：

设置系统级事件来产生ORA-04030错误时的trace文件，设置event如下：

alter system set events '4030 trace name errorstack level 3;name HEAPDUMP level 536870917';

得到报错的dump文件后，关闭生成event：

alter system set events '4030 trace name errorstack off';
alter system set events '4030 trace name HEAPDUMP off';

（二）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的报错形式如下所示：

ORA-04031:unable to allocate 4192 bytes of shared memory("shared pool","SELECT/*+FIRST_ROWS*/*F...","sql area(6,0)","kafco:qkacol")

在这个例子中，问题发生在共享池。错误消息也包含内存请求失败的大小的信息。在这里，请求SQL Area中4192byte时失败，并且发生在第6个子池中。

子池的个数跟以下3个方面相关：

①　CPU的个数 系统中每4个CPU可以分配一个子池，最多分配7个子池，由参数CPU_COUNT控制。

②　参数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%。

③　隐含参数“_KGHDSIDX_COUNT”的大小，默认为1，表示1个子池。设置的SQL命令为：“ALTER SYSTEM SET "_KGHDSIDX_COUNT"=2 SCOPE=SPFILE;”。

在以上规则中，若设置了隐含参数“_KGHDSIDX_COUNT”为大于1的值，则以该参数为准，否则取①和②中的最小值。例如，如果在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语句查询：

SELECT 'shared pool (' ||NVL(DECODE(TO_CHAR(KSMDSIDX), '0', '0 - Unused', KSMDSIDX), 'Total') || '):' SUBPOOL,
       ROUND(SUM(KSMSSLEN) / 1048576, 2) size_all_MB,
       ROUND(sum(CASE WHEN LOWER(KSMSSNAM) LIKE LOWER('%free memory%') THEN KSMSSLEN ELSE 0 END)/ 1048576, 2) size_free_mb
  FROM X$KSMSS
 WHERE KSMSSLEN > 0
 GROUP BY ROLLUP(KSMDSIDX)
 ORDER BY SUBPOOL ASC;


--通过以下查询可以详细列举不同子池的Free内存块情况：
set linesize 1000
col sga_heap format a15
col size format a10
SELECT KSMCHIDX SubPool,
       'sga heap(' || KSMCHIDX || ',0)' SGA_HEAP,
       KSMCHCOM CHUNKCOMMENT ,
       decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') "size",
        COUNT(*),
       KSMCHCLS STATUS,
       SUM(KSMCHSIZ) BYTES,
       ROUND(SUM(KSMCHSIZ) / 1048576, 2) MB
  FROM X$KSMSP d
 WHERE KSMCHCOM = 'free memory'
 GROUP BY KSMCHIDX,
          KSMCHCLS,
          'sga heap(' || KSMCHIDX || ',0)',
          KSMCHCOM,
          KSMCHCLS ,
          decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K')
 ORDER BY  SubPool
;

需要注意的是，在ASM环境中也用到了共享池。在Oracle 10gR1版本的ASM实例上有ORA-04031的问题，原因是默认值太小了，不能适应磁盘组管理活动的需要。

当试图在共享池中分配大块连续内存而失败时，Oracle会首先从池中清理当前不用的对象从而使得空闲内存碎片（chunk：内存块）得以合并。如果这样仍然没有足够大的单个chunk来满足分配需要，那么会产生ORA-04031报错。有许多ORA-04031错误直接原因都是由于共享池的大小或调整不当造成的。

在日常维护上可以查询视图V$SHARED_POOL_RESERVED，当REQUEST_FAILURES>0时，则说明Shared Pool在内存分配上存在一定的问题。

如果问题可以重现，那么可在执行有问题的SQL语句前，在会话级别对事件进行设置：

SQL> alter session set events '4031 trace name errorstack level 3';

SQL> alter session set events '4031 trace name HEAPDUMP level 536870914';

从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”（子池，可以7直接搜索Subpool）和granule size（粒度大小），确认子池的个数及粒度的大小等参数。

（3）找到各个Subpool的使用情况

从Subpool部分分析哪个组件用的内存最多，free memory还剩多少内存？可能有的子池的free memory已经为0了。可以从视图V$SGASTAT来检查是否有组件表现出非正常增长，查询SQL可以为“SELECT * FROM V$SGASTAT A WHERE A.NAME='obj stat memo';”。如果V$SGASTAT中没有记录，那么也可以查询DBA_HIST_SGASTAT视图。

l 如果用的最多的是SQLA（SQLAREA），那么很可能就是没有使用绑定变量。

l 如果是不常见的组件（例如，obj stat memo），那么很可能就是BUG。如果是BUG，那么可以拿关键字去MOS上进行搜索，基本上可以找到相关内容。

在产生的TRACE文件中还需要关注“LIBRARY CACHE STATISTICS”，它代表库缓存的信息，如下：

LIBRARY CACHE STATISTICS:  (emphasis added on key areas)
namespace           gets hit ratio      pins hit ratio    reloads   invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR          1201146935     0.999 4127355897    0.997     977858     467750
TABL             9017452     0.998 680339529     1.000      98991          0
BODY            45900052     1.000 102763403     1.000        428          0
TRGR              127502     0.999   1661141     1.000        194          0
INDX              880913     0.990   1846250     0.978      20895          0
CLST               15560     0.997     32730     0.997         58          0
KGLT                   0     0.000         0     0.000          0          0
PIPE                   0     0.000         0     0.000          0          0
LOB                    0     0.000         0     0.000          0          0

在Library Cache统计信息里，找到“hit ratio”百分比，这指示了碎片问题。目标是使得“hit ratio”尽可能的接近100%。另外要查看reloads和invalids信息。reloads和invalids很多意味着库缓存中发生了很多内存清理，可能意味着应用低效和碎片化。

在AWR中，可以查看“Load Profile”、“Shared Pool Advisory”和“Library Cache Activity”等部分进行分别分析。

可以通过如下的SQL语句分析ORA-04031出现的次数和需要分配的字节数：

SELECT INDX,KGHLURCR,KGHLUTRN,KGHLUFSH,KGHLUOPS,KGHLUNFU,KGHLUNFS FROM  SYS.X$KGHLU WHERE INST_ID = userenv('Instance');
      INDX   KGHLURCR   KGHLUTRN   KGHLUFSH   KGHLUOPS   KGHLUNFU   KGHLUNFS
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0       1233      30360      57700     518988        114       4096

其中，KGHLUNFU表示出现ORA-04031的次数，这里指出现了114次的4031错误。KGHLUNFS表示最后一次出现ORA-04031时，申请的分配大小，在以上结果中表示最后申请共享池的大小是4096字节。

综上所述，导致ORA-04031错误的原因有如下几点：

（1）配置问题，表现为某些池配置过小或配置错误。解决办法为增大相应的池（例如，Shared Pool）大小及使用AMM方式来管理内存。如果报错的是Large Pool或Java Pool，那么可以简单的增大参数LARGE_POOL_SIZE和JAVA_POOL_SIZE即可。

（2）内存中存在大量碎片，导致在分配内存的时候没有连续的内存可供分配从而导致ORA-04031错误。解决办法为刷新共享池（ALTER SYSTEM FLUSH BUFFER_CACHE;），该操作在生产库上要慎用。也可以重启数据库，重启数据库会释放内存，还会清理内存碎片。

（3）应用问题，主要表现在①没有使用绑定变量，不使用绑定变量会导致库缓存的过度使用。②有多个子游标，每个子游标都会在共享池中分配空间。③高解析率，可能由于使用了动态PL/SQL或在高负载阶段执行DDL语句。每次DDL语句执行，都会导致所有引用了这个对象的语句失效。下次执行引用了这个对象的SQL语句时，则不得不重新解析并加载到共享池中。④过度的打开CURSOR而不关闭，一般会导致Shared Pool中的ORA-04031错误。⑤SESSION_CACHED_CURSORS、OPEN_CURSOR设置过高。OPEN_CURSOR如果设置的过大，那么会导致Library Cache中很多对象都处于pin状态，而不能释放，那么当申请Shared Pool内存时，通过LRU依然不能找到可用空间，就会导致ORA-04031错误。遇见这种情况可以适当减少OPEN_CURSOR的值。

（4）Subpool的不均衡使用。Subpool的不均衡使用是使用Subpool一个缺点之一，对于这种情况可以设置隐含参数“_ENABLE_SHARED_POOL_DURATIONS＝FALSE”来改变Shared Pool的Subpool内存结构的分配方式，或者完全使用AMM。

（5）Oracle的BUG导致内存泄露。例如，在一些版本中查询V$SEGMENT_STATISTICS这样的视图导致内存泄露，使Shared Pool内存耗光。同样的情形还有类似于“obj stat memory”、“gcs resources”、“ges resources”等。这类内存通常是在分配时就确定了固定的用途，不能用于其它用途，因此极容易产生碎片。

有关ORA-04030错误的更多内容可以参考：http://blog.itpub.net/26736162/viewspace-2138387/。有关ORA-04031错误的更多内容可以参考：http://blog.itpub.net/26736162/viewspace-2138388/。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。