Toad World Blog

Optimize SQL (Plan Control)

Jan 10, 2013 11:00:00 PM by Steve Hilker

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 TB_CreateSession_OptimizeSQL 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 TB_GeneratePlans_PlanControl 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 2867.TB_BatchRun_OptimizeSQL 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 TB_Deploy_OptimizeSQL.

  2. Review the following for additional information:

    Deploy Description

    Select a plan to deploy

    Click 4812.TB_DownArrow 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 3173.TB_DeployButton to deploy the plan to Manage Plans.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki Tutorial

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.