19c 新特性: Hint Usage Reports详解

老张拉呱 2019-03-06
911



老张拉呱:thomas zhang,甲骨文云平台事业部资深技术顾问,2008年加入甲骨文公司数据库咨询部门,10+年甲骨文解决方案咨询支持经验,资深系统工程师、Oracle OCM认证专家,具有丰富的Cloud IT项目经验。目前主要负责甲骨文中国北方区(医院/卫生、交通、制造、教育、政府、证券、媒体、金融、零售等行业)客户的数据库、中间件、IaaS/PaaS、集成系统等相关技术解决方案咨询工作。


签名:我为人人,人人为我,三人行,必有我师

新浪微博: http://weibo.com/tomszrp

Oracle Hints 是 SQL语句中的注释机制,用来传递指令给 Oracle 数据库优化器,告诉优化器按照我们的告诉它的方式 选择执行计划,除非存在某种条件阻止优化器这样做,典型的比如设置了 OPTIMIZER_IGNORE_HINTS、OPTIMIZER_IGNORE_PARALLEL_HINTS 参数或发生了查询转换甚至 Hint 冲突,那么可能会导致 Hint 失效。

 

最早在 Oracle Database 7 中就引入了 Hint,那时候当优化器生成执行计划时,用户几乎没有什么可以求助的资源。不像现在 Oracle 数据库中提供了丰富的优化工具,典型的比如SQL Tuning Advisor、SQL plan management以及SQL Performance Analyzer 等等,从而可以帮助我们解决优化器无法解决的性能问题。


Oracle 建议尽量使用这些辅助的调优工具而不是 Hint,一是这些调优工具比 Hint 更加系统、全面、有效,二是当用了很多 Hint 后,在数据和数据库环境发生变化时,可能带来新的问题,也就是说使用 Hint 可能能带来短期好处,但不会长期持续提高性能。

 

所以,常规的使用 Hint 的场景应该是:当收集了相关的统计信息后,在不用 hints 使用 explain plan 评估了执行计划以后才应该谨慎使用。对于一些不得不用的在实际环境中,也可能经常面临这样的问题:比如语法不正确或 Hint 使用不当,系统不会给我们任何错误提示。所以很多时候可能也就“稀里糊涂”的、一顿“猛药”下去,"病"好了,哈哈。

 

随着 Oracle Database 19c 的发布,优化器有一个新的重要的增强:Hint Usage report,它可以报告 hint 的使用情况,该报告包括所有优化器提示的状态,包括 PARALLEL 和 INMEMORY。


Hint Usage report 会显示使用和忽略了哪些提示,并通常解释为什么忽略提示。忽略提示最常见的原因有语法错误(Syntax errors)、不正确的Hint(Unresolved hints)、冲突(Conflicting hints)、受影响的hint(Hints affected by transformations)等,我这里通过dbms_xplan来快速体验一下。


DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id            IN  VARCHAR2  DEFAULT  NULL,

   cursor_child_no   IN  NUMBER    DEFAULT  0, 

   format            IN  VARCHAR2  DEFAULT  'TYPICAL');

dbms_xplain.display_cursor大家应该经常用,不再多少。19c中新增加的flag:

       +HINT_REPORT_USED             显示使用的hints

       +HINT_REPORT_UNUSED         显示未使用和语法错误的hint, 缺省format=TYPICAL包含该flag,也就是会报告无效的Hint -- Invalid Hint

       +HINT_REPORT                      包含上面2个flag的内容,当format=ALL时自动包含该flag.

 

下面简单看个示例:


PDB1@ZRP>set feedback on sql_id      

PDB1@ZRP>select *+ full(test) index(nonexists) nonfunc(test) */ object_name from test where object_id=123;

OBJECT_NAME

-----------------------------------------------------------------------------------------------------------

OPQTYPE$


1 row selected.


SQL_ID: aqh25km72pgj3

PDB1@ZRP>PDB1@ZRP>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

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


PDB1@ZRP>


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


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


PDB1@ZRP>alter session set optimizer_ignore_hints=true;

Session altered.

PDB1@ZRP>select *+ full(test) */ object_name from test where object_id=123;

OBJECT_NAME

----------------------------------------------------------------------------

OPQTYPE$


1 row selected.

SQL_ID: 1azqdh1xrf33w

PDB1@ZRP>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: 159453698


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

PDB1@ZRP>


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

 

更多详细介绍,请大家参考SQL Tuning Guide(点击“阅读原文”)


原创:老张拉呱


资源下载

关注微信:数据和云(OraNews)回复关键字获取

2018DTCC , 数据库大会PPT

2018DTC,2018 DTC 大会 PPT

DBALIFE ,“DBA 的一天”海报

DBA04 ,DBA 手记4 电子书

122ARCH ,Oracle 12.2体系结构图

2018OOW ,Oracle OpenWorld 资料

产品推荐

云和恩墨Bethune Pro企业版,集监控,巡检,安全于一身,你的专属数据库实时监控和智能巡检平台,漂亮的不像实力派,你值得拥有!

最后修改时间:2020-05-07 23:22:04
文章转载自老张拉呱,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论