My Oracle Support Banner

How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1)

Last updated on OCTOBER 31, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.0.1 to 11.2]
Oracle Database - Standard Edition - Version to [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.


It is sometimes necessary to use a Stored Outline to enforce a particular well performing execution plan on a SQL statement that is currently executing using a different, sub-optimal plan.

When the desired execution plan is occurring for the same SQL statement on a different database, it is possible to transfer the Stored Outline using e.g. the method in <Note 728647.1>. This can then be directly used without modification.

When the desired execution plan is occurring for a SQL statement which is not identical in SQL text (white-spacing and case differences ignored) to the original SQL statement, then a Stored Outline for that SQL is not usable as the signature of the Stored Outline does not match.

The textual differences could be e.g. comments or hints. The logical meaning of the SQL is the same but the signatures used to match Stored Outlines are different.

This article shows how to modify a Stored Outline for the original SQL statement so that its execution plan is identical to the one produced by the non-matching Stored Outline for the second logically equivalent SQL statement.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 1. Start with two Stored Outlines OL1 for original SQL, OL2 to copy from
 2. Clone both (public) Stored Outlines into private Stored Outlines OLFROM and OLTO
 3. Switch the private Stored Outline OLFROM to use information from OLTO
 4. Resynchronize the private Stored Outline OLTO
 5. Test the private Stored Outline OLTO (optional but recommended)
 6. Publish the private Stored Outline OLTO, thus replacing the public Stored Outline OL1
 7. Test the new public Stored Outline OL1 (optional but recommended)
 8. Drop the public Stored Outline OL2 (optional)

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.