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 Performance Tuning Guide.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.
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 66-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 clears the outline 'used' flag.
DBMS_OUTLN.CLEAR_USED ( name IN VARCHAR2);
This procedure generates an outline 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');
Table 66-3 CREATE_OUTLINE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Hash value identifying the target shared cursor. |
|
|
Child number of the target shared cursor. |
|
|
Category in which to create outline (optional). |
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.
DBMS_OUTLN.DROP_BY_CAT ( cat VARCHAR2);
This procedure purges a category of outlines in a single call.
This example drops all outlines in the DEFAULT category:
DBMS_OUTLN.DROP_BY_CAT('DEFAULT');
This procedure drops outlines that have never been applied in the compilation of a SQL statement.
DBMS_OUTLN.DROP_UNUSED;
You can use DROP_UNUSED for outlines generated by an application for one-time use SQL statements created as a result of dynamic SQL. These outlines are never used and take up valuable disk space.
This procedure updates outline signatures to those that compute based on exact text matching.
DBMS.OUTLN.EXACT_TEXT_SIGNATURES;
This procedure is relevant only for downgrading an outline to 8.1.6 or earlier.
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');
Table 66-5 UPDATE_BY_CAT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The current category of outlines. |
|
|
The new category of outlines. |
This procedure updates outline signatures to the current version's signature.
DBMS.OUTLN.UPDATE_SIGNATURES;
You should execute this procedure if you have imported outlines generated in an earlier release to ensure that the signatures are compatible with the current release's computation algorithm.