The DBMS_OUTLN package, synonymous with OUTLN_PKG, contains the functional interface for subprograms associated with the management of stored outlines.
See Also:
For more information about using theDBMS_OUTLN package, see "Using Plan Stability" in Oracle Database SQL Tuning Guide.Note:
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.If you have existing stored outlines, please consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE Functions or LOAD_PLANS_FROM_SQLSET Function of theDBMS_SPM package. When the migration is complete, you should disable or remove the stored outlines.This chapter contains the following topics:
Overview
Security Model
A stored outline is the stored data that pertains to an execution plan for a given SQL statement. It enables the optimizer to repeatedly re-create execution plans that are equivalent to the plan originally generated along with the outline.The data stored in an outline consists, in part, of a set of hints that are used to achieve plan stability.
Stored outlines will be de-supported in a future release in favor of SQL plan management. As of 11g R1, stored outlines continue to function as in past releases, but Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.If you have existing stored outlines, please consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE Functions or the LOAD_PLANS_FROM_SQLSET Function of the DBMS_SPM package. When the migration is complete, you should disable or remove the stored outlines.
DBMS_OUTLN contains management procedures that should be available to appropriate users only. EXECUTE privilege is not extended to the general user community unless the DBA explicitly does so.
PL/SQL functions that are available for outline management purposes can be executed only by users with EXECUTE privilege on the procedure (or package).
Table 105-1 DBMS_OUTLN Package Subprograms
| Subprogram | Description |
|---|---|
|
Clears the outline 'used' flag |
|
|
Generates outlines from the shared cursor identified by hash value and child number |
|
|
Drops outlines that belong to a specified category |
|
|
Drops outlines that have never been applied in the compilation of a SQL statement |
|
|
Updates outline signatures to those that compute based on exact text matching |
|
|
Changes the category of outlines in one category to a new category |
|
|
Updates outline signatures to the current version's signature |
This procedure generates an outline by reparsing the SQL statement from the shared cursor identified by hash value and child number.
DBMS_OUTLN.CREATE_OUTLINE ( hash_value IN NUMBER, child_number IN NUMBER, category IN VARCHAR2 DEFAULT 'DEFAULT');
This procedure drops outlines that belong to a particular category. While outlines are put into the DEFAULT category unless otherwise specified, users have the option of grouping their outlines into groups called categories.
This procedure drops outlines that have never been applied in the compilation of a SQL statement.
This procedure updates outline signatures to those that compute based on exact text matching.
This procedure changes the category of all outlines in one category to a new category.
DBMS_OUTLN.UPDATE_BY_CAT ( oldcat VARCHAR2 default 'DEFAULT', newcat VARCHAR2 default 'DEFAULT');