To View Sort Area Information

Using the following query we can get some information about the sorting happening on a particular database.

SELECT *
  FROM v$sysstat
 WHERE NAME LIKE '%sorts%'

STATISTIC# NAME         CLASS VALUE         STAT_ID
341         sorts (memory) 64 27568047 2091983730
342         sorts (disk) 64 158         2533123502
343         sorts (rows) 64 9867427817 3757672740

sorts (memory) - If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the initialization parameter SORT_AREA_SIZE.

sorts (disk) - If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application workload. You can't do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

The sorting algorithms and resources used have improved with every release of oracle and I see future releases improving further in this regard.