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