Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







The best of IOUG. . .
If Your Memory Serves You Right

Don Burleson


If Your Memory Serves You Right

Richmond Shee

In one of the most comprehensive papers I've ever seen on Oracle sorting, Mr. Shee performs exhaustive benchmarks on Oracle sorting and comes to some surprising conclusions:

According to my tests results, when the same load is sorted and observed with various SORT_AREA_SIZE values, the runtime improves dramatically when a small initial SORT_AREA_SIZE is increased.

After a certain amount of memory, the runtime begins to plateau even though SORT_AREA_SIZE continues to increase. As more memory is given, the runtime may even rise, causing a steeper negative return. This continues until a certain amount of memory where the sort process completes quickly and will not go any faster.

Mr. Shee compares the optimal (in-RAM) sorting with, one-pass and multi-pass sorts which involve disk I/O in the TEMP tablespace (sorts - disk):

The disk sort performance is bad when the number of sort runs is larger than the merge width. In this case, multiple passes over the input data are necessary, creating additional I/Os to the TEMPORARY tablespace.

Shee goes-on to demonstrate this behavior by setting several important Oracle hidden sort parameters:

_sort_multiblock_read_count = 2
_sort_space_for_write_buffers = 1
_smm_auto_min_io_size = 56
_smm_auto_max_io_size = 248

The real surprise in Shee's landmark research is that response time for one-pass disk sorts may slow-down as the amount of sort_area_size is increased:


In the example above, Shee notes that an optimal sort (in-RAM) begins at about 75k, but that a sort_area_size of 40k is nearly as fast as a double-size sort_area_size of 80k.

Shee concludes by noting that the holy-grail of sort tuning is finding the right one-size-fits-all size for sort_area_size (or pga_aggregate_target) for the application as a whole:

"there are only two “valid” (or useful) work area sizes for any given sort. You either provide the best one-pass work area size or the cache size. Other values will either waste memory and/or degrade performance.. . . 

The right amount of sort memory reduces disk sorts and demand for storage, as well as improves performance. DBAs must find the right SORT_AREA_SIZE or PGA_AGGREGATE_TARGET for the application. Multi-pass sorts should be eliminated and one-pass sorts with high input phase comparisons must be kept at a minimum"

Where from here?

Shee confirms that the fastest way to sort a large result set is to reserve enough RAM for an optimal sort.  However, Shee notes that there is a point where a one-pass sort (some disk I/O) is almost as fast as an optimal sort (in-RAM).

The core question of-interest here is whether using RAM-SAN (Solid state disk) for the TEMP tablespace will change this dynamic, improving overall throughput for database that require large sort activities.

It has been suggested that one limitation of Oracle Real Application Clusters (RAC) is the hardware limitations of smaller RAM, when compared to a monolithic server with equal computing resources.

Oracle experts Mike Ault and Robert Freeman hope to shed some light on this phenomenon when performing the benchmarks for Oracle performance on RAC systems using SSD in their upcoming book:

Oracle RAC & Grid Tuning with Solid-state Disk



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.