How do I determine the best size for SORT_AREA_SIZE?
Prior to Oracle9i, one would issue queries similar to the following:
SELECT value AS memory_sorts FROM v$sysstat WHERE name='sorts (memory)'; SELECT value AS disk_sorts FROM v$sysstat WHERE name='sorts (disk)';
The idea is to keep the ratio of disk sorts to total sorts (disk + memory) very low, or even at zero. If this ratio was high (i.e. over 10%) then increase SORT_AREA_SIZE.
In Oracle9i, one would now use the PGA_AGGREGATE_TARGET instead of individually tuning the four *_AREA_SIZE parameters. I have more information on my Web site (http://www.peasland.net) in a white paper titled "Tuning PGA in Oracle 9i." Feel free to download this paper to see how manually tuning the SORT_AREA_SIZE is a thing of the past!