PGA_AGGREGATE_TARGET , PGA_AGGREGATE_TARGET and _PGA_MAX_SIZE

Use of PGA
=============

• Sort-based operators like order by, Group by
• Hash-join
• Bitmap merge
• Bitmap create
• Write buffers used by bulk load operations

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 (not a limit)

Excessive PGA usage can lead to high rates of swapping. When this occurs, the system may become unresponsive and unstable. In this case, consider using the PGA_AGGREGATE_LIMIT (hard limit) initialization parameter to limit overall PGA usage.


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

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 


PGA_AGGREGATE_LIMIT = 200% of PGA_AGGREGATE_TARGET

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

Increase pga for session or process 
=============================================

_PGA_MAX_SIZE -> maximum size which can be used for per-process PGA memory

The default value is 200MB and the range of valid values is from 10MB up to 4TB-1.