Written by Rene Woody
 

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings that determine which SQL statements will be automatically executed by the Batch Optimizer process and the method used for their execution.

 

 

When the optimization process is finished, the execution of the original SQL statement and the selected alternatives is automatically started if the Automatically execute SQL rewrites after optimization setting is selected.

 

The specific type of SQL statements which are executed automatically is determined by the SQL types selected under the Execute only these SQL statement types: SELECT, INSERT, UPDATE, or DELETE. By default, only the SELECT SQL statements are executed automatically.
 

 

Note: INSERT, UPDATE, and DELETE SQL statements are run in a transaction that is rolled back after each execution so that the data in the database is not changed by the execution process.

 

The Execute only these SQL alternatives setting also determines which SQL alternatives are executed. All of the options under this setting are based on the Oracle cost estimation for each SQL alternative in comparison to the Oracle cost of the original SQL statement. By default, only the ten SQL statements with the lowest Oracle cost are executed.
 

 

When executing the SQL statements in the Batch Optimizer, the default execution method is Run on server. This means that all SQL statements are executed on the server and do not return the data from the SELECT statements to the client. Executing the SQL statements with this method provides you with the time it takes the SQL statement to execute on the Server. It does not cause extra network traffic by sending the data to the client. When using this method, your logon account must have the privilege to use the SYS.DBMS_SQL package to execute SQL statements on the server.