SQL Optimizer for Oracle 10.0

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

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.
Click . The Create a New Manage Plans Session window displays.

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

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.
Select the Outlines Management tab.
Select a category from the Outline List.

Tip: Double-click the category to display/hide the list of outlines for that category.
Select an outline to review the outline's details in the SQL Text and the Outline Hints panes.

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

Select Manage Plans | Outlines Management.
Select an outline in the Outline List.
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

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.
Select the Baselines Management tab.

Note: You can only access the Baselines Management tab when connected to an Oracle 11g or later database.
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.
Select the checkbox in the Enabled column to enable the baseline.
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.
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.