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

Sorting Parameters

Question: What are the parameters that govern sorting in Oracle?
Oracle Database provides several initialization parameters that govern the sorting process. These parameters control the behavior of sort operations, dictating how much memory to allocate for sorting, and when to resort to disk operations. Understanding these parameters is crucial for managing the performance of sort operations in your Oracle Database.
  1. SORT_AREA_SIZE: This is the primary parameter that controls the size of the sort area in the Program Global Area (PGA) for dedicated server processes. The sort area is the memory region where Oracle performs in-memory sorts. The size of this area is specified in bytes. If the data to be sorted is larger than the specified size, Oracle will resort to disk sorts, which are slower than in-memory sorts. However, this parameter is primarily used in manual PGA memory management mode.
  2. PGA_AGGREGATE_TARGET: Introduced in Oracle 9i, this parameter represents a target for the total amount of memory to be allocated for all sorts (and other PGA operations) across the instance. This setting is used in the Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM) mode, and Oracle will dynamically distribute memory among running processes, seeking to keep as many sorts in memory as possible. The value is expressed in bytes.
  3. WORKAREA_SIZE_POLICY: This parameter determines the method that Oracle will use to allocate memory to sorting and other operations that use the PGA. It has two possible values: MANUAL (which means Oracle uses the SORT_AREA_SIZE parameter to govern sort operations) or AUTO (which means Oracle uses the PGA_AGGREGATE_TARGET setting).
  4. SORT_AREA_RETAINED_SIZE: This parameter sets the maximum amount of PGA memory a server process can use after it sorts the data and no longer needs to keep the entire sort result in memory. Only a portion of the memory is retained, and the remainder is freed for other uses. This parameter is applicable when using manual work area sizing policy.
  5. _SORT_ELIMINATION_COST_RATIO: This is a hidden parameter (as indicated by the underscore at the start of its name) that can influence the optimizer's decision whether or not to use a sort-merge join over a nested loop join. The default value is 5, meaning the sort-merge join path needs to be at least 5 times cheaper for the optimizer to choose it over a nested loop join. Manipulating hidden parameters should be done with care and typically under the guidance of Oracle Support.
  6. _SMALL_TABLE_THRESHOLD: Another hidden parameter that defines what Oracle considers a "small" table for certain operations, including sorts. The value represents a number of blocks.

It's important to note that the goal of configuring these parameters is to maximize the number of in-memory sorts, which are faster than disk sorts, while preventing excessive memory usage that could lead to performance issues. As a database administrator, you should monitor your system regularly, adjust these parameters as necessary based on the workload, and perform testing to ensure optimal performance.
Remember that since Oracle 9i, it's generally recommended to use automatic memory management (setting PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY=AUTO) which allows Oracle to manage PGA memory dynamically. This often leads to better results than manual tuning, although manual tuning can still be useful in some specialized circumstances.

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
maximum amount of PGA memory
  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.