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

Home » Articles » 12c » Here

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

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

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.

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:

Hope this helps. Regards Tim...

Back to the Top.