Oracle - Optimizing sorting operations

April 2017




Issue


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

Solution


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


Published by jak58. Latest update on February 10, 2010 at 08:28 AM by jak58.
This document, titled "Oracle - Optimizing sorting operations," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).