Tony’s Oracle Tips

January 6, 2009

Materialize and Inline hints do not appear in outlines

Filed under: Uncategorized — tonyhasler @ 6:20 pm

I discovered by accident today that the MATERIALIZE and INLINE hints for factored subqueries do not appear in the outline data for a query. A small oddity, you might say, but one which renders invalid the assertion made that an outline will contain all the hints necessary to fix an execution plan.

I ran this test on 10.2.0.4:


explain plan for
with x as (select /*+ inline */ count(*) c1 from dual)
select count(*) from x a,x b;
select * from table(dbms_xplan.display(null,null,'basic +outline')) ;

The above statements generate what should be a full set of hints to reproduce the same execution plan for the query without the risk of variance. Not only does the INLINE hint not appear but use of the hints that are given results in a differenet execution plan. First of all let us see the output from the statements above:


Plan hash value: 1347014984

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  SORT AGGREGATE   |      |
|   2 |   NESTED LOOPS    |      |
|   3 |    VIEW           |      |
|   4 |     SORT AGGREGATE|      |
|   5 |      FAST DUAL    |      |
|   6 |    VIEW           |      |
|   7 |     SORT AGGREGATE|      |
|   8 |      FAST DUAL    |      |
----------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$2" "B"@"SEL$2")
      LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
      NO_ACCESS(@"SEL$2" "B"@"SEL$2")
      NO_ACCESS(@"SEL$2" "A"@"SEL$2")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$C1841055")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Now let us see what hapens when we use these hints (minus the IGNORE_OPTIM_EMBEDDED_HINTS of course):


explain plan for
with x as (select
  /*+
      USE_NL(@"SEL$2" "B"@"SEL$2")
      LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
      NO_ACCESS(@"SEL$2" "B"@"SEL$2")
      NO_ACCESS(@"SEL$2" "A"@"SEL$2")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$C1841055")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
  */
count(*) c1 from dual)
select count(*) from x a,x b;
select * from table(dbms_xplan.display(null,null,'basic +outline')) ;

The result for me was:


Plan hash value: 3713891149

------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |
|   1 |  TEMP TABLE TRANSFORMATION |                             |
|   2 |   LOAD AS SELECT           |                             |
|   3 |    SORT AGGREGATE          |                             |
|   4 |     FAST DUAL              |                             |
|   5 |   SORT AGGREGATE           |                             |
|   6 |    NESTED LOOPS            |                             |
|   7 |     VIEW                   |                             |
|   8 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6630_3ACAC6AF |
|   9 |     VIEW                   |                             |
|  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6630_3ACAC6AF |
------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
      FULL(@"SEL$D67CB2D3" "T1"@"SEL$D67CB2D3")
      USE_NL(@"SEL$2" "B"@"SEL$2")
      LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
      NO_ACCESS(@"SEL$2" "B"@"SEL$2")
      NO_ACCESS(@"SEL$2" "A"@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$D67CB2D3")
      OUTLINE_LEAF(@"SEL$D67CB2D2")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

This is completely different!

I have tried this with a proper stored outline with the same effect. I went further and tried simply to use these hints globally. Also with no succcess:


with x as (select /*+ qb_name(qb1) inline(@qb1) */ count(*) c1 from dual) select count(*) from x a,x b;

Create a free website or blog at WordPress.com.