In 11g Stored Outlines are deprecated and suggestion is to use SQL Plan Management.Below are the commands for using Stored Outlines
- Enable parameter create_stored_outlines at system or session level before creating outlines
- Grant necessary privileges to user before creating outlines(CREATE ANY OUTLINE, EXECUTE_CATALOG_ROLE)
- Create outline for specific statement using CREATE OUTLINE or DBMS_OUTLN.CREATE_OUTLINE procedure.
- Ensure bind variables are used for the sql statements , else outlines will not be used during executions because of change in sql signature
Use outlines by setting following parameters ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;
PFB the example
We can also export /import outlines using export of tables OL$, OL$HINTS, and OL$NODES.
-- Switch on automatic creation of stored outlines.
At instance level
ALTER SYSTEM SET create_stored_outlines=TRUE;
At session level
ALTER SESSION SET create_stored_outlines=TRUE;
-- Switch off automatic creation of stored outlines.
ALTER SYSTEM SET create_stored_outlines=FALSE;
ALTER SESSION SET create_stored_outlines=FALSE;
-- Grant the necessary privileges.
CONN sys/password AS SYSDBA
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
===================
For SQL Plan management refer to below blog for more details...