Stored Outlines
Stored outlinescan be
very useful, especially when the SQL can't be changed (packaged
applications).
In order to use them, you need to capture the SQL
statement, and then create an outline with exactly the same SQL statement
that uses an alternative access path (perhaps via a hint).
Initially, set up security to create the outlines:
connect 'sys/xxx as sysdba'
grant create any outline to scott;
grant execute on dbms_outln to scott;
grant execute on dbms_outln_edit to scott;
grant select on dba_outlines
to scott;
To begin with, set up the session
to allow stored outlines, or to ensure that the
init.ora parameter
create_stored_outlinesis set accordingly.
connect scott/tiger
alter session set create_stored_outlines =
TRUE
create or replace outline first_outline
on select a.emp_id, a.name, b.dependent_id, b.name
from emp a,
dependent b
where a.empid_id = b.emp_id
Now a public outline has been created, but in order to
test it, make a private outline (personal copy).
In order to do this, you need to create the tables to hold the
private outline first:
execute dbms_outln_edit.create_edit_tables;
create private outline first_private_outline from
first_outline;
--- check out what is in the outline table by default,
--- just from creating the private outline
select * from ol$hints
where ol_name = 'first_private_outline';
--- change which ever access path we are interested in
--- for example, could change a nested_lop join to
--- a hash join
update ol$hints set hint_text='USE_HASH(B)'
where hint#=5;
commit;
--- check the result, and be sure the update worked
select * from ol$hints
where ol_name = 'first_private_outline';
--- resync the outline
execute dbms_outline_edit.refresh_private_outline ('first_private_outline')
--- test it out
alter session set use_private_outlines=true;
set autotrace on explain
select a.emp_id, a.name, b.dependent_id, b.name
from emp a,
dependent b
where a.empid_id = b.emp_id
;
--- if new access plan is correct, make the outline
public
--- for use by everyone
create or replace second_outline from private
first_private_outline;
Now, whenever the original SQL is
executed, it will use the revised access plan instead of the original
(assuming the init.ora
parameter use_stored_outlinesis set to true).
Note that the SQL used to create the stored outline and
the SQL that is run after the outline is created, must be IDENTICAL, or it
won't use the outline. Identical
means the text must be in the same case, same amount of white space, etc.
Oracle 9i and 10g are more forgiving on this requirement than Oracle
8i.
The catalog tables
dba_outlinesand
dba_outline_hintscontain relevant information about
the outlines. They are based on
catalog tables OUTLN.OL$ and OUTLN.OL$HINTS.
Stored Outlines have been greatly enhanced with the new
Oracle 10g features of SQL Profiles.
Swapping stored Outlines
MOSC note 92202 .1 describes a procedure to tune SQL
that you cannot touch by performing these steps:
-
Identify the sub-optimal SQL and create a stored
outline
-
Tune an equivalent query with a faster execution
plan and create a stored outline
-
Swap the bad stored outline for the tuned stored
outline
Oracle provides this example for swapping the outlines:
UPDATE
OUTLN.OL$HINTS
SET
OL_NAME=DECODE
(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
WHERE
OL_NAME IN ('HINTSQL','ORIGINALSQL');
Commit;
