The initialization parameter PGA_AGGREGATE_LIMIT has been introduced since Oracle Database 12c 12.1.0.1. It is used to put a hard limit on PGA memory usage. If PGA usage exceeds the PGA_AGGREGATE_LIMIT value defined, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory.

In Oracle 12.1.0.1, although the Oracle guides state that “By default, the PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the PROCESSES parameter. However, it will not exceed 120% of the physical memory size minus the total SGA size.“, Oracle actually uses 120% of the physical memory size minus the total SGA size to set the default value for PGA_AGGREGATE_LIMIT when PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT are not set.

Or if PGA_AGGREGATE_LIMIT is not set, but PGA_AGGREGATE_TARGET is set, it will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET. The actual value is printed in the alert.log when the database is started up.

The problem with that is PGA + SGA usage could exceeds 100% of the physical memory which causes swapping and the OS unresponsive. Please see Swapping While Sum of SGA Usage And PGA_AGGREGATE_LIMIT Is Larger Than The Amount Of Physical Memory (Doc ID 2273931.1)

From Oracle 12.1.0.2, the calculation of the default maximum is changed to be (90% of physical memory) minus the SGA size. The other 10% is assumed to be kernel memory, client memory, non-heap buffers such as network allocations, and memory in use by utility programs.

Since Oracle Database 12.2.0.1, the default value calculation has been adjusted again as below:

• If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
• If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
• If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
• In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).

The PGA_AGGREGATE_LIMIT initialization parameter can be set dynamically; a database restart is not necessary. However, if PGA_AGGREGATE_LIMIT is explicitly set to a small vlue, the database will not start up but throw the error similar to the following one:

ORA-01078: failure in processing system parameters
ORA-00093: pga_aggregate_limit must be between 6000M and 100000G

Here the parameter PROCESSES matters. If it is set to 2000, for example, then minimum value for PGA_AGGREGATE_LIMIT would be 6000M (6G). The MOS article Limiting Process Size with Database Parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1) has very good information about it.

“Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail. However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.

“Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail. However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.“

Last thing to note: “In general the trend will always be towards higher memory usage with more recent versions, so in going from 12.1.0.2 to 19c the you can expect to see more PGA allocated.“

Reference:

• Limiting Process Size with Database Parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1)
• Swapping While Sum of SGA Usage And PGA_AGGREGATE_LIMIT Is Larger Than The Amount Of Physical Memory (Doc ID 2273931.1)
• Oracle 19C Database Reference

• Oracle