Oracle 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, using 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).
.