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

Last updated on MAY 23, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.2 [Release 9.0.1 to 11.2]
Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Goal

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.

Solution

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