Limiting PGA with PGA_AGGREGATE_LIMIT in Oracle 12c

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
Immediate Kill Session#: 41, Serial#: 397
Immediate Kill Session: sess: 0xc13a5066  OS pid: 5233
PGA_AGGREGATE_LIMIT is a dynamic parameter and its value can be changed during run time. The Database restart is not required.
SQL> alter system set pga_aggregate_limit=4096M scope=both;
System Altered.
Now we can check verify the change.
SQL> show parameter pga_agg
NAME                 TYPE VALUE
-------------------- ----------- -------
pga_aggregate_limit  big integer 4G
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.
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.

Related Links
Oracle Document – PGA_AGGREGATE_LIMIT

Leave a Reply

Your email address will not be published.