Thursday, October 30, 2008

Tuning Sort Area Size

We can check the amount of sorting in memory and on disk by V$SYSSTAT view:

Select name, value from v$sysstat where name in ('sorts (memory)', 'sorts (disk)');

And if a we find that there is a large number of sorts require I/O to disk, increase the initialisation parameter SORT_AREA_SIZE.


  1. Isn't this tip version specific and dependent on the setting of WORK_AREA_SIZE_POLICY=MANUAL?


  2. I do know that in at least oracle 9i, setting sort_area_size will negate the use of PGA_AGGREGATE_TARGET , which would be the preferable method of auto-tuning sort_area_size

  3. PGA_AGGREGATE_TARGET=AUTO is the default now, however it can be usefull to manually set the sort area size at a session level for batch processing (where a really big sort area may help), although you then have to make sure you have enough memory for your normal pga plus the additional sort areas.