ORA-04030: out of process memory when trying to allocate

What does an ORA-4030 mean?

This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

What causes this error?

Since you run into this error, you can't allocate memory from the operating system. This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomodated for the systems virtual memory (physical memory + swap space). Many operating systems impose limits on the amout of memory a single process can acquire to protect itself.This leads to the following questions:

Is there an operating system limit set?

You can check limit information’s using >ulimit –a  and find the information  related to memory using vmstat

vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 1  0 5511440 243988 213924 92768888    1    1  1860   639    0    0  7  0 91  2  0

vmstat output contains the following fields:

Procs – r: Total number of processes waiting to run

Procs – b: Total number of busy processes

Memory – swpd: Used virtual memory

Memory – free: Free virtual memory

Memory – buff: Memory used as buffers

Memory – cache: Memory used as cache.

Swap – si: Memory swapped from disk (for every second)

Swap – so: Memory swapped to disk (for every second)

IO – bi: Blocks in. i.e blocks received from device (for every second)

IO – bo: Blocks out. i.e blocks sent to the device (for every second)

System – in: Interrupts per second

System – cs: Context switches

CPU – us:CPU user time

sy:system time

id:idle time

wa:wait time

Which process is requesting too much memory?

It's usually a good idea to confirm the process memory usage from the Operating System point of view. After all, it might no be an oracle server process that is using too much memory.Usually, for server processes, oracle and the operating system more or less agree on memory usage. The following command will allow you to find out the memory usage for processes from the operating system.

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;

How to collect information on what the process is actually doing

You can check in v$sqlarea what is beeing executed with the following query:

SELECT

           sql_text

         FROM

            v$sqlarea a, v$session s

         WHERE a.address = s.sql_address AND

                  s.SID =&SID

an trace using event 4030

Alter session set events '4030 trace name heapdump level 5';

Wecan collect Incident details from the location of oracle diagnostic and send to oracle support

We  can generate ARD report and summit to oracle support for solution  

General suggestions on avoiding this error

·          We can force a heapdump and have it examined by oracle support services:

·          Use ADRCI or Support Workbench to package the incident.

·          As mentioned before, some operations just require a lot of memory. For sort issues, decreasing SORT_AREA_SIZE can help. The Oracle server process will allocate SORT_AREA_SIZE bytes in the PGA for sort operations. When more memory is required to complete the search, the server process will use a temporary segment. This means that lowering SORT_AREA_SIZE can have a performance impact on queries requiring huge sort operations.

·          With 9i and higher, the automatic SQL execution memory management feature is enabled by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET in the initialization file. Using automatic PGA memory management will help reduce the possibility of ORA-4030 errors. Please note that PGA_AGGREGATE_TARGET is NOT supported on OpenVMS in Oracle 9i, but it is in Oracle 10g.  Refer to the following notes for more details:

·          "Performance Issues After Increasing Workload",

·          "Automatic PGA Memory Managment",

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

·          PL/SQL procedures can also allocate lots of memory, so it might be required to rewrite some parts of your application. While a PL/SQL table is easy to use, it does require memory to be allocated in the PGA.

·          Review the optimizer strategy, some access paths might need more memory due to sort operations, the use of functions on more rows,...

·          On some operating systems, like Microsoft windows, the size of the SGA might be decreased to allow bigger PGA's.

·          Make sure your operating system and oracle limits are set reasonably.

·          Make sure there is enough memory available (physical memory and swapspace)