Oracle Database 12c: PGA_AGGREGATE_LIMIT

There is a new default in town. Mark my words, you will encounter the following during your 12c life-cycle… 😉

From the Oracle 12c Reference manual PGA_AGGREGATE_LIMIT:

PGA_AGGREGATE_LIMIT

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

There is no difference in behavior between PGA_AGGREGATE_LIMIT being explicitly set or being set to the default.

Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated. SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

Parameter type	Big integer
Syntax	PGA_AGGREGATE_LIMIT = integer [K | M | G]
Default value	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 not exceed 120% of the physical memory size minus the total SGA size.
Modifiable	ALTER SYSTEM
Range of values	PGA_AGGREGATE_LIMIT cannot be set below its default value. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
Basic	No

So when you have a very heavy consumption load on your PGA_AGGREGATE_TARGET, for example, like I once had during in parallel loading of XMLType content, the following may happen…

Errors in file /u01/app/oracle/diag/rdbms/oow/oow/incident/incdir_93949/oow_ora_4584_i93949.trc:
ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 4000 MB

This can result in unwanted situations. In a multi-tenant environment, multiple Pluggable Databases, this might be something what you were hoping for so no one endures the performance decrease on the server due to unforeseen memory consumption, but on a single instance environment one might want a temporary prefer performance decrease as long as the heavy duty process at least keeps continuing doing it’s work.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Cause: Private memory across the instance exceeded the limit specified in the PGA_AGGREGATE_LIMIT initialization parameter. The largest sessions using Program Global Area (PGA) memory were interrupted to get under the limit.

Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce memory usage.

A bit of a chicken and egg dilemma, but at least now you are aware. If you liked the old behavior then set your PGA_AGGREGATE_LIMIT value back to 0 (pga_aggregate_limit=0) via a ALTER SYSTEM or an adjustment of the database parameter.

Have a closer look at the reasons and consequences behind PGA_AGGREGATE_LIMITED in the Oracle® Database Performance Tuning Guide 12c Release 1 (12.1).
.