At the beginning of this chapter, we saw that sorts may occur in memory or on disk, and that in-memory sorts are faster than on-disk ones because fewer I/O operations are involved.
However, system memory is finite and cannot expand the sort area above the limits of the physical memory available. If sorts exceed the sort area, it's better to use on-disk sort than over allocating memory—ending in very slow pagination (swap to disk managed by the host Operating System).
On-disk sort operations require space to save sort runs, which cannot be stored in memory. Oracle uses sort segments to store this type of information on disk.
In this recipe, we will see how to configure temporary tablespaces to speed up on-disk sort operations and some diagnostic queries to be used when we want to retrieve information about them.