Now and then I use a hint to change the behavior of a query. Before 19c, it was quite tricky to understand why the hint sometimes wasn’t used by the optimizer. By looking at the explain plan, one couldn’t even see if there was a syntax error in the hint.

As of 19c Oracle have introduced “Hint Usage Reports” which might give one a better idea of why the hint was used or not.

Let’s try a query with a hint that is misspelled(FULLL instead of FULL).

explain plan for select /*+ FULLL(d) */ 1 from dual d;
select * from table(dbms_xplan.display);

Plan hash value: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
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 one can cleary see that the hint have been ignored because of the syntax error. This occurs when we have a typo or an invalid argument.

Let’s try to do an index hint on the dual table.

explain plan for select /*+ INDEX(d dual_idx) */ 1 from dual d;
select * from table(dbms_xplan.display);

Plan hash value: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   1 -  SEL$1 / D@SEL$1
         U -  INDEX(d dual_idx)

This what Oracle calls an unresolved hints. There is no typo and the argument is not invalid. It is a mystery in other words 🙂

More about this new feature in 19c you may find here

Over & Out