Monitoring Sorting (Oracle)

Sorting is done in Oracle for SQL statements that use ORDER BY , GROUP BY and SORT MERGE JOIN operations, and for index creation.

Memory Sorts

Memory gives the number of sorts performed in memory. Sorts performed in memory are generally much faster than those done on disk. The init<SID>.ora parameter sort_area_size ( SORT_AREA_SIZE (Oracle)) determines the amount of process memory which can be used for sort operations. The memory space is allocated for each process, so you must ensure that there is sufficient operating system memory to accommodate an increase in this value. Otherwise, unnecessary paging or swapping may occur.

Disk Sorts

Disk gives the number of sorts that had to be written to temporary segments on disk in order to be sorted. SAP uses the tablespace PSAPTEMP to hold these segments.

The Disk to Memory ratio should be no more than 5%. If it is higher, investigate increasing the sort_area_size parameter.