10
Using Plan Stability

This chapter describes how to use plan stability to preserve performance characteristics.

This chapter contains the following sections:

Using Plan Stability to Preserve Execution Plans

Plan stability prevents certain database environment changes from affecting the performance characteristics of your 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 your applications.

Plan stability preserves execution plans in stored outlines. Oracle can create a 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.

The plans Oracle maintains in stored outlines remain consistent despite changes to your system's configuration or statistics. Using stored outlines also stabilizes the generated execution plan if the optimizer changes in subsequent Oracle releases. You can also group outlines into categories and control which category of outlines Oracle uses to simplify outline administration and deployment.

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

Note:
If you develop applications for mass distribution, then you can use stored outlines to ensure that all your customers access the same execution plans.

Hints and Exact Text Matching

The degree to which plan stability controls execution plans is dictated by how much Oracle's hint mechanism controls execution plans, because Oracle uses hints to record stored plans. Plan stability also relies on "exact text matching" of queries when determining whether a query has a stored outline.

There is a one-to-one correspondence between SQL text and its stored outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in your applications with bind variables. This gives your SQL statements the exact textual match for outline sharing.

Plan stability relies on preserving execution plans at a point in time when performance is satisfactory. In many environments, however, attributes for datatypes such as "dates" or "order numbers" can change rapidly. In these cases, permanent use of an execution plan may result in performance degradation over time as the data characteristics change.

This implies that techniques that rely on preserving plans in dynamic environments are somewhat contrary to the purpose of using cost-based optimization. Cost-based optimization attempts to produce execution plans based on statistics that accurately reflect the state of the data. Thus, you must balance the need to control plan stability with the benefit obtained from the optimizer's ability to adjust to changes in data characteristics.

How Outlines Use Hints

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 Oracle creates 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.

Note:
You cannot modify an outline. The OL$ and OL$HINTS tables are system tables in the sense that direct manipulation is prohibited.You can embed hints in SQL statements, but this has no effect on how Oracle uses outlines. Oracle considers a SQL statement that you revised with hints to be different from the original SQL statement stored in the outline.

Matching SQL Statements with Outlines

Oracle uses one of two scenarios when compiling SQL statements and matching them with outlines. The first scenario is that if you disable outline use by setting the system/session parameter USE_STORED_OUTLINES to FALSE, then Oracle does not attempt to match SQL text to outlines. The second scenario involves the following two matching steps.

First, if you specify that Oracle must use a particular outline category, then only outlines in that category are candidates for matching. Second, if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch.

Differences include spacing changes, carriage return variations, embedded hints, or even differences in comment text. These rules are identical to the rules for cursor matching.

Storing Outlines

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

The only effect outlines have on caching execution plans is that the outline's category name is used in addition to the SQL text to identify whether the plan is in cache. This ensures that Oracle does not use an execution plan compiled under one category to execute a SQL statement that Oracle should compile under a different category.

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

Creating Outlines

Oracle can automatically create outlines for all SQL statements, or you can create them for specific SQL statements. In either case, the outlines derive their input from the optimizer.

Oracle creates stored outlines automatically when you set the parameter CREATE_STORED_OUTLINES to TRUE. When activated, Oracle creates outlines for all compiled SQL statements.

Note:
You must ensure that schemas in which outlines are to be created have the CREATE ANY OUTLINE privilege. Otherwise, despite having turned on the CREATE_STORED_OUTLINE parameter, you will not find outlines in your database after you run your application.

You can create stored outlines for specific statements using the CREATE OUTLINE statement.

Using Category Names For Stored Outlines

Outlines can be categorized to simplify the management task. The CREATE OUTLINE statement allows for specification of a category, while the DEFAULT category is chosen if unspecified. Likewise, the CREATE_STORED_OUTLINES parameter lets you specify a category name, where specifying TRUE produces outlines in the DEFAULT category.

If you specify a category name using the CREATE_STORED_OUTLINES parameter, then Oracle assigns all subsequently created outlines to that category until you reset the category name. Set the parameter to FALSE to suspend outline generation.

If you set CREATE_STORED_OUTLINES to TRUE, or if you use the CREATE OUTLINE statement without a category name, then Oracle assigns outlines to the category name of DEFAULT.

Note:
The CREATE_STORED_OUTLINES and USE_STORED_OUTLINES parameters are system- or session-specific. They are not initialization parameters. For more information on these parameters, see the Oracle8i SQL Reference.

Using Stored Outlines

To use stored outlines when Oracle compiles a SQL statement, set the system parameter USE_STORED_OUTLINES to true or to a category name. If you set USE_STORED_OUTLINES to true, then Oracle uses outlines in the DEFAULT category. If you specify a category with the USE_STORED_OUTLINES parameter, then Oracle uses outlines in that category until you re-set the parameter to another category name or until you suspend outline use by setting USE_STORED_OUTLINES to FALSE. If you specify a category name and Oracle does not find an outline in that category that matches the SQL statement, then Oracle searches for an outline in the DEFAULT category.

The designated outlines only control the compilation of SQL statements that have outlines. If you set USE_STORED_OUTLINES to false, then Oracle does not use outlines. When you set USE_STORED_OUTLINES to false and you set CREATE_STORED_OUTLINES to true, Oracle creates outlines but does not use them.

When you activate the use of stored outlines, Oracle always uses the cost-based optimizer. This is because outlines rely on hints, and to be effective, most hints require the cost-based optimizer.

Test if an outline is being used with the V$SQL view. Query the OUTLINE_CATEGORY column in conjunction with the SQL statement. If an outline was applied, then this column contains the category to which the outline belongs. Otherwise, it is NULL. For example:

SELECT OUTLINE_CATEGORY 
FROM V$SQL 
WHERE SQL_TEXT LIKE 'SELECT count(*) FROM emp%';

Viewing Outline Data

You can access information about outlines and related hint data that Oracle stores in the data dictionary from the following views:

USER_OUTLINES
USER_OUTLINE_HINTS
ALL_OUTLINES
ALL_OUTLINE_HINTS
DBA_OUTLINES
DBA_OUTLINE_HINTS

Use the following syntax to obtain outline information from the USER_OUTLINES view, where the outline category is mycat:

SELECT NAME, SQL_TEXT 
FROM USER_OUTLINES 
WHERE CATEGORY='mycat';

Oracle responds by displaying the names and text of all outlines in category mycat.

To see all generated hints for the outline name1, use the following syntax:

SELECT HINT 
FROM USER_OUTLINE_HINTS 
WHERE NAME='name1';

See Also:
If necessary, you can use the procedure to move outline tables from one tablespace to another as described in "Moving Outline Tables".

Using the OUTLN_PKG Package to Manage Stored Outlines

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

See Also:
For detailed information on using OUTLN_PKG procedures, see Oracle8i Supplied PL/SQL Packages Reference.

Moving Outline Tables

Oracle creates the USER_OUTLINES and USER_OUTLINE_HINTS views based on data in the OL$ and OL$HINTS tables respectively. Oracle creates these tables in the SYS tablespace using a schema called OUTLN. If the outlines use too much space in the SYS tablespace, then you can move them. To do this, create a separate tablespace and move the outline tables into it using the following process.

Export the OL$ and OL$HINTS tables:

EXP OUTLN/OUTLN FILE = exp_file TABLES = 'OL$' 'OL$HINTS' SILENT=y

Remove the previous OL$ and OL$HINTS tables:

CONNECT OUTLN/outln_password;
DROP TABLE OL$; 
CONNECT OUTLN/outln_password;
DROP TABLE OL$HINTS;

Create a new tablespace for the tables:

CREATE TABLESPACE outln_ts 
DATAFILE 'tspace.dat' SIZE 2MB 
DEFAULT STORAGE (INITIAL 10KB NEXT 20KB 
MINEXTENTS 1 MAXEXTENTS 999 PCTINCREASE 10) ONLINE;

Enter the following statement:

ALTER USER OUTLN DEFALUT TABLESPACE outln_ts;

Import the OL$ and OL$HINTS tables:
```
IMPORT OUTLN/outln_password 
FILE=exp_file TABLES = 'OL$' 'OL$HINTS' IGNORE=y SILENT=y
```
The IMPORT statement re-creates the OL$ and OL$HINTS tables in the schema named OUTLN, but the schema now resides in a new tablespace called OUTLN_TS.

Plan Stability Procedures for the Cost-Based Optimizer

This section describes procedures you can use to significantly improve performance by taking advantage of cost-based optimizer functionality. Plan stability provides a way to preserve your system's targeted execution plans with satisfactory performance while also taking advantage of new cost-based optimizer features for the rest of your SQL statements.

Topics covered in this section are:

Using Outlines to Move to the Cost-Based Optimizer

If your application was developed using the rule-based optimizer, then a considerable amount of effort may 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 cost-based optimization.

By creating outlines for an application before switching to cost-based optimization, the plans generated by the rule-based optimizer can be used, while statements generated by newly written applications developed after the switch use cost-based plans. To create and use outlines for an application, use the following process.

Note:
Carefully read this procedure and consider its implications before executing it!

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>
```
Execute syntax similar to the following to designate, for example, the RBOCAT outline category.
```
ALTER SESSION SET CREATE_STORED_OUTLINES = rbocat;
```
Run your application long enough to capture stored outlines for all important SQL statements.

Suspend outline generation:

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

Gather statistics with the DBMS_STATS package.
Alter the parameter OPTIMIZER_MODE to CHOOSE.
Enter this syntax to make Oracle use the outlines in category RBOCAT:
```
ALTER SESSION SET USE_STORED_OUTLINES = rbocat;
```

Run the application.

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

Note:
If a query was not executed in step 2, then you can capture the old behavior of the query even after switching to cost-based optimization. To do this, change the optimizer mode to RULE, create an outline for the query, and then change the optimizer mode back to CHOOSE.

RDBMS Upgrades and the Cost-Based Optimizer

When upgrading to a new Oracle release under cost-based 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 in the vast majority of cases, you might have some applications that perform well and where 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 executing it!

Enter the following syntax to enable outline creation:

ALTER SESSION SET CREATE_STORED_OUTLINES = ALL_QUERIES;

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

Enter this syntax to suspend outline generation:

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

Upgrade the production system to the new version of the RDBMS.
Run the application.

After the upgrade, you can enable the use of stored outlines, or alternatively, you can use the outlines that were stored as a backup if you find that some statements exhibit performance degradation after the upgrade.

With the latter approach, you can selectively use the stored outlines for such problematic statements as follows:

For each problematic SQL statement, change the CATEGORY of the associated stored outline to a category name similar to this:
```
ALTER OUTLINE outline_name CHANGE CATEGORY TO problemcat;
```
Enter this syntax to make Oracle use outlines from the category "problemcat".
```
ALTER SESSION SET USE_STORED_OUTLINES = problemcat;
```

Upgrading with a Test System

A test system, separate from the production system, can be useful for conducting experiments with optimizer behavior in conjunction with an upgrade. You can migrate statistics from the production system to the test system using import/export. This may alleviate the need to fill the tables in the test system with data.

You can move outlines between the systems by category. For example, after you create outlines in the problemcat category, export them by category using the query-based export option. This is a convenient and efficient way to export only selected outlines from one database to another without exporting all outlines in the source database. To do this, issue these statements:

EXP OUTLN/outln_password FILE=<exp-file> TABLES= 'OL$' 'OL$HINTS' 
QUERY='WHERE CATEGORY="problemcat"'

10 Using Plan Stability