How to Edit a Stored Outline to Use the Plan from Another Stored Outline
(Doc ID 730062.1)
Last updated on OCTOBER 31, 2019
Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [Release 9.0.1 to 11.2] Oracle Database - Standard Edition - Version 18.104.22.168 to 22.214.171.124 [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!