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.
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_TARGETis larger than 90% of (physical memory - SGA). The setting can't be below the
- 3 MB times 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
- 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
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...