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.

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

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

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.
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:

Setting the parameter sort_direct_writes to TRUE, and thereby bypassing the need for the sort to contend for free blocks in the buffer cache will improve sorting performance by up to 50 percent. Of course, this is done at the expense of additional memory with the SGA. This movement towards segmenting the buffer into individual components can dramatically improve response times in Oracle.
Oracle tries to perform sort and hash operations in memory (PGA), but if a sort operation is too large to fit into memory, it uses the temporary tablespace to do the work. It is important to understand that even a single large sort operation has the potential to use up an entire temporary tablespace. Since all database sessions share the temporary tablespace, the session that runs the large sort operation could potentially result in other sessions receiving errors due to lack of room in that tablespace, or fail itself. Once the temporary tablespace fills up, all SQL statements that seek to use the temporary tablespace will fail with the

ORA-1652: unable to extend temp segment error.

New sessions may not be able to connect, and queries can sometimes hang and users may not be able to issue new queries. You try to find any blocking locks, but none exists. If the temporary tablespace fills up, transactions will not complete. If you look in the alert log, you will find that the temporary tablespace ran out of space. Operations that use an ORDER BY or GROUP BY clause frequently use the temporary tablespace to do their work. Large hash joins also need the temp space while they are execting. You must also remember that creating an index or rebuilding one also makes use of the temporary tablespace for sorting the index.

Oracle uses the PGA memory for performing the sort and hash operations. Thus, one of the first things you must do is to review the current value set for the PGA_AGGREGATE_TARGET initialization parameter and see if bumping it up will help if you happen to see a lot of I/O occurring in the temporary tablespace. Nevertheless, even a larger setting for the PGA_AGGREGATE_TARGET parameter does not guarantee that Oracle will perform a huge sort entirely in memory. Oracle allocates each session a certain amount of PGA memory, with the amount it allocates internally determined, based on the value of the PGA_AGGREGATE_TARGET parameter. Once a large operation uses its share of the PGA memory, Oracle will write intermediary results to disk in the temporary tablespace. These types of operations are called one-pass or multi-pass operations, and since they are performed on disk, they are much slower than an operation performed entirely in the PGA.

If your database is running out of space in the temporary tablespace, you must increase its size by adding a tempfile. Enabling autoextend for a temporary tablespace will also help prevent out of space errors. Since Oracle allocates space in a temporary tablespace that you have assigned for the user performing the sort operation, you can assign users that need to perform heavy sorting a temporary tablespace that's different from that used by the rest of the users, thus minimizing the effect of the heavy sorting activity on overall database performance. Note that unlike table or index segments, of which there are several for each object, a temporary tablespace has just one segment called the sort segment. All sessions share this sort segment. A single SQL statement can use multiple sort and hash operations. In addition, the same session can have multiple SQL statements executing simultaneously, with each statement using multiple sort and hash operations. Once a sort operation completes, the database immediately marks the blocks used by the operations as free and allocates them to another sort operation. The database adds extents to the sort segment as the sort operation gets larger, but if there is no more free space in the temporary tablespace to allocate additional extents, it issues the

ORA-1652:unable to extend temp segment error.

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.