Tuning sorts

Issue the following SQL statement to determine the amount of sorting in memory and on disk:

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

If a large number of sorts require I/O to disk, increase the initialisation parameter SORT_AREA_SIZE. As a guide less than 1% of the sorts being to disk is optimum.

If more than 1% of sorts are to disk then increase SORT_AREA_SIZE and then restart Oracle. I would suggest laving Oracle to run for a day or two before checking to see what difference the change has made.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.

About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.