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.
Isn't this tip version specific and dependent on the setting of WORK_AREA_SIZE_POLICY=MANUAL?
ReplyDeleteRegards,
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
ReplyDeletePGA_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.
ReplyDelete