Oracle 23c New Features for DBA'sUntil 12c we cannot restrict the PGA memory, even though you set pga_aggregate_target its just a soft limit, sessions can go beyond that.
However, we can restrict PGA using PGA_AGGREGATE_TARGET parameter and limiting the memory access, This is extremely useful in consolidated databases running on a host, but again the baseline should be done before setting this parameter. Even there is a default value for this parameter
PGA_AGGREGATE_LIMIT initialization parameter can be set dynamically; a database restart is not necessary. You can set the value of PGA_AGGREGATE_LIMIT regardless of whether automatic memory management is being used.
SQL> Alter system set pga_aggregate_limit = 5G;
If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:
In determining the sessions and processes to abort or terminate, Oracle Database treats parallel queries as a single unit.
Default Values
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. The default value is printed into the alert log. A warning message is printed in the alert log if the amount of physical memory on the system cannot be determined
-Thanks
Geek DBA
Follow Me!!!