How to use the Batch Optimizer with Rule Based Optimizer

This tip covers using the batch optimization process when your database is set to run with the rule based optimizer.

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 hte Oracle cost estimation to determine which is the best SQL alternative, in order to save time when testing the SQL alternatives, using the cost is an acceptable way to determine which SQL to execute first in order to reduce the testing time.

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 questions, "What option should be used to limit the number of SQL alternatives that are executed when the Optimizer Mode is set to Rule Based?? and "Which alternatives will be executed?"

The execution process in the Batch Optimizer uses the Use Intelligence Engine to execute best representation of SQL Alternativesoption first to determine which SQL alternatives to execute. It determines which alternatives to execute by first grouping the alternatives by the cost extimation and then picking one SQL from each group. So when you are using the rule base optimizer, this option is not used to determine which SQL to execute because there is no cost available to use to group the SQL alterntatives. So the next section, Select Additional SQL Alternatives to Execute, is used.

In reviewing the Batch Optimizer options for Select Additional SQL Alternatives to Execute, you can see that one option includes a setting for the maximum number of SQL statements to be executed. The % of alternatives with lowest cost option includes the Maximum alternatives executed. The % of alternatives with lowest cost option takes the first 25% (default setting) of the alternatives and executes them. When there is no cost estimation, it simply takes the first 25% of the 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. Then set the value (default = 50) for Maximum alternatives executed to limit the number of SQL alternatives executed so that if 500 alternatives are generated by the optimization process only 50 instead of 125 (25% of 500) are executed.

Note: If you really need the very best SQL execution results from the testing, you should not limit the number of alternatives that are tested but should test them all by selecting All alternatives.