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

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips


Minimize Oracle SQL sorting

Techniques to include in your overall performance strategy are those that relate to minimizing the amount of overall sort activity, and specifically, sort activity that takes place on disk.  A good place to start is to understand what things cause sorts in the first place.  A list of sort-related commands and SQL-related options include:


·        DISTINCT

·        ORDER BY

·        GROUP BY

·        UNION

·        INTERSECT

·        MINUS

·        IN, NOT IN

·        Certain unindexed joins

·        Certain correlated subqueries

All of these SQL commands have the potential to create a sort.  As a DBA, you probably will not know which queries will sort entirely in memory and which ones will be forced to go to disk.  However, you can get a feel for the overall sort performance by looking at the memory sort ratio that is contained in the output from the memsnap.sql query.

As has already been mentioned, when a sort exhausts its memory allotment, it will then be forced to go to disk (the actual place being the user's temporary tablespace assignment).  Oracle records the overall number of sorts that are satisfied in memory, as well as those that end up being finalized on disk.  Using these numbers, you can calculate the percentage of memory sorts vs. disk sorts and get a feel for how fast your sort activity is being resolved.  

If your memory sort ratio falls below 90%, you may want to increase the parameters devoted to memory sorts, sort_area_size and sort_area_retained_size.  Keep in mind that individual users may possess the ability to alter their own sessions and increase their sort_area_size assignments.  As a DBA, you may want to restrict users that have the alter session privilege so that this does not occur.

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.

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

Order directly from the publisher for only $19.95 and get instant access to the Oracle scripts.





Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

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

Oracle® is the registered trademark of Oracle Corporation.