Oracle Scratchpad

May 1, 2020

Execution Plans

Filed under: Oracle — Jonathan Lewis @ 1:58 pm BST May 1,2020

In previous articles on reading execution plans I’ve made the point that the optimizer is very “keen” to transform complex queries into queries consisting of a single query block and that there’s a simple “First Child First (FCF)” rule for reading the plan for a single query block. I’ve then pointed out that when the optimizer can’t transform your query into a single query block you can still apply FCF to each “final” query block (outline_leaf) in turn, but you then have to work out how Oracle is connecting those query blocks and FCF is not guaranteed to apply between query blocks.

In this note I want to follow-up an earlier comment that “The FILTER operation covers a multitude of sins.” because the filter operation (and variations thereof) often goes hand in hand with multiple query blocks and often (especially in recent versions of Oracle) needs a little care when you’re looking at a larger plan.

First though – a silly little question:

How many rows will be returned by the query “select * from tableX where 1 = 2”?

The answer is obviously “none”. But here’s the execution plan for a query of that form (cut and pasted from an SQL*Plus session in 19.3):

SQL> create table t1 as select * from all_objects where rownum <= 1000 -- > hint to avoid wordpress format issue
 2   /

Table created.

SQL> set autotrace traceonly explain
SQL> select * from t1 where 1 = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   116 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |   113K|     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Although it’s clearly impossible for any rows to be returned Oracle (apparently) wants to do a full tablescan. Of course it doesn’t actually do that tablescan; if you check the Predicate Information there’s a filter predicate at Operation 1 that tests for “null is not null”[1], which is never true, and one of the refinements on the basic “first child first” is that a child operation is called only if the parent thinks it is needed. Consider, for example, the following query with its execution plan (again cut-n-pasted from SQL*Plus 19.3.0.0 with the same table t1):


SQL> set serveroutput off
SQL> alter session set statistics_level = all;

Session altered.

SQL> select t1.*
  2  from   t1, t1 t2
  3  where  t1.object_id < 0 -- > comment to avoid wordpress format issue
  4  and    t2.object_id = t1.object_id 
  5  /

no rows selected 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  86ytbkc1fpbzt, child number 0
-------------------------------------
select t1.* from   t1, t1 t2 where  t1.object_id < 0 and
t2.object_id = t1.object_id

Plan hash value: 1734879205

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |      22 |     18 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |      22 |     18 |   799K|   799K|  199K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |      19 |     18 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
   2 - filter("T1"."OBJECT_ID"<0)
   3 - filter("T2"."OBJECT_ID"<0)

If you look at the Starts column for operation 3 you’ll see that the second tablescan doesn’t run. This makes sense since the A-Rows column of operation 2 reports zero rows, which means the hash join at operation 1 has no data for its build table, so there’s no point  in it calling operation 3 to search for probe  data that has nothing to match. (This optimization doesn’t necessarily appear for parallel hash joins).

You probably won’t see many cases of people writing code with literal predicates quite like “1 = 2”, of course, but you might see predicates like “:bindvar1 = ‘M'” fairly often, either because the client code really does contain the predicate or because the optimizer has produced it through some transformation (such as concatentation or nvl_or_expansion or transitive closure).

[1] In older versions of Oracle a predicate that was always false (i.e. a contradiction) would have been transformed into the predicate “1=0”

Constant Subqueries

Another example of a less common appearance of the FILTER operation comes from a requirement like: “if there are more than 10 orders outstanding show them to me”. Using the t1 table above, this could be modelled with a query like:


SQL> set serveroutput off
SQL> alter session set statistics_level = all;

Session altered.

SQL> select * from t1 
  2  where 10 <= (select count(*) from t1 where object_id < 0) -- > comment to avoid wordpress format issue
  3  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last alias'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2pha6dc0b9zzq, child number 1
-------------------------------------
select * from t1 where 10 <= (select count(*) from t1 where object_id < 0) -- > comment to avoid wordpress format issue

Plan hash value: 2626881942

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |      19 |
|*  1 |  FILTER             |      |      1 |        |      0 |00:00:00.01 |      19 |
|   2 |   TABLE ACCESS FULL | T1   |      0 |   1000 |      0 |00:00:00.01 |       0 |
|   3 |   SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      19 |
|*  4 |    TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |      19 |
--------------------------------------------------------------------------------------

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(>=10)
   4 - filter("OBJECT_ID"<0)

If you go at this plan in too much of a rush you might think it is a single query block and apply FCF to produce the description:
“For each row in the tablescan of t1 at operation 2 the FILTER operation at operation 1 calls operation 3 to do a full tablescan (operation 4) of the second copy of t1 – scalar subquery caching means we only do that tablescan once and then cache the result.”

But the query clearly starts out with two (unnamed) query blocks, and the Query Block Name / Object Alias information shows us we still have two query blocks, and the Starts column tells us that operation 2 didn’t run at all. This is a case where we have to think carefully about how to combine multiple query blocks after interpreting the sub-plan for each query block individually.

In this example, which I call the “constant subquery”, Oracle can evaluate the subquery first to decide whether or not to run the main query, and the visual impact of this is that the second (last) child of the FILTER runs before the first child. This is not breaking FCF – it’s just one of the patterns you have to recognise as Oracle combines multiple query blocks in a single plan.

Correlated Filter Subqueries

Moving on to one of the most common types of filter subquery – a simple correlated subquery – there are four patterns to watch out for, and a fifth pattern that isn’t a filter subquery that might fool you if you get too confident of handling filter subqueries. I’ll be using hints fairly aggressively to force the plans I want to see, but all the plans that I’ll show in this section could appear as a consequence of basic costing.

We start with an order_lines table and a products table, and have a requirement to report any order lines for a given product class where the number of items ordered is 6.


select  /*+ 
                qb_name(main) 
        */
        orl.*
from    order_lines     orl
where
        orl.quantity = 6
and     orl.id_product in (
                select  /*+ 
                                qb_name(class) 
                        */
                        prd.id
                from    products prd
                where   prd.class = 'Group25'
        )
;


In the four plans I’m going to show you I’ve added the /*+ no_unnest */ hint to the subquery (though I’ve not shown it in the text above) to make sure that the optimizer doesn’t transform the subquery into a join.

In the first plan I don’t have any indexes on the order_lines table that could help eliminate data early, I’ve also added the hint /*+ no_push_subq */ to the subquery so that the optimizer doesn’t try to move the subquery to the earlies possible point in the plan. The resulting plan looks like the “traditional” plan for a filter subquery – reporting a FILTER operation that (notionally) executes the subquery for each row returned by a tablescan.

NO_UNNEST, NO_PUSH_SUBQ
-----------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |          |
|*  1 |  FILTER                      |             |       |          |
|*  2 |   TABLE ACCESS FULL          | ORDER_LINES |  5556 | 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 | 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 | 00:00:01 |
-----------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN
   2 - MAIN  / ORL@MAIN
   3 - CLASS / PRD@CLASS
   4 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - filter("ORL"."QUANTITY"=6)
   3 - filter("PRD"."CLASS"='Group25')
   4 - access("PRD"."ID"=:B1)

   1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS") */ 0 
                       FROM "PRODUCTS" "PRD" 
                       WHERE "PRD"."ID"=:B1 AND "PRD"."CLASS"='Group25'))

As you can see, I’ve reported the Query Block / Object Alias information and the two original query blocks are still clearly visible in this plan. Since I’ve pulled this plan from memory you’ll also note in the Predicate Information that the FILTER operation has “lost” the text of the filter() predicate.  This is why I’ve added at the end of the output the filter predicate reported by using explain plan with dbms_xplan.display().

For the next run I’m going to add an index on the quantity column of the order_lines table, and I’m going to tell the optimizer to run the subquery at the earliest possible moment (using the push_subq hint). This can produce two different plans – depending on whether or not the optimizer thinks it would be efficient to use the new index.

First, when the optimizer ignores the index:

NO_UNNEST, PUSH_SUBQ, order_lines index ignored
-----------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   282 | 00:00:01 |
|*  1 |  TABLE ACCESS FULL           | ORDER_LINES |   282 | 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 | 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 | 00:00:01 |
-----------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN  / ORL@MAIN
   2 - CLASS / PRD@CLASS
   3 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ORL"."QUANTITY"=6 AND  IS NOT NULL)
   2 - filter("PRD"."CLASS"='Group25')
   3 - access("PRD"."ID"=:B1)


   1 - filter("ORL"."QUANTITY"=6 AND  EXISTS (SELECT /*+ PUSH_SUBQ
              NO_UNNEST QB_NAME ("CLASS") */ 0 FROM "PRODUCTS" "PRD" WHERE
              "PRD"."ID"=:B1 AND "PRD"."CLASS"='Group25'))

In the above you can see that Operation 2 appears to be the first child of operation 1 and if we applied FCF we would read this as “first use the primary key on products to pick up a single product and use it during a full tablescan of order_lines. Fortunately we can see the separate query blocks and so we ignore FCF. The filter() predicate for operation 1 (again revealed by the explain plan version) tells us that we have a filter subquery so “for each row we read in the tablescan we check if the quantity is 6 and if so we execute the subquery to see if the product is in class 25″. You’ll notice the :B1 in the filter predicate – this is the correlation variable where Oracle passes the order_lines.id_product to the subquery.

Now when the optimizer uses the index (which I had to hint in this case):

NO_UNNEST, PUSH_SUBQ, order_lines index used (quantity)
------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_LINES |   122 | 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORL_QTY     |  5556 | 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID       | PRODUCTS    |     1 | 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN                | PRD_PK      |     1 | 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN  / ORL@MAIN
   2 - MAIN  / ORL@MAIN
   3 - CLASS / PRD@CLASS
   4 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access("ORL"."QUANTITY"=6)
   3 - filter("PRD"."CLASS"='Group25')
   4 - access("PRD"."ID"=:B1)


   1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS")
              */ 0 FROM "PRODUCTS" "PRD" WHERE "PRD"."ID"=:B1 AND
              "PRD"."CLASS"='Group25'))

In this case operation 1 is a “table access by rowid” that appears to have two child operations! Again, though, the query block information tells us that there is a separate query block starting at operation 3. So FCF applies only to operations 1 and 2, and we have to decide how to bring in the query block described by operations 3 and 4.

Checking the predicate information we can see, once again, that there is a “filter() gone empty” at operation 1. And the filter() predicate from explain plan tells us that this corresponds to executing a subquery against the products table. In this case we have simply used the index on (order_lines.quantity) to access only the rows with the correct quantity, and then executed the products subquery for each of those rows.

Finally I’ve created an index on order_lines(quantity, id_product), and forced the optimizer to use it with the following effect:

NO_UNNEST, PUSH_SUBQ, order_lines index used (quantity, id_product)
------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_LINES |   122 | 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORL_QTY_PRD |   122 | 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID      | PRODUCTS    |     1 | 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | PRD_PK      |     1 | 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN  / ORL@MAIN
   2 - MAIN  / ORL@MAIN
   3 - CLASS / PRD@CLASS
   4 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORL"."QUANTITY"=6)
       filter( IS NOT NULL)
   3 - filter("PRD"."CLASS"='Group25')
   4 - access("PRD"."ID"=:B1)


   2 - access("ORL"."QUANTITY"=6)
       filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS")
              */ 0 FROM "PRODUCTS" "PRD" WHERE "PRD"."ID"=:B1 AND
              "PRD"."CLASS"='Group25'))

It would be very easy to read this plan using FCF from top to bottom – and then have to wonder how Oracle managed to find a single product to drive the query. But the query block information rescues us from this error, and we realise that there’s no predicate associatecd with operation 1 so we need to see a way that we can connect the index range scan at operation 2 with the subquery at operations 3 and 4.

Checking the predicate section (and the explan plan predicate) at operation 2 we can see that we execute the subquery as we are running the index range scan of the order_lines index and before we use any rowids to visit the table. So Oracle picks up an index entry (which includes a product id), executes the subquery for that entry’s product id, and only visits the order_lines table if the subquery says the product is in class 25.

Summary Warning

If the optimizer needs to execute a subquery as a filter subquery there are 4 basic patterns you might see in the execution plan. One of them includes an explicit, standalone, FILTER operation; the other three “hide” the filter operation and it appears only as a filter() predicate.

In two of the “hidden filter” cases the shape of the plan is distorted in a way that looks a little strange until you realise that there are two query blocks involved and FCF doesn’t apply across the query blocks; in the third case it would be very easy to read down the plan thinking that FCF was appropriate because the shape of the plan looks perfectly reasonable.

Footnote

It’s worth seeing one more query involving the order_lines table and giving us a fifth execution plan that highlights the ease with which subqueries can cause (temporary) confusion.

select  /*+ qb_name(main) */
        orl.*
from    order_lines orl
where   orl.id_ord = (
                select
                        /*+ qb_name(max) */ 
                        max(ord.id) 
                from    orders ord
        )
/

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     5 |    90 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_LINES |     5 |    90 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORL_PK      |     5 |       |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE                   |             |     1 |     4 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)       | ORD_PK      |     1 |     4 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN / ORL@MAIN
   2 - MAIN / ORL@MAIN
   3 - MAX
   4 - MAX  / ORD@MAX

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORL"."ID_ORD"=)

   2 - access("ORL"."ID_ORD"= (SELECT /*+ QB_NAME ("MAX") */ MAX("ORD"."ID") FROM "ORDERS"
              "ORD"))

As in earlier examples in this note I’ve reported the Predicate Information from the in-memory plan then followed this with the predicate section I got from using explain plan.

The basic shape of the plan suggests a simple FCF, and in this case if you follow that route you’ll get the right interpretation of how Oracle executes the query – but you’ll have got it right for the wrong reason.

The query block information tells you that operations 3 and 4 come from a separate query block so should be examined in isolation from operations 1 and 2, which form the main query block. To combine the query blocks we then note that operation 2 (the index range scan) uses the subquery as an access predicate, not a filter predicate, so the subquery has to execute first to supply a value for the main query to use as a driving value for the index range scan.

When you have to handle subqueries in execution plans make sure you check whether they operate as filter subqueries or access subqueries before you try to interpret how the interact with the rest of the plan.

 

5 Comments »

  1. Hi Jonathan,

    I was just looking at that line and recalled that the explain plan does not have that problem, i.e. it shows the whole subquery (ditto with INTERNAL_FUNCTION):

    1 - filter(>=10)
    

    Then, I tried to reconstruct the query that produced the plan with that line: ‘1 – filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME (“CLASS”)’ and found that Explain Plan reverses the order of hints and discards any “invalid” ones, which I was not really aware of. The explain plan of this:

    explain plan for
    select  /*+ 
                    qb_name(main) 
                    full(orl)
            */
            orl.*
    from    order_lines     orl
    where
            orl.quantity = 6
    and     orl.id_product in (
                    select  /*+ 
                                    not_a_hint
                                    qb_name(class)
                                    not_a_hint_as_well
                                    no_unnest
                                    index(no_such_index_really)
                                    no_push_subq
                                    a_comment
                                    push_subq
                            */
                            prd.id
                    from    products prd
                    where   prd.class = 'Group25'
            )
    ;
    select * from dbms_xplan.display(format=> 'hint_report');
    

    Has this – the order of hints is reversed and only used and “unused” hints are kept, so that unresolved and syntax error hints are removed (the “unused” refers to how Oracle reports that hint in the Hint Usage Report):

       1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_PUSH_SUBQ NO_UNNEST QB_NAME
                  ("CLASS") */ 0 FROM "PRODUCTS" "PRD" WHERE "PRD"."ID"=:B1 AND
                  "PRD"."CLASS"='Group25'))
    

    The corresponding hint report is below:

    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 10 (U - Unused (1), N - Unresolved (1), E - Syntax error (3))
    ----------------------------------------------------------------------------------------
    
       1 -  MAIN
               -  qb_name(main)
    
       2 -  MAIN / ORL@MAIN
               -  full(orl)
    
       3 -  CLASS
             N -  index(no_such_index_really)
             U -  push_subq / hint conflicts with another in sibling query block
             E -  a_comment
             E -  not_a_hint
             E -  not_a_hint_as_well
               -  no_push_subq
               -  no_unnest
               -  qb_name(class)
    

    Just a thing that I learnt today.

    Regards,
    Mikhail.

    Comment by Mikhail Velikikh — May 1, 2020 @ 4:30 pm BST May 1,2020 | Reply

    • Mikhail,

      Thanks for the comment.
      It prompted me to look at what dbms_utility.expand_sql() would produce with that input – and it produces an SQL statement with the “non-hints” removed and the hints reversed (though it’s only the hints in the subquery that are reversed):

      
      SELECT /*+ QB_NAME ("MAIN") FULL ("A1") */ "A1"."ID_ORD" "ID_ORD","A1"."ID_LINE" "ID_LINE","A1"."ID_PRODUCT" "ID_PRODUCT
      ","A1"."QUANTITY" "QUANTITY","A1"."LINE_VALUE" "LINE_VALUE" FROM "TEST_USER"."ORDER_LINES" "A1" WHERE "A1"."QUANTITY"=6
      AND "A1"."ID_PRODUCT"=ANY (SELECT /*+ PUSH_SUBQ NO_PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS") */ "A2"."ID" "ID" FROM "TEST_US
      ER"."PRODUCTS" "A2" WHERE "A2"."CLASS"='Group25')
      
      

      Which version of Oracle are you using – it’s Hint Report is more informative than the 19.3 version which gives me:

      
      Hint Report (identified by operation id / Query Block Name / Object Alias):
      Total hints for statement: 5 (U - Unused (1), N - Unresolved (1), E - Syntax error (3))
      ---------------------------------------------------------------------------------------
         3 -  CLASS
               N -  index(no_such_index_really)
               U -  push_subq / hint conflicts with another in sibling query block
               E -  a_comment
               E -  not_a_hint
               E -  not_a_hint_as_well
      
      

      The comment about “sibling” query block is interesting – it suggests that the optimizer can notice that hints in different query blocks contradict each other.

      Comment by Jonathan Lewis — May 1, 2020 @ 5:02 pm BST May 1,2020 | Reply

  2. Jonathan,

    I have done it on 19.5 and the output was from (I believe it has the same output on 12.2.0.1.190416):

    explain plan for
    select  /*+ 
                    qb_name(main) 
                    full(orl)
            */
            orl.*
    from    order_lines     orl
    where
            orl.quantity = 6
    and     orl.id_product in (
                    select  /*+ 
                                    not_a_hint
                                    qb_name(class)
                                    not_a_hint_as_well
                                    no_unnest
                                    index(no_such_index_really)
                                    no_push_subq
                                    a_comment
                                    push_subq
                            */
                            prd.id
                    from    products prd
                    where   prd.class = 'Group25'
            )
    ;
    select * from dbms_xplan.display(format=> 'hint_report');
    

    Here how it looks – all hints (10) are reported:

    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 10 (U - Unused (1), N - Unresolved (1), E - Syntax error (3))
    ----------------------------------------------------------------------------------------
    
       1 -  MAIN
               -  qb_name(main)
    
       2 -  MAIN / ORL@MAIN
               -  full(orl)
    
       3 -  CLASS
             N -  index(no_such_index_really)
             U -  push_subq / hint conflicts with another in sibling query block
             E -  a_comment
             E -  not_a_hint
             E -  not_a_hint_as_well
               -  no_push_subq
               -  no_unnest
               -  qb_name(class)
    

    Your output is most likely from DBMS_XPLAN.DISPLAY_CURSOR (I used DISPLAY):

    
    select  /*+ 
                    qb_name(main) 
                    full(orl)
            */
            orl.*
    from    order_lines     orl
    where
            orl.quantity = 6
    and     orl.id_product in (
                    select  /*+ 
                                    not_a_hint
                                    qb_name(class)
                                    not_a_hint_as_well
                                    no_unnest
                                    index(no_such_index_really)
                                    no_push_subq
                                    a_comment
                                    push_subq
                            */
                            prd.id
                    from    products prd
                    where   prd.class = 'Group25'
            )
    ;
    select * from dbms_xplan.display_cursor();
    

    The output is this:

    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 5 (U - Unused (1), N - Unresolved (1), E - Syntax error (3))
    ---------------------------------------------------------------------------------------
    
       3 -  CLASS
             N -  index(no_such_index_really)
             U -  push_subq / hint conflicts with another in sibling query block
             E -  a_comment
             E -  not_a_hint
             E -  not_a_hint_as_well
    

    Notice that the ‘total hints for statement: 5’ – that threw me for a loop initially because ‘5’ is not what I would call ‘Total hints for statement’ (I believe it should have been ‘unused hints’).
    That is really coming from format=>’HINT_REPORT_UNUSED’ (probably it is included in the default format, TYPICAL). The other possible formats which I discovered: HINT_REPORT, HINT_REPORT_USED, HINT_REPORT_UNUSED.

    Regards,
    Mikhail.

    Comment by Mikhail Velikikh — May 1, 2020 @ 5:48 pm BST May 1,2020 | Reply

  3. Hi Jonathan,

    This is a very interesting and useful post to not to have misinterpretation on the plans.
    Please keep posting.

    Thank You,
    Satish

    Comment by satish — May 2, 2020 @ 7:33 am BST May 2,2020 | Reply

    • Satish,

      Thank you.
      Another comment I got on the same day as yours asked me to do the same for another plan that’s on my blog – so I’ll do that one some time in the next few days.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — May 3, 2020 @ 8:00 am BST May 3,2020 | 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 )

Google photo

You are commenting using your Google 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.

Powered by WordPress.com.