ORA-04030: out of process memory

ORA-04030: out of process memory

Error in Alert Log
Sun Nov 25 02:14:17 2018
Unable to allocate memory for new incident error in file E:\ORACLE12C\diag\rdbms\ic\ic\trace\ic_m001_6448.trc:
ORA-04030: out of process memory when trying to allocate 20520 bytes (pga heap,KTI PGA static small pool)

Solution
Following are the steps to check for solution:

1. Check the size of Physical Memory in the System.

2. Check the following parameters allocated in the Oracle.

Show parameter MEMORY_TARGET

show parameter SGA_TARGET

show parameter PGA

Note: If Memory target value is set then sga_target & pga_aggregate_target is 0 if not then it is the minimum value of memory allocated at time of startup.

3. If you have enough physical memory then you can increase the MEMORY_TARGET parameter value to sufficient value.
Because memory target will manage the PGA and SGA both. If Memory target is not in use then increase the PGA value separately will help.

Estimate the PGA value with help of Maximum session and process running

1. Check the Maximum utilization column of table for getting maximum session made from last startup.

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ('sessions', 'processes');

--Check history of maximum value for session utilized
select resource_name,max(max_utilization) from DBA_HIST_RESOURCE_LIMIT
where resource_name in ('sessions','processes') group by resource_name;

2. Use maximum utilization session value in below query to find the optimal value for PGA.

SELECT MAx_utilization_session*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';

3. Change the PGA Aggregate parameter meter according to that

ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;

Note: its good to take 3 value as set in pga_Aggregate_target
alter system set pga_aggregate_limit=pga_aggregate_target*3 Scope=both;

Check the current PGA memory allocation

For memory allocation total by process:
SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) max,
ROUND(SUM(pga_alloc_mem)/(1024*1024),2) alloc,
ROUND(SUM(pga_used_mem)/(1024*1024),2) used,
ROUND(SUM(pga_freeable_mem)/(1024*1024),2) free
FROM V$PROCESS;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.