Oracle Sorting Basics

Oracle has two techniques for sorting. If the sort is smaller than sort_area_size, the sort will be performed very quickly in the memory allocated to sort_area_size. These are known as in-memory sorts. For large sorts that exceed sort_area_size, Oracle will sort the result set in the TEMP tablespace. These are known as disk sorts, and they require longer execution time than in-memory sorts.
The Slide Show below shows you this process.

Disk sorts are expensive for several reasons. First, they consume resources in the temporary tablespaces. Oracle must also allocate buffer pool blocks to hold the blocks in the temporary tablespace. Disk sorts will surely slow down an individual task, as well as impact concurrent tasks on the Oracle instance. Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer. For these reasons, in-memory sorts are always preferable to disk sorts.
In the next lesson, you will look at the specific Oracle parameters that govern sorting operations.