Insights into Statistics, Query Optimization and the Oracle Optimizer

LiveSQL Now Live on Oracle Database 19c

Nigel Bayliss
Product Manager

Since LiveSQL is running on Oracle Database 19c, now is the time to be one of the first to take a look at the new Hint Usage Reporting feature.

Trying to figure out why a hint is not being used is not on my top-100-fun-things-to-do list. Luckily, there's now a way to do it much more easily than before.

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);
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 !! */ 



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:


| Id  | Operation         | Name  |
|   0 | SELECT STATEMENT  |       |

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

   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 ?

  • 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.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.