在早期版本中，如果使用了不正確的提示或提示使用中存在任何語(yǔ)法錯(cuò)誤，則不會(huì)報(bào)告任何錯(cuò)誤。調(diào)整一個(gè)次優(yōu)的執(zhí)行計(jì)劃變得很困難，有時(shí)我們會(huì)想，為什么在指定了索引提示之后，仍然會(huì)發(fā)生一個(gè)完整的表掃描呢！

數(shù)據(jù)庫(kù)沒有記錄或發(fā)出任何錯(cuò)誤消息以獲取它忽略的提示。

但是，現(xiàn)在Oracle 19c中的一項(xiàng)新功能是提示使用情況報(bào)告功能，并且在使用任何DBMS_XPLAN函數(shù)（例如DISPLAY，DISPLAY_CURSOR，DISPLAY_WORKLOAD_REPOSITORY或DISPLAY_SQL_PLAN_BASELINE）時(shí)，默認(rèn)情況下啟用此功能。

讓我們看看oracle19c中提示使用情況報(bào)告的一個(gè)工作示例

在這種情況下，我們指定了正確的INDEX提示，但該提示指向的是表中不存在的索引-我們輸入了錯(cuò)字（MYOBJECT_IND而不是MYOBJECTS_IND）。

在早期版本中，提示輸入錯(cuò)誤不會(huì)被發(fā)現(xiàn)，但是現(xiàn)在提示使用情況報(bào)告清楚地指出了為什么沒有使用提示。

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

我們現(xiàn)在更正了該語(yǔ)句，提示用法報(bào)告指出，這次使用了提示。

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)

這里是一個(gè)錯(cuò)誤使用USENL提示的例子，它被檢測(cè)為語(yǔ)法錯(cuò)誤。

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