Stored outlines are deprecated. They are still supported for backward compatibility. However, Oracle recommends that you use SQL plan management instead. SQL plan management creates SQL plan baselines, which offer superior SQL performance stability compared with stored outlines.
You can migrate existing stored outlines to SQL plan baselines by using the
MIGRATE_STORED_OUTLINE function of the
DBMS_SPM package or Enterprise Manager Cloud Control. When the migration is complete, the stored outlines are marked as migrated and can be removed. You can drop all migrated stored outlines on your system by using the
DROP_MIGRATED_STORED_OUTLINE function of the
See Also: Oracle Database SQL Tuning Guide for more information about SQL plan management and Oracle Database PL/SQL Packages and Types Reference for information about the
OUTLINE statement to create a stored outline, which is a set of attributes used by the optimizer to generate an execution plan. You can then instruct the optimizer to use a set of outlines to influence the generation of execution plans whenever a particular SQL statement is issued, regardless of changes in factors that can affect optimization. You can also modify an outline so that it takes into account changes in these factors.
The SQL statement you want to affect must be an exact string match of the statement specified when creating the outline.
To create a public or private outline, you must have the
OUTLINE system privilege.
If you are creating a clone outline from a source outline, then you must also have the
USE_STORED_OUTLINESparameter to use public outlines.
USE_PRIVATE_OUTLINESparameter to use private stored outlines.
None of the clauses after
outline are required. However, you must specify at least one clause after
outline, and it must be either the
FROM clause or the
PUBLIC | PRIVATE
PRIVATE to create an outline for private use by the current session only. The data of this outline is stored in the current schema.
Specify the unique name to be assigned to the stored outline. The name must satisfy the requirements listed in "Database Object Naming Rules". If you do not specify
outline, then the database generates an outline name.
FROM source_outline Clause
FROM clause to create a new outline by copying an existing one. By default, Oracle Database looks for
source_category in the public area. If you specify
PRIVATE, then the database looks for the outline in the current schema.
Restriction on Copying an Outline
If you specify the
FROM clause, then you cannot specify the
FOR CATEGORY Clause
Specify an optional name used to group stored outlines. For example, you could specify a category of outlines for end-of-week use and another for end-of-quarter use. If you do not specify
category, then the outline is stored in the
Specify the SQL statement for which the database will create an outline when the statement is compiled. This clause is optional only if you are creating a copy of an existing outline using the
You can specify any one of the following statements:
Restrictions on the ON Clause
This clause is subject to the following restrictions:
If you specify the
ONclause, then you cannot specify the
You cannot create an outline on a multitable
The SQL statement in the
ONclause cannot include any DML operation on a remote object.
In subsequent statements, you can specify additional outlines for the same SQL statement, but each outline for the same statement must specify a different category in the
Creating an Outline: Example
The following statement creates a stored outline by compiling the
ON statement. The outline is called
salaries and is stored in the category
CREATE OUTLINE salaries FOR CATEGORY special ON SELECT last_name, salary FROM employees;
When this same
SELECT statement is subsequently compiled, if the
USE_STORED_OUTLINES parameter is set to
special, the database generates the same execution plan as was generated when the outline
salaries was created.
Creating a Private Clone Outline: Example
The following statement creates a stored private outline
my_salaries based on the public category
salaries created in the preceding example.
CREATE OR REPLACE PRIVATE OUTLINE my_salaries FROM salaries;
Publicizing a Private Outline to the Public Area: Example
The following statement copies back (publicizes) a private outline to the public area after private editing:
CREATE OR REPLACE OUTLINE public_salaries FROM PRIVATE my_salaries;