Oracle 19C Hints Report

Oracle 19C xplan支持hints report，方便确认SQL中hint的是否生效、使用情况。
如full全表扫的hint：
[code]SQL> col XPLAN for a100

SQL> explain plan for select /*+ full(a) */* from par_hybrid_tab a;

Explained.

SQL> SELECT dbms_xplan.display_plan(format=>'typical +HINT_REPORT') as XPLAN from dual;

XPLAN
--------------------------------------------------------------------------------------------
Plan Hash Value : 1973910200

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8415 | 143055 | 30 | 00:00:01 |
| 1 | PARTITION RANGE ALL | | 8415 | 143055 | 30 | 00:00:01 |
| 2 | TABLE ACCESS HYBRID PART FULL | PAR_HYBRID_TAB | 8415 | 143055 | 30 | 00:00:01 |
| 3 | TABLE ACCESS FULL | PAR_HYBRID_TAB | | | | |
--------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
-------------------------------------------------------------------------------

2 - SEL$1 / A[/code]
如写错hint，会提示错误：
[code]SQL> explain plan for select /*+ index(noindex) */* from par_hybrid_tab;

Explained.

SQL> SELECT dbms_xplan.display_plan(format=>'typical +HINT_REPORT') as XPLAN from dual;

XPLAN
--------------------------------------------------------------------------------------------
Plan Hash Value : 1973910200

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8415 | 143055 | 30 | 00:00:01 |
| 1 | PARTITION RANGE ALL | | 8415 | 143055 | 30 | 00:00:01 |
| 2 | TABLE ACCESS HYBRID PART FULL | PAR_HYBRID_TAB | 8415 | 143055 | 30 | 00:00:01 |
| 3 | TABLE ACCESS FULL | PAR_HYBRID_TAB | | | | |
--------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
-------------------------------------------------------------------------------[/code]
可以根据自己的需求，灵活使用该特性