诊断并解决 ORA-4030 错误

--摘抄于Metalink

诊断并解决 ORA-4030 错误 

 

适用于:

Oracle Database - Enterprise Edition - 版本 8.1.7.4 和更高版本

本文档所含信息适用于所有平台

 

用途

如何诊断 ORA-4030 错误

 

排错步骤

 

诊断并解决 ORA-4030 错误

 

ORA-4030 意味着什么？

 

你可能在日志文件中或者屏幕上看到这个错误: ORA-04030 'out of process memory when trying to allocate %s bytes (%s,%s)'

 

该错误意味着 Oracle Server 进程无法从操作系统分配更多内存。该内存由 PGA（Program Global Area）组成，其内容取决于服务器配置。对于专用的服务器进程，内存包含堆栈以及用于保存用户会话数据、游标信息和排序区的 UGA（User Global Area）。在多线程配置中（共享服务器），UGA 被分配在 SGA（System Global Area）中，所以在这种配置下 UGA 不是造成 ORA-4030 错误的原因。

 

因此，ORA-4030 表示进程需要更多内存（堆栈 UGA 或 PGA）来执行其任务。

 

是什么导致了该错误？

 

由于发生了这个错误，您因此无法从操作系统分配内存。这个错误可能是进程本身导致的，例如进程需要过多的内存，或者一些其他原因导致操作系统内存被耗尽，例如 SGA 太大或系统虚拟内存（物理内存 + 交换空间）中要容纳的进程过多。许多操作系统会对单个进程能够获取的内存量加以限制，以便自我保护。所以我们就会有下列问题：

 

问题：

 

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

 

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

 

    是否设置了 Oracle 限制？

 

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

 

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

 

下面我们将讨论这些内容。

 

其他主题：

 

    有关避免此错误的一般建议

 

 

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

 

要回答这个问题，我们需要使用操作系统特定的工具来检查内存使用情况。

 

    1.OpenVMS 系统：show memory 会提供关于物理内存和页面文件使用情况的信息：

 

    Physical Memory Usage (pages):     Total        Free      In Use    Modified

    Main Memory (256.00Mb)          32768       24849        7500         419

 

                      .....

Paging File Usage (blocks):                      Free   Reservable      Total

  DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS    30720       30720        39936

  DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS    226160      201088      249984

  DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS  462224      405296      499968

  

   作为一般准则，页面文件中的可用空间总和应不低于整个空间总和的一半。

   交换文件应几乎不使用，可用空间的大小应大致等于总空间的大小。

 

  2. Windows 系统：在“任务管理器”的性能选项卡中检查内存使用情况。

 

  3. Unix 系统：每种 unix 系统通常都有自己的工具来检查系统上的全局内存使用情况，如 top、vmstat……并且在不同的 OS 上，内存管理的运作方式各不相同。

 

        top 通常会显示物理内存和交换空间的统计信息

 

        swapon -s 显示交换空间使用情况

 

        vmstat 显示可用物理内存

Linux 上的 top 输出示例：

 

top - 10:17:09 up  1:27,  4 users,  load average: 0.07, 0.12, 0.05

Tasks: 110 total,   4 running, 105 sleeping,   0 stopped,   1 zombie

Cpu(s):         0.3% user,       1.6% system,           0.0% nice,                98.0% idle

Mem:   1033012k total,      452520k used,    580492k free,       59440k buffers

Swap:  1052248k total,                   0k used,  1052248k free,   169192k cached

 

如果有足够的内存可用，请检查操作系统是否存在强制限制。如果内存已被耗尽，我们就需要找出内存被用到了哪些地方。

 

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

 

1.如果似乎仍剩余大量的虚拟内存，那么有可能是我们需要使用的内存量是不被允许的。下面的步骤可以用来检查由操作系统实施的限制。

UAF> show oracle7

 

Username: ORACLE7                          Owner:  Oracle7 DBA

Account:  SUPPORT                          UIC:    [200,2] ([SUPPORT,ORACLE7])

CLI:      DCL                              Tables: DCLTABLES

Default:  DISK$BOBBIE_USER1:[ORACLE7]

LGICMD:   LOGIN

Flags:

Primary days:   Mon Tue Wed Thu Fri

Secondary days:                     Sat Sun

No access restrictions

Expiration:            (none)    Pwdminimum:  6   Login Fails:     0

Pwdlifetime:           (none)    Pwdchange:   3-DEC-1997 15:38

Last Login: 27-MAY-2003 14:54 (interactive), 26-MAY-2003 16:15 (non-interactive)

Maxjobs:             0  Fillm:          1200  Bytlm:         180000

Maxacctjobs:     0  Shrfillm:           0  Pbytlm:                  0

Maxdetach:        0  BIOlm:         500  JTquota:         8192

Prclm:                20  DIOlm:         500  WSdef:            2500

Prio:                      4  ASTlm:      4000  WSquo:           4096

Queprio:              0  TQElm:      4000  WSextent:     30000

CPU:        (none)  Enqlm:       18000  Pgflquo:       750000

Authorized Privileges: .....

 

$ sho proc/id=20200139/quota

 

24-JUN-2003 12:30:54.39   User: ORACLE7          Process ID:   20200139

                          Node: BOBBIE          Process name: "ORA_BOB901_PMON"

 

Process Quotas:

 Account name: SUPPORT

 CPU limit:               Infinite  Direct I/O limit:            100

 Buffered I/O byte count quota:   9994816  Buffered I/O limit:       100

 Timer queue entry quota:         99  Open file quota:       29997

 Paging file quota:              145968  Subprocess quota:         10

 Default page fault cluster:          64  AST quota:                    496

 Enqueue quota:                      49995  Shared file limit:                0

 Max detached processes:             0  Max active jobs:           

 

  2.Windows 系统：在 Microsoft Windows 操作系统中，Oracle 各个进程是在一个进程中以多线程实施的。对于 32 位的系统，可寻址的内存量为 2Gb（包括堆栈、PGA 和 SGA）。此限制可以增加到 3Gb 或更高。有关更多信息，请参阅“Oracle Database and the Windows NT memory architecture, Technical Bulletin”。对于 64 位的系统这个限制就高多了。Oracle 进程使用的总内存（不包括进程堆栈和代码）可通过此查询进行确定。

 

  3. Unix 系统：使用 limit/ulimit shell builtin 命令。请注意，unlimited 的不一定意味着不受限制，实际也可能是基于历史原因的限制，例如 2Gb。推荐基于真实需要的量进行设置：

Linux 输出示例：

 

aroelant@aroelant-be:~> ulimit -a

 

core file size                   (blocks, -c)    0

data seg size                  (kbytes, -d)    unlimited

file size                              (blocks, -f)    unlimited

max locked memory     (kbytes, -l)    unlimited

max memory size        (kbytes, -m)    unlimited

open files                                        (-n)    1024

pipe size                     (512 bytes, -p)    8

stack size                         (kbytes, -s)    unlimited

cpu time                         (seconds, -t)    unlimited

max user processes                    (-u)    7168

virtual memory               (kbytes, -v)    unlimited

 

有的问题可能是因为限制设置得过低造成的，所以需要进行相应的增加。也有的问题可能是由于我们需要使用的太多造成的。

 

请注意：其他 OS 参数设置（例如 maxuproc）可能会导致问题

 

例如，”ORA-4030 (QERHJ HASH-JOI,KLLCQAS:KLLSLTBA)”

Status: 92,Closed, Not a Bug

 

***来自于 bug - "Increased MAXUPROC from 1000 to 2000, restarted the listener and ORA-4030 errors were resolved"

 

是否设置了 Oracle 限制？

 

从 Oracle Version 9i 开始我们引入了参数 PGA_AGGREGATE_TARGET，该参数尝试限制一个实例可以分配的 PGA 总量。“Automatic PGA Memory Managment”部分提供了关于此问题的更多信息。以下查询可用于查找分配给所有会话的 PGA 区的内存总量：

 

SQL> select

         sum(value)/1024/1024 Mb

         from v$sesstat s, v$statname n

         where n.STATISTIC# = s.STATISTIC#

and name = 'session pga memory';

 

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

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

 

SQL> col name format a30

SQL> 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 进程。通常，对于服务器进程而言，Oracle 和操作系统之间基本都可以就内存的使用情况达成一致。通过以下命令，您可以查找操作系统中进程的内存使用情况。

 

  1. OpenVMS 系统：show system 将显示关于进程和资源的总体使用情况。频繁调用页面失败的进程通常会使用大量虚拟内存。“Pages”列指示物理页的使用情况。show process/continious 命令显示物理（工作集）和虚拟内存的使用情况。

$ show system/pag  OpenVMS V7.2-1 on node BOBBIE 13-JUN-2003 09:56:30.44 Uptime 17 18:58:18

Pid   Process Name   State   Pri   I/O       CPU        Page flts   Pages

20200101  SWAPPER    HIB    16      0      0 00:00:02.45   0            0

20200106 CLUSTER_SERVER  HIB    13   104     0 00:00:00.03   87          104

20200107 CONFIGURE      HIB    10     21     0 00:00:00.06   77          17

 

$ sho process/id=xxx/cont:

Process AROELANT                            10:00:53

State CUR                                Working set 131

Cur/base priority 6/4            Virtual pages 11714

Current PC 800D9B28   CPU time 0 00:00:01.28

Current PSL 00000003                Direct I/O 178

Current user SP 7A5227F0       Buffered I/O 962

PID 20200469                         Page faults 1312

UIC [SUPPORT,AROELANT]  Event flags C0000003

                                                                C0000000

 

2.Windows 系统：在 Microsoft windows 系统中， Oracle 是通过在单个进程中使用多个线程来实施的。直到现在，尚未找到可以查看每个线程的内存使用情况的方法。但是，我们可以检查 Oracle 和操作系统是否就 Oracle 所使用的内存达成一致。从操作系统的角度看，我们可以使用任务管理器。单击查看按钮并选择选择列(S)...，确保已选中虚拟内存大小(V)。oracle.exe 的虚拟内存大小  列中显示的大小应与 SGA、总 PGA 内存以及进程堆栈和代码大小的总和相同。以下查询可用于获取 Oracle 所查看的内存大小，但是不包括进程堆栈和代码大小：

SQL> select

              sum(bytes)/1024/1024 Mb

           from (

                  select

                     bytes

                  from

                     v$sgastat

                  union

                  select

                     value bytes

                  from

                     v$sesstat s, v$statname n

                  where

                      n.STATISTIC# = s.STATISTIC# and

                      n.name = 'session pga memory'

                  );

MB

----------

517.296406

 

在我的系统中，这个值要比通过任务管理器所看到的 VM值小约 30 Mb。

  当您确定 Oracle 就是那个正在大量使用内存的进程时，此查询会显示使用内存最多的会话

 

  3. Unix 系统：top 是一个非常有用的工具，您可以自定义列显示和基于关键字排序。ps 命令在大部分系统上都可用，但具体使用方法不尽相同。例如，在 Linux 系统上，“ps -AF --sort resident”会列出具有最大驻留集大小的所有进程。另请参阅“UNIX: Determining the Size of an Oracle Process”。

 

 

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

这部分将只讨论 Oracle Server 进程。通过前面介绍的方法，您应该可以确定一个或多个 Oracle Server 进程导致了内存消耗。请记住，并非总是出现 ORA-4030 的进程导致了内存消耗，这个进程可能只是无法申请到需要的内存而已。

 

对于不断增加内存使用的进程，我们可以在其运行时进行查看以下方面的信息：

 

    您可以使用以下查询检查 v$sqlarea 从而找到进程正在执行的 SQL：

 

SQL> select

 

           sql_text

 

         from

 

            v$sqlarea a, v$session s

 

         where a.address = s.sql_address and

 

                  s.sid =;

 

我们可以做heapdump，并将结果提交给 Oracle 技术支持：

SQL> select PID from v$process p, v$session s where p.addr=s.paddr and sid=;

 

SQL> oradebug setorapid

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

 

 

如果问题间歇出现或某一进程由于报错太快而导致无法进行检查，且这个进程最有可能是内存消耗的原因，那么，在进程发生错误时我们可以使用以下事件来获取 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;

 

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

Oracle技术支持工程师可使用该heapdump查找过多内存分配的原因。

 

 

有关避免此错误的一般建议

  1. 如上所述，一些操作需要大量的内存。对于排序问题，减少 SORT_AREA_SIZE 会有所帮助。Oracle Server 进程会将 PGA 中的 SORT_AREA_SIZE 字节分配给排序操作。如果完成搜索需要更多内存，服务器进程将会使用temporary segment。这意味着，减少 SORT_AREA_SIZE 会对需要大量排序操作的查询性能产生影响。

 

  2. 对于 9i 及更高版本，通过将参数 WORKAREA_SIZE_POLICY 设置为 AUTO，以及在初始化文件中指定 PGA_AGGREGATE_TARGET 的大小，即可启用自动 SQL 执行内存管理功能。使用自动 PGA 内存管理将有助于减少发生 ORA-4030 错误的可能性。请注意，OpenVMS 操作系统在Oracle 9i版本上不支持 PGA_AGGREGATE_TARGET，但是在 Oracle 10g 版本上是支持的。有关更多详细信息，请参阅以下文档：

  "Performance Issues After Increasing Workload",

  "Automatic PGA Memory Managment",

 "Top Oracle 9i init.ora Parameters Affecting Performance"

 

 

 

3.PL/SQL 程序也可分配大量内存，因此可能需要重写应用程序的某些部分。尽管 PL/SQL 表非常容易使用，但它确实需要在 PGA 中分配内存。

 

4.查看 optimizer 策略，一些访问路径可能会因排序操作、较多行上的函数使用等原因而需要更多内存。

 

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

 

6.确保您的操作系统和 Oracle 限制设置合理。

 

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