| |
 |
|
Oracle Tips by Burleson |
Tuning Oracle Sorts
Sorts are done when Oracle performs operations that retrieve
information and requires the information retrieved to be an ordered
set--in other words, sorted. Sorts are done when the following
operations are performed:
Index creation
Group by or Order by statements
Use of the distinct operator
Join operations
Union, Intersect and Minus set operators
Each of these operations requires a sort. Primarily, there is one
indicator that your sorts are going to disk and therefore your sort
area in memory is too small. This area is defined by the
initialization parameters SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE
in Oracle8, Oracle8i, and Oracle.
The primary indicator is the sorts (disk) statistic shown in Figure
13.19. If this parameter exceeds 10 percent of the sum of
sorts(memory) and sorts(disk), increase the SORT_AREA_SIZE
parameter. Large values for this parameter can induce paging and
swapping, so be careful not to overallocate. In Oracle8i and
Oracle, you can increase the SORT_AREA_SIZE for a specific session
by using the ALTER SESSION SET SORT_AREA_SIZE = x, where x is the
size in bytes for the new sort area allocation. This dynamic sort
area allocation allows the developer or DBA to tune the sort needs
on a per-session or per-transaction basis.
For standard sorts, you should set the SORT_AREA_SIZE to the average
sort size for your database. The temporary tablespaces initial and
next default storage parameters should be set to the value of
SORT_AREA_SIZE. For use with parallel query sorts, a temporary
tablespace should be spread (striped) across as many disks as the
degree of parallelism.
The initialization parameter SORT_MULTIBLOCK_READ_COUNT does for
sorts what DB_MULTIBLOCK_READ_COUNT does for full table scans: it
forces Oracle to read at least that amount of data specified per
merge read pass.
This is an excerpt by
Mike Ault’s book “Oracle
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
