# tunning PGA_AGGREGATE_LIMIT#####

 

# tunning PGA_AGGREGATE_LIMIT#####

WITH
MAX_PGA as
(select round(value/1024/1024,1) max_pga from v$pgastat where name=’maximum PGA allocated’),
MGA_CURR as
(select round(value/1024/1024,1) mga_curr from v$pgastat where name=’MGA allocated (under PGA)’),
MAX_UTIL as
(select max_utilization as max_util from v$resource_limit where resource_name=’processes’)
SELECT
a.max_pga “Max PGA (MB)”,
b.mga_curr “Current MGA (MB)”,
c.max_util “Max # of processes”,
round(((a.max_pga – b.mga_curr) + (c.max_util * 5)) * 1.1, 1) “New PGA_AGGREGATE_LIMIT (MB)”
FROM MAX_PGA a, MGA_CURR b, MAX_UTIL c
WHERE 1 = 1;
Referent:
In Oracle release 18c and above: Sizing the PGA in Oracle 19c – How to Account for the MGA Size (Doc ID 2808761.1)
Note: To reduce PGA usage and avoid hitting the PGA_AGGREGATE_LIMIT, you can reduce the value of the PROCESSES parameter based on your system requirement.