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.
   Performance Comparison	Description
   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.