Toad World Blog

Display Order for Alternatives SQL Statements

May 10, 2013 11:00:00 PM by Steve Hilker

This tip explains the order in which the alternative SQL statements are displayed in the Tuning Lab.

After the optimization process is finished, the SQL alternatives are displayed in the order of their Oracle cost from the smallest cost to the largest. The Oracle cost is found in the execution plan and is generated by Oracle. The Oracle cost provides an estimate of the system resources that will be used by this execution plan to process the SQL statement. The theory is that the SQL statement with the lowest cost should be the best alternative. When actually testing the performance of alternative SQL statements in your database environment, you will find that frequently the SQL statements with the lowest cost are not the best performing SQL, since if Oracle cost estimation was correct for this SQL, you probably would not need to tune this SQL, so the best practice is to execute all the SQL alternatives to find the best one and not just assume that the SQL statement with the lowest estimate is the best.

But no algorithm can accurately guess which SQL statement will be the best performing SQL for your database. The best performing SQL statement is unique to each environment since the performance depends on the design of the database, the indexes, the amount of data, how accurate the table and index statistics are, database parameter settings, and your hardware all of which combined to make a unique environment.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.