Stored Outlines

Stored Outlines

Oracle preserves the execution plans in objects called “Stored Outlines.” You can create a Stored Outline for one or more SQL statements and group Stored Outlines into categories. Grouping Stored Outlines allows you to control which category of outlines Oracle uses.


select * from v$parameter where name like '%create_stored_outlines%';
select * from dictionary where table_name like '%OUTLINE%';


ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;



GRANT CREATE ANY OUTLINE TO LDBO;
GRANT EXECUTE_CATALOG_ROLE TO LDBO;


-- Create an outline for a specific SQL statement.

CREATE OUTLINE client_email FOR CATEGORY ldbo_outlines
ON select distinct accounts.fibsacct,accountemaildetail.email from accounts,accountemaildetail where accounts.oowncode=accountemaildetail.oowncode;

-- Check the outline as been created correctly.

SELECT name, category, sql_text FROM user_outlines WHERE category = 'LDBO_OUTLINES';

-- List the hints associated with the outline.

SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'CLIENT_EMAIL';


SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE 'select distinct accounts.fibsacct,accountemaildetail.email from accounts,account%';


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

-- Check the outline as been created correctly.

SELECT name, category, sql_text FROM user_outlines WHERE category = 'LDBO_OUTLINES';

SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'SYS_OUTLINE_10080512161704577';


-- Check if the outlines have been used.
SELECT name, category, used FROM user_outlines;


--------In the following example we will enable stored outlines for the current session.

ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;



--DROPPING OUTLINES
BEGIN
DBMS_OUTLN.drop_by_cat (cat => 'LDBO_OUTLINES');
END;
/



---------------------