SQL Optimizer for Oracle 9.2.3 - User Guide

Using SQL Optimizer

Tutorial: 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

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 and select New SQL Rewrite Session.

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

4. Click to retrieve the execution plan for your SQL statement.The Select Connection and Schema window displays.

5. Select a connection and schema to use.

6. Click to optimize the SQL statement.

7. 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

To test the alternatives SQL Optimizer generates, you can test run one or more 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 after you finish comparing your original SQL statement with the alternatives generated.
2. Click the drop-down arrow beside and select Test Run - All to execute all SQL alternatives.

3. The Test Run Settings dialog opens. Select criteria to apply to this test run. You can answer the questions on the Usage and Symptom page to allow SQL Optimizer to determine settings for you. Or you can click the Customize Test Run Settings link at the bottom of the page to manually specify test run settings.

   Tip: You can specify default values for some Test Run Settings options by clicking and selecting Optimize SQL | Test Run.

4. Review the execution statistics in the Alternatives pane.

Tutorial: Optimize SQL (Plan Control)

Using Plan Control mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates execution plan alternatives for your SQL statement without changing the source code. You can then execute the alternatives to retrieve run time statistics and identify the best alternative for your database environment. In the second step, you can use Plan Control mode to deploy the execution plan to the Manage Plans module as an Oracle plan baseline.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

Step 1: Generate and Execute Execution Plan Alternatives

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

2. Select Plan Control from the Optimize SQL start page.

   Note: If the start page does not display, click the arrow beside and select New Plan Control Session.

3. Enter a SQL statement in the Original SQL pane.

   Tip: Select the This SQL is contained inside a PL/SQL block checkbox if your SQL statement originated from a PL/SQL block. Selecting this checkbox ensures that the SQL text for the baseline you create matches the SQL text in your database.

4. Click to generate alternative execution plans for your SQL statement. The Select Connection and Schema window displays.

5. Select a connection and schema to use.

6. Click to execute all alternative execution plans to retrieve run time statistics.

7. Review the run time statistics in the Plans pane to identify the best alternative.

Step 2: Deploy Execution Plan as a Baseline

1. Click .

2. Review the following for additional information:

   Deploy	Description
   Select a plan to deploy	Click and select an execution plan alternative to deploy as a baseline plan.
   Performance comparison	Use this to review the performance of selected plans against the original.
   Mark the plan as	Review the following for additional information: Enabled—Select whether to enable or disable this plan. Fixed—Select whether to deploy this plan as fixed or non-fixed. Not Auto-Purged—Select whether to auto-purge when it is not used.
   Plan name	Enter a name for the plan.
   Description	Enter a description for this plan.

3. Click to deploy the plan to Manage Plans.

Tutorial: Generate Indexes

SQL Optimizer identifies columns to use as index alternatives for a SQL statement after it analyzes SQL syntax, relationships between tables, and selectivity of data. SQL Optimizer then combines identified alternatives into index sets.

To generate an index alternative

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

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

3. Click . The Select Connection and Schema window displays.

4. Select a connection and schema to use.

5. Select Index Details in the SQL Information pane to view index generation information.

6. Select an index set to view index name, details and generation script in the Alternative Details pane.

7. To test an index, select it in the Alternatives pane and click .

   Note: The Test Run function allows you to test an index set SQL Optimizer generated. It physically creates the indexes on the database, runs the SQL statement, retrieves execution statistics, and drops the indexes. Since this process physically creates indexes on your database, it may impact performance of other SQL statements.