PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

“PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

“By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. 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.”

Default Value Change in Oracle Database 12.2.0.1

In Oracle Database 12.2.0.1 the default value gets adjusted a bit as it turned out that restricting PGA_AGGREGATE_LIMIT to a value too low will lead to a significant number of issues.

See the Oracle Database 12.2.0.1 documentation on PGA_AGGREGATE_LIMIT explaining:

“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 please remember, DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.

What happened in Oracle Database 12.1.0.2 when you set PGA_AGGREGATE_LIMIT too low?

How much is too low? Too low means it has been set lower that 2x the value of PGA_AGGREGATE_TARGET. And then it got adjusted “silently”, i.e. the value of PGA_AGGREGATE_LIMIT got adjusted internally after startup to reflect the minimum. Unfortunately this adjusted value does not get displayed by “show parameter”.

Short example in Oracle Database 12.1.0.2:

SQL> show parameter pga 

NAME			TYPE                      VALUE
----------------------- ------------------------- -----------------------------
pga_aggregate_limit     big integer               2G
pga_aggregate_target    big integer               120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

After restarting the database:

SQL> show parameter pga

NAME				     TYPE	  VALUE
------------------------------------ ------------ ------------------------------
pga_aggregate_limit		     big integer  1G
pga_aggregate_target		     big integer  1G

Different behavior in Oracle Database 12.2.0.1

Repeating the same test in Oracle Database 12.2.0.1 leads to ORA-93:

SQL> show parameter pga 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 2G
pga_aggregate_target		     big integer 120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

SQL> create pfile from spfile;
File created.

SQL> startup force
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
ORA-01078: failure in processing system parameters

After increasing PGA_AGGREGATE_LIMIT=2G I can startup my database again. PGA_AGGREGATE_LIMIT gets enforced as at least 2x the value of PGA_AGGREGATE_TARGET since Oracle Database 12.2.0.1.

–Mike