Difference between PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT

PGA_AGGREGATE_TARGET

Property	Description
Parameter type	Big integer
Syntax	PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value	10 MB or 20% of the size of the SGA, whichever is greater
Modifiable	ALTER SYSTEM
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

- Only controls “tunable” memory allocations

- “Tunable” means the oprations can opt to use PGA or temp space, like hash join, sort, etc

- Actual PGA usage is often much higher (3x) since operations for “untunable” memory do not heed this parameter

- Particularly problematic with parallel queries with high DOPs, badly behaved PL/SQL

PGA_AGGREGATE_LIMIT ^{(New in 12c)}

Property	Description
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 thePROCESSES parameter. It will not exceed 120% of the physical memory size minus the total SGA size.
Modifiable	ALTER SYSTEM
Range of values	Oracle recommends that you do not set PGA_AGGREGATE_LIMIT below its default value. PGA_AGGREGATE_LIMITcannot be set below its default value except in a text initialization parameter file (pfile) or binary server parameter file (spfile). If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
Basic	No

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.

What is the value for PGA_AGGREGATE_LIMIT if value is 0? Either not set or under AMM control.

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 0

SQL> select name,value,unit from v$pgastat
  2  where name in ('aggregate PGA target parameter','aggregate PGA auto target');

NAME                                     VALUE UNIT
----------------------------------- ---------- ------------
aggregate PGA target parameter       587202560 bytes
aggregate PGA auto target            417484800 bytes