Error in Alert Log
Sun Nov 25 02:14:17 2018
Unable to allocate memory for new incident error in file E:ORACLE12Cdiagrdbmsicictraceic_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;
