Oracle Scratchpad

July 26, 2022

Hinting

Filed under: Execution plans,Hints,Oracle — Jonathan Lewis @ 1:05 pm BST Jul 26,2022

This is just a lightweight note on the risks of hinting (which might also apply occasionally to SQL Plan Baselines). I’ve just rediscovered a little script I wrote (or possibly last tested/edited) in 2007 with a solution to the problem of how to structure a query to use an index fast full scan (index_ffs) following by a “table access by rowid” – a path which is not available to the optimizer for select statements (even when hinted, though it became available for deletes and updates in 12c).

It’s possible that this method was something I designed for a client using 9i, but the code still behaves as expected in 11.1.0.7. Here’s the setup and query:

rem
rem     Script:         wildcard.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2007
rem
rem     Last tested
rem             11.1.0.7
rem

create table t1
as
select
        cast(dbms_random.string('a',8) as varchar2(8))  str,
        rpad('x',100)                                   padding
from
        all_objects
where
        rownum <= 10000
;

alter table t1 modify str not null;
create index t1_i1 on t1(str);

begin
        dbms_stats.gather_table_stats(
                user, 't1', 
                cascade => true,
                method_opt => 'for all columns size 1'
        );
end;
/

explain plan for
select  
        /*+ 
                qb_name(main) 
                unnest(@subq1)
                leading(@sel$2fc9d0fe t1@subq1 t1@main)
                index_ffs(@sel$2fc9d0fe t1@subq1(t1.str))
                use_nl(@sel$2fc9d0fe t1@main)
                rowid(@sel$2fc9d0fe t1@main)
        */
        * 
from    t1 
where   rowid in (
                select  /*+ qb_name(subq1) */
                        rowid 
                from    t1 
                where   upper(str) like '%CHD%'
)
;

select * from table(dbms_xplan.display(format=>'outline alias'));

As you can see, I’ve got an IN subquery (query block subq1) to generate a list of rowids from the table for the rows that match my predicate and then my main query (query block main) selects the rows identified by that list.

I’ve added hints to the main query block to unnest the subquery (which will result in a new query block appearing) then do a nested loop from the t1 referenced in subq1 (t1@subq1) to the t1 referenced in main (t1@main), starting with an index fast full scan of t1@subq1 and accessing t1@main by rowid.

The unnest hint was actually redundant – unnesting happened automatically and uncosted. You’ll notice all the other hints are directed at a query block called sel$2fc9d0fe which is the resulting query block name when subq1 is unnested into main.

Here’s the resulting execution plan showing, amongst other details in the Outline Data, that this really was running on 11.1.0.7

Plan hash value: 1953350015

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   500 | 65500 |   509   (0)| 00:00:07 |
|   1 |  NESTED LOOPS               |       |   500 | 65500 |   509   (0)| 00:00:07 |
|*  2 |   INDEX FAST FULL SCAN      | T1_I1 |   500 | 10500 |     9   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY USER ROWID| T1    |     1 |   110 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2FC9D0FE
   2 - SEL$2FC9D0FE / T1@SUBQ1
   3 - SEL$2FC9D0FE / T1@MAIN

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$2FC9D0FE" "T1"@"MAIN")
      LEADING(@"SEL$2FC9D0FE" "T1"@"SUBQ1" "T1"@"MAIN")
      ROWID(@"SEL$2FC9D0FE" "T1"@"MAIN")
      INDEX_FFS(@"SEL$2FC9D0FE" "T1"@"SUBQ1" ("T1"."STR"))
      OUTLINE(@"SUBQ1")
      OUTLINE(@"MAIN")
      UNNEST(@"SUBQ1")
      OUTLINE_LEAF(@"SEL$2FC9D0FE")
      ALL_ROWS
      DB_VERSION('11.1.0.7')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(UPPER("STR") LIKE '%CHD%')

Running the test under 19.11.0.0 (and adding the hint_report option to the dbms_xplan format) this is the resulting plan:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 | 55000 |    47   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   500 | 55000 |    47   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$48592A03 / T1@MAIN

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$48592A03" "T1"@"MAIN")
      OUTLINE(@"SUBQ1")
      OUTLINE(@"MAIN")
      ELIMINATE_SQ(@"SUBQ1")
      OUTLINE_LEAF(@"SEL$48592A03")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("T1"."STR") LIKE '%CHD%')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (1), N - Unresolved (4))
---------------------------------------------------------------------------
   0 -  SEL$2FC9D0FE
         N -  index_ffs(@sel$2fc9d0fe t1@subq1(t1.str))
         N -  leading(@sel$2fc9d0fe t1@subq1 t1@main)
         N -  rowid(@sel$2fc9d0fe t1@main)
         N -  use_nl(@sel$2fc9d0fe t1@main)

   0 -  SUBQ1
         U -  unnest(@subq1)

Clearly the plan has changed – but the hint report says that Oracle has NOT ignored my hints; instead it tells us that they cannot be resolved. If we check the Query Block Name / Object Alias list and the Outline Data we see why: there is no query block named @sel$2fc9d0fe and the reason it doesn’t exist is that the optimizer has applied a previously non-existent transformation ‘eliminate_sq’ (which appeared in 12c) to subq1.

So, on the upgrade from 11.1.0.7 to 19.11.0.0 an SQL Plan Baseline that forced the path we wanted would no longer work (though it might be reported as “applied”) because there is a new transformation that we had (necessarily) not been blocking.

The solution is easy: add the hint no_eliminate_sq(@subq1) to our query and try again.

We still get the full tablescan even though the hint report tells us that the optimizer used the new hint. Here’s the new Outline Data, and the Hint Report showing that the hint was used.

  Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$8C456B9A" "T1"@"SUBQ1")
      OUTLINE(@"SUBQ1")
      OUTLINE(@"MAIN")
      UNNEST(@"SUBQ1")
      OUTLINE(@"SEL$2FC9D0FE")
      ELIMINATE_JOIN(@"SEL$2FC9D0FE" "T1"@"MAIN")
      OUTLINE_LEAF(@"SEL$8C456B9A")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("STR") LIKE '%CHD%')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (4))
---------------------------------------------------------------------------
   0 -  SUBQ1
           -  no_eliminate_sq(@subq1)
           -  qb_name(subq1)

   1 -  SEL$8C456B9A
         U -  leading(@sel$2fc9d0fe t1@subq1 t1@main)
           -  qb_name(main)

   1 -  SEL$8C456B9A / T1@MAIN
         U -  rowid(@sel$2fc9d0fe t1@main)
         U -  use_nl(@sel$2fc9d0fe t1@main)

   1 -  SEL$8C456B9A / T1@SUBQ1
         U -  index_ffs(@sel$2fc9d0fe t1@subq1(t1.str))

But now the Outline Data is showing us a new hint – eliminate_join(@sel$2fc9dofe t1@main). So we’re not losing the subquery, but we’ve lost the join thanks to a transformation that was actually available in 10.2 but presumably couldn’t be applied to our code pattern until at least 12.1. So let’s try again adding in another blocking hint no_eliminate_join(@sel$2fc9dofe t1@main).

We still get the full tablescan – and this time the Outline Data tells us that the problem hint is now eliminate_join(@sel$2fc9dofe t1@subq1) – which we might have anticipated, and now address by adding no_eliminate_join(@sel$2fc9dofe t1@subq1) to the query and having one more go. This finally gets us back to the path that we had previously seen in 11.1.0.7.

Summary

This note is just another simple demonstration that hints do not guarantee plan stability across upgrades – and then showing that it can take a few experimental steps to discover what’s new in the optimizer that is making your previous set of hints ineffective.

Typically the problem will be the availability of new transformations (or enhancements to existing transformations) which manage to invalidate the old hints before the optimizer has had a chance to consider them.

5 Comments »

  1. […] Upgrade issues (July 2022): an example of how new transformations can change plans that had been hinted as completely as possible. […]

    Pingback by “Ignoring Hints” Catalogue | Oracle Scratchpad — July 26, 2022 @ 1:12 pm BST Jul 26,2022 | Reply

  2. […] Upgrade issues (July 2022): an example of how new transformations can change plans that had been hinted as completely as possible. […]

    Pingback by Transformations Catalogue | Oracle Scratchpad — July 26, 2022 @ 1:13 pm BST Jul 26,2022 | Reply

  3. […] Upgrade issues (July 2022): an example of how new transformations can change plans that had been hinted as completely as possible. […]

    Pingback by Execution Plans Catalogue | Oracle Scratchpad — July 26, 2022 @ 2:10 pm BST Jul 26,2022 | Reply

  4. Hello Jonathan,

    In my opinion, once a query is hinted to indicate completely how to perform it, Oracle should not apply any other transformations when creating the execution plan.

    Also, the fact that there is a need to perform “preliminary work” to find out the somewhat “cryptic” name of the query block resulting from unnesting looks to me a little bit “out of the common work” of a developer … and maybe that cryptic query block name might also change from one version to another …

    It is still not completely clear to me why does the optimizer create a new query block name, and does not use the user-given name “MAIN” for the query that results after unnesting the subquery.

    So, just out of curiosity, I tried a little bit different coding for the query, with the NO_QUERY_TRANSFORMATION hint added, in the hope of avoiding any other transformations to be applied, but without using the cryptic intermediary block name, and I got the following, tested in LiveSQL, now having version 19.14.

    explain plan for
    select  
            /*+ 
                    no_query_transformation 
                    qb_name(main)                 
                    leading(@main t2 t1)                
                    use_nl(@main t1)
                    rowid(@main t1)
            */
            * 
    from    t1,
           ( 
               select  /*+ qb_name(subq1)
                           index_ffs(t1 (t1.str))
                       */
                       rowid  row_id
               from    t1 
               where   upper(str) like '%CHD%'
           )   t2
    where
           t1.rowid = t2.row_id
    /
    
    select * from table(dbms_xplan.display(format=>'outline alias'))
    /
    
    
    PLAN_TABLE_OUTPUT
    Plan hash value: 2919084326
     
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |   500 | 61000 |   511   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS               |       |   500 | 61000 |   511   (0)| 00:00:01 |
    |   2 |   VIEW                      |       |   500 |  6000 |    11   (0)| 00:00:01 |
    |*  3 |    INDEX FAST FULL SCAN     | T1_I1 |   500 | 10500 |    11   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS BY USER ROWID| T1    |     1 |   110 |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - MAIN 
       2 - SUBQ1 / T2@MAIN
       3 - SUBQ1 / T1@SUBQ1
       4 - MAIN  / T1@MAIN
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          BEGIN_OUTLINE_DATA
          INDEX_FFS(@"SUBQ1" "T1"@"SUBQ1" ("T1"."STR"))
          USE_NL(@"MAIN" "T1"@"MAIN")
          LEADING(@"MAIN" "T2"@"MAIN" "T1"@"MAIN")
          ROWID(@"MAIN" "T1"@"MAIN")
          NO_ACCESS(@"MAIN" "T2"@"MAIN")
          OUTLINE_LEAF(@"MAIN")
          OUTLINE_LEAF(@"SUBQ1")
          ALL_ROWS
          DB_VERSION('19.1.0')
          OPTIMIZER_FEATURES_ENABLE('19.1.0')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - filter(UPPER("STR") LIKE '%CHD%')
    
    43 rows selected.
    
    

    Except for the VIEW step, which maybe means “materializing” the list of rowid-s produced from the INDEX_FFS, this plan looks almost identical to the desired one.

    For some reason, my philosophy tells me that, even if we used hints for driving the optimizer, we should be able to do this as the “first stage” of the query optimization, separated from the “second stage”, which is the work done by the optimizer itself.

    In other words, it looks to me that having to use an optimizer-generated query block name in a hint means to “interleave” the work of the developer with that of the optimizer,

    Thanks a lot & Best Regards,
    Iudith Mentzel

    Comment by Iudith Mentzel — July 26, 2022 @ 2:39 pm BST Jul 26,2022 | Reply

    • Iudith,

      Thanks for the comment; you’ve coevered a lot of ground in a short space and I think it would be quite easy to take up a 45 slot at a user group conference having an on-stage discussion about the points you’ve raised.

      I think there are 2 big points that make like difficult:

      a) “completely hinted” – it’s very hard to hint completely, and that’s a comment I’ve made many times in the past and the explanation for many claims of “the optimizer ignored my hint”. An interesting detail is of this example is that to “hint completely” I should have included the hint “optimizer_features_enable(‘11.1.0.7’)” and then the problem wouldn’t have arisen in 19.11.0.0. (OFE is a hint that always appears in SQL Plan Baselines, of course; on the other hand Mike Dietrich told us in 2013 that “users” shouldn’t set it)

      b) “interleaving the developer with the optimizer” – I don’t think there’s any way around this problem. At a minimum the default “don’t do anything new” step – adding optimizer_features_enable – requires the developer to know which version of Oracle they are developing for. Two issue contributing to this: (i) a hint may be correct in version 11g, but ambiguous by version 19c – e.g. the merge() hint which had to have its syntax extended when the optimizer got smarter (ii) if a new feature appears all your hints may still be correct and appropriate an be applied, but in the absence of generated query block names the optimizer may be able to produce a bad plan that also uses the new feature when a plan that doesn’t use the feature may look work (because of your hinting) but perform better.

      Just as a little throwaway – here’s what happens on my 19c when I convert your version of the query to “ANSI” syntax:

      explain plan for
      select
              /*+
                      no_query_transformation
                      qb_name(main)
                      leading(@main t2 t1)
                      use_nl(@main t1)
                      rowid(@main t1)
              */
              *
      from    t1
      join    (
                 select  /*+ qb_name(subq1)
                             index_ffs(t1 (t1.str))
                         */
                         rowid  row_id
                 from    t1
                 where   upper(str) like '%CHD%'
              )   t2
      on
             t1.rowid = t2.row_id
      /
      
      select * from table(dbms_xplan.display(format=>'outline alias'))
      /
      
      
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 2039976606
      
      ---------------------------------------------------------------------------------
      | Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT        |       |   500 | 35000 |    56   (0)| 00:00:01 |
      |   1 |  VIEW                   |       |   500 | 35000 |    56   (0)| 00:00:01 |
      |*  2 |   HASH JOIN             |       |   500 | 61000 |    56   (0)| 00:00:01 |
      |   3 |    VIEW                 |       |   500 |  6000 |     9   (0)| 00:00:01 |
      |*  4 |     INDEX FAST FULL SCAN| T1_I1 |   500 | 10500 |     9   (0)| 00:00:01 |
      |   5 |    TABLE ACCESS FULL    | T1    | 10000 |  1074K|    47   (0)| 00:00:01 |
      ---------------------------------------------------------------------------------
      
      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------
         1 - SEL$1 / from$_subquery$_004@MAIN
         2 - SEL$1
         3 - SUBQ1 / T2@SEL$1
         4 - SUBQ1 / T1@SUBQ1
         5 - SEL$1 / T1@SEL$1
      
      Outline Data
      -------------
        /*+
            BEGIN_OUTLINE_DATA
            INDEX_FFS(@"SUBQ1" "T1"@"SUBQ1" ("T1"."STR"))
            USE_HASH(@"SEL$1" "T1"@"SEL$1")
            LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
            FULL(@"SEL$1" "T1"@"SEL$1")
            NO_ACCESS(@"SEL$1" "T2"@"SEL$1")
            NO_ACCESS(@"MAIN" "from$_subquery$_004"@"MAIN")
            OUTLINE_LEAF(@"MAIN")
            OUTLINE_LEAF(@"SEL$1")
            OUTLINE_LEAF(@"SUBQ1")
            ALL_ROWS
            DB_VERSION('19.1.0')
            OPTIMIZER_FEATURES_ENABLE('19.1.0')
            IGNORE_OPTIM_EMBEDDED_HINTS
            END_OUTLINE_DATA
        */
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - access("T1".ROWID="T2"."ROW_ID")
         4 - filter(UPPER("STR") LIKE '%CHD%')
      
      Hint Report (identified by operation id / Query Block Name / Object Alias):
      Total hints for statement: 3 (N - Unresolved (3))
      ---------------------------------------------------------------------------
         0 -  MAIN
               N -  leading(@main t2 t1)
               N -  rowid(@main t1)
               N -  use_nl(@main t1)
      
      Note
      -----
         - this is an adaptive plan
      
      

      The frequency with which the optimizer introduces new query blocks as it works through its options make like particularly difficult when using ‘ANSI’ format. In this case we can get the plan we want (i.e. with the nested loop) if we simply take out the “@main” that appears in the manual hints. But it wouldn’t always be that easy, and the developer might have to examine the Outline Information to see how that can take advantage of it (and hack bits of it) to get the plan they need.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — July 30, 2022 @ 11:33 am BST Jul 30,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by WordPress.com.

%d bloggers like this: