Re: Fudging outlines

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

/Bjørn.

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 <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk





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: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Bj=F8rn_Engsig?=
  INET: bjorn_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).