破解ORA-04030

ORA-04030的含义

关于4030的错误，ORACLE给的解释是没有更多内存可以分配给进程使用
，可以理解成，进程需要更多的内存来执行任务，但获取内存失败。

$ oerr ora 04030
04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause:  Operating system process private memory was exhausted.
// *Action:

我们知道，当用户进程通过监听连接到ORACLE服务器，此时ORACLE会分配一部分内存空间（PGA）来处理用户的请求，同时会分配Oracle Server与用户进程通信，负责主要工作。当Oracle Server 进程无法从操作系统申请更多可用内存，系统便会抛出ORA-04030的错误。

因为ORACLE是从PGA中将内存分配给Oracle Server，那么当4030错误出现时，需要考虑PGA部分的配置两种连接模式。

对于专用服务器连接模式，PGA包含堆栈以及用于保存用户会话数据、游标信息和排序区的UGA（User Global Area）。在共享连接模式中，UGA 被分配在 SGA（System Global Area）中而不是PGA，所以在这种模式下，UGA不是造成4030错误的原因，也就是需要重点关注PGA中其他区域。 

导致4030错误的原因

1.进程本身

当进程本身出现问题，最常见的可能就是bug了，这个不花时间讨论。

2.操作系统上是否有可用内存

这一步非常好确认，主流操作系统都提供了检查系统资源使用情况的工具，这里用Unix系统举例，Unix提供了如：top
,swapon
,vmstat
这样的工具来查看当前系统资源的现状。

3.操作系统限制

如果在第二步中检查的结果显示，系统中仍然有大量可用内存，就可以排除硬件级别的一些问题，继而从这里开始，做进一步的检查。在Unix系统中，使用如下命令查看操作系统对资源使用的限制。

$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1028931
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

在这一步中，可能会造成4030错误的原因，会是限制设置过低导致。

4.Oracle限制

与第三步的限制不同的是，本步骤中提到的限制是Oracle通过参数文件对自身使用系统资源的限制。比如SGA,PGA的大小等。与4030错误最接近的限制应该是PGA_AGGREGATE_TARGET
，这个参数控制了一个实例可以获取到的PGA总量automatic pga memory management。

查看PGA总量（这里的总量是指，一个实例中为所有会话分配的PGA之和）

select
sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';

        MB
----------
1276.39923

5.占用内存多的进程

不考虑进程本身的问题，占用内存过多的进程一定与操作大表或者大量排序相关。

当4030错误被抛出之前，这类进程会正常运行一段时间，止到无法再获取更多的内存。此时可以找出最近占用PGA和UGA最大的进程。

col name format a30
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;

       SID NAME                                VALUE
---------- ------------------------------ ----------
      2024 session uga memory max           69315160
      3885 session pga memory max           72822864
      2024 session pga memory max           73805904
      3261 session pga memory max           76492880
      3281 session pga memory max           76558416
      2275 session pga memory max           79376464
      2258 session pga memory max           79376464
      1516 session uga memory              130622472
      1516 session uga memory max          132868512
      1516 session pga memory              133050448
      1516 session pga memory max          135016528

当然，以上的方式是从ORACLE的角度出发，找到占用PGA和UGA过大的进程从而诊断它为问题进程。不过操作系统上不仅仅只有ORACLE在工作，可能还会有其他非ORACLE进程在不断申请新的内存空间，导致ORACLE抛出4030错误。

通常，Oracle和操作系统之间会对内存的使用情况达成一致，想进一步确认，可以通过top
工具确认进程的状况。还有ps
，这也是一个常用的Linux命令，比如ps -AF --sort resident
可以列出有最大驻留集的所有进程。

6.进程在干嘛

当找到占用内存较大的进程后，我们需要知道的是它在干嘛？

• 根据第五步，我们可以找到进程信息，通过SID
  可以找到更多关于进程的信息。

  select sql_text from
  v$sqlarea a, v$session s
  where a.address = s.sql_address ands.sid =<SID>;

• Heapdumpwhat

  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

如果面临的问题是复杂的，比如4030错误间断发生，这时获取进程信息比较困难，可以设置事件
来获取Heapdump。

SQL> alter session set events '4030 trace name heapdump level 536870917';

或者在数据库初始化文件中设置此事件并重新启动实例。

init.ora: event="4030 trace name heapdump level 536870917"
spfile: 运行: SQL> ALTER SYSTEM SET EVENT='4030 trace name heapdump level 536870917' scope=spfile;

Note:对于 低于 9.2.0.5 的版本，请使用级别 5，而非级别 536870917

避免4030错误的建议

• 减少SORT_AREA_SIZE

SORT_AREA_SIZE
specifies (in bytes) the maximum amount of memory Oracle will use for a sort.

sort_area_size指定了用于在PGA中做排序操作的内存量，默认值是65536bytes，除非你的实例被配置成了共享连接模式，否则Oracle是不建议你使用sort_area_size参数的，它推荐你使用PGA_AGGREGATE_TARGET。

当排序操作结束，结果被返回之前，Oracle释放为排序操作分配的内存，但是sort_area_retained_size规定的这部分内存保留，直到最后一行结果被返回，Oracle才释放剩下内存。

综上所述，Oracle Server进程会将PGA中的 SORT_AREA_SIZE分配给排序操作。如果减少这部分空间，意味着其他内存区域富裕了，但是当一项排序任务需要更多内存，服务器进程将会使用temporary segment，那么减少 SORT_AREA_SIZE 会对需要大量排序操作的查询性能产生影响。

• 在某些操作系统上（例如 Microsoft Windows），可能要降低 SGA 的大小以便于 PGA 获得更大的内存。

• 确保操作系统和 Oracle 限制设置合理。

• 确保有足够的可用内存（物理内存和交换空间）。