ORA-04036: PGA memory exceeds PGA_AGGREGATE_LIMIT
Understand PGA_AGGREGATE_LIMIT and its default limit in 12c, and two ways to set it when ORA-04036 terminates sessions.
From Oracle 12c, PGA_AGGREGATE_LIMIT sets a hard limit on the total PGA memory of the instance. When the limit is reached, Oracle terminates the sessions that use the most PGA memory and reports ORA-04036.
Background
Before 12c, PGA_AGGREGATE_TARGET is a soft target: the real PGA use can reach three times its value, which can cause memory swapping and slow the instance down. PGA_AGGREGATE_LIMIT turns this into a hard limit.
When PGA_AGGREGATE_LIMIT is not set, Oracle uses the largest of the following values:
| Rule | Value |
|---|---|
| Fixed minimum | 2 GB |
| Twice the target | 2 x PGA_AGGREGATE_TARGET |
| Process count | PROCESSES x 3 MB (for example, 300 processes give 900 MB) |
Check the parameters
SHOW PARAMETERS pga_aggregate_limit;
SHOW PARAMETERS sga_target;
SHOW PARAMETERS sga_max_size;
Option 1: remove the limit
Setting the limit to 0 disables it, which restores the pre-12c behaviour:
ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;
A value of 0 does not mean that PGA memory is unlimited: it means the parameter no longer caps the instance.
Option 2: set an explicit limit
Sizing example from the original case: a server with 32 GB of memory and an SGA of 13 GB. 120% of the server memory is about 38 GB; minus the SGA, about 25 GB remain for the PGA. The original case then used lower values, shown here as examples:
ALTER SYSTEM SET pga_aggregate_limit=15G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_limit=7G SCOPE=BOTH;
Notes
- The SGA changes shown in the original case (
sga_targetandsga_max_sizeset to 5G) are not part of the PGA fix and are not reproduced here. - Reference: How do I limit Oracle's memory use (Stack Exchange).