Why PGA_AGGREGATE_LIMIT appears to be set, when it is not?

Today I had to explain why the pga_aggregate_target was showing a value, but one does not have to care about it most of the cases. Here the same explanation.

When doing:

SQL> show parameter pga

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ----------------------
pga_aggregate_limit                  big integer                                  3000M
pga_aggregate_target                 big integer                                  1G

One would think that pga_aggregate_target is set to 3000M. However when one checks on the spfile, there is nothing defined:

SQL> show spparameter pga_aggregate_limit

SID      NAME                          TYPE                                         VALUE
-------- ----------------------------- -------------------------------------------- --------------------
*        pga_aggregate_limit           big integer

The answer comes from the documentation. When PGA_AGGREGATE_LIMIT is not defined on spfile, or if this value is not big enough, then it calculated based on PGA_AGGREGATE_TARGET or PROCESSES parameter, as it is clearly described on the documentation.

The value used will be the greatest of:

  • value of PGA_AGGREGATE_LIMIT parameter
  • 2 times the value of PGA_AGGREGATE_TARGET parameter
  • 3 times the number of PROCESSES parameter (= 3MB/process)

One can use the following query to show what value will be used by Oracle as PGA_AGGREGATE_LIMIT:

from (select value/(1024*1024) PGA_AGGREGATE_LIMIT from v$spparameter where name='pga_aggregate_limit'),
     (select value*3 PROCESSES_x_3 from v$spparameter where name='processes'),
     (select value/(1024*1024)*2 PGA_AGGREGATE_TARGET_x_2 from v$spparameter where name='pga_aggregate_target');

------------------- ------------- ------------------------ ----------
                             3000                     1024       3000

If you still use Automatic Memory Management (MEMORY_TARGET parameter), then the PGA_AGGREGATE_TARGET will be the value of MEMORY_MAX_SIZE, which I don’t mention on the SQL.

Leave a comment

Your email address will not be published. Required fields are marked *