ORA-04030 message

[nancy rippey]

The following message is being received ORA-04030: out of process mmory when rying to alloate 920 bytes (callheap,temporary memory)

Ouput from swapinfo
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 3072 882 2190 29% 0 - 1 /dev/vg00/lvol2
reserve - 1175 -1175
memory 3147 2298 849 73%
total 6219 4355 1864 70% - 0 -

maxdsiz id st to 0x08000000 (134217728)

Any suggestions on what I should look at/change would be greatly appreciated.

Thanks

[Alan Casey]

this is an Oracle bug:

Bug 1839080 Fixed: 8173
Hash Join
Resource Leaks (eg: Memory Leak)
Complex join queries involving Hash-Join may result in
Memory leak leading to ORA-4030 out of process memory.

[Alan Casey · Accepted solution]

Here's some more information on other possable causes:


Doc ID: Note:199746.1
Subject: How to Resolve ORA-4030 Errors on UNIX
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 16-JUN-2002
Last Revision Date: 07-NOV-2002


PURPOSE ======= Provide assistance in resolving ORA-4030 errors on UNIX systems. SCOPE & APPLICATION =================== This document is useful for DBA's and System Administrators tasked with resolving an ORA-4030 error. BACKGROUND ========== The ORA-4030 error is caused when an Oracle process(while doing work on behalf of a client program) requests additional memory from the operating system, but the operating system cannot accommodate the request. This can occur because the operating system does not have enough physical memory or swap available for the process, the OS is configured to limit the amount of memory available for UNIX processes or an actual Oracle BUG has been encountered. HOW TO RESOLVE THE ORA-4030 =========================== 1. Reduce the PGA (Program Global Area) for the client process encountering the error. This is valid if the database is not configured with MTS (Multi Threaded Server) or Shared Servers. For Oracle 8i and below the major portion of ORA-4030 instances can be solved by reducing the SORT_AREA_SIZE for the sessions. This will lessen the demand that the process places on physical memory. SORT_AREA_SIZE can be modified by the 'alter session' command but persistent changes must be recorded in the init.ora. See the Oracle Reference guide available on your Online Generic Documentation CD-ROM for more information on the 'sort_area_size' parameter. For Oracle 9i and up, the PGA can be sized using the PGA_AGGREGATE_TARGET parameter set in the init.ora or spfile.ora (See [NOTE:153367.1] and [NOTE:146577.1]). 2. Increase the amount of memory a UNIX process can request and use from the operating system. This usually refers to stack and/or data size UNIX process resource limits. This process varies slightly depending on the UNIX platform and the type of UNIX shell you are using. Generally speaking either the 'limit' or 'ulimit' command will allow your System Administrator to increase memory and data size limits. Oracle Support cannot recommend a specific value for these limits. However, doubling existing values or setting them to 'unlimited' is usually sufficient. For more information please refer to: [NOTE:188149.1] How to Display and Change UNIX Process Resource Limits 3. Increase the amount of swap available on your system. You should have 2-3 times the amount of physical memory available as swap space. 4. Finally, if you still experience the problem after addressing the above issues, it's recommended that you move to the latest patchset release to eliminate any possible Oracle product defects: [NOTE:169547.1] Understanding and Obtaining Oracle RDBMS Patchsets REFERENCES ========== [NOTE:19836.1] OERR: ORA 4030 "out of process memory when [NOTE:1023744.6] ORA-04030 -07324: AFTER INCREASING THE SGA OR EXECUTING A LARGE QUERY [NOTE:61896.1] SOLARIS: SGA size, sgabeg attach address and Sun architectures [NOTE:153655.1] SOLARIS Determing Oracle Memory Usage on Solaris AIX Determining Oracle memory usage on AIX [NOTE:174555.1] Determining the Size of an Oracle Process

[nancy rippey]

Alan,
Thanks for the input.
The dba has changed the sort area size the help alleviate the problem. He had done this earlier this am.
The document was a great for helping me understand the problem.
Happy Holidays!
Nancy