Selecting Which SQL to Execute First

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.

Option: Plan Cost

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.