Query Optimizer  «Prev 

Using Stored Outlines


Location 1 Required keywords.
Location 2 Required keywords to turn on the collection of stored outlines.
Location 3 A category name that allows stored outlines to be grouped into logical categories.

Oracle 12c Performance Tuning


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:
  1. Drop outlines that have never been used
  2. Drop outlines within a specific category
  3. 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:
(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.
(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).