Outline hint for query tuning

I had a situation today where I had two slightly different queries that I thought should have the same plan but they didn’t.  So, I needed a way to force the slower query to run with the same plan as the faster one.  I wanted to do this so I could see if the row counts, bytes, or costs for the second query would be different than on the first.  Something must be different or the optimizer would have chosen the same plan for both queries.  By imposing the fast query’s plan on the slow one I could see where the optimizer’s calculations are different.

In the past I’ve done this by adding hints such as an INDEX hint if the faster one used an index and the slower one didn’t.  Today the query was pretty complex so I used an outline hint to force all aspects of the plan on the slow query to be the same as the fast one.  I’ve put together a simplistic example of how to do this.  Script and log is in this zip.

My fast query example is hinted to use an index just to make it different from my slow example:

SQL> -- Get plan for query with index hint including outline
SQL> 
SQL> explain plan into plan_table for
  2  select /*+index(test testi) */ * from test
  3  /

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,
'OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 2400950076

--------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 31471 |  7191K|  1190 
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST  | 31471 |  7191K|  1190 
|   2 |   INDEX FULL SCAN           | TESTI | 31471 |       |    80 
--------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OWNER"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_unnest_subquery' 'false')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

It’s not a realistic example.  The index version of this plan is really slower.  But pretend you wanted to force a similar query to use the same plan as this first query.

Here is the second query with no hints:

SQL> -- Get plan for query with no hint
SQL> 
SQL> explain plan into plan_table for
  2  select * from test
  3  /

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,
'BASIC'));

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 217508114

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| TEST |
----------------------------------

Now here is the second query with the outline hint added and its resulting plan – same as the first query’s:

SQL> -- Get plan forcing query using outline
SQL> 
SQL> explain plan into plan_table for
  2  select
  3    /*+
  4        BEGIN_OUTLINE_DATA
  5        INDEX(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OWNER"))
  6        OUTLINE_LEAF(@"SEL$1")
  7        ALL_ROWS
  8        OPT_PARAM('_unnest_subquery' 'false')
  9        DB_VERSION('11.2.0.3')
 10        OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
 11        IGNORE_OPTIM_EMBEDDED_HINTS
 12        END_OUTLINE_DATA
 13    */
 14  * from test
 15  /

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,
'BASIC'));

PLAN_TABLE_OUTPUT
------------------------------------------------
Plan hash value: 2400950076

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |
|   2 |   INDEX FULL SCAN           | TESTI |
---------------------------------------------

So, imagine a scenario where the two queries were similar enough to have the same plan but complex enough that figuring out individual hints to force the second one to have the same plan as the first would take some time.  This outline hint method is an easy way to impose the first query’s plan on the second.  It won’t work if the two queries are too different but today I used this in a real tuning situation and it saved me a lot of time.

– Bobby