|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
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
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.
For more information on plan stability, see the Oracle9i Database Performance Guide and Reference.
There are two ways to launch 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:
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".