Batch Optimizer Part 5 – Viewing the SQL alternatives and execution run time statistics

Richard To — Fri, 23 May 2008 12:07:00 GMT

Written by Rene Woody

After a SQL statement has been optimized in the Batch Optimizer in Quest SQL Optimizer for Oracle, you can view the text of the SQL alternatives and the execution plans in the Tuning Lab module. If the original SQL statement and alternatives have been executed, you can view the run time statistics as well.

1. In the SQL in Selected Job window in the Batch Optimizer, click the SQL statement to highlight it.

2. Click Open in Tuning Lab .

Functions Available in the Tuning Lab

Since the Batch Optimizer limits the number of SQL alternatives that are executed automatically, in the Tuning Lab you can execute the alternatives that were not executed in the Batch Optimizer to see if you can find a faster alternative.

When you are viewing the details of a SQL statement that was optimized in the Batch Optimizer, in addition to the Execute function, you may also use the Test for Scalability, Store Session, Reports, Retrieve Results, and Alter Session Parameters functions.

Select the Best SQL Alternative

In the Tuning Lab as you view the SQL alternatives and the run time statistics from the execution of the SQL statements, you can change the alternative that is the “best” alternative based on your own review of all of the run time statistics.

In the Tuning Lab | SQL Details Layout | Scenario Explorer window, right-click the SQL statement and select Mark as Best in Batch Optimizer.

This changes the SQL alternative that is selected as the “Best Alternative” in the Batch Optimizer. When you generate the optimized script, this “Best Alternative” will be used to replace the original SQL statement.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

Richard To's Blog

Batch Optimizer Part 5 – Viewing the SQL alternatives and execution run time statistics