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)
