Insights into Statistics, Query Optimization and the Oracle Optimizer
What is Hint Usage Reporting?
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 )
https://docs.oracle.com/en/database/oracle/oracle-database/index.html
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.
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
Regards,
Nigel.