PGA_AGGREGATE_TARGET

Useful info about PGA sizing from http://www.dba-oracle.com/oracle_tips_sorts.htm

Oracle recommends that the value of pga_aggregate_target be set to the amount of remaining memory (less a 10 percent overhead for other UNIX tasks) on the UNIX server after the instance has been started.


The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size.
No RAM sort may use more than 5% of pga_aggegate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 10 megabytes for sorting or hash joins.

The following settings would increase the default sizes for large sorts and hash joins.
pga_aggregate_target = 1000m
_pga_max_size = 1000m
_smm_px_max_size = 333m
With these hidden parameters set we see a 5x large size increase for parallel queries and sorts:
A RAM sort or hash join may now have up to 50 megabytes (5% of pga_aggegate_target) a 5x increase.
Parallel queries may now have up to 330 megabytes of RAM (30% of pga_aggegate_target), such that a DEGREE=4 parallel query would have 83 megabytes (333 meg/4).