Oracle - Optimizing sorting operations

Ask a question



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 ().

Oracle - Display parameters
Oracle - The lifecycle of a cursor