20 Using Plan Stability

20.1.1 Using Hints with Plan Stability

The degree to which plan stability controls execution plans is dictated by how much the Oracle hint mechanism controls execution plans, because Oracle uses hints to record stored plans.

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 situation, replace literals in applications with bind variables.

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 can 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 query optimization. Query 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.

20.1.2 Storing Outlines

Oracle stores outline data in the OL$, OL$HINTS, and OL$NODES tables. 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.

20.1.3 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

20.1.4 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.

Users need the EXECUTE_CATALOG_ROLE role to execute DBMS_OUTLN, but public has execute privileges on DBMS_OUTLN_EDIT. The DBMS_OUTLN_EDIT package is an invoker's rights package.

Some of the useful DBMS_OUTLN and DBMS_OUTLN_EDIT procedures are:

• CLEAR_USED - Clears specified outline

• DROP_BY_CAT - Drops outlines that belong to a specified category

• UPDATE_BY_CAT - Changes the category of outlines in one specified category to a new specified category

• EXACT_TEXT_SIGNATURES - Computes an outline signature according to an exact text matching scheme

• GENERATE_SIGNATURE - Generates a signature for the specified SQL text

  See Also:

  Oracle Database PL/SQL Packages and Types Reference for detailed information on using DBMS_OUTLN package procedures

20.1.5 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 initialization parameter CREATE_STORED_OUTLINES to true. When activated, Oracle creates outlines for all compiled SQL statements. You can create stored outlines for specific statements using the CREATE OUTLINE statement.

When creating or editing a private outline, the outline data is written to global temporary tables in the SYSTEM schema. These tables are accessible with the OL$, OL$HINTS, and OL$NODES synonyms.

20.1.6 Using Stored Outlines

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

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 reset 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.

If you want to use a specific outline rather than all the outlines in a category, use the ALTER OUTLINE statement to enable the specific outline. If you want to use the outlines in a category except for a specific outline, use the ALTER OUTLINE statement to disable the specific outline in the category that is being used. The ALTER OUTLINE statement can also rename a stored outline, reassign it to a different category, or regenerate it.

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.

The USE_PRIVATE_OUTLINES parameter lets you control the use of private outlines. A private outline is an outline seen only in the current session and whose data resides in the current parsing schema. Any changes made to such an outline are not seen by any other session on the system, and applying a private outline to the compilation of a statement can only be done in the current session with the USE_PRIVATE_OUTLINES parameter. Only when you explicitly choose to save your edits back to the public area are they seen by the rest of the users.

While the optimizer usually chooses optimal plans for queries, there are times when users know things about the execution environment that are inconsistent with the heuristics that the optimizer follows. By editing outlines directly, you can tune the SQL query without having to alter the application.

When the USE_PRIVATE_OUTLINES parameter is enabled and an outlined SQL statement is issued, the optimizer retrieves the outline from the session private area rather than the public area used when USE_STORED_OUTLINES is enabled. If no outline exists in the session private area, then the optimizer will not use an outline to compile the statement.

Any CREATE OUTLINE statement requires the CREATE ANY OUTLINE privilege. Specification of the FROM clause also requires the SELECT privilege. This privilege should be granted only to those users who would have the authority to view SQL text and hint text associated with the outlined statements. This role is required for the CREATE OUTLINE FROM command unless the issuer of the command is also the owner of the outline.

You can 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. The OUTLINE_SID column tells you if this particular cursor is using a public outline (value is 0) or a private outline (session's SID of the corresponding session using it).

For example:

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

20.1.7 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';

You can check the flags in _OUTLINES views for information on compatibility, format, and whether an outline is enabled. For example, check the ENABLED field in the USER_OUTLINES view to determine whether an outline is enabled or not.

SELECT NAME, CATEGORY, ENABLED FROM USER_OUTLINES;

20.1.8 Moving Outline Tables

Oracle Database creates the USER_OUTLINES and USER_OUTLINE_HINTS views based on data in the OL$ and OL$HINTS tables, respectively. These tables and the OL$NODES table reside in the outln schema.

The outln schema stores data in the SYSTEM tablespace. If outlines use too much space in the SYSTEM tablespace, then you can move them. To achieve this goal, create a separate tablespace and move the outline tables into this tablespace.

To move outline tables into a new tablespace: 

1. Use the Oracle Data Pump Export utility to export the OL$, OL$HINTS, and OL$NODES tables.

   The following example exports these tables to the exp.dmp file located in the directory that maps to the outln_dir object:

   % expdp outln DIRECTORY=outln_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES
   Password: password
   

2. Start SQL*Plus and connect to the database as the outln user, as shown in the following example:

   SQL> CONNECT outln
   Enter password: password
   

3. Remove the previous OL$, OL$HINTS, and OL$NODES tables, as shown in the following example:

   SQL> DROP TABLE OL$;
   SQL> DROP TABLE OL$HINTS; 
   SQL> DROP TABLE OL$NODES; 
   

4. Create a new tablespace for the tables.

   The following example connects as SYSTEM and creates a tablespace named outln_ts:

   SQL> CONNECT SYSTEM
   Enter password: password
   
   SQL> CREATE TABLESPACE outln_ts DATAFILE 'tspace.dat' SIZE 2M
     2 DEFAULT STORAGE ( INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
     3                   PCTINCREASE 10 ) ONLINE; 
   

5. Change the default tablespace for the outln schema.

   The following statement changes the default tablespace to outln_ts:

   SQL> ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;
   

6. To force the import into the outln_ts tablespace, perform the following tasks:

   1. Set the quota for the SYSTEM tablespace to 0K for the outln user.

   2. Revoke the UNLIMITED TABLESPACE privilege and all roles, such as the RESOURCE role, that have unlimited tablespace privileges or quotas.

   3. Set a quota for the outln tablespace.

7. Use the Data Pump Import utility to import the OL$, OL$HINTS, and OL$NODES tables, as in the following example:

   % impdp outln DIRECTORY=outln_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES
   Password: password
   

   When the import completes, the OL$, OL$HINTS, and OL$NODES tables are re-created in the schema named outln and reside in the outln_ts tablespace.

8. Optionally, adjust the tablespace quotas for the outln user appropriately by adding any privileges and roles that were removed in a previous step.

20.2.1 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 
   

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

   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:

   ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
   

5. Gather statistics with the DBMS_STATS package.

6. Alter the parameter OPTIMIZER_MODE to CHOOSE.

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

   ALTER SESSION SET USE_STORED_OUTLINES = rbocat;
   

8. 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 query optimization. To achieve this goal, change the optimizer mode to RULE, create an outline for the query, and then change the optimizer mode back to CHOOSE.

20.2.2 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.

1. Enter the following syntax to enable outline creation:

   ALTER SESSION SET CREATE_STORED_OUTLINES = ALL_QUERIES;
   

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

3. Enter this syntax to suspend outline generation:

   ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
   

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

5. 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:

1. 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;
   

2. Enter this syntax to make Oracle use outlines from the category problemcat.

   ALTER SESSION SET USE_STORED_OUTLINES = problemcat;
   

% expdp outln DIRECTORY=outln_dir DUMPFILE=exp_file.dmp \
?  TABLES=OL$,OL$HINTS,OL$NODES QUERY='WHERE CATEGORY="problemcat"'
Password: password