Oracle Stored Outlines

Summary

Stored outlines, also known as plan stability, can be through o as a representation of a SQL statement execution plan that gives the ability to change a SQL Statements execution behavior without having to change the SQL statement in the application code. This allows the tuning of SQL statements for vendor systems where access to the code is usually restricted or inaccessible. It also provides a better way for tuning SQL allowing for better testing of upgrades. Oracle constantly improves the cost based optimizer with every release and when SQL statements are changed in the application code by adding hints undoing the tuning work can be difficult and time consuming. Stored Outlines can be removed to allow upgrades to be tested to see if Oracle makes better decisions in the newer version therefore allowing Oracle to make the decisions without the use of outlines. If the outlines are still needed then they can be put back in and/or tuned for the new Oracle version.



Requirements for Stored Outlines

Database Parameters
To utilized stored outlines certain database parameters
must be set in the init.ora or spfile.ora

query_rewrite_enabled
star_transformation_enabled
optimizer_features_enabled


System Level Parameter Setting
Also a system level parameter must be set to allow the
use of the stored outlines. This can not be set in the
init.ora or spfile.ora therefore a database startup trigger
is a great way to ensure this is set each time the database
is started.

ALTER SYSTEM SET USE_STORED_OUTLINES = true

CREATE OR REPLACE TRIGGER enable_outline
AFTER STARTUP ON DATABASE
BEGIN
execute immediate 'alter system set use_stored_outlines = true';
END ;


Implementing a Stored Outline
1. Create the stored outline for the SQL Statement the needs
improvement as shown via v$sql.

CREATE OR REPLACE OUTLINE SCHEDULING_QUERY on
select :"SYS_B_00" ,sit_code ,sit_division ,sh_serial ,sh_schstat ,sh_estlen,
TO_CHAR(sh_date,:"SYS_B_01")...
/

Or utilize a SQL statement already in the shared pool. Keep in mind this will generate the name for you. Therefore will need to find out what name the system gave it to manipulate later

-- Identify the SQL statement in the V$SQL view.
SELECT hash_value, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE 'select :"SYS_B_00" ,sit_code ,sit_division%';

HASH_VALUE CHILD_NUMBER
---------- ------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
1119283366 0
select :"SYS_B_00" ,sit_code ,sit_division ,sh_serial ,sh_schstat ,sh_estlen, TO_CHAR(sh_date,:"SYS_B_01")...

1 row selected.

-- Create an outline for the statement.
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 1119283366,
child_number => 0,
category => 'MY_OUTLINES');
END;
/


2. Develop a tuned SQL Statement


3. Create the stored outline for the tuned SQL statement

CREATE OR REPLACE OUTLINE SCHEDULING_QUERY_HINTED on
select /*+ leading(INTSTAMP) */
:"SYS_B_00" ,sit_code ,sit_division ,sh_serial ,sh_schstat ,sh_estlen ,
TO_CHAR(sh_date,:"SYS_B_01") ...;


4. Update OUTLN.OL$HINTS and put the plan for the tuned SQL
Statement into the untuned stored outline.

UPDATE OUTLN.OL$HINTS
SET OL_NAME = DECODE(OL_NAME, 'SCHEDULING_QUERY_HINTED', 'SCHEDULING_QUERY',
'SCHEDULING_QUERY', 'SCHEDULING_QUERY_HINTED'
WHERE OL_NAME IN ('SCHEDULING_QUERY','SCHEDULING_QUERY_HINTED');
commit;


5. Execute an explain plan for the original query to recognized
the stored outline. This will update the Outline to used if
Oracle sees the outline and is using it.

EXPLAIN PLAN FOR
select :"SYS_B_00" ,sit_code ,sit_division ,sh_serial
,sh_schstat ,sh_estlen ,TO_CHAR(sh_date,:"SYS_B_01")...


6. Check to see that the stored outline is now used.

select name, category, used from dba_outlines;

NAME CATEGORY USED
---------------- -------- ---------
SCHEDULING_QUERY DEFAULT USED