LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon