Oracle - Optimizing sorting operations

December 2016


You may certainly have noticed that when performing a sorting operation, there is an increase of the number of input/output to disk.


This will somehow slow down the performance of our requests and to counter this we must increase the value of the SORT_AREA_SIZE parameter. This parameter specifies the maximum size in bytes of memory that Oracle can use to perform the sorting. Once the sorting operation performed, Oracle frees the memory allocated for sorting except the memory specified by the parameter SORT_AREA_RETAINED_SIZE (of course before returning the results). After the return of the last row of the result of the query, Oracle frees the rest from memory.

Note that: the sorting operations are included in the SELECT DISTINCT, MINUS, INTERSECT, UNION, min (), max () and count ().

Related :

This document entitled « Oracle - Optimizing sorting operations » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.