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


    • Natik Ameen says

      Hi Ghassen,

      Thanks for checking. The MEMORY_MAX_TARGET is inclusive of the PGA_AGGREGATE_TARGET and the SGA MAX SIZE parameters. If you want to increase the SGA_MAX_SIZE parameter, I would suggest that the MEMORY_MAX_TARGET size be increased appropriate as well.

      You can use the following query to obtain the PGA and SGA memory values.

      SELECT name, value
      FROM v$parameter
      WHERE name IN (‘pga_aggregate_target’, ‘sga_target’);

      Let me know if you have further questions.

      – Natik Ameen

Leave a Reply

Your email address will not be published.