Using Plan Stability

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#g35579

 plan stability used to preserve performance characteristics.

Plan stability also facilitates migration from the rule-based optimizer to the query optimizer when you upgrade to a new Oracle release.

Using Plan Stability to Preserve Execution Plans

Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in 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.
Plan stability preserves execution plans in stored outlines. An outline is implemented as a set of optimizer hints that are associated with the SQL statement. If the use of the outline is enabled for the statement, Oracle automatically considers the stored hints and tries to generate an execution plan in accordance with those hints.
Oracle can create a public or private stored outline for one or all SQL statements. The optimizer then generates equivalent execution plans from the outlines when you enable the use of stored outlines. You can group outlines into categories and control which category of outlines Oracle uses to simplify outline administration and deployment.
The plans Oracle maintains in stored outlines remain consistent despite changes to a system's configuration or statistics. Using stored outlines also stabilizes the generated execution plan if the optimizer changes in subsequent Oracle releases.

Storing Outlines

Oracle stores outline data in the OL$, OL$HINTS, and OL$NODES tables. Unless you remove them, Oracle retains outlines indefinitely.

Enabling Plan Stability

Settings for several parameters, especially those ending with the suffix _ENABLED, must be consistent across execution environments for outlines to function properly. These parameters are:

·         QUERY_REWRITE_ENABLED

·         STAR_TRANSFORMATION_ENABLED

·         OPTIMIZER_FEATURES_ENABLE

Using Supplied Packages to Manage Stored Outlines

The DBMS_OUTLN and DBMS_OUTLN_EDIT package provides procedures used for managing stored outlines and their outline categories.

Using Plan Stability with Query Optimizer Upgrades

This section describes procedures you can use to significantly improve performance by taking advantage of query optimizer functionality. Plan stability provides a way to preserve a system's targeted execution plans with satisfactory performance while also taking advantage of new query optimizer features for the rest of the SQL statements.
While there are classes of SQL statements and features where an exact reproduction of the original execution plan is not guaranteed, plan stability can still be a highly useful part of the migration process. Before the migration, outline capturing of execution plan should be turned on until all or most of the applications SQL-statement have been covered. If, after the migration, there are performance problems for some specific SQL-statement, the use of the stored outline for that statement can be turned on as a way of restoring the old behavior.

Moving from RBO to the Query Optimizer

If an application was developed using the rule-based optimizer, then a considerable amount of effort might have gone into manually tuning the SQL statements to optimize performance. You can use plan stability to leverage the effort that has already gone into performance tuning by preserving the behavior of the application when upgrading from rule-based to query optimization.
By creating outlines for an application before switching to query optimization, the plans generated by the rule-based optimizer can be used, while statements generated by newly written applications developed after the switch use query plans.
To create and use outlines for an application, use the following process.

1.      Ensure that schemas in which outlines are to be created have the CREATE ANY OUTLINE privilege.

For example, from SYS:

GRANT CREATE ANY OUTLINE TO user-name 

1.      Execute syntax similar to the following to designate; for example, the RBOCAT outline category.

2.            ALTER SESSION SET CREATE_STORED_OUTLINES = rbocat;

3.      Run the application long enough to capture stored outlines for all important SQL statements.

4.      Suspend outline generation:

5.            ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

6.      Gather statistics with the DBMS_STATS package.

7.      Alter the parameter OPTIMIZER_MODE to CHOOSE.

8.      Enter the following syntax to make Oracle use the outlines in category RBOCAT:

9.            ALTER SESSION SET USE_STORED_OUTLINES = rbocat;

10.  Run the application.

Subject to the limitations of plan stability, access paths for this application's SQL statements should be unchanged.

Moving to a New Oracle Release under the Query Optimizer

When upgrading to a new Oracle release under query optimization, there is always a possibility that some SQL statements will have their execution plans changed due to changes in the optimizer. While such changes benefit performance, you might have applications that perform so well that you would consider any changes in their behavior to be an unnecessary risk. For such applications, you can create outlines before the upgrade using the following procedure.

Note:

Carefully read this procedure and consider its implications before running it!

1.      Enter the following syntax to enable outline creation:

2.            ALTER SESSION SET CREATE_STORED_OUTLINES = ALL_QUERIES;

3.      Run the application long enough to capture stored outlines for all critical SQL statements.

4.      Enter this syntax to suspend outline generation:

5.            ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

6.      Upgrade the production system to the new version of the RDBMS.

7.      Run the application.