The limitations of the
Pachot Franck has published an excellent
overview of the limitations of the pga_aggregate_target parameter:
Even if you set
PGA_AGGREGATE_TARGET to high values (gigabytes) the memory allowed
for one process is limited by undocumented parameter _pga_max_size
that is set to 200Mb for every instance.
cannot use more than 200Mb workarea for all of their sorts or hash.
sort cannot use more than _smm_max_size that is calculated from the
- _pga_max_size /
- or 5%
Then by default
and whatever pga_aggregate_target is, one sort cannot use more than
100Mb memory for a serial process.
operations, limit is _smm_px_max_size which defaults to 30%
pga_aggregate_target divided by degree of parallelism.
Franck also notes that you can add more RAM for sorting and hash
joins by adjusting the hidden parameter _pga_max_size up to 1Gb ...
with oracle support agreement!