In Oracle 12c, a new interesting much needed feature, has been introduced which controls the maximum amount of the PGA memory consumed by the user processes. The PGA_AGGREGATE_LIMIT parameter limits the amount of memory used by processes in the PGA. Prior to this, the only control that was available was by setting the hidden parameter _pga_max_size, which put limits on the memory used by each individual process. Here the PGA_AGGREGATE_LIMIT sets the aggregate limit of total PGA.
When the PGA_AGGREGATE_LIMIT value is over its limit, the sessions or processes using the most PGA memory will be terminated. The error below will be written to the alert log.
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 3072 MB
PGA_AGGREGATE_LIMIT is a dynamic parameter and its value can be changed during run time. The Database restart is not required.
Immediate Kill Session#: 41, Serial#: 397
Immediate Kill Session: sess: 0xc13a5066 OS pid: 5233
SQL> alter system set pga_aggregate_limit=4096M scope=both;
Now we can check verify the change.
SQL> show parameter pga_agg
However you cannot decrease the value of pga_aggregate _limit too much. If you try to set it to very low, you will receive the following error.
NAME TYPE VALUE
-------------------- ----------- -------
pga_aggregate_limit big integer 4G
SQL> alter system set pga_aggregate_limit=100M scope=both;
alter system set pga_aggregate_limit=100M scope=both
ERROR at line 1:
ORA-02097: parameter cannot be modified specified value invalid
ORA-00093: pga_aggregate_limit must be between 1694M and 100000G
For DBA’s it is very important to be aware of the behavior when setting the this parameter. When working in 12c and sessions terminate, the DBA should look out for the effect of this parameter.
Oracle Document – PGA_AGGREGATE_LIMIT