66 DBMS_OUTLN

The DBMS_OUTLN package, synonymous with OUTLN_PKG, contains the functional interface for subprograms associated with the management of stor ed outlines.

Using DBMS_OUTLN

Overview
Security Model

Overview

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.

Security Model

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).

Summary of DBMS_OUTLN Subprograms

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

CLEAR_USED Procedure

This procedure clears the outline 'used' flag.

Syntax

DBMS_OUTLN.CLEAR_USED (
   name    IN    VARCHAR2);

Parameters

Table 66-2 CLEAR_USED Procedure Parameters

Parameter	Description
`name`	Name of the outline.

CREATE_OUTLINE Procedure

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).

DROP_BY_CAT Procedure

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

Table 66-4 DROP_BY_CAT Procedure Parameters

Parameter	Description
`cat`	Category of outlines to drop.

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');

DROP_UNUSED Procedure

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.

EXACT_TEXT_SIGNATURES Procedure

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.

UPDATE_BY_CAT Procedure

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.

UPDATE_SIGNATURES Procedure

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.