12C以后PGA_AGGREGATE_LIMIT

oracle自12C以后提供PGA_AGGREGATE_LIMIT参数来硬件限制pga

Property	Description
Parameter type	Big integer
Syntax	PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value	10 MB or 20% of the size of the SGA, whichever is greater For a PDB, the default value is the same as the CDB's default value.
Modifiable	ALTER SYSTEM
Modifiable in a PDB	Yes
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

To set a hard limit for aggregate PGA memory, use the PGA_AGGREGATE_LIMIT parameter.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. With this setting, SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. With this setting, SQL working areas are sized using the *_AREA_SIZE parameters.

PGA_AGGREGATE_LIMIT 参数有20% of the SGA  、pga的2倍、process进程数×3M（单机3M，rac 5M）的最大值来决定。