|Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)
Part Number A96533-02
This chapter describes how to use plan stability to preserve performance characteristics.
This chapter contains the following sections:
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
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. 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. Plan stability also facilitates migration from the rule-based optimizer to the cost-based optimizer when you upgrade to a new Oracle release.
If you develop applications for mass distribution, then you can use stored outlines to ensure that all customers access the same execution plans.
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.
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 applications with bind variables.
Oracle can force similar statements to share SQL by replacing literals with system-generated bind variables. This works with plan stability if the outline was generated using the
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
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 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.
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.
Oracle creates the
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.
Oracle stores outline data in the
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.
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:
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 package is an invoker's rights package.
Oracle9i Supplied PL/SQL Packages and Types Reference for detailed information on using
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
true. When activated, Oracle creates outlines for all compiled SQL statements. You can create stored outlines for specific statements using the
You must ensure that schemas in which outlines are to be created have the
Also, the default system tablespace can become exhausted if the
CREATE_EDIT_TABLES procedure in the
DBMS_OUTLN_EDIT package creates tables in the invoker's schema. This is necessary for editing private outlines. This is callable by anyone with
EXECUTE privilege on
Outlines can be categorized to simplify the management task. The
OUTLINE statement allows for specification of a category. 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
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
true, or if you use the
OUTLINE statement without a category name, then Oracle assigns outlines to the category name of
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.
To use stored outlines when Oracle compiles a SQL statement, set the system parameter
true or to a category name. If you set
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
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
The designated outlines only control the compilation of SQL statements that have outlines. If you set
false, then Oracle does not use outlines. When you set
false and you set
true, Oracle creates outlines but does not use them.
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 a private outline is created, an error is returned if the prerequisite outline tables to hold the outline data do not exist in the local schema. These tables can be created using the
CREATE_EDIT_TABLES procedure. You can also use 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.
OUTLINE statement requires the
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
FROM command unless the issuer of the command is also the owner of the outline.
When you begin an editing session,
USE_PRIVATE_OUTLINES should be set to the category to which the outline being edited belongs. When you are finished editing, this parameter should be set to
false to restore the session to normal outline lookup according to the
You can use the GUI Outline Editor of the Oracle Enterprise Manager Tuning Pack to update outlines. Figure 7-1 is an illustration of the Outline Editor.
Database Tuning with the Oracle Tuning Pack for more information on the GUI tool for editing outlines
Assume that you want to edit the outline
ol1. The steps are as follows:
SELECTprivileges have been granted.
OL$HINTStables and performing DML against the appropriate hint tuples.
CHANGE_JOIN_POSis available for changing join order.
You can also use
SHARED_POOL to accomplish this.
USE_PRIVATE_OUTLINES=TRUE, and issue the outline statement or run
PLANon the statement.
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
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).
You can access information about outlines and related hint data that Oracle stores in the data dictionary from the following views:
Use the following syntax to obtain outline information from the
USER_OUTLINES view, where the outline category is
Oracle responds by displaying the names and text of all outlines in category
To see all generated hints for the outline
name1, use the following syntax:
If necessary, you can use the procedure to move outline tables from one tablespace to another as described in "Moving Outline Tables".
Oracle creates the
USER_OUTLINE_HINTS views based on data in the
OL$HINTS tables, respectively. Oracle creates these tables, and also the
OL$NODES table, in the
SYS tablespace using a schema called
OUTLN. If 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.
CREATE_STORED_OUTLINESparameter is on and if the running application has many literal SQL statements. If this happens, then use the
DROP_UNUSEDprocedure to remove those literal SQL outlines.
Oracle9i Supplied PL/SQL Packages and Types Reference for detailed information on using the
CONNECT OUTLN/outln_password; DROP TABLE OL$; CONNECT OUTLN/outln_password; DROP TABLE OL$HINTS; CONNECT OUTLN/outln_password; DROP TABLE OL$NODES;
IMPORT statement re-creates the
OL$NODES tables in the schema named
OUTLN, but the schema now resides in a new tablespace called
If Oracle8i outlines are imported into an Oracle9i database, then the
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 a system's targeted execution plans with satisfactory performance while also taking advantage of new cost-based optimizer features for the rest of the SQL statements.
Topics covered in this section are:
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 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.
OUTLINEprivilege. For example, from
Subject to the limitations of plan stability, access paths for this application's SQL statements should be unchanged.
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, 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.
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:
CATEGORYof the associated stored outline to a category name similar to this:
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 can 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: