55
OUTLN_PKG

The OUTLN_PKG package contains the functional interface for subprograms associated with the management of stored outlines.

A stored outline is the stored data that pertains to an execution plan for a given SQL statement. It enables the optimizer to repeatedly recreate 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.

Requirements

OUTLN_PKG contains management procedures that should only be made available to appropriate users. Execute privilege is not extended to the general user community unless the DBA explicitly does so.

Security

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 Subprograms

Table 55-1 OUTLN_PKG Package Subprograms

DROP_UNUSED procedure

DROP_BY_CAT procedure

UPDATE_BY_CAT procedure

DROP_UNUSED procedure

This procedure drops outlines that have never been applied in the compilation of a SQL statement.

Syntax

OUTLN_PKG.DROP_UNUSED;

Parameters

None.

Usage Notes

You may want to use DROP_UNUSED for outlines created on behalf of dynamic SQL statements, that were generated by an application for one time use only. For these statements, the outlines are never used and are simply taking up valuable disk space.

DROP_BY_CAT procedure

This procedure drops outlines that belong to a particular category.

Syntax

OUTLN_PKG.DROP_BY_CAT (
   cat VARCHAR2);

Parameters

Table 55-2 DROP_BY_CAT Procedure Parameters

cat

Usage Notes

You may occasionally want to purge a category of outlines. This procedure accomplishes that in a single call.

Example

This example drops all outlines in the DEFAULT category:

OUTLN_PKG.DROP_BY_CAT('DEFAULT'); 

UPDATE_BY_CAT procedure

This procedure changes the category of all outlines in one category to a new category. If the SQL text in an outline already has an outline in the target category, then it is not merged into the new category.

Syntax

OUTLN_PKG.UPDATE_BY_CAT ( 
   oldcat VARCHAR2 DEFAULT 'DEFAULT', 
   newcat VARCHAR2 DEFAULT 'DEFAULT');

Parameters

Table 55-3 UPDATE_BY_CAT Procedure Parameters

oldcat

newcat

Usage Notes

Once satisfied with a set of outlines, you might chose to move outlines from an experimental category to a production category. Likewise, you might want to merge a set of outlines from one category into another pre-existing category.

Example

This example changes all outlines in the DEFAULT category to the CAT1 category:

OUTLN_PKG.UPDATE_BY_CAT('DEFAULT', 'CAT1');