Blog

pga_aggregate_limit when changing pga_aggregate_target

Share this article

A quick note on some peculiar behavior of the pga_aggregate_limit parameter that I noticed today.

The pga limit parameter parameter was introduced with Oracle 12.1.0.1 and aims to form a hard cap (although not a hard hard cap, see Frits’ blog about this) on the amount of pga memory that can be used by the processes.
According to the documentation, it’s default value is calculated as:

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.

On one of our databases I noticed regular ORA-4036 (PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT) errors. Checking the parameter values I saw that the pga_aggregate_target was set to 2 GB and the limit to 4,5 GB.
The value of the latter was not explicitly set and with a processes parameter set to 1500, matches the calculation from the documentation (max between, 2 GB, 2 * pga_aggregate_target or 3 MB * processes).

The pga_aggregate_limit parameter

So far, everything looked normal.
But when I increased the pga_aggregate_target from 2 to 4 GB (after analysis on what was using the pga and based upon the values in v$pgastat) I noticed that the limit parameter was still 4,5 GB and not the 8 GB I expected.

SQL> show spparameter pga
 
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        pga_aggregate_limit           big integer
*        pga_aggregate_target          big integer 2G
 
SQL> show parameter pga                                      
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 4500M
pga_aggregate_target                 big integer 2G

SQL> show spparameter pga
 
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        pga_aggregate_limit           big integer
*        pga_aggregate_target          big integer 2G
 
SQL> show parameter processes                                          
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     3
gcs_server_processes                 integer     2
global_txn_processes                 integer     1
job_queue_processes                  integer     35
log_archive_max_processes            integer     4
processes                            integer     1500
 
SQL> alter system set pga_aggregate_target=4G scope=both sid='*';
 
System altered.
 
SQL> show parameter pga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 4500M
pga_aggregate_target                 big integer 4G
 
SQL> show spparameter pga
 
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        pga_aggregate_limit           big integer
*        pga_aggregate_target          big integer 4G

It looks like the default value of the limit parameter is not updated when changing the pga_aggregate_target parameter.
So, I just updated the limit parameter in memory to avoid further issues

SQL> alter system set pga_aggregate_limit=8G scope=memory sid='XXXX1';

System altered.

SQL> alter system set pga_aggregate_limit=8G scope=memory sid='XXXX2';

System altered.

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 8G
pga_aggregate_target                 big integer 4G

SQL> show spparameter pga

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        pga_aggregate_limit           big integer
*        pga_aggregate_target          big integer 4G

Further testing

Problem solved and everything ok, but I was curious what would happen if I increased the pga_aggregate_target to a value higher than the calculated limit.
So, on a test db on my laptop I increased the pga_aggregate target from 360 MB to 3 GB, pushing it over the original calculated value of 2 GB for the limit parameter.

sys@CDBA> show parameter pga

name                                 TYPE        value
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 360M

sys@CDBA> show spparameter pga

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        pga_aggregate_limit           big integer
*        pga_aggregate_target          big integer 360M

sys@CDBA> alter system set pga_aggregate_target=3G scope=both sid='*';

System altered.

sys@CDBA> show parameter pga_aggregate_limit

name                                 TYPE        value
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G

According to the documentation the limit parameter cannot be lower than the pga_aggregate_target (which seems logical), but here Oracle did not even blink.
Which left me wondering (again), if Oracle was telling me the truth about the limit.

Using the pl/sql procedure from Frits’ blog post I referenced earlier (just adding a char filler column to the table to make it allocate pga faster), I tried to allocate 4 GB of pga.
As expected the pl/sql procedure threw an ORA-04036 exception:

ERROR at line 1:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at line 22

But it did so at the 3 GB limit:

Wed Jun 14 23:10:25 2017
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 3072 MB

This was also confirmed by monitoring v$sesstat from a different session: the pl/sql procedure was consuming around 3 GB of pga before getting killed.
And 3 GB matches the pga_aggregate_target setting.

Conclusion is that Oracle does change the calculated pga_aggregate_limit value when you change the pga_aggregate_target.
It just won’t tell you about it.

Tags: Blog
Why DB_BIG_TABLE_CACHE_PERCENT_TARGET was a good solution
We played paintball…

You May Also Like