From:Steve Adams
Date:08-Nov-2000 11:46
Subject:   Tuning sort_area_size

The suggestion that some sort memory is persistent is not correct. Sort areas are dynamically allocated in the UGA or CGA. They grow in small increments up to the limit set by sort_area_size. The sort_area_retained_size parameter sets amount of memory that will be retained for the final phase of a sort while rows are being fetched. Once the last row has been fetched, all sort memory is released to its parent heap. Free/unused PGA memory will be readily paged out by the operating system under memory pressure. So the amount of physical memory required for sorting is approximately sort_area_size times the number of concurrent sorts only.

When considering an increase to sort_area_size the growth in physical memory requirements will be no more than the proposed increase multiplied by the peak number of concurrent disk sorts. You can get the peak number of concurrent disk sorts ever performed by an instance by looking at MAX_UTILIZATION for sort_segment_locks (or temporary_table_locks if you have a permanent temporary tablespace) in V$RESOURCE_LIMIT. The APT script disk_sorts.sql on the Ixora web site reports the number of disk sorts, their average size, and the peak number of concurrent disk sorts, which is much of what is needed to choose a new value for the sort_area_size parameter. In general, there is room for much more generous sort_area_size settings than are commonly used.

In my opinion (as well as Oracle's) you should strive for less than 1% sorts on disk. But then again, this may not be obtainable at all cases, but it should be your ultimate goal. Sometimes to go from 3% - 0.8% might mean 5Mb sort_area_size, which is not practical as this is PER SESSION and can quickly steal memory and cause a swap out of your SGA, specially if you don't make the RETAINED_AREA_SIZE relatively small to shrink it when done.