Oracle 19c新功能提示使用情况报告

在早期版本中，如果使用了不正确的提示或提示使用中存在任何语法错误，则不会报告任何错误。调整一个次优的执行计划变得很困难，有时我们会想，为什么在指定了索引提示之后，仍然会发生一个完整的表扫描呢！

数据库没有记录或发出任何错误消息以获取它忽略的提示。

但是，现在Oracle 19c中的一项新功能是提示使用情况报告功能，并且在使用任何DBMS_XPLAN函数（例如DISPLAY，DISPLAY_CURSOR，DISPLAY_WORKLOAD_REPOSITORY或DISPLAY_SQL_PLAN_BASELINE）时，默认情况下启用此功能。

让我们看看oracle19c中提示使用情况报告的一个工作示例

在这种情况下，我们指定了正确的INDEX提示，但该提示指向的是表中不存在的索引-我们输入了错字（MYOBJECT_IND而不是MYOBJECTS_IND）。

在早期版本中，提示输入错误不会被发现，但是现在提示使用情况报告清楚地指出了为什么没有使用提示。

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

我们现在更正了该语句，提示用法报告指出，这次使用了提示。

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)

这里是一个错误使用USENL提示的例子，它被检测为语法错误。

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