May 10, 2013 11:00:00 PM by Steve Hilker
This tip explains how Quest SQL Optimizer selects which SQL alternatives to execute first in the Batch Run.
The Optimize SQL | Optimizer | Batch Run | Order and Termination | Execution Order option enables you to use either the Intelligence order or the Plan cost order to execute the SQL alternatives.
The Plan Cost option executes the SQL alternatives in the Batch Run from the lowest Plan Cost (Oracle Cost) to the highest.
Option: Intelligent order (Running representatives from different plan cost first)
The Intelligent order option uses an artificial intelligence engine to select the SQL statements that are executed first. This engine uses a unique process that determines a grouping of the SQL statements based on the Oracle cost. It then selects one SQL statement from each group to execute first. Once the Batch Run has finished executing these SQL statements, it will execute all the other SQL alternatives in the Plan Cost order.
By executing one SQL from each group first, you increase the likelihood that you will find a faster alternative quickly because SQL statements with similar Oracle costs tend to have similar performance. If you find a SQL alternative that meets your performance goal, you can stop the Batch Run and save a lot of time by not having to test all the alternatives.
Let’s take a look at how the Intelligence Order works. In this example, we have the original SQL statement and 15 alternatives generated by Quest SQL Optimizer for Oracle. The cost of all the SQL alternatives and the cost distribution for these statements are shown as follows:
As you can see in the chart, the cost distributions of all SQL alternatives are grouped into multiple regions. SQL statements with similar cost normally have similar execution plans which results in similar performance.
Executing the original and alternative SQL statements produced the following run times.
In reviewing the run times, you can look at the Plan Cost and see that the Total Elapsed Time and Total CPU time for each of the alternatives with a similar cost are very close. Sometimes, because of how long it takes to execute a SQL statement, testing all of the alternatives is not practical. So, to save time when testing the alternatives, instead of testing only the SQL statements with the lowest cost, it is best to test one SQL statement from each group of SQL with a similar cost.
For this example, Alt #12 is the fastest SQL statement even though the estimated cost from Oracle is among the highest. The fastest SQL statement would have been missed if only the SQL statements with the lowest cost were selected for testing.
The behavior we see in this example, that SQL statements with similar cost have similar run time, is common across the broad whether the syntax of the SQL statement is simple or very complex.
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.