Lesson 10

In this module we carefully explored all of the causes and remedies for internal Oracle sorting.
Now you should be able to:

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 an SQL query

To monitor disk sorts in Oracle, you can use several methods to track how often sorts are spilling to disk instead of being handled in memory:

1. Query V$SYSSTAT View:
   • This view provides statistics on memory and disk sorts. You can use the following SQL command to see these statistics:
     

     SELECT name, value 
     FROM v$sysstat 
     WHERE name LIKE 'sort%';
             

   • sorts (memory) shows sorts completed in memory, while sorts (disk) indicates sorts that spilled to disk. Monitoring these can help understand the frequency and impact of disk sorts. More Info Tuning Sorts
2. Using Oracle Enterprise Manager (EM):
   • Oracle EM provides metrics like "Sorts to Disk (per second)" or "Sorts to Disk (per transaction)" which can alert you when disk sorts exceed specific thresholds, indicating performance issues. Sorts to Disk Per Transaction Sorts to Disk Per Second
3. Autotrace in SQL*Plus:
   • When running queries with AUTOTRACE enabled, you can see statistics like sorts (disk) in the output, helping identify which queries are causing disk sorts. More Info

Enabling Direct Sorting in Oracle
To enable direct sorting, you can adjust Oracle initialization parameters to manage how sorts are performed:

1. Adjust Initialization Parameters:
   • sort_direct_writes: Set this parameter to TRUE to enable direct writes for sorting operations. When set to AUTO (default), Oracle will automatically decide based on the sort_area_size.
     

     ALTER SYSTEM SET sort_direct_writes = TRUE SCOPE=SPFILE;
             

   • sort_area_size: This parameter determines the amount of memory allocated to sorts. Increasing this can help keep sorts in memory, but for very large sorts, setting sort_direct_writes to TRUE ensures direct writes to temporary tablespace:
     

     ALTER SYSTEM SET sort_area_size = <size_in_bytes> SCOPE=SPFILE;
             

   • Ensure that sort_write_buffers and sort_write_buffer_size are appropriately sized for direct writes if you're setting sort_direct_writes to TRUE.
2. Use of Temporary Tablespaces:
   • For sorting operations that are too large for memory, Oracle uses temporary tablespaces. Ensure these are properly sized and managed, particularly if you're engaging in large sorting tasks:
     • Use locally managed tablespaces with uniform extent sizes for efficiency.
     • Monitor I/O rates on temporary tablespaces to understand when sorts fail to perform in memory. Tuning Sorts

By understanding and adjusting these parameters, you can optimize how Oracle handles sorting operations, potentially reducing the reliance on disk sorts for better performance. Remember, the exact configuration might vary based on your specific Oracle version and workload characteristics.

Here are some terms from this module that may be new to you:

1. sorting: The process of re-sequencing result sets from Oracle queries.
2. in-memory sorts: Sorts that are performed very quickly in the memory allocated to sort_area_size.
3. disk sorts: Occurs when sorts exceed sort_area_size and Oracle will sort the result set in the TEMP tablespace.
4. SMON: The Oracle system monitor process.
5. index hint: A directive made in an SQL statement to force the use of an index when servicing a query.

In the next module, you will begin to learn about how the Oracle locking scheme impacts database performance.

To complete this module, click the Quiz link below to check your knowledge of the material in this module.
Sorting Tuning Techniques - Quiz