Lesson 1

As a small but very important component of SQL syntax, sorting is a frequently overlooked aspect of Oracle tuning. In general, an Oracle database will automatically perform sorting operations on row data as requested by

1. a CREATE INDEX or
2. an SQL ORDER BY or GROUP BY statement.

In short, the Oracle database will perform sorting operations whenever it detects the need to re-sequence row information.
In this module, you will learn about the tuning considerations related to sorting. By the end of this module, you will be able to:

• Module Objectives:
  1. Describe how Oracle performs sorts
  2. Identify the parameters that govern sorting operations
  3. Identify sorts in SQL statements
  4. Monitor for disk sorts
  5. Enable direct sorting in Oracle
  6. Allocate and size dedicated TEMP tablespace
  7. Monitor temporary segments in the TEMP tablespace
  8. Remove a sort from a SQL query
• Identify Sort Operations:
  As we have discussed in prior modules, Oracle sorting can be one of the most time consuming and expensive operations in the Oracle database. Your job as an Oracle tuner is to identify sort operations and verify that there is no possible way to avoid the sort. Remember, there are legitimate cases where a sort may be required such as the computation of a sum or an average number within a table. The trick is to investigate sorts and see if they can be tuned by adding a sorted index. We will begin this module with a review of the scripts that you can use to identify the amount of sorting in your Oracle database.

Sorting in an Oracle database can have a significant impact on the performance and tuning of an Oracle instance. Sorting is a resource-intensive operation, especially for large datasets, and tuning it properly can enhance overall system performance. Here’s how sorting impacts Oracle instance tuning and what to consider:

1. Impact on Memory Allocation
   • Program Global Area (PGA):
     • Sorting requires memory from the PGA. If sufficient memory is available, sorting operations are performed in memory (in-memory sort), which is fast.
     • If the required memory exceeds the allocated PGA, the database spills the sort to temporary tablespaces on disk (disk-based sort), which is slower and impacts I/O performance.
   • Tuning Consideration:
     • Use the PGA_AGGREGATE_TARGET parameter (for PGA management) or SORT_AREA_SIZE (if PGA management is not in use) to allocate sufficient memory for sorts.
     • Monitor the PGA usage via views like V$PGASTAT to ensure sorts are happening in memory.
2. Impact on Temporary Tablespace
   • Disk Usage:
     • When a sort operation spills to disk, it uses the temporary tablespace to store intermediate results. Excessive or poorly tuned sorts can cause contention or even exhaustion of the temporary tablespace.
   • Tuning Consideration:
     • Allocate enough space for the temporary tablespace and monitor its usage using views like V$TEMPSEG_USAGE.
     • Use multiple temporary tablespaces in a temporary tablespace group for better scalability and reduced contention.
3. I/O Performance
   • Disk-based Sorts:
     • Disk-based sorts increase I/O operations, potentially leading to performance bottlenecks, especially in environments with high concurrency or large datasets.
   • Tuning Consideration:
     • Minimize disk-based sorts by tuning memory parameters.
     • Ensure that the storage subsystem for temporary tablespaces is optimized for high I/O throughput.
4. CPU Usage
   • Sorting Algorithms:
     • Sorting is CPU-intensive, especially for operations like ORDER BY, GROUP BY, or large-scale index creation.
   • Tuning Consideration:
     • Ensure the database has adequate CPU resources for handling sort operations, particularly in systems with frequent or complex queries requiring sorting.
5. SQL Execution Plans
   • Execution Plan Cost:
     • Sorting operations add to the cost of SQL execution plans. If a query unnecessarily sorts data or if indexes are not used efficiently, sorting can degrade query performance.
   • Tuning Consideration:
     • Review execution plans using tools like EXPLAIN PLAN or SQL Tuning Advisor to identify and eliminate unnecessary sorting.
     • Use indexes (like sorted indexes) or optimized SQL queries to reduce sorting requirements.
6. Parallel Query Execution
   • Parallelism and Sorting:
     • Sorting operations can benefit from parallel execution for large datasets, but poorly tuned parallel queries can lead to contention and resource exhaustion.
   • Tuning Consideration:
     • Use parallel execution judiciously and monitor its impact on system resources.
7. Impact of Parameters
   • Key Parameters:
     • PGA_AGGREGATE_TARGET: Determines the total memory available for sorting and other PGA operations.
     • WORKAREA_SIZE_POLICY: Determines whether memory for sorting is managed manually or automatically.
     • SORT_AREA_SIZE: Used when PGA automatic management is disabled.
     • TEMP_TABLESPACE: Manages disk space for temporary sort spillovers.
   • Tuning Consideration:
     • Set these parameters appropriately based on workload and system capacity.
8. Monitoring and Diagnostics
   • Use views and tools to monitor sorting performance and detect bottlenecks:
     • V$SQL_WORKAREA_HISTOGRAM: Shows the distribution of sort operations by size.
     • V$SORT_SEGMENT and V$SORT_USAGE: Monitor temporary tablespace usage.
     • AWR and ADDM Reports: Analyze the impact of sorting on overall database performance.

Summary of Tuning Actions:

1. Optimize memory parameters to minimize disk-based sorts.
2. Monitor and size the temporary tablespace to handle spillover effectively.
3. Review SQL queries to reduce unnecessary sorting using execution plans and indexing strategies.
4. Leverage parallel execution for large sorts but manage concurrency carefully.
5. Regularly analyze system performance using AWR, ADDM, and dynamic performance views.

By tuning sorting operations effectively, you can significantly reduce resource contention and improve the performance of the Oracle instance.

Use the ORDER BY clause to order the rows selected by a query. Sorting by position is useful in the following cases:

1. To order by a lengthy select list expression, you can specify its position in the ORDER BY clause rather than duplicate the entire expression.
2. For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

The mechanism by which Oracle Database sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause.

In Oracle, sorting occurs under the following circumstances:

1. Using the ORDER BY clause in SQL
2. Using the GROUP BY clause in SQL
3. When an index is created
4. When a SORT is invoked by the SQL optimizer because inadequate indices exist for a table join
5. Using UNION, INTERSECTION, or MINUS clauses that cause sort-merge joins
6. Using the DISTINCT clause

In the next lesson, we will begin looking at how sorts are performed and the parameters governing sorts.