Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Optimizing Sorting for Oracle SQL Statements
Oracle Tips by Burleson
 

As a small but very important component of SQL execution, sorting is a frequently overlooked aspect of Oracle SQL tuning. In general, an Oracle database will automatically perform sorting operations on row data as requested by an order by or group by clause, but there are other SQL clauses that also cause sorting. Oracle sorting happens transparently to the user and occurs under the following circumstances:

  • When SQL contains an order by clause

  • When SQL contains a group by clause

  • When SQL contains a select distinct clause

  • When an index is created

  • When SQL contains a union or minus operation

  • When a sort merge join is invoked by the SQL optimizer

At the time a session is established with Oracle, a private sort area is allocated in RAM 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 region allocated by the large_pool_size initialization parameter.

Unfortunately, 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 while not overloading the RAM demands on the database server.  You must also keep in mind that the extra sort area will be allocated and not used by tasks that do not require intensive sorting. Of course, sorts that cannot fit into the sort_area_size will be paged out into the TEMP tablespace for a disk sort.

Disk sorts are about 14,000 times slower than memory sorts. Also, a disk sort consumes resources in the TEMP tablespace and may impact the performance of other concurrent SQL sorts because Oracle must allocate buffer pool blocks to hold the blocks in the TEMP tablespace.

In-memory sorts are always preferable to disk sorts, and disk sorts will surely slow down an individual task and may also 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 Oracle block buffer. In short, our goal is to perform a sort only when it is required to service the query, and to ensure that the settings for in-memory sorts and disk sorts are optimal.

Let’s begin with a review of the Oracle initialization parameters that affect sorting behavior.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter