Written by Rene Woody
This blog is the beginning of a series that will cover the optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle.
When writing a SQL statement, it is easy to be satisfied when you have figured out how to get the correct results. This is especially true when you are writing a complicated SQL statement. But in order to have good database performance, you need to find the best execution plan that retrieves the desired results.
Quest SQL Optimizer has a unique optimization process for finding the best performing SQL statements for each unique database environment. It generates SQL alternatives for a given SQL statement in order to find alternative execution plans for that SQL statement.
SQL Syntax Transformation
The optimization process first transforms the syntax of a SQL statement to generate alternative SQL statements through a proprietary and unique artificial intelligence algorithm. It applies SQL syntax transformation rules to rewrite a SQL statement in a multitude of different ways that still produces the same result. The knowledge base in the optimization engine has more than 60 SQL transformation rules, which can theoretically generate up to 60! alternatives. (60! = 8.3209871127413901442763411832e+81)
This optimization process is a recursive process that rewrites a SQL statement until there are no more possible alternatives. Then it will rewrite each of the alternatives. It will stop when it can no longer generate any more SQL alternatives or when it has reached a user-defined quota for the maximum number of SQL alternatives it should generate while transforming the syntax.
Database Optimization Techniques
After it has finished rewriting the SQL statement using the SQL syntax transformation rules, it will generate more SQL statements by applying techniques that are unique to the database platform. For Oracle, it applies the Oracle optimization hints. For SQL Server, it applies hints. For DB2 LUW, it applies SQL Options. And for Sybase, it applies forces, optimization goals and optimization criteria. For all database platforms, it applies these techniques to original SQL statement and the SQL alternatives. You can control which specific items are applied to the SQL statements through the settings in the Optimization options.
Looking for unique execution plans
After the optimization process has finished generating the SQL alternatives, it will retrieve the execution plan for each alternative SQL. It compares the execution plans so that it can eliminate the SQL alternatives that have duplicate execution plans.
The final result of this optimization process is one SQL alternative for each unique execution plan.
Using Quest SQL Optimizer, you can be sure that you will have tried most of possible alternative execution plans for your original SQL statement, which can be mathematically proved to generate the same results. The next step is then to test the alternatives to findthe one that performs the best in your database environment.
