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!
This was first published in March 2004