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 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 typeBig integer
SyntaxPGA_AGGREGATE_LIMIT = integer [K | M | G]
Default valueBy 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.
Range of valuesPGA_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.

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).

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

1 Comment

  1. Probably also good to know…from 12c and onwards there will be NO 32 bit version of the database any more…