Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
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 |
---|---|
CLEAR_USED Procedure |
Clears the outline 'used' flag |
CREATE_OUTLINE Procedure |
Generates outlines from the shared cursor identified by hash value and child number |
DROP_BY_CAT Procedure |
Drops outlines that belong to a specified category |
DROP_UNUSED Procedure |
Drops outlines that have never been applied in the compilation of a SQL statement |
EXACT_TEXT_SIGNATURES Procedure |
Updates outline signatures to those that compute based on exact text matching |
UPDATE_BY_CAT Procedure |
Changes the category of outlines in one category to a new category |
UPDATE_SIGNATURES Procedure |
Updates outline signatures to the current version's signature |
This procedure clears the outline 'used' flag.
Syntax
DBMS_OUTLN.CLEAR_USED ( name IN VARCHAR2);
Parameters
This procedure generates an outline from the shared cursor identified by hash value and child number.
Syntax
DBMS_OUTLN.CREATE_OUTLINE ( hash_value IN NUMBER, child_number IN NUMBER, category IN VARCHAR2 DEFAULT 'DEFAULT');
Parameters
Table 66-3 CREATE_OUTLINE Procedure Parameters
Parameter | Description |
---|---|
hash_value |
Hash value identifying the target shared cursor. |
child_number |
Child number of the target shared cursor. |
category |
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.
Syntax
DBMS_OUTLN.DROP_BY_CAT ( cat VARCHAR2);
Parameters
Usage Notes
This procedure purges a category of outlines in a single call.
Examples
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.
Syntax
DBMS_OUTLN.DROP_UNUSED;
Usage Notes
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.
Syntax
DBMS.OUTLN.EXACT_TEXT_SIGNATURES;
Usage Notes
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.
Syntax
DBMS.OUTLN.UPDATE_BY_CAT ( oldcat VARCHAR2 default 'DEFAULT', newcat VARCHAR2 default 'DEFAULT');
Parameters
Table 66-5 UPDATE_BY_CAT Procedure Parameters
Parameter | Description |
---|---|
oldcat |
The current category of outlines. |
newcat |
The new category of outlines. |
This procedure updates outline signatures to the current version's signature.
Syntax
DBMS.OUTLN.UPDATE_SIGNATURES;
Usage Notes
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.