Sorts Tuning in Oracle

Oracle will do sorting when executing queries that use ORDER BY, GROUP BY, when an Index is created, and when MERGE SORT is invoked by the SQL optimizer.

This is a query to see the amount of sorting we are using in disk, memory and the amount of rows:

SELECT name, value FROM v$sysstat
WHERE name LIKE 'sort%';
NAME           VALUE
-------------- ----------
sorts (memory) 20302
sorts (disk)   50
sorts (rows)   256085

Sorts problems can be identified if the disk sorts are close to the memory sorts in the results there is no problem because 50 is not even 1% of 20302.

Change some variables in your ini.ora to increase or reduce your memory including:

sort_direct_writes, sort_write_buffer_size, sort_write_buffers

You might set the sort_direct_writes=true this will allow disk sorts to use a direct write area, this setting can improve sort performance by 50%.

Recommendation: Verify that sort_direct_writes=true, check your performance and if the TEMP tablespace is setup, then increase the buffers if you think is needed.