Lesson 3	Sorting Parameters
Objective	Identify the Parameters that govern Sorting

Oracle Parameters which govern Sorting

In Oracle 11g R2, sorting operations are governed by several parameters related to memory allocation, disk usage, and sorting policies. Below is a list of key parameters that influence sorting in Oracle:

Memory Management Parameters
- PGA_AGGREGATE_TARGET
  - Specifies the total amount of memory available for Program Global Area (PGA) operations, including sorting.
  - Oracle dynamically adjusts memory allocation for sorting operations based on this parameter.
- SORT_AREA_SIZE
  - Specifies the amount of memory allocated for sorting in each user session when PGA management is not used.
  - Relevant if WORKAREA_SIZE_POLICY is set to MANUAL.
- SORT_AREA_RETAINED_SIZE
  - Specifies the amount of memory retained for sorting after the sort operation is complete.
  - Relevant for manual PGA management.
- WORKAREA_SIZE_POLICY
  - Determines whether the sort memory is managed manually or automatically:
Disk and Temporary Tablespace Parameters
- TEMP_TABLESPACE
  - Determines the temporary tablespace used for sorting when memory is insufficient, and the sort spills to disk.
- DB_FILE_MULTIBLOCK_READ_COUNT
  - Affects the efficiency of disk reads during disk-based sorting by determining the number of blocks read in a single I/O operation.
Query Execution and Optimization Parameters
- PARALLEL_EXECUTION-related parameters
  - Enable and govern the use of parallel execution for sorting operations, particularly for large queries.
- OPTIMIZER_MODE
  - Influences query execution plans, which in turn affect whether sorting is performed and how it is handled.
Resource Management Parameters
- RESOURCE_MANAGER_PLAN
  - If Resource Manager is enabled, it may allocate memory and CPU resources that indirectly affect sorting performance.
- SESSION_MAX_SORT_SIZE (hidden parameter)
  - Specifies the maximum amount of memory a session can allocate for sorting operations.
Monitoring and Diagnostics Parameters
- STATISTICS_LEVEL
  - When set to TYPICAL or ALL, it enables monitoring and collection of statistics for sorting operations, which can guide tuning.

Best Practices for Sorting in Oracle 11g R2:

Use PGA_AGGREGATE_TARGET with WORKAREA_SIZE_POLICY set to AUTO for effective automatic memory management.
Monitor temporary tablespace usage using views like V$TEMPSEG_USAGE and ensure it is appropriately sized.
Optimize SQL queries to minimize unnecessary sorting, leveraging indexes and execution plans.
Analyze sort-related performance metrics using AWR or ADDM Reports.

These parameters and their appropriate configurations ensure efficient memory and resource usage for sorting in Oracle 11g R2.

init.ora parameters govern Sorting Operations

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.
The following diagram displays the output shown below to see information about the two most important parameters:
1. sort_area_size
2. sort_area_retained_size

Show Parameters

SQL> show parameter sort
| NAME                     | TYPE    | VALUE |
|--------------------------|---------|-------|
| nls sort                 | string  |       |
| sort_area_retained_size  | integer | 0     |
| sort_area_size           | integer | 65536 |
| sort_direct_writes       | string  | AUTO  |
| sort_read_fac            | integer | 5     |
| sort_spacemap_size       | integer | 512   |
| sort_write_buffer_size   | integer | 32768 |
| sort_write_buffers       | integer | 2     |

show parameter sort

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

Tuning the Oracle Instance via Sorting
A small but very important component of Oracle tuning, sorting is often overlooked. An Oracle database will automatically perform sorting operations on row data under the following circumstances:
1. When an index is created
2. When using the ORDER BY clause in SQL
3. When using the GROUP BY clause in SQL
At the time a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting, based on the value of the sort_area_size initialization parameter. 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 DBA must strike a balance between allocating enough sort area to avoid disk sorts for the large sorting tasks, keeping in mind that the extra sort area will be allocated and not used by tasks that do not require intensive sorting.
Whenever a sort cannot be completed within the assigned space, a disk sort is invoked using the temporary tablespace for the Oracle instance. A sort in the temporary tablespace is very I/O intensive and can slow down the entire database. As a general rule, the sort_area_size should be large enough that only index creation and ORDER BY clauses using functions should be allowed to use a disk sort. However, operations on large tables will always perform disk sorts. For example, the following query will sort the salaries for all 101,000 employees at Oracle Corporation:
```
select salary
from employee
order by salary;
```

Sort in RAM Space

Oracle always tries to sort in the RAM space within sort_area_size and only goes to a disk sort when the RAM memory is exhausted. Disk sorts are expensive for several reasons.

First, they consume resources in the temporary tablespaces.
Oracle must also allocate buffer pool blocks to hold the blocks in the temporary tablespace.
In-memory sorts are always preferable to disk sorts, and disk sorts will surely slow down an individual task, as well as affect concurrent tasks on the Oracle instance.

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.
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_size	Defines the size of the in-memory sort area
sort_write_buffers	Defines the number of buffer blocks
sort_direct_writes	If set to TRUE will bypass the need for the sort to contend for free blocks in the buffer cache

Oracle Sorting Parameters

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.

PGA memroy
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.
What to do if Database is running out of space
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.
```
The SQL statement that is using the sort operation will fail as a result. 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.