Oracle Database 10g R2 -- Stored Outlines Are Not Created As Expected

Problem

You are creating stored outlines with DBMS_OUTLN.CREATE_OUTLINE procedure but the stored outlines created does not match the execution plans for the child cursors specified:

SQL> select sql_id, hash_value,
  2         child_number, plan_hash_value
  3  from V$SQL
  4  where sql_id='ap11rjm5q6c2w';

SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
------------- ---------- ------------ ---------------
ap11rjm5q6c2w 3412275292            0      2267701654
ap11rjm5q6c2w 3412275292            1       627713330

Creating two outlines for Child #0 and Child #1. Note that each child cursor has a different execution (PLAN_HASH_VALUE column):

SQL> begin
  2  DBMS_OUTLN.CREATE_OUTLINE (3412275292, 0, 'otln0');
  3  DBMS_OUTLN.CREATE_OUTLINE (3412275292, 1, 'otln1');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Check that outlines are created:

SQL> select name, category from dba_outlines;

NAME                           CATEGORY
------------------------------ --------------
SYS_OUTLINE_14110615471736710  otln0
SYS_OUTLINE_14110615471743011  otln1

Now checking the HINT column of the DBA_OUTLINE_HINTS view for the both outlines show that outlines are identical. Also from sql traces it can identified that the execution plan is not forced by the outline, although it is marks as outline used.

Solution

You are probably hitting the Bug 7554067 : OUTLINE USED BUT PLAN IS DIFFERENT FROM THE OUTLINE. 

According to Oracle Support there was no fix for this bug.

Reference:

Bug 7554067 : OUTLINE USED BUT PLAN IS DIFFERENT FROM THE OUTLINE