How to Edit a Stored Outline to Use the Plan from Another Stored Outline
Last updated on MAY 23, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 9.0.1 to 11.2]
Oracle Database - Standard Edition - Version 184.108.40.206 to 220.127.116.11 [Release 11.2]
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms