X

Insights into Statistics, Query Optimization and the Oracle Optimizer

What is Hint Usage Reporting?

Nigel Bayliss
Product Manager

Trying to figure out why a hint is not being used is often time-consuming and difficult. Luckily, from Oracle Database 19c onwards you can generate a hint usage report.

Here is a very simple example - but it's just the tip of the iceberg...

create table mytab (a number(10) primary key);
insert into mytab values (1);
commit;
exec dbms_stats.gather_table_stats(null,'mytab');

explain plan for select /*+ FULLL(t) */ * from mytab t;      /* The word FULL 
                                                                doesn't have 
                                                                three "L"'s !! */ 

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'TYPICAL'));

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));

Here's some example output. Hint - take a look at the bottom of the report. :-)

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  INDEX STORAGE FAST FULL SCAN| SYS_C0010006659 |
--------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
 
   1 -  SEL$1
         E -  FULLL

Here is what we really want to see:

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 -  SEL$1 / T@SEL$1
           -  FULL(t)

Join the discussion

Comments ( 5 )
  • Rajeshwaran, Jeyabal Thursday, January 17, 2019
    Thanks. Any idea when 19c documentation will be available ?

    https://docs.oracle.com/en/database/oracle/oracle-database/index.html
  • Nigel Bayliss Thursday, January 17, 2019
    It won't be long. I can't be more specific than that I'm afraid.
  • Iudith Mentzel Thursday, January 17, 2019
    Thanks for notifying us :)

    Unfortunately, DBMS_XPLAN.DISPLAY_CURSOR is still not available in LiveSQL ... already since the first 18c ...

    Second, 18c XE for Windows is also still not available :(

    My personal opinion is that this "yearly rate" for releasing new versions is a little bit too high for keeping the path with all what is required, documentation being one good example, and 18c XE another one.
  • Quanwen Zhao Friday, January 18, 2019
    Hi Nigel,

    I'm very excited for hearing from you and I've also tested your simple demo on LiveSQL now,but I have noticed some detail that even if I don't add this format 'hint_report'(just has 'typical' or 'basic') on calling dbms_xplan.display and I can still output hint report when I write a wrong hint (if being right hint and not, this time has added 'hint_report').

    On the other hand about the online docs for 19c hasn't been released, and I'm looking forward to publishing a bit earlier.

    Best Regards
    Quanwen Zhao
  • Nigel Bayliss Friday, January 18, 2019
    Hi - yes. If you supply no format, it defaults to TYPICAL.
    Regards,
    Nigel.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.