Oracle – Optimization of sorting
When we perform a sort operation, we note the increase in I / O to disk. To overcome this increase slows down somewhere the performance of our queries, increase the value of SORT_AREA_SIZE. This parameter specifies the maximum size in bytes of memory Oracle can use to do the sorting. After the sorting operation performed, but before the return of the result, Oracle frees the memory allocated for sorting except the memory specified by the parameter SORT_AREA_RETAINED_SIZE. After the return of the last row of the query result, Oracle releases the remainder of the memory.
Note that the sorting operations are included in the query SELECT DISTINCT, MINUS, INTERSECT and UNION and the level of min (), max () and count ()