Diagnosing and Resolving ORA-4030 errors
What does an ORA-4030 mean?
You may see this error on screen or in a trace file or log: ORA-04030 'out of process memory when trying to allocate %s bytes (%s,%s)'
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. The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform its job.
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 accommodated for the system virtual memory (physical memory + swap space). Many operating systems impose limits on the amount of memory a single process can acquire to protect itself.
Is there still sufficient memory available?
To answer this question, we will need to use operating system specific utilities to examine the memory usage.
OpenVMS systems: show memory will give you information about physical memory and pagefile usage: 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$<NODE NAME>AXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS 30720 30720 39936 DISK$<NODE NAME>AXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS 226160 201088 249984 DISK$<NODE NAME>_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS 462224 405296 499968 As a general guideline, the sum of the free space in the pagefile(s) should not get lower than half the sum of the total space. The swapfile(s) should almost be unused, free space should be about the same as the total space.
Windows systems: Check MEM Usage in the performance tab in Task Manager.
Unix systems: Every UNIX flavour usually has its own utilities to check for the global memory usage on the system like top, vmstat,... And memory management does work differently on each OS.
top usually displays physical memory and swap space statistics.
swapon -s displays swap space usage
vmstat displays free physical memory
Sample top output on Linux:
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 .....
When sufficient memory is available, check for operating system enforced limits. When the memory has been depleted, we will have to find out where the memory has been used for.
Is there an operating system limit set?
When there still seems to be plenty of virtual memory left, we might not be allowed to use the amount of memory we are asking for. Check for limits imposed by the operating system.
OpenVMS systems: To check for the amount of physical memory you can use, check the working set quotas and the pagefile quota with the authorised utility. Refer to the reference for the OpenVMS section on what quotas are used and how to modify them. Depending on what process and how it is started, the quotas used will not be those of the oracle account. Show process/id=/quota will show you how much quota is left for a process.
$ sho proc/id=20200139/quota
2. Windows systems: On Microsoft Windows operating systems, oracle processes are implemented as threads in 1 process. For 32-bit systems, the addressable amount of memory is 2Gb (including stack, PGA, SGA). This limit can be increased to 3Gb or higher. See "Oracle Database and the Windows NT memory architecture, Technical Bulletin" for more information. That limit is much higher for 64-bit systems. The total memory used by the oracle process, excluding process stack and code, can be determined with this query.
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');
3. Unix systems: The top utility is a useful tool here, as you can customize the display and sort key. The ps command will be available on most systems, but the possibilities will vary. For example, on Linux, 'ps -AF --sort resident' will list all processes with those having the biggest resident set size last. Also refer to"UNIX: Determining the Size of an Oracle Process".
How to collect information on what the process is actually doing
This section will only discuss Oracle server processes. You should have determined with the methods discusses in the previous sections, that one or more oracle server processes are responsible for the memory consumption. Remember it is not always the process of getting the ORA-4030 that is responsible for the memory consumption. It happens to be the process of not getting the memory it requests. For processes steadily increasing their memory, we can have a look while it runs
You can check in v$sqlarea what is being executed with the following query:
select sql_text from v$sqlarea a, v$session s
where a.address = s.sql_address and s.sid =514;
We can force a heap dump and have it examined by oracle support services:
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
When the problem is intermittent or some process fails too fast to examine and it is most likely the cause of the memory consumption, we can use events to get a heap dump when the process hits the error:
SQL> alter session set events '4030 trace name heapdump level 536870917'; or set this event in the database initialization file and restart the instance. - init.ora: event="4030 trace name heapdump level 536870917" - spfile: issue the command: SQL> ALTER SYSTEM SET EVENT='4030 trace name heapdump level 536870917' scope=spfile;
For versions below 220.127.116.11, use level 5 instead of level 536870917. This dump can be used by Oracle Support analysts to find the cause of the excessive memory allocation.
General suggestions on avoiding this error
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", Management", "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 swap space)
Need more review files, scripts or examples? OK Let me know here - Email Me and we have a complete zone of documentation, scripts and solutions.
More information here in the Paddock Zone.