Stored Outline export from test to production environment

Stored Outline for backup the Execution Plan in Oracle

A stored outline is used to maintain the execution plan for the SQL Queries.It is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of any changes in the system environment or associated statistics. Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable.
The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to display information about existing outlines.

Step 1: Grant the outline permission to users

---Connect As sys:
grant create any outine to  users;
grant execute_catalog_role to users;

Step 2: Identify the hash_value for the SQL statement in the V$SQL view.

SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT e.empno, e.ename, d.dname, e.job%';

---------- ------------ ----------------------------------------
1867249038 0            SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d
                        WHERE e.deptno = d.deptno AND d.dname = 'SALES';

STEP 3: Create the outline for the hash value

hash_value => 1867249038,
child_number => 0,
category => 'EXP_OUTLINES');
exec dbms_outln.create_outline(1867249038,0,'EXP_OUTLINES');

Note: First parameter is SQL hash_value, not plan_has_value as I wrote earlier. Second parameter is child number,Give an name to outline otherwise it go to default)

STEP 4: Checked the outline is created

select * from user_outlines;
SELECT name, category, sql_text FROM user_outlines WHERE category = 'EXP_OUTLINES';

STEP 5: Exported the outline table with exp backup

exp system/xxx file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) statistics=none

STEP 6: Before import on the other server first take the export backup of ouln tables
Another Server:

exp system/xxx file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) statistics=none--Now imported to another server backup taken from first server.
imp system/xxx file=myoutln.dmp full=y ignore=y

STEP 7: Set the parameter use Stored outline on pre-prod and check SQL query
alter system set use_stored_outlines=true;
- And it we used immediately (verified by select * from dba_outlines)

STEP 8: IF server restart then it automatically disable because this parameter not able to set in pfile. To make this change permanent created a startup trigger in preprod as sys.

create or replace trigger enable_outlines
after startup on database
execute immediate('alter system set use_stored_outlines=true');

Rollback Plan for Outline
STEP 1: Import the backup taken for the tables of preprod.

imp system/xxx file=myoutln.dmp full=y ignore=y

STEP2: DROP THE Trigger created to enable_outlines.

Drop trigger enable_outlines;


4 thoughts on “Stored Outline export from test to production environment

  1. Pingback: Stored outline in Oracle | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s