In earlier releases no error was reported if an incorrect hint was used or if there was any syntax error in the hint usage. Tuning a sub-optimal execution plan became difficult and sometimes we are left wondering why a full table scan is still occurring when an INDEX hint has been specified!
The database did not record or issue any error messages for hints that it ignores.
But now a new feature in Oracle 19c is the Hint Usage Report feature and this is enabled by default when using any DBMS_XPLAN functions like DISPLAY, DISPLAY_CURSOR, DISPLAY_WORKLOAD_REPOSITORY or DISPLAY_SQL_PLAN_BASELINE.
Let us look at a worked example of Hint Usage Report in Oracle 19c.
In this case we specify the correct INDEX hint, but the hint refers to an index which does not exist on the table – we have made a typo (MYOBJECT_IND instead of MYOBJECTS_IND).
In earlier releases the hint typo would have gone undetected, but now the Hint Usage Report clearly indicates why the hint was not used.
SQL> select /*+ INDEX (MYOBJECTS_19C,MYOBJECT_IND) */ distinct object_type from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
SYNONYM
...
...
TABLE
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8xcj1mg0ht48d, child number 0
-------------------------------------
select /*+ INDEX (MYOBJECTS_19C,MYOBJECT_IND) */ distinct object_type
from myobjects_19c where owner='SYS'
Plan hash value: 1625058500
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 624 (100)| |
| 1 | HASH UNIQUE | | 26 | 2054 | 624 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| MYOBJECTS_19C | 95948 | 7402K| 621 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / MYOBJECTS_19C@SEL$1
U - INDEX (MYOBJECTS_19C,MYOBJECT_IND) / index specified in the hint doesn't exist
We now correct the statement and the hint usage report indicates that this time the hint has been used.
SQL> select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */ distinct object_type
from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
SYNONYM
...
...
TABLE
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor (format=>'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rmk7qfazvuju, child number 0
-------------------------------------
select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */ distinct object_type
from myobjects_19c where owner='SYS'
Plan hash value: 3518837258
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1019 (100)| |
| 1 | HASH UNIQUE | | 26 | 2054 | 1019 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MYOBJECTS_19C | 95948 | 7402K| 1016 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MYOBJECTS_IND | 95948 | | 101 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
2 - SEL$1 / MYOBJECTS_19C@SEL$1
- INDEX (MYOBJECTS_19C,MYOBJECTS_IND)
Here in an example of incorrect usage of the USE_NL hint which has been detected as a syntax error.
SQL> select /*+ USE_NL */ distinct object_type
from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
SYNONYM
...
...
TABLE
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 35s4c53z8x1g8, child number 0
-------------------------------------
select /*+ USE_NL */ distinct object_type from myobjects_19c where
owner='SYS'
Plan hash value: 1625058500
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 624 (100)| |
| 1 | HASH UNIQUE | | 26 | 2054 | 624 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| MYOBJECTS_19C | 95948 | 7402K| 621 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - USE_NL
