Optimizer Plan Stability - does not resolve object reference

Optimizer Plan Stability does not, by design, resolve object references in the SQL text. It just stores a string, and when it gets another string that matches in the category you have enabled, it will utilize the stored hints

test@9iR2> select *
  2  from scott.emp e,scott.dept d
  3  where e.deptno =d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
 
This Query could have better execution if it uses HASH join rather than Nested loop join.

test@9iR2> select /*+  use_hash(e) use_hash(d) */ *
  2  from scott.emp e,scott.dept d
  3  where e.deptno =d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9594)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9594)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2460)

We would like the applications that issue the first query, to get the hash join plan rather than the nested loop plan, but we need this to happen without changing the application code.

Well, since query plan outlines are based on character string comparisons, we can accomplish this using a different schema and some hinted views

test@9iR2> grant select on scott.emp to outline_test;

Grant succeeded.

test@9iR2> grant select on scott.dept to outline_test;

Grant succeeded.

test@9iR2> connect outline_test/outline_test
Connected.
outline_test@9iR2> create or replace view emp
  2  as
  3  select /*+ use_hash(emp) */ *
  4  from scott.emp emp;

View created.

outline_test@9iR2> create or replace view dept
  2  as
  3  select /*+ use_hash(dept) */ *
  4  from scott.dept dept;

View created.

outline_test@9iR2> set autotrace traceonly explain;
outline_test@9iR2> select *
  2  from emp e,dept d
  3  where e.deptno =d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9594)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9594)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2460)

Now we generate a stored outline for our application query.

outline_test@9iR2> alter session set create_stored_outlines=emp_test_outline;

Session altered.

outline_test@9iR2> select *
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  /
...........................
...........................
...........................
14 rows selected.

outline_test@9iR2> alter session set create_stored_outlines=false;

Session altered.

outline_test@9iR2> SELECT name, sql_text
  2  FROM user_outlines
  3  /  

NAME                                                   SQL_TEXT
------------------------------                 ----------------------------------------------
SYS_OUTLINE_100604172908133     select *
                                                               from emp e,dept d
                                                               where e.deptno = d.deptno


So, in the OUTLINE_TEST schema, we have our hinted views of the base objects and weʹve created
a stored outline of the query we wanted in that Schema. We could drop the views at this point if we wanted to – we have what we want, the stored outline using hash joins. Now, when we log in as TEST again, we see the following:

outline_test@9iR2> connect test/test
Connected.
test@9iR2> alter session set use_stored_outlines=TEST_OUTLINE;

Session altered.

test@9iR2> set autotrace traceonly explain;
test@9iR2> select * from emp e,dept d where e.deptno = d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9594)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9594)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2460)

test@9iR2> drop outline SYS_OUTLINE_100604173512322;

Outline dropped.

test@9iR2> set autotrace traceonly explain;
test@9iR2> select *
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'EMP'