Oracle Scratchpad

January 17, 2019

Hint Reports

Filed under: dbms_xplan,Execution plans,Hints,Oracle — Jonathan Lewis @ 9:59 am GMT Jan 17,2019

Nigel Bayliss has posted a note about a frequently requested feature that has now appeared in Oracle 19c – a mechanism to help people understand what has happened to their hints.  It’s very easy to use, it’s just another format option to the “display_xxx()” calls in dbms_xplan; so I thought I’d run up a little demonstration (using an example I first generated 18 years and 11 versions ago) to make three points: first, to show the sort of report you get, second to show you that the report may tell you what has happened, but that doesn’t necessarily tell you why it has happened, and third to remind you that you should have stopped using the /*+ ordered */ hint 18 years ago.

I’ve run the following code on livesql:


rem
rem     Script:         c_ignorehint.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2001
rem


drop table ignore_1;
drop table ignore_2;

create table ignore_1
nologging
as
select
        rownum          id,
        rownum          val,
        rpad('x',500)   padding
from    all_objects
where   rownum <= 3000
;

create table ignore_2
nologging
as
select
        rownum          id,
        rownum          val,
        rpad('x',500)   padding
from    all_objects
where   rownum <= 500
;

alter table ignore_2
add constraint ig2_pk primary key (id);


explain plan for
update
        (
                select
                        /*+
                                ordered
                                use_nl(i2)
                                index(i2,ig2_pk)
                        */
                        i1.val  val1,
                        i2.val  val2
                from
                        ignore_1        i1,
                        ignore_2        i2
                where
                        i2.id = i1.id
                and     i1.val <= 10
        )
set     val1 = val2
;

select * from table(dbms_xplan.display(null,null,'hint_report'));

explain plan for
update
        (
                select
                        /*+
                                use_nl(i2)
                                index(i2,ig2_pk)
                        */
                        i1.val  val1,
                        i2.val  val2
                from
                        ignore_1        i1,
                        ignore_2        i2
                where
                        i2.id = i1.id
                and     i1.val <= 10
        )
set     val1 = val2
;

select * from table(dbms_xplan.display(null,null,'hint_report'));

As you can see I’ve simply added the format option “hint_report” to the call to dbms_xplan.display(). Before showing you the output I’ll just say a few words about the plans we might expect from the two versions of the update statement.

Given the /*+ ordered */ hint in the first statement we might expect Oracle to do a full tablescan of ignore_1 then do a nested loop into ignore_2 (obeying the use_nl() hint) using the (hinted) ig2_pk index. In the second version of the statement, and in the absence of the ordered hint, it’s possible that the optimizer will still use the same path but, in principle, it might find some other path.

So what do we get ? In order here are the two execution plans:


Plan hash value: 3679612214
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |          |    10 |   160 |   111   (0)| 00:00:01 |
|   1 |  UPDATE                               | IGNORE_1 |       |       |            |          |
|*  2 |   HASH JOIN                           |          |    10 |   160 |   111   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| IGNORE_2 |   500 |  4000 |    37   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                   | IG2_PK   |   500 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS STORAGE FULL          | IGNORE_1 |    10 |    80 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I2"."ID"="I1"."ID")
   5 - storage("I1"."VAL"<=10)
       filter("I1"."VAL"<=10)
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$DA9F4B51
           -  ordered
 
   3 -  SEL$DA9F4B51 / I2@SEL$1
         U -  use_nl(i2)
           -  index(i2,ig2_pk)




Plan hash value: 1232653668
 
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |          |    10 |   160 |    76   (0)| 00:00:01 |
|   1 |  UPDATE                       | IGNORE_1 |       |       |            |          |
|   2 |   NESTED LOOPS                |          |    10 |   160 |    76   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |          |    10 |   160 |    76   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL | IGNORE_1 |    10 |    80 |    74   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | IG2_PK   |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| IGNORE_2 |     1 |     8 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - storage("I1"."VAL"<=10)
       filter("I1"."VAL"<=10)
   5 - access("I2"."ID"="I1"."ID")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   5 -  SEL$DA9F4B51 / I2@SEL$1
           -  index(i2,ig2_pk)
           -  use_nl(i2)

As you can see, the “Hint Report” shows us how many hints have been seen in the SQL text, then the body of the report shows us which query block, operation and table (where relevant) each hint has been associated with, and whether it has been used or not.

The second query has followed exactly the plan I predicted for the first query and the report has shown us that Oracle noted, and used, the use_nl() and index() hints to access table ignore2, deciding for itself to visit the tables in the order ignore_1 -> ignore_2, and doing a full tablescan on ignore_1.

The first query reports three hints, but flags the use_nl() hint as unused. (There is (at least) one other flag that could appear against a hint – “E” for error (probably syntax error), so we can assume that this hint is not being ignored because there’s something wrong with it.) Strangely the report tells us that the optimizer has used the ordered hint but we can see from the plan that the tables appear to be in the opposite order to the order we specified in the from clause, and the chosen order has forced the optimizer into using an index full scan on ig2_pk because it had to obey our index() hint.  Bottom line – the optimizer has managed to find a more costly plan by “using but apparently ignoring” a hint that described the cheaper plan that we would have got if we hadn’t used the hint.

Explanation

Query transformation can really mess things up and you shouldn’t be using the ordered hint.

I’ve explained many times over the years that the optimizer evaluates the cost of an update statement by calculating the cost of selecting the rowids of the rows to be updated. In this case, which uses an updatable join view, the steps taken to follow this mechanism this are slightly more complex.  Here are two small but critical extracts from the 10053 trace file (taken from an 18c instance):


CVM:   Merging SPJ view SEL$1 (#0) into UPD$1 (#0)
Registered qb: SEL$DA9F4B51 0x9c9966e8 (VIEW MERGE UPD$1; SEL$1; UPD$1)

...

SQE: Trying SQ elimination.
Query after View Removal
******* UNPARSED QUERY IS *******
SELECT
        /*+ ORDERED INDEX ("I2" "IG2_PK") USE_NL ("I2") */
        0
FROM    "TEST_USER"."IGNORE_2" "I2",
        "TEST_USER"."IGNORE_1" "I1"
WHERE   "I2"."ID"="I1"."ID"
AND     "I1"."VAL"<=10


The optimizer has merged the UPDATE query block with the SELECT query block to produce a select statement that will produce the necessary plan (I had thought that i1.rowid would appear in the select list, but the ‘0’ will do for costing purposes). Notice that the hints have been preserved as the update and select were merged but, unfortunately, the merge mechanism has reversed the order of the tables in the from clause. So the optimizer has messed up our select statement, then obeyed the original ordered hint!

Bottom line – the hint report is likely to be very helpful in most cases but you will still have to think about what it is telling you, and you may still have to look at the occasional 10053 to understand why the report is showing you puzzling results. You should also stop using a hint that was replaced by a far superior hint more than 18 years ago – the ordered hint in my example should have been changed to /*+ leading(i1 i2) */ in Oracle 9i.

13 Comments »

  1. Thank you. I still see some ordered hints in old code.

    Comment by Rich (@oracle_man) — January 17, 2019 @ 3:14 pm GMT Jan 17,2019 | Reply

  2. Jonathan, let me add just a little remark. In Oracle 9i (even in 9iR2), it was not possible to use /*+ LEADING(i1 i2) */ hint. At 9i times, it was allowed to use only one argument ( /*+ LEADING(i1) */). Multiple arguments have been officialy documented in 10gR1 or 10gR2. Unfortunately I have no more 9i database to proof again with by old testcase.

    Regards
    Pavol Babel

    Comment by Pavol Babel — January 17, 2019 @ 11:12 pm GMT Jan 17,2019 | Reply

    • I have found it also in 9i documentation https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#5730

      Comment by Pavol Babel — January 17, 2019 @ 11:25 pm GMT Jan 17,2019 | Reply

    • Pavol,

      Thanks, you’re right, of course, and adding the second table to the hint in 9i would have been a syntax error resulting in the hint ceasing to be a hint. That means people have only had about 15 years to eliminate the ordered hint from their SQL ;)

      Somewhere I’ve probably published a noted about how Oracle finally finished coding up the leading() hint in 10g, but I haven’t been able to find it yet; though it’s not really until you can also get the qb_name() hint in place that you can guarantee to change every ordered hint into a matching leading() hint.

      Comment by Jonathan Lewis — January 18, 2019 @ 7:58 am GMT Jan 18,2019 | Reply

  3. Hi Jonathan,

    Thanks for sharing this good post after the New Year. I’ve also tested your SQL on livesql and if using this hint ‘leading (i2 i1)’ to replace ‘ordered’ and its explain plan is the same to your first one (after using ‘leading (i1 i2) to replace ‘ordered’ and its explain plan is the same to your second one).

    Here is my some confusion – why is the cost of using ‘hash join’ is 111 and adopting ‘nested loops’ is only 76? Commonly ‘hash join’ is faster than ‘nested loops’ when two tables’ volume is not too much (‘ignore_1’ is 3000 and ‘ignore_2’ is 500).

    Best Regards
    Quanwen Zhao

    Comment by Quanwen Zhao — January 18, 2019 @ 7:58 am GMT Jan 18,2019 | Reply

    • Quanwen Zhao,

      “why is the cost of using ‘hash join’ is 111 and adopting ‘nested loops’ is only 76?”

      The general answer to a question like this is that the optimizer is TRYING to estimate which would be faster but doesn’t have all the information it needs to make an accurate estimate. In theory “cost” = “time”, in practice there are lots of cases where the optimizer fails to meet that ideal.

      In this particular case, though, the answer comes from the fact that I forced Oracle into a bad plan. For small tables like this you would generally expect the optimizer to do a tablescan of ignore_2 to do the hash join, but my hinting has force Oracle to use an index full scan then access by rowid – and the optimizer has given than operation a total cost of 37.

      The total cost of the hash join is then 37 (cost of ignore_2 data) + 74 (cost of ignore_1 tablescan) + {cost of hash operation} = 111; and when you do the arithmetic it turns out that (thanks fo rounding errors in the output) the hash operation itself seems to have a cost of zero.

      Comment by Jonathan Lewis — January 18, 2019 @ 8:09 am GMT Jan 18,2019 | Reply

  4. Jonathan ;)

    I understood your explanation now, and if I don’t add ‘ordered’ or ‘LEADING(i1 i2)’ hint and then manually replace the location of two table on FROM clause (such as ‘ignore_2 i2, ignore_1 i1’) and why the explain plan is as follows (rather than first scanning ‘ignore_2’ and next ‘ignore_1’?),

    explain plan for
    update
            (
                    select
                            i1.val  val1,
                            i2.val  val2
                    from
                            ignore_2        i2,
                            ignore_1        i1
                    where
                            i2.id = i1.id
                    and     i1.val <= 10
            )
    set     val1 = val2
    ;
    
    Plan hash value: 1232653668
    
    ------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |          |    10 |   160 |    65   (0)| 00:00:01 |
    |   1 |  UPDATE                       | IGNORE_1 |       |       |            |          |
    |   2 |   NESTED LOOPS                |          |    10 |   160 |    65   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS               |          |    10 |   160 |    65   (0)| 00:00:01 |
    |*  4 |     TABLE ACCESS FULL         | IGNORE_1 |    10 |    80 |    63   (0)| 00:00:01 |
    |*  5 |     INDEX UNIQUE SCAN         | IG2_PK   |     1 |       |     0   (0)| 00:00:01 |
    |   6 |    TABLE ACCESS BY INDEX ROWID| IGNORE_2 |     1 |     8 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("I1"."VAL"<=10)
       5 - access("I2"."ID"="I1"."ID")
    

    Best Regards
    Quanwen Zhao

    Comment by Quanwen Zhao — January 18, 2019 @ 9:22 am GMT Jan 18,2019 | Reply

    • Quanwen Zhao,

      In the absence of any hints the optimizer will simply have worked out that the nested loop in that order using that index is cheaper than either order for the hash join with two tablescans.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — January 18, 2019 @ 9:39 am GMT Jan 18,2019 | Reply

      • Thanks, Jonathan

        As you can see from previous explain plan my attached, ‘ignore_1’ has been as driving table but it’s obvious that my sql code’s first FROM clause is table ‘ignore_2’. Why doesn’t ignore_2 join to ignore_1 (ignore_2 -> ignore_1)?

        Best Regards
        Quanwen Zhao

        Comment by Quanwen Zhao — January 18, 2019 @ 1:34 pm GMT Jan 18,2019 | Reply

        • Quanwen Zhao,

          There’s never any guarantee that the order of the tables in the FROM clause should be used as the join order that Oracle uses to run the query – unless you’ve used the ordered hint (and even then, as I’ve shown in this example, that hint doesn’t always manage to leave you with a path that uses the join order you wanted).

          Comment by Jonathan Lewis — January 18, 2019 @ 1:38 pm GMT Jan 18,2019

  5. Jonathan,

    This time I eventually comprehend the hint ‘ordered’ or ‘leading’ by your simply SQL demo.
    Very appreciate to you sincerely for guiding!

    Best Regards
    Quanwen Zhao

    Comment by Quanwen Zhao — January 18, 2019 @ 2:13 pm GMT Jan 18,2019 | Reply

  6. Hi,

    Seeing that Oracle rewrites various hints already :
    – USE_NL(a b) -> USE_NL(a)/USE_NL(b), which was a rude awakening to me since I had always assumed the list works like LEADING, but without having to pin all other tables in the statement to specific positions as well, same for USE_HASH
    – INDEX(t Ix_Col1_Col2) -> INDEX(t (Col1 Col2)), i.e. resolving index names to their column lists at parse time
    it should have all along rewritten
    – ORDERED -> LEADING(t1…tx) in the order of the tables in the original statement and the rewriting pit-falls could have been avoided
    or maybe add ORDERED((t2 t6) t4) for partial ordering (brakets means must be direct neighbours, otherwise there can be unmentioned tables stuffed between)

    regards,

    Comment by Racer I. — February 15, 2019 @ 10:14 am GMT Feb 15,2019 | Reply

    • Racer I.

      There’s always the problem of backwards compatibility with ideas like “rewrite /*+ ordered */ to mean /*+ leading(…) */”
      At the very least there would be some existing queries where the “obvious” translation would result in changes in execution plan that could have a massive impact on performance.
      My personal opinion is that Oracle should have deprecated then desupported the ordered hint as soon as they had completed the code for the leading() hint.

      I still have to remind people about the proper use of the use_nl() (and use_hash()) hints from time to time/ According to the following blog note, it’s not entirely surprising a lot of people had the incorrect interpretation hard-writed into their brains – the manuals didn’t explain in properly until 10g: https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/

      Talking of manuals, there’s a lovely example of inertia around the ordered/leading hints in the 10.1 reference manual ( https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm ) where is has a line:
      “In Example 17-1, the ORDERED hint specifies the exact join order to be used; the join methods to be used on the different tables are also specified.”
      But the example doesn’t contain an ordered hint, it uses the leading() hint!

      Comment by Jonathan Lewis — February 16, 2019 @ 11:20 am GMT Feb 16,2019 | 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.