59
DBMS_OUTLN

The DBMS_OUTLN package, synonymous withOUTLN_PKG, contains the functional interface for subprograms associated with the management of stored outlines.

This chapter contains the following topics:

• Using DBMS_OUTLN
  • Overview
  • Security Model
• Summary of DBMS_OUTLN Subprograms

---

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