在oracle 19c引入了新的format option “hint report”, hint report 显示我们sql文本中使用的hint, report body中会显示hint对应查询块hint是否使用， display_xplan的TYPICAL默认只是显示无效的hint. sql hint是从oracle 7时引入， 用于干涉CBO/RBO优化化器指定执行计划的一种手段， 19c前对于sql中指定了hint，可能因为某些原因sql hint未被使用，但不知道原因， 从19c的hint report很直观的给出sql hint的使用情况和未使用的原因.，如语Syntax errors、Unresolved hints、Conflicting hints、Hints affected by transformations etc.. 如配置了OPTIMIZER_IGNORE_HINTS参数和19c 中的OPTIMIZER_IGNORE_PARALLEL_HINTS或index hint 的index已经rename或drop\invalid.
SQL> select /*+ FULL(tobj) INDEX(BLABLABLA) BLABLABLA(tobj) bb */ count(*) from tobj;
COUNT(*)
----------
285000
SQL> select * from dbms_xplan.display_cursor(format=>'-cost');
PLAN_TABLE_OUTPUT
----------------------------------------------
SQL_ID 7ht5n0h8c87h7, child number 0
-------------------------------------
select /*+ FULL(tobj) INDEX(BLABLABLA) BLABLABLA(tobj) bb */ count(*)
from tobj
Plan hash value: 1381534028
------------------------------------------------------
| Id | Operation | Name | Rows | Time |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| TOBJ | 285K| 00:00:01 |
------------------------------------------------------
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(BLABLABLA)
E - BLABLABLA
上面的hint report显示SEL$1查询块中2个hint不能使用，一个是blablabla()这是语法错误，因为没有这个hint，另一个是index(alias name), 因为该表不是那个alias name, 所有是Unresolved. 别外Full是正确的索引， 而bb当成了注释直接忽略。
SQL> select * from dbms_xplan.display_cursor('7ht5n0h8c87h7',format=>'+HINT_REPORT');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 7ht5n0h8c87h7, child number 0
-------------------------------------
select /*+ FULL(tobj) INDEX(BLABLABLA) BLABLABLA(tobj) bb */ count(*)
from tobj
Plan hash value: 1381534028
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1501 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TOBJ | 285K| 1501 (1)| 00:00:01 |
-------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
N - INDEX(BLABLABLA)
E - BLABLABLA
2 - SEL$1 / TOBJ@SEL$1
- FULL(tobj)
这里hint report显示总共3个hint，其中full() 是正确的hint， 在使用hint_report时才会显示。另外format还有hint_report_unused这是默认，还有hint_report_used发现和hint_report是一样的，也可能是当前版本的缺陷。
另外如plan_table,v$sql_plan的OTHER_XML列也是可以得到hint信息，只是格式不直观，同是也是undocument.
SQL> select extract(xmltype(other_xml),’//hint_usage’) from v$sql_plan where other_xml like ‘%hint_usage%’ and sql_id=’7ht5n0h8c87h7′;
EXTRACT(XMLTYPE(OTHER_XML),’//HINT_USAGE’)
—————————————————————————————————————-
<hint_usage><q><n><![CDATA[SEL$1]]></n><h o=”EM” st=”PE”><x><![CDATA[BLABLABLA]]></x></h><t><f><![CDATA[“TOBJ”@”SEL$1″]]></f><h o=”EM”><x><![CDATA[FULL(tobj)]]></x></h></t><t st=”UR”><h o=”EM”><x><![CDATA[INDEX(BLABLABLA)]]></x></h></t></q></hint_usage>
-
‘<n>’ is the query block name (hint scope can statement ‘<s>’, query block ‘<n>’, or alias ‘<f>’)
-
‘@st’ is PE for parsing syntax error (‘E’ in dbms_xplan note)
-
‘@st’ is UR for unresolved (‘N’ in dbms_xplan note)
-
‘@st’ is ‘NU’ or ‘EU’ for unused (‘U’ in dbms_xplan note)
-
‘<x>’ is the hint text
-
we might get a reason for unused ones in ‘<r>’
10053 TRACE
Hint Report:
Query Block: SEL$1
Syntax Error: BLABLABLA
Table: ("TOBJ"@"SEL$1")
FULL(tobj)
Table:
Unresolved: INDEX(BLABLABLA)
End Hint Report
Dumping Hints
=============
atom_hint=(@=0x7f1639d83668 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("BLABLABLA") )
atom_hint=(@=0x7f1639d81498 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("TOBJ") )
其它例子
SQL> select /*+ first_rows(1) first_rows(2) */ count(*) from tobj;
COUNT(*)
----------
285000
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1381534028
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1501 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TOBJ | 285K| 1501 (1)| 00:00:01 |
-------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - first_rows(1) / conflicting optimizer mode hints
U - first_rows(2) / conflicting optimizer mode hints
17 rows selected.
SQL> select /*+ first_rows(1) first_rows(1) */ count(*) from tobj;
COUNT(*)
----------
285000
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
Plan hash value: 1381534028
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1501 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TOBJ | 285K| 1501 (1)| 00:00:01 |
-------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - first_rows(1) / duplicate hint
16 rows selected.
SQL> select /*+index(tobj idx1) ignore_optim_embedded_hints */ count(*) from tobj;
COUNT(*)
----------
285000
SQL> @x2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 1381534028
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1501 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TOBJ | 285K| 1501 (1)| 00:00:01 |
-------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / TOBJ@SEL$1
U - index(tobj idx1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
16 rows selected.
SQL> alter session set optimizer_ignore_parallel_hints=true;
Session altered.
SQL> select /*+parallel(tobj 8) */ count(*) from tobj;
COUNT(*)
----------
285000
SQL> @x2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 1381534028
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1501 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TOBJ | 285K| 1501 (1)| 00:00:01 |
-------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / TOBJ@SEL$1
U - parallel(tobj 8) / because of _optimizer_ignore_parallel_hints
— over —