Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fudging outlines

Re: Fudging outlines

From: Bjørn Engsig <>
Date: Fri, 07 Mar 2003 02:18:58 -0800
Message-ID: <>

I have been using the attached script to do exactly this on an 8.1.7 database - usual disclaimers apply.


Chuck Hamilton wrote:

>I have an application query that I do not have the source code for. It gets
>a crappy execution plan. I can add a hint or two to it and significantly
>improve the execution plan. I want to stuff that execution plan into a
>stored outline so that the unhinted query uses that plan plan each time it
>executes. Can this be done?

Bjørn Engsig, Miracle A/S
Member of Oak Table Network <> -

rem rem This sql script can be used to attach a (hopefully good) stored outline to rem a SQL statement. rem rem To use it, first run your application with the (poor) execution plan and verify rem your SQL statement is in v$sqlarea. Next, use some tool (sqlplus, OEM or whatever) rem adding hints, etc. to your sqlstatement to make it perform well. Make also sure rem this new version of the SQL statement is in v$sqlarea. Note the hash_value and rem address of the two sqlstatements and run this script. It will take the execution rem plan from the good version and apply it is a stored outline to the bad version. rem variable gad varchar2(20) variable ghv number variable bad varchar2(20) variable bhv number variable gna varchar2(50) variable bna varchar2(50) set serveroutput on exec :bad := '&SQL_ADDRESS_incorrect_plan'; :bhv := &SQL_HASH_incorrect_plan; :bna := '&NAME_incorrect_plan'; exec :gad := '&SQL_ADDRESS_wanted_plan'; :ghv := &SQL_HASH_wanted_plan; :gna := '&NAME_wanted_plan'; rem declare gq varchar2(32767) ; -- sql for good outline bq varchar2(32767) ; -- sql for bad outline begin -- -- Creeate the two 'create outline' statements gq := ''; for z in (select sql_text from v$sqltext_with_newlines where address = hextoraw(:gad) and hash_value = :ghv order by piece) loop gq := gq || z.sql_text; end loop; dbms_output.put_line(gq); -- bq := ''; for z in (select sql_text from v$sqltext_with_newlines where address = hextoraw(:bad) and hash_value = :bhv order by piece) loop bq := bq || z.sql_text; end loop; dbms_output.put_line(bq); -- -- and execute them execute immediate 'create outline "' || :gna || '" on ' || substr(gq,1,length(gq)-1); execute immediate 'create outline "' || :bna || '" on ' || substr(bq,1,length(bq)-1); -- -- delete the hints from the bad outline delete from outln.ol$hints where ol_name = :bna; -- rename the good hints so they apply to the bad outline update outln.ol$hints set ol_name = :bna where ol_name = :gna; -- -- update count of hints on the bad outline to that of the good one update outln.ol$ set hintcount = (select hintcount from outln.ol$ where ol_name = :gna) where ol_name = :bna; -- -- delete the entry in ol$ for the good outline delete from outln.ol$ where ol_name = :gna; dbms_output.put_line('Exchanged outline '||:bna||' with hints from '||:gna); -- commit; end; / -- Please see the official ORACLE-L FAQ: -- Author: =?ISO-8859-1?Q?Bj=F8rn_Engsig?= INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Received on Fri Mar 07 2003 - 04:18:58 CST

Original text of this message