25
Introduction to Outline Management

The Outline Management tool allows you to manage stored outlines directly using a GUI-based tool. Specifically you can browse, sort, delete, and edit outlines with this tool. Outlines are preserved by Oracle's plan stability functionality, so before using Outline Management or Outline Editor to create or edit an outline, it is important to understand how plan stability works. Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes to the optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. Plan stability is most useful when you cannot risk any performance changes in an application.

An outline consists primarily of a set of hints that is equivalent to the optimizer's results for the execution plan generation of a particular SQL statement. When you create an outline, plan stability examines the optimization results using the same data used to generate the execution plan. That is, Oracle uses the input to the execution plan to generate an outline, and not the execution plan itself.

As mentioned above, an outline is a means for modifying optimizer behavior. When the optimizer encounters the SQL text of the outline, it applies the supplied hints. The plans Oracle maintains in stored outlines remain consistent despite changes to a system's configuration or statistics. Using outlines also stabilizes the generated execution plan if the optimizer changes in subsequent Oracle releases. Plan stability facilitates migration from the rule-based optimizer to the cost-based optimizer when you upgrade to a new Oracle release.

Accessing the Outline Management Tool

There are two ways to launch the Outline Management Tool:

• From the Oracle Enterprise Manager console:
  1. Select the database that contains stored outlines.
  2. Choose Tuning Pack\Outline Management from the Tools Menu.
  3. The Outline Management tool will appear with the names and SQL text of the stored outlines.
• From the Outline Editor Tool:
  1. Choose Manage Outlines... from the Outline menu.
  2. When the Outline Management tool appears, you will see all of the existing outlines for the database.

Figure 25-1 Outline Management main window

Text description of the illustration om_main.gif

Using the Outline Management Tool

The Outline Management tool provides an easy-to-use graphical interface for managing your stored outlines. The tool can tell you how many outlines exist in the database and whether or not the outlines have been used since the last database upgrade.

Specifically, the tool allows you to:

• Choose an outline to edit
• Produce a report showing all outline information for the current user
• Delete a selected outline
• Reset the 'Used' flag in order to verify whether the optimizer has used the outline
• Change the category of selected outline(s)

The Outline Management Tool can also be used to launch the Outline Editor to create a new outline or edit an existing outline. When you want to create a new outline, Outline Management gives you two options: Create and Create Like. The Create feature launches the Outline Editor which allows you to specify the name, category, and SQL text for your new outline. Use the Create Like feature to make a new outline similar to an existing one.

When you edit an existing outline, the Outline Editor lets you to see the SQL text and modify the graphical explain plan for the outline. For more information on using the Outline Editor, see Chapter 26, "Introduction to the Outline Editor".