May 10, 2013 11:00:00 PM by Steve Hilker
This tip explains how you can view the SQL alternatives generating in the Batch Optimizer in the Tuning Lab.
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.
Since the Batch Optimizer limits the number of SQL alternatives that are executed automatically, in the Tuning Lab you can execute any of 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.
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.
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.
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.