By using a stored outline you may be forcing the optimizer to choose a substandard execution plan
Oracle Recommend SQL Plan Manager since oracle 11g onward link
store
ol$
OL$HINTS
OL$NODES
views
DBA_OUTLINES
ALL_OUTLINES
USER_OUTLINES
DBA_OUTLINE_HINTS
ALL_OUTLINE_HINTS
USER_OUTLINE_HINTS
Creating Outlines ( create_stored_outlines)
To switch on
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;
To switch Off
ALTER SYSTEM SET create_stored_outlines=FALSE;
ALTER SESSION SET create_stored_outlines=FALSE;
or by using DBMS_OUTLN.CREATE_OUTLINE
Grant for creating and execution outline
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
To create a outline for a specific SQL statement.
CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
To check a specific outline
COLUMN name FORMAT A30
SELECT name, category, sql_text
FROM user_outlines
WHERE category = '&created_category'; e.g. (SCOTT_OUTLINES)
To list the hints associated with the outline
COLUMN hint FORMAT A50
SELECT node, stage, join_pos, hint
FROM user_outline_hints
WHERE name = '&outline_name'; e.g. (EMP_DEPT)
To create outline from v$sql (shared pool) with DBMS_OUTLN.CREATE_OUTLINE
1# identify SQL statement
SELECT hash_value, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE 'SELECT e.empno, e.ename, d.dname, e.job%';
2# To create outline with DBMS_OUTLN.create_outline
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'SCOTT_OUTLINES');
END;
/
To check if the outlines have been used.
SELECT name, category, used FROM user_outlines;
If the status is unused you can run the query and check againt, if it still unused you should to enable query rewrites
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;
and then you can run again the query and check status is used
To drop outline
BEGIN
DBMS_OUTLN.drop_by_cat (cat => 'SCOTT_OUTLINES');
END;
/
note: in a category maybe there are many category name
