Donald K. Burleson Oracle Tips

Internals of Oracle sort operations

Oracle SQL does far more than simply extract the row information from the tables, and the row data can be sorted and summarized in a number of ways. By using Oracle SQL in conjunction with SQL*Plus, row information can be sorted, transformed, formatted, and displayed in a report format. In fact, SQL*Plus allows for sophisticated reports to be generated.

Many Oracle professionals fail to realize that the execution plan for Oracle SQL will often change when the data is sorted and transformed. To fully understand how Oracle SQL execution is affected, let’s explore the different methods for sorting data. Oracle provides several SQL directives that cause sorting of row data.

• Order by - An operation that sorts a set of rows for a query with an order by clause

• Join - An operation that sorts a set of rows before a merge join

• Group by - An operation that sorts a set of rows into groups for a query with the group by clause

• Aggregate - A retrieval of a single row that is the result of applying a group function to a group of selected rows

• Select unique - An operation that sorts a set of rows to eliminate duplicates

• Select distinct - An operation that forces duplicate row to be eliminated from the result set and requires sorting to identify the duplicate rows

• Create Index - The creation of an index always invokes a sort of the symbolic keys and ROWID values

Sadly, sorting is a frequently overlooked aspect of Oracle tuning. In general, an Oracle database will automatically perform sorting operations, and the SQL tuning expert must understand  all of the SQL directives that invoke Oracle sorting.

At the time a SQL*Plus session is established with Oracle, a private sort area is allocated in RAM memory for use by the session for sorting. If the connection is via a non-MTS dedicated connection, a program global area (PGA) is allocated according to the sort_area_size init.ora parameter. For connections via the multithreaded server, sort space is allocated in the large_pool. Unfortunately, until Oracle9i, the amount of memory used in sorting must be the same for all sessions, and it is not possible to add additional sort areas for tasks that require large sort operations.

Of course, sorts that cannot fit into the sort_area_size will be paged out into the TEMP tablespaces for a disk sort, and disk sorts are thousands of times slower than memory sorts.

Disk sorts are expensive for several reasons. First, they are extremely slow when compared to in-memory sorts. Also, a disk sort consumes resources in the temporary tablespace. Oracle must also allocate buffer pool blocks to hold the blocks in the temporary tablespace. In-memory sorts are always preferable to disk sorts, and 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.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm