Stored Outline in Oracle
Note: Benefit of creating outline is when database is using bind variable. May cursor sharing parameter set to force. So that, Multiple query want to execute with same execution plan.
A stored outline is a collection of hints with a SQL statement that allows a execution plan to be maintained, regardless of changes in the system or statistics. Plan stability preserves execution plans in stored outlines when performance is good. The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables.
Following view is used to verify the outline created:
select * from [USER|ALL|DBA]_OUTLINES;
select * from [USER|ALL|DBA]_OUTLINE_HINTS;
Oracle can create a public or private stored outline for one or all SQL statements.
1. There is a one-to-one correspondence between SQL text and its stored outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in applications with bind variables.
2. Oracle can allow similar statements to share SQL by replacing literals with system-generated bind variables. This works with plan stability if the outline was generated using the CREATE_STORED_OUTLINES parameter, not the CREATE OUTLINE statement.
DBMS_OUTLN and DBMS_OUTLN_EDIT package provides procedures used for managing stored outlines and their outline categories.
Oracle creates stored outlines automatically when you set the initialization parameter CREATE_STORED_OUTLINES to true. Set the system parameter USE_STORED_OUTLINES to true or to a category name.
OUTLINE Can be created automatic or manually.
For automatic creation of stored outlines:
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;
After creating outline continue with step 3 below for use outline.
ALTER SYSTEM SET create_stored_outlines=FALSE;
ALTER SESSION SET create_stored_outlines=FALSE;
Note: Every SQL is executed after setting true, oracle automatic create outline for SQL queries.
For Manually creation of Stored Outlines:
Two ways for creating outline with create outline statement or DBMS_OUTLN.create_outline Procedure.
Note: With create outline statement it’s not convert literal to bind variable. So same query is consider as outline it is one to one relation with SQL and its stored outline. For this you need to create outline by create_stored_outlines parameter to avoid one to one relation.So, tried to create with above alter system command.
1. Following permission needed for create outline as :
GRANT CREATE ANY OUTLINE TO USER;
GRANT EXECUTE_CATALOG_ROLE TO USER;
2. Create the outline with both method:
--Create outline statement
CREATE OUTLINE EMP_DEPT_JOIN FOR CATEGORY DB_outline
ON SELECT e.empno, e.ename,d.dept_no, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
–Create outline with package
--Run the SQL statement to placed it in shared pool
SELECT e.empno, e.ename, d.dept_name FROM emp e, dept d WHERE e.deptno =
d.deptno AND d.dept_name = 'HR';
==Find has value from v$sql view
SELECT hash_value, child_number FROM v$sql WHERE sql_text LIKE 'SELECT e.empno,
e.ename, d.dname FROM emp e%';
HASH_VALUE CHILD_NUMBER ---------- ------------ 5010394479 0
--Create outline with DBMS_OUTLINE package:
hash_value => 5010394479,
child_number => 0,
category => 'DB_OUTLINE');
3. For Enable the outline use:
- Enable stored outlines.
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=DB_OUTLINE;
USE_STORED_OUTLINES parameter have options: TRUE/CATEGORY NAME/FALSE;
In above example i used category name.
Execute the SQL query at session level/you can also configure at system level
Check the execution plan it will show you the outline is used
4. Check the outline is used.
SELECT name, category, used FROM dba_outlines;
--used column value is unused : means outline is not used
Drop the outline
exec DBMS_OUTLN.drop_by_cat (cat => 'DB_OUTLINE');
ORA-18004: outline already exists
Change the name of outline by checking following query:
SELECT name, category, used FROM dba_outlines;
Change the name of outline
alter outline SYS_OUTLINE_07072313512890701 rename to MYOUTLN;
Change the catagory Name of outline
alter outline DB_OUTLINE change category to CAT_OULINE;
Export the Statistics to another Server
Complete step of moving the execution plan which is stored in outline tables of TEST environment to production environment as following link: Transfer outline from Test to Prod
exp system/ file=E:\outlinedump.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=\"where category='CAT_OUTLINE'\" statistics=none)
--Ftp to other Server
imp system/ file=E:\outlinedump.dmp full=y ignore=y
--Enable the outline for use
alter session set use_stored_outlines = CAT_OUTLINE;
In 10g above use EXPDP as follows:
-- Expdp the backup
expdp outln DIRECTORY=datapump_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES
-- Transfer the dump
-- Import the backup
impdp outln DIRECTORY=datapump_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES TABLE_EXISTS_ACTION=TRUNCATE
Note: If outlines is not used then you need to purge the existing cursor by dbms_shared_pool.purge().