Oracle can force similar statements to share SQL by replacing literals with system-generated bind variables.
This works with plan stability if the outline was generated using the CREATE_STORED_OUTLINES parameter, not the CREATE OUTLINE statement.
Also, the outline must have been created with the CURSOR_SHARING parameter set to SIMILAR or FORCE, and the parameter must also set to SIMILAR or FORCE when attempting to use the outline.
To manage stored outlines, use the OUTLN_PKG package, which gives you three capabilities:
- Drop outlines that have never been used
- Drop outlines within a specific category
- Move outlines from one category to another
Each of these three capabilities has a corresponding procedure within OUTLN_PKG.
To drop outlines that have never been used, execute the DROP_UNUSED procedure, as shown in the following listing:
To drop all of the outlines within a category, execute the DROP_BY_CAT procedure, which has the name of the category as its only input parameter. The following example drops all of the outlines within the DEVELOPMENT category:
execute OUTLN_PKG.DROP_BY_CAT -
(cat => 'DEVELOPMENT');
To reassign outlines from an old category to a new category, use the UPDATE_BY_CAT procedure, as shown in the following example.
execute OUTLN_PKG.UPDATE_BY_CAT -
(old_cat => 'DEVELOPMENT', -
new_cat => 'TEST');
To drop a specific outline, use the drop outline command.
You can enable the usage of stored outlines by setting the USE_STORED_OUTLINES parameter to TRUE within your session (via alter session).