Sorting Operations   «Prev  Next»
Lesson 3Sorting Parameters
ObjectiveIdentify the Parameters that govern Sorting

Sorting Parameters

Several init.ora parameters govern sorting operations. These include:
sort_area_size             655326    Size of in-memory sort work area
sort_area_retained_size      0       Size of in-memory sort work area
                                     retained between fetch calls
sort_direct_writes           AUTO    Use direct write
sort_write_buffers           2       Number of sort direct write buffers
sort_write_buffer_size       32768   Size of each sort direct write buffer
sort_spacemap_size           512     Size of sort disk area space map
sort_read_fac                20      Multiblock read factor for sort

Displaying Parameter Values

You can display the current values of these parameters in SQL Plus with the show parameters sort command. Roll your mouse over the output shown below to see information about the two most important parameters:
  1. sort_area_size
  2. sort_area_retained_size
  1. This defines the maximum amount of PGA memory that can be used for disk sorts. For very large sorts, Oracle will sort data in its temporary tablespace, and the sort_area_size memory will be used to manage the sorting process.
  2. This is a threshold that specifies the maximum amount of sort memory to retain for future sorts. When a sort is finished, any sort area memory in excess of this amount will be released.
Maximum amount of PGA memory that can be used for disk sorts

Important Sorting Parameters

Allocating Sort Memory

When a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting. Unfortunately, the amount of memory must be the same for all sessions and it is not possible to add additional sort areas for tasks that are sort intensive. Therefore, the designer must strike a balance between allocating enough sort area to sort the majority of requests while recognizing that some very large sorts will never be able to fit into the space allocated for sort_area_size.
The size of the private sort area is determined by the sort_area_size init.ora. The size for each individual sort is specified by the sort_area_retained_size init.ora parameter. Whenever a sort cannot be completed within the assigned space, a disk sort is invoked using the temporary tablespace for the Oracle instance.
As a rule, only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.

New sorting parameters

Legacy Oracle version 7.2 added several new parameters to the init.ora file for use in allocating a new in-memory sort area, including:

sort_write_buffer_sizeDefines the size of the in-memory sort area
sort_write_buffersDefines the number of buffer blocks
sort_direct_writesIf set to TRUE will bypass the need for the sort to contend for free blocks in the buffer cache

Oracle SQL Performance Tuning
Remember that the single most important factor in the performance of an Oracle database is the minimization of disk I/O. Since sort to disk is I/O intensive, reducing disk sorts is a primary tuning goal. In the next lesson, you will learn how to avoid SQL programming that causes unnecessary sorts.