Improving the Oracle Optimizer - Oracle Database 11gR2 Performance Tuning - page 310

We have also seen that we can create stored outlines for a single statement. We can also
collect outlines for all the statements executed, using the following statement:

ALTER SYSTEM SET CREATE_STORED_OUTLINES = our_category_name;

Hereafter, we collect the outlines for all the executed statements in our_category_name .
The outlines will have system-generated names, of course, and we can stop collecting
outlines with the following statement:

ALTER SYSTEM SET CREATE_STORED_OUTLINES = FALSE;

We can use the ALTER OUTLINE statement to rename stored outlines, to change their
category, or to change the corresponding SQL statement and execution plan. The OUTLN_PKG
package helps us in massive operations, such as:

F Drop outlines of a specified category, using the DROP_BY_CAT procedure
F Drop outlines that will never be used, using the DROP_UNUSED procedure
F Changing the category of outlines to a new category, using the
UPDATE_BY_CAT procedure

If we are using the FOO category in the session, and if
there is no matching outline for our statement, but there
is one in the DEFAULT category, the outline from the
DEFAULT category will be used.

Private and public stored outlines
We can have private and public stored outlines; in this recipe, we have seen how to create
public stored outlines and how to use them.

When we have a public stored outline in place, we may want to test another stored outline for
the same query, without affecting the performance of other users who are using the same
statement.

In this situation, we can create a private stored outline, test it, and—if the results are
satisfactory—publish the outline to let other users access it.

To do so, we need to:

1. Create the outline tables in our schema:

EXEC DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;

2. Copy a public stored outline in our schema tables:

CREATE PRIVATE OUTLINE PVT_CUST_LIST_OUTLINE
FROM CUST_LIST_OUTLINE;