Oracle Sorting Basics

Question: How does Oracle perform in-memory sorts?
Oracle Database provides in-memory sorting capabilities as a part of its comprehensive suite of performance-enhancing features. This function is particularly useful for query-intensive operations where data sorting plays a critical role. The mechanics of in-memory sorting involve several steps and configurable parameters. Firstly, the Oracle Database determines the optimal way to execute the SQL statement based on the associated query optimizer. This step could involve sorting data in-memory if it's predicted to increase efficiency. Here, data sorting can be critical for operations like GROUP BY, ORDER BY, analytical functions, joins, etc.
When a sort operation is needed, Oracle uses available memory in the Program Global Area (PGA), a region of memory that contains data and control information for a single server process. Oracle calls this memory region the sort area. Oracle reads the data to be sorted into the sort area and performs the sorting operation.
The size of the sort area is managed by the SORT_AREA_SIZE parameter. This parameter specifies the maximum amount of memory Oracle can use for a sort before it starts to write sorted data to disk in temporary segments. Therefore, setting this parameter to a larger value allows more data to be sorted in memory, thereby minimizing disk I/O, which is relatively more expensive in terms of performance.
However, since Oracle 9i, Oracle introduced the PGA_AGGREGATE_TARGET parameter, which specifies the total memory allocated to all server processes attached to the instance. With this setting, Oracle manages the individual sort area sizes, aiming to maximize performance by keeping most sorts in memory while preventing excessive swapping caused by overly large memory requests.
When the size of the data to be sorted exceeds the memory allocated in the sort area, Oracle uses a method called a sort merge. In this process, the data is broken down into smaller, sortable subsets that fit in the sort area. Each subset is sorted and written to a temporary disk segment as a 'run'. Once all data has been sorted into runs, Oracle performs a merge operation to combine these runs back into a single, sorted dataset.
In Oracle 12c, the In-Memory column store feature was introduced. If specific tables or partitions are assigned to the In-Memory column store, then they will be stored in memory in a columnar format. This feature can greatly improve the performance of queries that perform sorts, by reducing the I/O operations and making effective use of the server's CPU cache.
However, remember that tuning sort operations is a balance. Increasing the size of the sort area or the PGA aggregate target may speed up individual operations but can also use up system memory that might be needed for other operations. Therefore, careful monitoring and performance testing are necessary to determine the optimal configuration.
It's also crucial to note that efficient indexing, data normalization, and effective SQL query writing can significantly reduce the need for sorting, thereby optimizing the overall performance. So, while understanding in-memory sorts is important, it's only one aspect of comprehensive database optimization.

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.