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:
select PGA_AGGREGATE_LIMIT,
PROCESSES_x_3,
PGA_AGGREGATE_TARGET_x_2,
greatest(nvl(PGA_AGGREGATE_LIMIT,0), PROCESSES_x_3, PGA_AGGREGATE_TARGET_x_2) USED_VALUE
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');
PGA_AGGREGATE_LIMIT PROCESSES_X_3 PGA_AGGREGATE_TARGET_X_2 USED_VALUE
------------------- ------------- ------------------------ ----------
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.