SQL Optimizer for Oracle - Optimize SQL (SQL Rewrite)

Using SQL Rewrite mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. An Oracle cost estimate displays for each alternative generated. In the second step, SQL Optimizer executes the alternatives to test each statement’s performance. This provides execution times and run time statistics that allow you to find the best SQL statement for your database environment.

Tip: The Oracle cost only provides an estimate of resource usage to execute a SQL statement. Since statements with higher cost may perform better, you should test alternatives generated to determine the best statements for your database environment.

Step 1: Optimize the SQL Statement

Select the Optimize SQL tab in the main window.
Select SQL Rewrite from the Optimize SQL start page.

Note: If the start page does not display, click the arrow beside and select New SQL Rewrite Session.
Enter a SQL statement in the Alternative Details pane.
Click . The Select Connection and Schema window displays.
Select a connection and schema to use.
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.

Step 2: Test Alternative SQL Statements

The Batch Run function provides an efficient way to test alternatives SQL Optimizer generates. You can execute selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements run in a transaction that is rolled back after execution.

To test a SQL statement alternative

Click after you finish comparing your original SQL statement with the alternatives generated.
Click to execute all SQL alternatives.

Tip: You can review batch run settings before executing the alternatives by clicking and selecting Optimize SQL | Batch Run.
Review the execution statistics in the Alternatives pane.