The limitations of the
pga_aggregate_target parameter
Pachot Franck has published an excellent
overview of the limitations of the pga_aggregate_target parameter:
http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=2844
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.
Thus, queries
cannot use more than 200Mb workarea for all of their sorts or hash.
Moreover, each
sort cannot use more than _smm_max_size that is calculated from the
lowest from:
- _pga_max_size /
2
- or 5%
pga_aggregate_target
Then by default
and whatever pga_aggregate_target is, one sort cannot use more than
100Mb memory for a serial process.
For parallel
operations, limit is _smm_px_max_size which defaults to 30%
pga_aggregate_target divided by degree of parallelism.
Mr.
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!
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
