Toad World Blog

SQL Optimizer for Oracle - Optimize SQL (SQL Rewrite)

Jan 11, 2013 12:13:00 PM by Richard To

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

  1. Select the Optimize SQL tab in the main window.

  2. Select SQL Rewrite from the Optimize SQL start page.

    Note: If the start page does not display, click the arrow beside TB_NewSession_OptimizeSQL.png-550x0 and select New SQL Rewrite Session.

  3. Enter a SQL statement in the Alternative Details pane.

  4. Click B_Rewrite.png-550x0. The Select Connection and Schema window displays.

  5. Select a connection and schema to use.

  6. Click TB_Compare_OptimizeSQL.png-550x0 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

  1. Click TB_SQLRewrite_OptimizeSQL.png-550x0 after you finish comparing your original SQL statement with the alternatives generated.

  2. Click TB_BatchRun_OptimizeSQL.png-550x0 to execute all SQL alternatives.

    Tip: You can review batch run settings before executing the alternatives by clicking TB_Options.png-550x0 and selecting Optimize SQL | Batch Run.

  3. Review the execution statistics in the Alternatives pane.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki Tutorial

Richard To

Written by Richard To

Richard specializes in using AI technique to solve database performance problems.