ANBOB™

Prior to 12c,PGA_AGGREGATE_TARGET was the Parameter used to control amount of memory allocated to User Processes(mainly work areas). However PGA_AGGREGATE_TARGET is a soft target and not a hard limit. The actual PGA usage can be as high as three times of the value of PGA_AGGREGATE_TARGET.This can lead to memory swapping & performance degradation.

In automatic PGA memory management mode, Oracle Database attempts to adhere to the PGA_AGGREGATE_TARGET value by dynamically controlling the amount of PGA memory allotted to work areas. However, PGA memory usage may exceed the PGA_AGGREGATE_TARGET setting at times due to the following reasons:

1.  The PGA_AGGREGATE_TARGET setting acts as a target, and not a limit. 
2. PGA_AGGREGATE_TARGET only controls allocations of tunable memory.

12c introduced new parameter PGA_AGGREGATE_LIMIT to allow database administrators to limit the aggregate pga memory usage.

The PGA_AGGREGATE_LIMIT initialization parameter enables you to specify a hard limit on PGA memory usage.  The background process CKPT checks every three seconds to see whether the amount of memory exceeds the value of the PGA_AGGREGATE_LIMIT initialization parameter.  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:

1.  Calls for sessions that are consuming the most untunable PGA memory are aborted. 
2.  If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.

The sessions using the most untunable memory will have their calls aborted. Parallel queries will be treated as a unit. If the total PGA memory usage is still over the limit, then sessions using the most untunable memory will be terminated.

SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

Untunable PGA memory is like context information for each session, for each open/active cursor, PL/SQL or Java memory.
Tunable PGA memory is memory allocated for intensive memory SQL operators like sorts, hash-join, group-by, bitmap merge and bitmap index create.

The PGA_AGGREGATE_LIMIT initialization parameter dynamically sets an instance-wide hard limit for PGA memory. a database restart is not necessary. Because the parameter responds to changing memory conditions, you do not need to set the parameter value explicitly. By default, PGA_AGGREGATE_LIMIT is set to:

In Oracle release 12.1:  the greater of the following:

1. 2 GB 
2. 200% of PGA_AGGREGATE_TARGET 
3. (Value of PROCESSES initialization parameter setting) * 3 MB

It will not exceed 120% of the physical memory size minus the total SGA size.

In Oracle release 12.2:

* 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.

In Oracle release 18c and above, it should be at least 5MB times the PROCESSES parameter for an Oracle RAC instance.

The MGA (Managed Global Area) is accounted for out of the PGA.
What this means in practical terms is that the PGA size must be increased in 18c and above versions to accommodate the MGA.

A rule of thumb is:

PGA_AGGREGATE_LIMIT = (original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

Another effect of this issue is that customers can report increased PGA usage after upgrading to 18c and above.
This is expected as the MGA is now accounted for out of the PGA.

NOTE: If a value of 0 is specified for PGA_AGGREGATE_LIMIT, it means there is no limit to the aggregate PGA memory consumed by the instance.

On 19c(19.7+), The value of PGA_AGGREGATE_LIMIT has some internal restrictions. For example, its upper limit is calculated from physical memory and the SGA usage. from db alert log will show that.

Also, its lower limit requires to be at least 3MB * processes parameter (or 5MB * processes if RAC).

If error during  upgrade using DBUA cause by PGA_AGGREGATE_LIMIT，like this:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

The solution is to look at increasing the PGA_AGGREGATE_TARGET to a higher value & retry the Upgrade.

the wait event “Acknowledge Over PGA Limit” is a new wait event that was introduced with PGA_AGGREGATE_LIMIT in 12.1,  and it will force a process that wants more PGA to wait a bit if the instance is getting close to hitting the limit.

The solution you can set PGA_AGGREGATE_LIMIT = 0 for unlimited.

References Doc ID 1520324.1

打赏

微信扫一扫，打赏作者吧～