How the Batch Optimizer Works

This tip gives an overview on the functionality in the Batch Optimizer.

Batch Optimizer Process

The Batch Optimizer combines into one simple process the finding of SQL statements in your application source code and database objects, rewriting the SQL syntax and adding Oracle optimization hints to create SQL alternatives, and testing the SQL alternatives. That is, it combines the functions from the SQL Scanner (finding SQL) and the Tuning Lab (rewriting and executing SQL.) 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 processing 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. You can specify the classification criteria to suit your database environment through the SQL Classification 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 SQL statements that are semantically equivalent to, and 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 setting optimizes the SQL statements that are classified as “Problematic” and "Complex".

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. 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.

Notes: If a SQL statement has a bind variable, the execution process waits until you enter the value for the variable to execute the SQL statement. With the default option setting, the INSERT, UPDATE, or DELETE statements are not automatically executed. You must manually start the execution.

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 for the job, 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.