Brother-Eagle™ Oracle Performance
Advice: % Memory Sorts

This parameter indicates the percentage of disk sorts that are done in memory. The goal is 100% although that is not always realistic especially when dealing with large tables and query results. Sorts performed outside of memory require disk and utilize the Oracle user's TEMP tablespace. The maximum size of a sort that will occur in memory is determined by SORT_AREA_RETAINED_SIZE. The parameter SORT_AREA_SIZE is used to specify the maximum amount of memory to be used for an external disk sort, utilizing temporary segments. 

Your mission is to find out which SQL statements are requiring sorting on disk and optimize those statements.

Some simple fixes include using a UNION ALL instead of a UNION.  If you know that the result set does not contain duplicates, a UNION ALL will work and therefore not require an expensive sort operation. 

The following are additional things that can cause a sort:

• CREATE INDEX
• ORDER BY
• GROUP BY
• DISTINCT
• IN, NOT IN

View full list of Oracle advice topics