Stored Outline for backup the Execution Plan in Oracle

Guide to Using Stored Outlines in Oracle SQL

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.

Enable the outline:

ALTER SYSTEM SET use_stored_outlines = TRUE;

--at session level:
ALTER SESSION SET use_stored_outlines = TRUE;

Step 1: Grant the outline permission to users

GRANT EXECUTE_CATALOG_ROLE TO username;
OR
GRANT CREATE ANY OUTLINE TO username;
GRANT ALTER ANY OUTLINE TO username;
GRANT DROP ANY OUTLINE TO username;

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

SELECT sql_id,
       hash_value,
       address,
       sql_text
FROM   v$sql
WHERE  UPPER(sql_text) LIKE '%YOUR KEYWORD HERE%';

STEP 3: Create the outline for the hash value

SELECT hash_value, address, sql_text
FROM   v$sql
WHERE  UPPER(sql_text) LIKE '%YOUR KEYWORD%';

CREATE OUTLINE outline_name
FOR CATEGORY category_name
ON
SELECT /*+ OUTLINE_SQ */ * 
FROM TABLE(DBMS_OUTLN.CREATE_OUTLINE(
    hash_value => <your_hash_value>,
    address    => '<your_address>'
));

OR

CREATE OUTLINE outline_name
FOR STATEMENT
    hash_value <your_hash_value>
    address '<your_address>';

STEP 4: Checked the outline is created

SELECT name, category, sql_text
FROM   dba_outlines
WHERE  category = 'YOUR_CATEGORY';

STEP 5: Exported the outline table with exp backup

OL$

OL$HINTS

OL$NODES

OL$PLAN

OL$SIGNATURE

OL$STATS

exp userid=system/password owner=OUTLN file=outln_backup.dmp

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

imp userid=system/password show=y file=outln_backup.dmp

STEP 7: Set the parameter use Stored outline on pre-prod and check SQL query

alter system set use_stored_outlines=true scope=both;

- 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.

alter system set use_stored_outlines=true scope=both;