Oracle will do sorting when executing queries that use ORDER BY, GROUP BY, when a 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.
Tags: Performance Tips