Chat now with support
Chat with Support

SQL Optimizer for Oracle 10.0 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

About Managing Plans

You can use Manage Plans to manage the stored outlines and plan baselines deployed from Optimize SQL.

Stored Outlines

Stored outlines are a set of Oracle optimization hints that help maintain the execution plan for a SQL statement. Using SQL Rewrite mode in Optimize SQL, you can create and deploy stored outlines for SQL statements to minimize performance degradation during database changes.

The Outline Management feature of Manage Plans helps you organize and manage stored outlines deployed from SQL Rewrite mode. You can also manage the categories created for saved outlines.

Caution: Oracle only uses stored outlines if the SQL text saved with the outline is identical to the SQL statement in the source code.

SQL Plan Baselines

You can use the SQL Plan Management feature introduced in Oracle 11g to tune execution plans without changing the original source code. Plan control mode in Optimize SQL creates plan baselines and deploys them to Manage Plans.

The Baseline Management feature in Manage Plans helps you organize and manage plan baselines deployed from Optimize SQL. You can enable, disable, fix, or delete plan baselines for each SQL statement and search for plan baselines by SQL text.

Note: You can only create a Baseline Management session when connected to an Oracle 11g or later database.

 

Related Topics

Manage Outlines

Manage Baselines

Create Manage Plans Sessions

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

To create a new session

  1. Select the Manage Plans tab in the main window.

    Tip: Select the Show Manage Plans checkbox on the Manage Plans options page to display the Manage Plans tab.

  2. Click . The Create a New Manage Plans Session window displays.

  3. Review the following for additional information: 
    Connection Click to select an existing connection, or click to create a new connection.
    Tab name

    Enter a session name.

    Note: By default, the Manage Plans module uses your connection name for the tab name.

Note: You cannot change the database connection for a Manage Plans session. You must create a new session to use a different connection.

 

Related Topics

About Managing Plans

Manage Outlines

Outlines Management displays Oracle stored outlines deployed from SQL Rewrite mode in Optimize SQL.

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

To manage an outline

  1. Select Manage Plans.

    Note: You need to create a new Manage Plans session before managing your stored outlines. See Create Manage Plans Sessions for more information.

  2. Select the Outlines Management tab.

  3. Select a category from the Outline List.

    Tip: Double-click the category to display/hide the list of outlines for that category.

  4. Select an outline to review the outline's details in the SQL Text and the Outline Hints panes.

  5. Right-click an outline to select additional options for managing outlines. Review the following for additional information:

      Function Description
    Refresh List Click to refresh the Outline List to ensure that all recently deployed outlines and categories are displayed.
    Change (or create new)
    Outline Category
    • To move an outline to another category, right-click the outline and select Change Outline Category.
    • To create a new category, right-click an outline and select Change Outline Category. The outline is moved to the new category you created.
    Reset Used Flag To reset an outline that is flagged as used, right-click the outline and select Reset Used Flag.
    Rename Outline To rename an outline, right-click the outline and select Rename Outline.

To send to Optimize SQL or Batch Optimize SQL

  1. Select Manage Plans | Outlines Management.
  2. Select an outline in the Outline List.

  3. In the SQL pane, click the arrow beside and select an option for sending your SQL to Optimize SQL or Batch Optimize SQL.

     

    Note: By default, Manage Plans sends your SQL text to a SQL Rewrite session in Optimize SQL . Click the arrow beside and select Set Default to | option to change the default location.

 

Related Topics

About Managing Plans

Create Manage Plans Sessions

Deploy Outlines

Manage Baselines

Baselines Management displays Oracle plan baselines that you deployed from Plan Control sessions in Optimize SQL.

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

To manage a baseline

  1. Select Manage Plans.

    Note: You need to create a new Manage Plans session before managing your stored outlines. See Create Manage Plans Sessions for more information.

  2. Select the Baselines Management tab.

    Note: You can only access the Baselines Management tab when connected to an Oracle 11g or later database.

  3. Select a baseline plan in the Baselines pane.

    Tip: Click if you do not see a baseline plan that you deployed from Optimize SQL.

    Note: After plans are imported from SQL Optimizer, Oracle automatically renames the baseline plans using system-generated names.

  4. Select the checkbox in the Enabled column to enable the baseline.

  5. Select the checkbox in the Fixed column to fix the baseline.

    Note: If you have multiple baselines enabled for a SQL statement, Oracle gives priority to fixed baselines.

  6. Select the checkbox in the Auto Purge column to have Manage Plans automatically purge the baseline.

    Tip: You can change the duration that Manage Plans keeps unused baselines. Click the Configurations tab in the Baselines Management window and enter a value for number of weeks before purging unused baselines.

To view statistics information

  • Select the Statistics Information tab in the Manage Plans window to display statistics for the selected baseline.

To view baseline information

  • Select the Baseline Information tab in the Manage Plans window to display information for the selected baseline.

  

Related Topics  

About Managing Plans

Deploy Baselines

Export, Import, and Migrate Baselines

Configure General Settings (Baselines Management)

Send to SQL Optimizer (Baselines Management)

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating