A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is non-shared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process.
Some key parameters related to PGA we set as a DBA are:
PGA_AGGREGATE_TARGET: It specifies the target aggregate PGA memory available to all server processes attached to the instance. PGA_AGGREGATE_TARGET only controlled the allocation of PGA memory to each process via the oracle algorithm. It is not a hard limit.
If PGA_AGGREGATE_TARGET is insufficient, then sorts overflow to the temp tablespace.
PGA_AGGREGATE_LIMIT: It was a new parameter introduced in 12c. It put a hard limit instead of a soft limit on the PGA memory usage.
“_pga_max_size”: This hidden parameter regulates the size of the PGA memory allocated to a single process. Oracle sets this using calculations based on pga_aggregate_target and pga_max_size. This hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.
Please KEEP IN MIND, Any hidden parameter like “_pga_max_size” can NOT be set unless Oracle confirms. Hidden parameters are hidden because of that reason only.
In our case since we are using a large PGA_AGGREGATE_TARGET because our server has ample RAM we increased the _pga_max_size value to 2 GB after a discussion with Oracle.
- Oracle Database Service for Azure (ODSA) 3 – Database Services - May 1, 2023
- Oracle Database Service for Azure (ODSA) 2 – Interconnect Details - April 30, 2023
- Oracle Database Service for Azure (ODSA) 1 – Fundamentals - April 29, 2023