When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session.
Oracle PGA Memory Management
Theory
By default, Oracle Database automatically manages the total amount of memory dedicated to the instance PGA.
You can control this amount by setting the initialization parameter PGA_AGGREGATE_TARGET.
This sets a logical ceiling to Oracle for all database server processes and background processes , but this ceiling can be exceeded.
What if the value is not set
If PGA_AGGREGATE_LIMIT is not set, then Oracle Database determines an appropriate default limit.
If PGA_AGGREGATE_TARGET is not set, the database chooses a default value for it.
Type of Control:
PGA_AGGREGATE_TARGET (Target): Acts as a target, not a strict limit. Oracle uses this to estimate the ideal memory for SQL operations.
PGA_AGGREGATE_LIMIT (Limit): Sets an absolute, hard maximum on the aggregate PGA memory that can be allocated.
Behavior When Exceeded:
Target: PGA can exceed the target, potentially causing swapping if it grows too high.
Limit: If this limit is hit, Oracle immediately terminates sessions consuming the most "untunable" PGA memory.
Calculate Values:
Target: Usually set manually by the DBA based on system memory.
Limit: Defaults to the greater of either 2GB, 200% of PGA_AGGREGATE_TARGET, or 3MB per process (up to 120% of physical memory minus SGA).
How to Calculate PGA_AGGREGATE_TARGET:
PGA_AGGREGATE_TARGET
is calculated based on available physical memory.
Take 80% of total OS memory(reserving 20% for the OS)
For OLTP systems it is typically set to 20% of the taken memory. (Total Physical Memory * 0.8) * 0.2
For DSS/Data Warehouse systems it is set to 50% of the taken memory. (Total Physical Memory * 0.8) * 0.5
PGA_AGGREGATE_LIMIT
SELECT limit_value*3 as 3x_proc_limit_mb
FROM V$RESOURCE_LIMIT
WHERE resource_name = 'processes';
Take the higher of these 3 values:
A. 2*PGA_AGGREGATE_TARGET,
B. 3x_proc_limit_mb (above)
C. 2Gb
But... do not exceed 90% of physical memory minus total SGA.
To see the total SGA:
SELECT ROUND(SUM(value)/1024/1024) "TOTAL SGA (MB)" FROM V$SGA;
Purpose:
Target: Used for managing performance and tuning automatic memory usage in work areas (sorts, hashes).
Limit: Used to prevent overall PGA consumption from taking down the server by exhausting physical memory.
Monitor:
See Sizing estimated performance:
SELECT * FROM V$PGA_TARGET_ADVICE
ORDER BY pga_target_for_estimate;
See Current Usage:
SELECT ROUND(value/1024/1024) AS curr_pga_target_mb
FROM V$PGASTAT
WHERE name = 'aggregate PGA target parameter';
Maximum instance PGA allocated in megabytes since the database was started:
SELECT ROUND(value/1048576) as value_mb
FROM V$PGASTAT
WHERE name='maximum PGA allocated';
If AMM is used
If AMM (Automatic Memory Management) is used ( == MEMORY_TARGET set), only the memory_target is set, and Oracle manages the PGA, and this parameter acts as a minimum.
With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA.
By setting both of these to zero, there are no minimums, and the SGA and instance PGA can grow as needed.
The sizing of SQL work areas remains automatic.
In addition, you can use the PGA_AGGREGATE_LIMIT initialization parameter to set an instance-wide hard limit for PGA memory. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management.
For AMM use:
SELECT * FROM V$MEMORY_TARGET_ADVICE ORDER BY memory_size;
If you are hitting ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT error message you have 2 options
Option 1:
Set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.