8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

PGA_AGGREGATE_LIMIT : A Hard Limit for PGA Usage in Oracle Database 12c Release 1 (12.1)

Oracle 9i introduced automatic management of the PGA using the PGA_AGGREGATE_TARGET parameter. This only provides a soft limit on the PGA used by the instance, in that it will attempt to honour this target, but ultimately it can keep allocating more memory if needed, which could result in the server swapping.

Oracle 12c Release 1 introduced the PGA_AGGREGATE_LIMIT to provide a hard limit on the PGA used by the instance.

Default Settings
Manual Settings
Impact of Limits

Default Settings

The PGA_AGGREGATE_LIMIT is set by default in Oracle 12c. The default value is the greater of the following.

2 GB.
200% of PGA_AGGREGATE_TARGET. It can be set below 200% if the PGA_AGGREGATE_TARGET is larger than 90% of (physical memory - SGA). The setting can't be below the PGA_AGGREGATE_TARGET.
3 MB times the PROCESSES parameter.

Manual Settings

The PGA_AGGREGATE_LIMIT parameter can be set dynamically using the following command. Remember, it can't be set to a value lower than the PGA_AGGREGATE_TARGET parameter value.

ALTER SYSTEM SET pga_aggregate_limit=6G SCOPE=BOTH;

To revert to the pre-12c functionality, set the PGA_AGGREGATE_LIMIT parameter to "0", which means the maximum PGA usage will no longer be managed.

ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;

Impact of Limits

When the instance exceeds the PGA_AGGREGATE_LIMIT setting, Oracle attempts to reduce PGA usage in the following way.

Sessions holding the most untunable memory have their calls aborted in an attempt to get below the PGA usage specified by the PGA_AGGREGATE_LIMIT parameter.
If the instance PGA usage is still too big, the sessions holding the most untunable memory will be killed.

SYS processes and job queue processes are not affected by this limit, but will write their PGA usage to trace files. Parallel queries will be treated like a single session, with their PGA usage summed.

When a call or session is killed messages are directed to the client. In addition errors such as the following are written to the alert log.

Errors in file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_53324.trc  (incident=66188):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

If the PGA_AGGREGATE_LIMIT is not set high enough, your system may experience unexpected behaviour after an upgrade because of this. In addition, so people have reported high numbers of node evictions in RAC environments because of this.

For more information see:

PGA_AGGREGATE_LIMIT

Hope this helps. Regards Tim...

Back to the Top.