creating stored outline from cached execution plan

Beginning from oracle 10gR2, The DBMS_OUTLN package contains a procedure called CREATE_OUTLINE that can be used to create a Stored Outline containing the execution plan of a Cursor in the Shared Pool using its Hash Value and Child Number.

PROCEDURE create_outline(hash_value IN NUMBER,
child_number IN NUMBER,
category IN VARCHAR2 DEFAULT 'DEFAULT');

In order to use the procedure, a user needs to be able to uniquely identify the Cursor for which
the outline is to be stored. In the following example, the text string: "OL_TEST" is used to do this
in order to store an outline of an illustrative query SELECT FROM V$SQL.

Example:

SQL> GRANT CREATE ANY OUTLINE TO SCOTT;

Grant succeeded.
SQL> GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
select hash_value, child_number, sql_text
from v$sql
where sql_text like '%OL_TEST%';
HASH_VALUE CHILD_NUMBER SQL_TEXT
--------- ---------- -------
1556463076 0 select hash_value, child_number, sql_text from v$sql where sql_text like '%OL_TEST%'

SQL> alter session set create_stored_outlines = true; -- This step is to avoid Bug:5454975 fixed 10.2.0.4
Session altered.

SQL> exec dbms_outln.create_outline(1556463076,0);
SQL> select name from dba_outlines;
NAME
------------------------------
SYS_OUTLINE_07072313512890701

MORE on such outlines: example-

SQL> exec dbms_outln.create_outline(1556463076,0,'MYCAT');


PL/SQL procedure successfully completed.We can find the name of the newly created Stored Outline by querying e.g. DBA_OUTLINES. This will be a system-generated name at first, we can change it to a more human-friendly format using:

SQL> alter outline SYS_OUTLINE_07072313512890701 rename to MYOUTLN;

Note: prior to Oracle10g you will have to use one of the alternative methods i.e. manually create a Stored Outline using the CREATE OUTLINE command or capture it using ALTER SESSION SET CREATE_STORED_OUTLINES = .

If the Stored Outline already exists, we can change its category name to MYCAT as follows:

SQL> alter outline MYOUTLN change category to MYCAT;