Author: Burleson

Oracle Sorting is amongst the important components of Oracle Tuning. In earlier databases the data used to be extracted unsorted and external methods were used to sort it.

External Sorting in Oracle: 

After SQL*Plus retrieves the unsorted rows, a separate step is added id added that sorts the results.

#!/bin/ksh
sqlplus /nolog @/u01/emp.sql > /u01/ssd/unsorted.txt 2>&1 |sort > /u01/ssd/sorted.txt

Automatic Sorting in Oracle:

Automatic Sorting is performed in Oracle whenever an index is created, ORDER BY clause is used in SQL or a GROUP BY clause is used in SQL.

Sorting in Oracle:

Oracle uses several sorting algorithms. Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources. There is a hidden parameter _newsort_enabled = {TRUE|FALSE} which decides whether the new sort algorithm will be used. Amongst the several methods to sequence the output rows includes

• Oracle internal sort mechanism.
• Use of indexes to retrieve rows.
• Use of third-party sort product s.

Choosing Pre-sorting or Post-sorting:

The choice of pre-sorting or post-sorting depends on several factors. These include

Resource Allocation:

Whenever a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting purpose. This allocation is based on the value of the sort_area_size initialization parameter. The database administrator must allocate enough resources in order to avoid dist sorts for larger disk. Unfortunately, the amount of memory must be the same for all sessions, and it's not possible to add additional sort areas for tasks that are sort intensive.

Disk Sort using temporary tablespaces:

A disk sort is invoked using the temporary tablespace for the Oracle instance if the sort is unable to complete within assigned space.. A sort in the temporary tablespace is very I/O intensive and can slow down the entire database.

Disk sort:

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.

Operations on large tables:

The operations on large tables will always perform disk sorts. For example, the following query will sort the salaries for all employees.

select salary from employee order by salary;

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:

• Disk sorts are expensive for several reasons.
• They consume resources in the temporary tablespaces.
• Disk sorts slow down an individual task, as well as affect concurrent tasks on the Oracle instance.
• Excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer.

Analyzing Amount of Sorts:

The amount of disk and in-memory sorts can be analyzed by issuing the query. The sort information obtained in the output can be captured in STATSPACK tables and plotted to determine the times when disk sorts are experienced by the instance.

SPOOL/tmp/sorts
COLUMN VALUE FORMAT 999,999,999
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE ‘sort%';
SPOOL OFF

Total Work Area:

There are limitations to pga_aggregate_target as no RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size , whichever is smaller. This means that no task may use more than 10 megabytes for sorting. The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size . 

Increasing size of Large Sorts:

A DBA can avoid a time-consuming disk sort by RAM in the TEMP tablespace. The following settings would increase the default sizes for large sorts.

pga_aggregate_target = 1000m
_pga_max_size = 1000m
_smm_px_max_size = 333m

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips for Oracle Cancel-Based Recovery!!
   Oracle Multitable Inserts, Important Concerns!!
   DBA Tips for Troubleshooting Oracle Orphaned Sessions!!
   Avoiding Rollback Generation in Bulk Delete!!