Saturday, August 23, 2008

How to resolve ORA-4030 errors on UNIX

Reasons of ORA-04030
When a client program connects to oracle database an oracle process does work on behalf of the client. The Oracle process allocated memory from PGA(Program Global Area). For dedicated server process PGA contains stack, user session data, cursor information and the sort area. For shared server configuration UGA(user session data, cursor information and the sort area) is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

The ORA-4030 can come based on various reasons which is noted below.
1)The Oracle process need more memory in order to request client program and it requests additional memory from Operating System but the Operating System can't serve the request. This is likely to happen if OS does not have enough memory or swap space is not available.

2)There is a memory limit restriction from OS for oracle process to use. Oracle process already use the limit and now it requested additional memory and hence error appears.

3)Might be an oracle bug.

Solution of The Problem
1)Reduce the PGA(Program Global Area): If you don't have enough memory on your system then reducing the PGA can help you to avoid the error. In that case oracle process will not request more memory from OS and if needed more space it will use temporary tablespace segment. But lower the PGA can impact on database performance. If you don't have PGA_AGGREGATE_TARGET set then in order to set lower PGA set SORT_AREA_SIZE parameter to a lower value. You can set it dynamically by,
ALTER SYSTEM SET SORT_AREA_SIZE=10M;

If you have set PGA_AGGREGATE_TARGET then to reduce PGA you have to set PGA_AGGREGATE_TARGET toa lower value.
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;

2)If you did not enabled automatic PGA management then enable the automatic SQL execution memory management feature by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET then it will lessen to happen ORA-4030 error. As by setting automatic SQL execution memory management feature SORT_AREA_SIZE, HASH_AREA_SIZE are handled by oracle automatically and allocate/deallocate by demand.

ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO;

3)If you impose limit to oracle to use memory then increase the amount of memory a UNIX process can request and use from the operating system. The system administrator can see this soft limit
by issuing,
$limit
In order to see hard limit issue,
$limit -h

If you want to increase a specific resource soft limit by specifying it's name and the new value then on
C shell
use,
csh> limit -h datasize 524289
csh> limit -h datasize
To make all resource unlimited issue,
csh>unlimit
If you use
Bash and Korne Shell
then use,
$ulimit -a
In order to set all resource to unlimited.

4)Increase the amount of swap space available on the system. You should have 2-3 times the amount of physical memory available as swap space.

5)Increase the physical RAM on your system if you have lower RAM. If you don't have the scope to increase your RAM then lower the SGA settings. Lowering SGA size will allow to use more PGA.

6)If some PL/SQL procedures need much memory then you might think to rewrite it so that it needs less memory.

7)Install the latest patch set.

No comments:

Post a Comment