Oracle 19c 新特性 —— Hint Usage Reports

新特性作用

在添加hint时，可能会遇到hint被忽略无法改变sql执行计划的情况，但是hint被忽略的原因有时人可能难以发现。19c引入了Hint Usage Reports，显示使用和忽略了哪些hint，忽略的原因是什么（比如语法错误、无法解析的Hint、hint冲突、配置了OPTIMIZER_IGNORE_HINTS参数、配置了OPTIMIZER_IGNORE_PARALLEL_HINTS参数、index hint 的index已rename或drop\invalid等）。

用法

用法很简单，用的还是查看执行计划的dbms_xplain.display_cursor。19c中，dbms_xplain.display_cursor默认的TYPICAL模式将报告hint失效的原因，也可以自行指定模式。

The DBMS_XPLAN formats are:

• +HINT_REPORT_USED -- 显示使用的hints
• +HINT_REPORT_UNUSED -- 显示未使用和语法错误的hint，默认的format=TYPICAL包含该flag
• +HINT_REPORT -- 包含上面2个flag的内容，当format=ALL时自动包含该flag

As an example, the following formats are the same

format=>'ALL -HINT_REPORT_UNUSED'
format=>'BASIC +HINT_REPORT_USED'
select * from dbms_xplan.display_cursor('3ps01tc9mxuhd',format=>'+HINT_REPORT');

示例

SQL> create table test as select * from dba_objects;

Table created.

SQL> set feedback on sql_id  
SQL> select /*+ full(test) index(nonexists) nonfunc(test) */ object_name from test where object_id=123;

OBJECT_NAME
--------------------------------------------------------------------------------
I_REFCON2

1 row selected.

SQL_ID: aqh25km72pgj3

SQL> select * from dbms_xplan.display_cursor(sql_id=>'aqh25km72pgj3');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  aqh25km72pgj3, child number 0
-------------------------------------
select /*+ full(test) index(nonexists) nonfunc(test) */ object_name from test where object_id=123

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   110 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     4 |   316 |   110   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=123)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  index(nonexists)
         E -  nonfunc

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Hint Report，它告诉我们对于执行计划Id=1这行，查询块SEL$1有2个未使用的提示：1个是语法错误(E)，因为nonfunc不是一个提示。另一个是index()，它虽然是一种有效的语法，但是它提到了一个不在查询中的别名（nonexists)，而无法被解析(N)。

下面再看一个忽略正常提示的Hint Report示例：

SQL> alter session set optimizer_ignore_hints=true;

Session altered.

SQL_ID: 7vyv9z1ycpdb6
SQL> select /*+ full(test) */ object_name from test where object_id=123;

OBJECT_NAME
--------------------------------------------------------------------------------
I_REFCON2

1 row selected.

SQL_ID: 1azqdh1xrf33w

SQL> select * from dbms_xplan.display_cursor(sql_id=>'1azqdh1xrf33w');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1azqdh1xrf33w, child number 0
-------------------------------------
select /*+ full(test) */ object_name from test where object_id=123

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   110 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     4 |   316 |   110   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=123)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1 / TEST@SEL$1
         U -  full(test) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

29 rows selected.

最后一部分Hint Report告诉我们有1个未使用的提示，是因为IGNORE_OPTIM_EMBEDDED_HINTS设置被拒绝了

参考：

19c 新特性： Hint Usage Reports详解_Enmotech的博客-CSDN博客

https://medium.com/@FranckPachot/oracle-19c-hint-usage-reporting-345563a461f0