Written by Rene Woody
This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle.
In the Batch Optimizer in Quest SQL Optimizer for Oracle you may notice that the options for selecting which SQL alternatives to execute by the batch process are all based on the Oracle cost estimation. Although it is not a good idea to trust Oracle cost estimation for problematic SQL statements, in order to save time when testing the SQL alternatives, it is an acceptable way to reduce the test run time.
Note: If you really want the best SQL optimization results from the testing, you will not limit the number of alternatives that are tested but will test them all by selecting All alternatives.
When you have the Oracle Optimizer Mode parameter set to Rule Based in your Oracle database, there is no cost estimation in the execution plan of the SQL statements. So, that prompts the question, “Which Auto Select SQL Rewrites for Execution option should be used to limit the number of SQL alternatives that are executed when the Optimizer Mode is set to Rule Based?”
In reviewing the Batch Optimizer option Auto Select SQL Rewrites for Execution, you can see that one option includes a setting for the minimum number of SQL statements to be executed. The % of alternatives with lowest cost option includes the Minimum alternatives executed. This setting takes the first 10 (default setting) alternatives and executes them. When there is no cost estimation, it simply takes the first 10 alternatives that were generated and executes them. Therefore, this is the setting that should be selected when the Optimizer Mode parameter is set to Rule Based and you want to limit the number of SQL alternatives executed.