Intention to write Blog is recently we faced scenario where PGA_AGGREGATE_LIMIT value was automatically increasing when it been set lower then desired value .
The initialization parameter PGA_AGGREGATE_LIMIT has been introduced since Oracle Database 12.1.0.1. It is used to put a hard limit on PGA memory usage. If PGA usage exceeds the PGA_AGGREGATE_LIMIT value defined, Oracle Database aborts or terminates the sessions or processes that are consuming the most PGA memory.
From Oracle 12.1.0.2, By default, PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the PROCESSES parameter. However, it will not exceed 120% of the physical memory size minus the total SGA size.“
Since Oracle Database 12.2.0.1, the default value calculation has been adjusted again as below:
> If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
> If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
> If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
> In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).
Reference:
Limiting Process Size with Database Parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1)
Swapping While Sum of SGA Usage And PGA_AGGREGATE_LIMIT Is Larger Than The Amount Of Physical Memory (Doc ID 2273931.1)
No comments:
Post a Comment