/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?
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;
/
