Written by Rene Woody

This blog is a continuation of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers how to select which SQL alternatives to test after Quest SQL Optimizer has generated the alternatives.

In Quest SQL Optimizer when you generate alternative SQL statements for your original SQL statement you may be presented with hundreds of SQL alternatives. The only way to be completely sure that you have the fastest one is to run all the SQL alternatives. But unless the SQL statements run relatively quickly, this is not always practical. So then you are left to determine which SQL statements you should select to test.

It is generally assumed that the SQL alternatives with the lowest plan cost (Oracle cost) should be the better performing SQL statements. But this frequently is not the case. So if you are using this criteria for selecting the SQL alternatives to test, faster executing SQL statements that have a higher cost are easily overlooked. So it is best if you do not just select a few of the lower cost SQL statements when testing the alternatives.

We have found that SQL statements with similar plan costs also have similar execution plans and consequently have similar execution times. So when selecting which alternatives to test, it is a good idea to select one alternative from each group of SQL statements with a similar plan cost.

For example, if you see a list of SQL alternatives like this:

Alt #1, Alt #2, and Alt #3 has very similar costs -  around 500. So instead of running all three of them, you may choose to run only Alt #1 as a representative from this group. Applying the same logic to other SQL alternatives, the list of SQL alternatives to execute would be Alt #1, Alt #4, and Alt #7. It may give you a better result to run these SQL alternatives from different groups of costs than to simply run the SQL with the lower costs.