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.

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’);

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database GoldenGate

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud. Stay connected with Natik at LinkedIn or FaceBook.