Oracle Sorting Basics

In Oracle 11g, "in-memory" sorts are a performance optimization technique used when sorting data in memory without writing to disk. Here's an overview of how Oracle performs in-memory sorts:

1. PGA (Program Global Area) Usage:
   • Oracle uses the Program Global Area (PGA) to perform in-memory operations, including sorting. The PGA is a memory region allocated for a server process that contains data and control information exclusive to that process.
   • For an in-memory sort to occur, Oracle tries to allocate enough memory from the PGA to hold the data that needs to be sorted.
2. Sort Area Size:
   • The memory available for in-memory sorts is determined by the PGA_AGGREGATE_TARGET parameter. This parameter specifies the total amount of memory allocated to all PGAs in an instance.
   • Oracle manages the memory dynamically, trying to perform sorts in memory as long as there is sufficient space. If the sort fits within the allocated memory, the entire sort operation is completed in memory, significantly improving performance.
3. Single-Pass and Multi-Pass Sorts:
   • Single-Pass Sort: If the data set is relatively large and cannot entirely fit in memory but can be processed in a single pass using temporary segments, Oracle will perform a single-pass sort.
   • Multi-Pass Sort: When the data set is too large to fit into memory or be managed in a single pass, Oracle performs a multi-pass sort, which involves writing parts of the data to temporary tablespaces on disk and then merging them later.
4. Automatic Memory Management:
   • Oracle 11g uses automatic memory management features to dynamically adjust memory allocation. If PGA_AGGREGATE_TARGET is set and automatic memory management is enabled, Oracle determines the optimal amount of memory for sorting based on system load and the number of concurrent sessions.
   • Oracle tries to minimize disk I/O by performing as many sorts in memory as possible, adjusting memory allocations dynamically to optimize performance.
5. Temporary Tablespaces:
   • If the data set exceeds the available PGA memory for sorting, Oracle uses temporary tablespaces to store intermediate sort results on disk. The data is then merged to produce the final sorted output.

Key Points:

• The goal of in-memory sorts is to minimize disk I/O and leverage the speed of memory for sorting operations.
• In-memory sorts are more efficient and faster compared to sorts that require writing data to disk.
• Tuning parameters like PGA_AGGREGATE_TARGET and monitoring memory usage can help optimize the performance of in-memory sorts.

Oracle has two techniques for sorting. If the sort is smaller than sort_area_size, the sort will be performed very quickly in the memory allocated to sort_area_size. These are known as in-memory sorts. For large sorts that exceed sort_area_size, Oracle will sort the result set in the TEMP tablespace. These are known as disk sorts, and they require longer execution time than in-memory sorts. The followig series of images shows you this process.

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

There are several ways to sequence Oracle output rows:

1. Oracle internal sort (in sort_area_size or TEMP tablespace)
2. Retrieve the rows in pre-sorted order (using an index)
3. Using a third-party sort product: This is for businesses that have to sequence millions of rows of output on an hourly basis, external sort products can be faster than using Oracle to sort the data.

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. Disk sorts will surely slow down an individual task, as well as impact concurrent tasks on the Oracle instance. Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer. For these reasons, in-memory sorts are always preferable to disk sorts. In the next lesson, you will look at the specific Oracle parameters that govern sorting operations.