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

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Outline hint for query tuning

  1. rajiviyer says:

    Hi Bobby.
    You could also transfer the outline hints from one SQL to another using dbms_sqltune.import_sql_profile. It will be much neater without the need to add outline hints to sql text explicitly.

  2. vaurob says:

    Hello
    I was wondering if IGNORE_OPTIM_EMBEDDED_HINTS should be left out.
    In my testcase this hint in the sql body makes Oracle ignore the other embedded hints.

    For instance:
    select /*+ full(t) IGNORE_OPTIM_EMBEDDED_HINTS */ * from t where id = 777;

    ————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 1005 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1005 | 2 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 |
    ————————————————————————————

    Whereas
    select /*+ full(t) */ * from t where id = 777;

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 1005 | 68 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| T | 1 | 1005 | 68 (0)| 00:00:01 |
    ————————————————————————–

    Cheers,
    Rob

    • Bobby says:

      Rob,

      I don’t think you should leave IGNORE_OPTIM_EMBEDDED_HINTS out of the outline hints. dbms_xplan.display includes it so I just copy and paste whatever it generates. I have not tested it but my guess is that IGNORE_OPTIM_EMBEDDED_HINTS does not negate the other hints in the outline hint. It probably just negates hints outside of the outline block of hints. Maybe try something like this to see if I am right:

      select
      /*+
      BEGIN_OUTLINE_DATA
      full(t)
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
      */
      * from t where id = 777;

      Does this use the full scan or not?

      Bobby

Leave a Reply to Bobby Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.