A small but very important component of Oracle tuning, sorting is often overlooked. An Oracle database will automatically perform sorting operations on row data under the following circumstances:
- When an index is created
- When using the ORDER BY clause in SQL
- When using the GROUP BY clause in SQL
At the time a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting, based on the value of the sort_area_size initialization parameter. The amount of memory must be the same for all sessions, and it is not possible to add additional sort areas for tasks that are sort intensive. Therefore, the DBA must strike a balance between allocating enough sort area to avoid disk sorts for the large sorting tasks, keeping in mind that the extra sort area will be allocated and not used by tasks that do not require intensive sorting.
Whenever a sort cannot be completed within the assigned space, a disk sort is invoked using the temporary tablespace for the Oracle instance. A sort in the temporary tablespace is very I/O intensive and can slow down the entire database. As a general rule, the sort_area_size should be large enough that only index creation and ORDER BY clauses using functions should be allowed to use a disk sort. However, operations on large tables will always perform disk sorts. For example, the following query will sort the salaries for all 101,000 employees at Oracle Corporation:
Oracle always tries to sort in the RAM space within
sort_area_size
and only goes to a disk sort when the RAM memory is exhausted. 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.
- In-memory sorts are always preferable to disk sorts, and disk sorts will surely slow down an individual task, as well as affect concurrent tasks on the Oracle instance.