Toad World Blog

How the Batch Optimizer Works

May 11, 2013 12:20:00 PM by Steve Hilker

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.

TT_BatchOptimizer_HowBatchOptimizerWorks_Pic1.png-550x0

 

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.

TT_BatchOptimizer_HowBatchOptimizerWorks_Pic2.png-550x0

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.