By Franck Pachot

.
The latest DBA Essentials Workshop training I’ve given raised a question about PGA_AGGREGATE_LIMIT. The default depends on PGA_AGGREGATE_TARGET. So how is it calculated in AMM where PGA_AGGREGATE_TARGET is dynamic? Is it also dynamic or is it determined by the value at instance startup only?

The PGA_AGGREGATE_LIMIT default value is documented. I’ll use the following query to display the values of the concerned parameters:


select
dbms_stats_internal.get_parameter_val('pga_aggregate_limit')/1024/1024/1024 "pga_aggregate_limit",
 2 "2GB",
 3*dbms_stats_internal.get_parameter_val('processes')/1024 "3MB*processes",
 2*dbms_stats_internal.get_parameter_val('__pga_aggregate_target')/1024/1024/1024 "2*__pga_aggregate_target",
 dbms_stats_internal.get_parameter_val('__sga_target')/1024/1024/1024 "__sga_target",
 dbms_stats_internal.get_parameter_val('__pga_aggregate_target')/1024/1024/1024 "__pga_aggregate_target"
from dual
/

and I start with the following:


pga_aggregate_limit        2GB 3MB*processes 2*__pga_aggregate_target __sga_target __pga_aggregate_target
------------------- ---------- ------------- ------------------------ ------------ ----------------------
            2.40625          2     .87890625                  2.40625     1.796875               1.203125

I’m in AMM with only MEMORY_TARGET set to 3G. The dynamic SGA is at 1.8G and the PGA at 1.2G. The PGA_AGGREGATE_LIMIT is at 200% of the PGA which is 2.4G

I increase the SGA in order to see a resize of the PGA


SQL> alter system set sga_target=2500M;
System altered.

The PGA is now about 500M in order to release some space for SGA:

pga_aggregate_limit        2GB 3MB*processes 2*__pga_aggregate_target __sga_target __pga_aggregate_target
------------------- ---------- ------------- ------------------------ ------------ ----------------------
            2.40625          2     .87890625                  1.09375     1.796875                .546875

However, the PGA_AGGREGATE_LIMIT did no change. the formula is not dynamic. The value that has been calculated at startup remains.

spfile

When dynamic components are resized, the values are written into the spfile with double underscore parameters, so that a restart of the instance starts with same value:

SQL> host strings /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileCDB.ora     | grep target
CDB.__pga_aggregate_target=587202560
CDB.__sga_target=1929379840
*.memory_max_target=5G
*.memory_target=3G
.sga_target=2634022912

So let’s restart and see what happens to PGA_AGGREGATE_LIMIT (which has no double underscore entry in spfile)

SQL> startup force
ORACLE instance started.
 
Total System Global Area 5368709120 bytes
Fixed Size                  2935712 bytes
Variable Size            3976201312 bytes
Database Buffers          721420288 bytes
Redo Buffers               13840384 bytes
In-Memory Area            654311424 bytes
Database mounted.
Database opened.
 
SQL> select
  2   dbms_stats_internal.get_parameter_val('pga_aggregate_limit')/1024/1024/1024 "pga_aggregate_limit",
  3   2 "2GB",
  4   3*dbms_stats_internal.get_parameter_val('processes')/1024 "3MB*processes",
  5   2*dbms_stats_internal.get_parameter_val('__pga_aggregate_target')/1024/1024/1024 "2*__pga_aggregate_target",
  6   dbms_stats_internal.get_parameter_val('__sga_target')/1024/1024/1024 "__sga_target",
  7   dbms_stats_internal.get_parameter_val('__pga_aggregate_target')/1024/1024/1024 "__pga_aggregate_target"
  8   from dual
  9  /
 
pga_aggregate_limit        2GB 3MB*processes 2*__pga_aggregate_target __sga_target __pga_aggregate_target
------------------- ---------- ------------- ------------------------ ------------ ----------------------
                  2          2     .87890625                  1.09375     2.453125                .546875

The good thing is that the value is calculated from the actual values. Here 200% of PGA is smaller than 2G so 2G is used.

The bad thing is that a restart of the instance may bring a different behavior than before than restart.

So what?

This instability is easy to solve: don’t use AMM. SGA and PGA are different things and you should size them separately.
But the problem is wider. There are other parameters that can show same behavior. For example, the default db_file_multiblock_read_count can be limited by processes x __db_block_buffers.
You may have to change some values either manually or automatically at the start of a new application because you don’t know which is the best setting. But once the application is more stable, you should stabilize the dynamic sizing by setting minimum values.