Donald K. Burleson Oracle Tips

Tuning Oracle sorting operations

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 a create index or an SQL ORDER BY or GROUP BY statement. In general, Oracle sorting occurs under the following circumstances:

• SQL using the ORDER BY clause

• SQL using the GROUP BY clause

• When an index is created

• When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

At the time a 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 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.

Prior to 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. Therefore, the designer must strike a balance between allocating enough sort area to avoid disk sorts for the large sorting tasks, keeping in mind that the extra sort area will be allocated and not used by tasks that do not require intensive sorting.

Starting in Oracle9i Oracle has introduced a new parameter called pga_aggregate_target.  When the pga_aggregate_target parameter is set and you are using dedicated Oracle connections, Oracle9i will ignore all of the PGA parameters in the init.ora file, including 

• sort_area_size 

• sort_area_retained_size

Instead of using sort_area_size, Oracle9i will use a common PGA area for sorting (as defined by pga_aggregate_target).  This leads to a more efficient use of RAM memory and less sorts that must be performed on disk in the TEMP tablespace.

Remember, sorts that cannot fit into the sort_area_size will be paged out into the TEMP tablespaces for a disk sort. Disk sorts are thousands of times slower than RAM sorts.

Tip! In some cases Oracle is able to bypass a sort by reading the data in sorted order from the index. Oracle will even read data in reverse order from an index to avoid an in-memory sort.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!)

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.