Oracle19c新特性: hint report

在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 —