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 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');
The default format (TYPICAL) shows you hints that are unused:
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')); -------------------------------------------------------- | 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
If you want to see the hints that are used and unused, add +HINT_REPORT to BASIC, TYPICAL and so on:
explain plan for select /*+ FULL(t) FULL(w) */ * from mytab t; 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: 2 (N - Unresolved(1)) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - SEL$1 N - FULL(w) 1 - SEL$1 / "T"@"SEL$1" - FULL(t)
'Unused' or 'used' hints alone can be shown using +HINT_REPORT_UNUSED and +HINT_REPORT_USED. If you use format=>'ALL', you will see both used and unused hints.
It is documented in the SQL Tuning Guide.