Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Initialization Parameters Relating to Oracle Sorting
Oracle Tips by Burleson

As I noted, the size of the private sort area is determined by the sort_area_size initialization parameter. In addition, there are several other parameters that affect the performance of disk sorts. To see your initialization parameters, just enter the following commands in SQL*Plus:

SQL> connect / as sysdba;
SQL> show parameters sort;

NAME                                 TYPE    VALUE
--------------------------------- ------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer 0
sort_area_size                       integer 10000
sort_multiblock_read_count           integer 2

We will be looking at most of these parameters in this chapter. Let’s begin with the most important, the sort_area_size parameter.

The sort_area_size Parameter

The proper value for the sort_area_size parameter is a delicate trade-off for the Oracle DBA. Unlike other initialization parameters, sort_area_size is allocated for each and every connected Oracle session (unless you are using the multithreaded server). Hence, for databases with a large number of users, an increase in sort_area_size can cause a RAM overload. If sort_area_size is set too low, then there will be excessive disk sorts. As sort_area_size increases, more and more sorts will be able to complete without going to disk.

However, there is a decreasing benefit to making sort_area_size too large. Because every connected session will allocate a memory region of sort_area_size, a large amount of RAM may be wasted just to improve the speed of a few queries.

In practice, the DBA will increase the value of sort_area_size over time, using STATSPACK to monitor the number of memory sorts and disk sorts each hour. When increasing sort_area_size results in only marginal reductions on disk sorts, then the proper size has been reached (Figure 11-1). Hence, setting the optimal sort_area_size is an ongoing activity, and the DBA will recheck the value every few months in case the nature of the queries has changed.

Figure 1: Diminishing marginal reductions in disk sorts by increasing sort_area_size

The sort_area_retained_size Parameter

The sort_area_retained_size initialization parameter governs the amount of memory to retain after a disk sort has completed. The purpose of this parameter is to remove the overhead of reissuing OS memory allocation command (the malloc command in UNIX) whenever the Oracle session requires memory for another sort operation. The sort_area_retained_size defaults to zero.

On non-UNIX systems, when the process completes, the memory region is returned to the operating system (in case of a dedicated connection) or to the shared pool in cases of shared connections using the multithreaded server. On UNIX systems with  dedicated listener connections, setting sort_area_retained_size will have little effect. This is because Oracle releases malloc memory with the free command, and the free command releases UNIX memory to the user global area, where it remains available for subsequent sorts by the session.

Hence, the sort_area_retained_size parameter is seldom useful for improving the performance of sorting.

The sort_multiblock_read_count Parameter

Under Oracle 8i, another way to improve sort performance using temporary tablespaces is to tune the parameter sort_multiblock_read_count for large disk sorts that require segments in the TEMP tablespace. The sort_multiblock_read_count parameter has the same effect as the parameter db_file_multiblock_read_count on full-table scans, except it applies to sort operations on blocks within the TEMP tablespace. The default value for sort_multiblock_read_count is 2.

If the sort space requirement is greater than sort_area_size, increasing the value of sort_multiblock_read_count forces the sort to read a larger section of each run into memory during the merge pass phase of the sort. Setting sort_multiblock_read_count also forces the sort process to reduce the merge width, or number of runs, that can be merged in one merge pass, thereby improving performance.

Obsolete Oracle8 Sort Parameters

There are several obsolete parameters in Oracle8i that may be of interest to SQL tuners who are still using Oracle8.

  • sort_direct_writes   This parameter was used to speed up disk sorts. In Oracle8, sort_direct_writes was found to be always useful, but starting with Oracle8i the database always uses direct writes and the parameter has disappeared. In prior releases of Oracle7 and Oracle8, sort_direct_writes could be set to TRUE.

  • sort_write_buffers   This parameter specified the number of buffers per sort process. For example, if sort_write_buffers is set to 2 and you have five slaves sorting, each slave process gets two buffers.

Optimizer Mode and Sort Activity

As you will recall from an earlier chapter, the all_rows optimizer mode favors disk sorts, while the first_rows mode prefers indexes. This is because of the differences between the goals of reducing overall machine resources (all_rows) and delivering the rows as quickly as possible (first_rows).

However, there are cases when an order by clause on a large result set causes a disk sort. In these cases, none of the rows will be available to the query even though we may be getting excellent response time from the I/O subsystem.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter