 |
|
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;
Connected.
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.
|
