18.6 Disk Sort Ratio

---

Increase the size of the SORT_AREA_SIZE parameter to prevent sorting to disk. When using shared servers a smaller sort buffer may ultimately preserve memory resources for other essential processing. Retain a chunk of sort space memory for idle connections by setting the SORT_AREA_RETAINED_SIZE parameter to a small percentage of the SORT_AREA_SIZE parameter, usually 10%. Do not always set SORT_AREA_RETAINED_SIZE equal to SORT_AREA_SIZE.

SELECT 'Sorts in Memory ' "Ratio" , ROUND(   (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts      (memory)') / (SELECT SUM(value) FROM V$SYSSTAT     WHERE name IN ('sorts (memory)', 'sorts (disk)')) *       100, 2) ||'%' "Percentage" FROM DUAL;

A ratio of memory to disk sorts of less than 100% is not necessarily a problem.

Ratio                Percentage ------------------   ---------- Sorts in Memory      98.89%

Note 

 Oracle Database 10 Grid   Using the two parameters WORKAREA_SIZE_POLICY = AUTO and PGA_AGGREGATE_TARGET for automated session connection memory management has been recommended by Oracle Corporation since Oracle9i Database as better performing. Dedicated server connection configurations can perform well even at 1,000 concurrent connections. Automated session connection memory management only functions for a dedicated server configuration.

---