The best of
IOUG. . .
If Your Memory Serves You Right
If Your Memory Serves You Right
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
Shee goes-on to demonstrate this behavior by
setting several important Oracle hidden sort parameters:
_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:
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
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
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
Oracle RAC & Grid Tuning with Solid-state Disk