Written by Rene Woody
We all know that it is important to create SQL statements that perform well in our database environment, but all too often, after creating a SQL statement that retrieves the correct results, the optimizing of the SQL statement is left for another time. Frequently, that time never arrives. Quest SQL Optimizer for Oracle automates the SQL optimization process so that it can be done for you automatically.
This blog is the beginning of a series that will cover the Batch Optimizer module and how to use it to find SQL statements with performance problems and generated better alternatives.
Batch Optimization
The Batch Optimizer in Quest SQL Optimizer for Oracle combines finding the SQL statements in your application source code and database objects, optimizing SQL, and testing SQL alternatives into one simple process. It fully automates the whole process of identifying problematic SQL in your database applications, rewriting the syntax of SQL statements, and executing the original SQL statement along with the alternative SQL statements to find the fastest alternative. Then it creates a script from your original source code in which the poor performing SQL statements are replaced with better SQL alternatives.
The Batch Optimizer begins when you add a “job” to the batch queue. You can do this from within Quest SQL Optimizer or from Toad, SQL Navigator, Spotlight, or Performance Analysis. A job consists of text which is expected to contain one or more SQL statements. A job may be a block of text, a database object, an ASCII or binary file, a job from the SQL Scanner module, a job from the SGA Inspector module, or a Performance Analysis SQL repository.
Finding SQL
The first action of the Batch Optimizer is to search through the text in each job to find the INSERT, UPDATE, DELETE, and SELECT SQL statements. These SQL statements are classified according to characteristics in the execution plan that are likely to cause performance problems and users can specify the classification criteria to suit their only needs through the options.
Optimizing SQL
The second action of the Batch Optimizer is to optimize the SQL statements that were found during the searching process. It exhaustively rewrites the syntax of the SQL statement and applies the Oracle optimization hints. This process produces a list of semantically equivalent and syntactically correct SQL statements that produce the same result set as the original SQL statement.
Since this process is done automatically in a batch process, option settings are provided to determine which SQL statements are optimized. The default settings only optimize the SQL statements that are classified as “problematic.”
Testing SQL alternatives
The third action of the Batch Optimizer is to execute the original SQL statement and the alternative statements to see if any of the SQL alternatives outperform the original SQL statement.
Since the optimization process can provide hundreds of SQL alternatives, option settings are provided to control the number of SQL statements that are automatically executed. By default, only 10 SQL alternatives with the lowest Oracle cost will be executed. This means that there are likely to be many more SQL statements that can be tested if no faster alternative was found by the initial test.
Note: If a SQL statement has a bind variable, the execution process will wait until you enter the value for the variable to execute the SQL statement. If the SQL statement is an INSERT, UPDATE, or DELETE statement, it will require you to confirm the execution before running it when you are using the default settings.
Creating Optimized Script
After the SQL alternatives are executed, if one of the alternatives is faster than the original SQL statement, then an “optimized” script can be created. This script is a copy of the original text that was scanned, with the poor performing SQL statements commented out and the faster SQL alternatives inserted after the comment. You can then take this script and replace the code in the database object or application source code.