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

9876 文檔 ID … How to Edit a Stored Outline to Use the Plan from Another Stored Outline (文檔 ID 730062.1) 轉到底部 In this Document Goal Solution 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) References 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. Checked for relevance on 10-Feb-2012 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, suboptimal plan. When the desired execution plan is occuring 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 occuring for a SQL statement which is not identical in SQL text (whitespacing 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 Here is the sequence of steps required to accomplish the above task: start with two Stored Outlines: OL1 is for the original SQL and OL2 is the one we wish to copy from clone both (public) Stored Outlines into private Stored Outlines OLFROM and OLTO switch the private Stored Outline OLFROM to use information from OLTO resynchronize the private Stored Outline OLTO test the private Stored Outline OLTO (optional but recommended) publish the private Stored Outline OLTO, thus replacing the public Stored Outline OL1 test the new public Stored Outline OL1 (optional but recommended) drop the public Stored Outline OL2 (optional) Now follow the above steps in detail: 1. start with two Stored Outlines OL1 for original SQL, OL2 to copy from On the database where the original SQL is currently executing with a suboptimal plan, create a Stored Outline for it using e.g. Note 445126.1. On the database where the second non-matching (but logically equivalent) SQL statement exists, create a Stored Outline for it using the same method. If this is on a different database than the original SQL, transfer the Stored Outline using e.g. Note 728647.1. Rename the Stored Outlines to use human-friendly names using the following syntax: SQL> alter outline SYS_OUTLINE_08080311442300505 rename to OL1; Outline altered. SQL> alter outline SYS_OUTLINE_08080311444872408 rename to OL2; Outline altered. If different, alter the Stored Outlines so that they belong to the same category: SQL> alter outline OL1 change category to MYCAT; Outline altered. SQL> alter outline OL2 change category to MYCAT; Outline altered. 2. clone both (public) Stored Outlines into private Stored Outlines OLFROM and OLTO Use the following commands for this: SQL> create private outline OLFROM from OL1; Outline created. SQL> create private outline OLTO from OL2; Outline created. At this point we have user-modifiable details for the two Stored Outlines in the global temporary tables stored in SYSTEM schema and accessible via public synonyms OL$, OL$HINTS and OL$NODES. For more information on this see Note 726802.1. 3. switch the private Stored Outline OLFROM to use information from OLTO This can be done using the following commands: SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLTO') where ol_name='OLFROM'; 1 row updated. SQL> delete from ol$ where ol_name='OLTO'; 1 row deleted. SQL> update ol$ set ol_name='OLTO' where ol_name='OLFROM'; 1 row updated. SQL> commit; Commit complete. 4. resynchronize the private Stored Outline OLTO This is done using the following command: SQL> execute dbms_outln_edit.refresh_private_outline('OLTO'); PL/SQL procedure successfully completed. 5. test the private Stored Outline OLTO (optional but recommended) Here is one way of doing this: SQL> alter session set use_private_outlines=true; Session altered. SQL> set autotrace traceonly SQL> ... execute the original SQL statement ... Execution Plan ---------------------------------------------------------- Plan hash value: 989401810 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |     1 |    13 |     2   (0)| 00:00:01 | ... the rest of the execution plan follows ... --------------------------------------------------------------------------- Note -----    - outline "OLTO" used for this statement Statistics ... The message highlighted in bold confirms the OLTO outline is used and the plan can be checked to confirm it is the one we intended to use from the second SQL statement. 6. publish the private Stored Outline OLTO, thus replacing the public Stored Outline OL1 The command for this is: SQL> create or replace outline OL1 from private OLTO for category MYCAT; Outline created. 7. test the new public Stored Outline OL1 (optional but recommended) Here is one way of doing this: SQL> alter session set use_private_outlines=false; Session altered. SQL> alter session set use_stored_outlines=MYCAT; Session altered. SQL> set autotrace traceonly SQL> ... execute the original SQL statement ... Execution Plan ---------------------------------------------------------- Plan hash value: 989401810 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |     1 |    13 |     2   (0)| 00:00:01 | ... the rest of the execution plan follows ... --------------------------------------------------------------------------- Note -----    - outline "OL1" used for this statement Statistics ... The message highlighted in bold confirms the OL1 outline is used and the plan can be checked to confirm it is the one we intended to use from the second SQL statement. 8. drop the public Stored Outline OL2 (optional) The public Stored Outline OL2 is no longer necessary so we can drop it as follows: SQL> drop outline OL2; Outline dropped. The procedure is now complete. Note: there is no need to manually clean up the private Stored Outlines as they are automatically removed when the session which created them terminates. References NOTE:445126.1 - HOW TO: Create a Stored Outline Based Upon an Existing Cursor NOTE:726802.1 - Editing Stored Outlines in Oracle10g and Oracle11g NOTE:728647.1 - How to Transfer Stored Outlines from One Database to Another (9i and above Editing Stored Outlines in Oracle10g and Oracle11g (文檔 ID 726802.1)