Oracle原厂老兵:从负面案例看Hint的最佳使用方式

罗敏 2016-10-12 10:15:02

 

作者介绍

罗敏从事Oracle技术研究、开发和服务工作20余年,在Oracle中国公司的10多年,分别在顾问咨询部、技术服务部担任资深技术顾问。曾参与国内银行、电信、政府等多个行业大型IT系统的建设和运维服务工作,为国内主要软件开发商和集成商进行过多场Oracle高级技术应用培训和交流活动。著有书籍《品悟性能优化》、《感悟Oracle核心技术》、《Oracle数据库技术服务案例精选》。

 

Oracle真灵活

 

 

某银行广泛采用了Oracle和IBM DB2两种数据库,该银行很多开发人员也游走在两种平台之间,感受了不同的产品特性和风格。某天,我在为该银行提供服务过程中,与几位开发人员闲聊时好奇地问道:

 

“你们觉得DB2怎么样?”

“还是Oracle好用,更灵活。” 他们一致的回答。

“体现在哪些方面?”我继续问。

“Oracle可以通过Hint控制SQL语句执行计划,而DB2没有为开发人员提供这种可控制的空间和手段,DB2自己想怎么执行就怎么执行。”

 

哦,原来如此,Oracle的确非常灵活,可以通过各种Hint来控制优化器产生不同的执行计划。例如,你想让Oracle走哪个索引,就可以用Hint: /*+ USE_INDEX(表名 索引名) */,你想让Oracle走全表扫描,就可以用Hint: /*+ FULL(表名) */。

 

的确,这几位银行的开发人员水平非常高,非常熟悉自己的数据分布情况和访问方式,广泛使用了多种Hint,有效保证了SQL语句执行计划的最优化,也确保了系统的响应速度和吞吐量。例如他们通过Hint:/*+ ORDERED USE_NL(B C) INDEX(B IDX_TRANSSTBL_RETURN) */,就是指定了按From顺序进行连接操作,并且连接方式为Nested_Loop,同时按IDX_TRANSSTBL_RETURN索引访问B表。

 

曾几何时,能广泛、深入使用Hint的开发人员的确是高手,他们甚至比Oracle优化器自己产生的SQL语句执行计划还要好,执行效率还要高。可是,成也萧何、败也萧何,10多年之后,尤其很多系统升级到11g之后,大量Hint的使用,尤其是原有9i、10g版本SQL语句中的Hint反而成了导致性能问题的罪魁祸首!

 

这就是本文要深入探讨的话题。我们不妨先回顾一下Oracle Hint的知识,再介绍一些Hint的负面案例,然后介绍Oracle公司对Hint的官方观点,以及个人的实施经验。

 

回顾Hint

 

所谓Hint,就是开发人员在SQL语句中编写一段一定格式的注释,目的就是强制Oracle优化器按自己的意愿去产生执行计划。例如,如下语句就是强制Oracle优化器按索引PRODUCTS_PROD_CAT_IX去访问products表。

 

UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/

products p

SET   p.prod_min_price =

        (SELECT

         (pr.prod_list_price*.95)

FROM products pr

WHERE p.prod_id = pr.prod_id)

WHERE p.prod_category = 'Men'

AND   p.prod_status = 'available, on stock'

/

 

Oracle到底有多少种Hint,主要作用是什么呢?我们不妨进行如下归类:

 

序号

大类

Hint

Hint含义

1

优化器方式

All_ROWS

指定按最大吞吐量为目标的CBO模式

FIRST_ROWS(n)

制定Oracle优化器按最快返回n条记录产生执行计划

RULE

强制Oracle采用RBO模式

2

访问路径

FULL

强制Oracle对制定表进行全表扫描访问

CLUSTER

强制Oracle对指定表按cluster scan方式进行访问

HASH

强制Oracle对指定表按hash scan方式进行访问

ROWID

强制Oracle对指定表按ROWID方式进行访问

INDEX

强制Oracle对指定表按指定索引进行访问

INDEX_ASC

强制Oracle对指定索引按升序进行访问

INDEX_DESC

强制Oracle对指定索引按降序进行访问

INDEX_COMBINE

强制Oracle对指定表按指定的Bitmap索引进行访问

INDEX_JOIN

强制Oracle对指定索引进行合并操作,并访问指定的表。

INDEX_FFS

强制Oracle对指定表按指定索引进行Fast  Full Scan方式访问

INDEX_SS

强制Oracle对指定表按指定索引进行skip scan方式访问

NO_INDEX

强制Oracle对指定表不按指定索引进行访问

3

查询转换

NO_QUERY_TRANSFORMATION

跳过所有查询转换,包括OR操作转换、视图合并、子查询和主查询合并、星型转换、物化视图语句重写等

USE_CONCAT

强制Oracle将OR操作按UNION ALL操作执行

NO_EXPAND

阻止Oracle将OR操作按UNION ALL操作执行

REWRITE

按物化视图对语句进行重写

NO_REWRITE

关闭REWRITE功能

UNNEST

强制Oracle将子查询和主查询合并

NO_UNNEST

关闭UNNEST功能

MERGE

将复杂的视图与调用该视图的语句合并

NO_MERGE

阻止将复杂的视图与调用该视图的语句合并

STAR_TRANSFORMATION

强制Oracle对星型模型的访问转换为子查询,并按相关Bitmap索引进行访问

FACT

与STAR_TRANSFORMATION   Hint配合,指定哪个表为事实表

NO_FACT

与STAR_TRANSFORMATION   Hint配合,指定哪个表不为事实表

4

表连接顺序

ORDERED

强制Oracle按From短语中表的顺序,进行表连接操作

LEADING

强制Oracle在表连接操作时,先访问指定的表

5

表连接操作

USE_NL

强制Oracle对指定表按Nest_Loop方式进行表连接操作

NO_USE_NL

强制Oracle对指定表不按Nest_Loop方式进行表连接操作

USE_NL_WITH_INDEX

与USE_NL类似,但是必须按指定索引访问驱动表

USE_MERGE

强制Oracle对指定表按Sort-Merge方式进行表连接操作

NO_USE_MERGE

强制Oracle对指定表不按Sort-Merge方式进行表连接操作

USE_HASH

强制Oracle对指定表按HASH方式进行表连接操作

NO_USE_HASH

强制Oracle对指定表不按HASH方式进行表连接操作

DRIVING_SITE

强制Oracle在SQL语句发起的另外一个节点上执行

6

其它类

APPEND

强制Oracle按Direct-path Insert方式插入数据

NOAPPEND

强制Oracle按传统方式插入数据

CURSOR_SHARING_EXACT

阻止Oracle将SQL语句中的常量替换为绑定变量

CACHE

强制Oracle将指定表缓存在Buffer Cache中

PUSH_PRED

强制Oracle将主语句中表与视图中相关表进行连接操作

PUSH_SUBQ

强制Oracle先执行非合并的子查询模块

DYNAMIC_SAMPLING

强制Oracle进行动态统计数据采样,采样率参数为0到10,值越大,动态采样数据越多。

MONITOR

强制Oracle启动实时SQL语句性能监控功能

NO_MONITOR

关闭实时SQL语句性能监控功能

RESULT_CACHE

强制Oracle将当前查询结果集缓存在RESULT CACHE中

NO_RESULT_CACHE

强制Oracle不将当前查询结果集缓存在RESULT CACHE中

 

限于篇幅,还有更多类的Hint没有再一一罗列,例如与Parallel处理相关的Hint等。

 

有关Hint使用的负面案例

 

本文重点并不是要讲解上述纷繁的Hint技术使用技巧,恰恰相反,本文将介绍若干Hint使用的负面案例。

 

  • 错误使用/*+ use_nl(a)*/

 

某移动CRM系统中如下一条语句最消耗资源:

 

 

其实smtemplate、smnotify_kf表都非常小,因此全表扫描是正确执行路径,但两个表被语句的HINT:/*+ use_nl(a)*/强制按nested loop进行连接,导致内存消耗非常大:9171128。如果去掉上述HINT,语句执行情况如下:

 

 

可见,Oracle自动根据两个表的统计数据情况,选择按HASH_JOIN方式进行两个表的连接。实际执行效果内存消耗非常小,从9171128下降为3385。

 

  • 错误使用/*+ use_merge */

 

前不久的某天,我在一旁观摩我的同事为某银行一条Merge语句进行优化,可惜我没有实际操作,无法将语句优化过程完整记录下来,现在仅以文字形式进行描述。

 

该Merge语句涉及一大一小两个表的连接,现有执行计划为两个表的全表扫描,显然不合理,于是我的同事在大表的连接字段上建了一个索引,执行计划果然走新建索引了,但Cost依然非常高,执行效率不佳。我在一旁仔细分析执行计划,发现两个表走的是Sort-Merge连接方式,再仔细看语句,原来有一个/*+ use_merge */的Hint,于是我果断建议把这个Hint去掉,结果Cost大大下降,实际效果是执行计划变为两个表按Nested-Loop进行连接,其中小表为驱动表,大表按新建索引进行访问,语句执行时间为7秒,我的同事马上回头问旁边的开发人员,7秒能满足需求吗?开发人员喜出望外:“啊,原来这条语句是20多分钟呢!”

 

可见,Oracle完全可以根据统计数据准确判断出最优的执行计划,即通过Nested-Loop进行表连接,并按新建的索引访问大表。而开发人员的/*+ use_merge */强制Oracle按Sort-Merge进行表连接,实在是弄巧成拙。

 

这就是错误使用Hint的典型负面案例!

 

有关Hint的Oracle官方观点

和最佳实践经验

 

  • Hint是优化过程中的最后一招

 

这意味着Oracle公司对自己的CBO优化器非常自信,Oracle认为只要统计数据准确, CBO绝大部分情况下是没有问题的。若统计数据已经准确,甚至已经采集了SQL Profile技术,执行计划依然不合理,Oracle这时候才建议使用Hint。

 

我们再回顾一下Oracle自动化优化工具的使用:Oracle自动化优化工具一般会给出四个方面的分析建议,而第一条建议就是分析是否有统计数据,以及统计数据是否过期了。这就是Oracle对性能优化的理念,也再次验证上述观点:只要统计数据准确, CBO绝大部分情况下是没有问题的,而Hint只是优化过程中的最后一招。

 

  • 稳定和固化并不代表着最优

 

很多Oracle同仁们使用Hint的一个重要目的就是为了执行计划的稳定和固化,防止SQL语句因执行计划变异而导致性能衰减。可是,这个世界上没有一成不变的事情,唯一不变的事情就是变。

 

SQL语句是访问数据的,而数据库中的数据是在不断动态变化之中,因此SQL语句执行计划应该是根据数据变化情况不断演变的,关键是要不断变好,而不是出现性能衰减。而Oracle的Hint、Stored Outline等技术只追求稳定和固化,是一种落后、淘汰、简单、粗暴的技术,并不能适应客户数据的动态变化,因此,Oracle在11g、12c中推出了SPM、Adaptive Cursor Sharing、Adaptive Query Optimization等更多动态、自适应的优化技术。

 

  • Hint将导致昂贵的维护成本

 

大量使用Hint,将要求开发人员非常熟谙被访问表的数据分布情况,而一旦数据量发生陡变,或者表结构发生变化,或者数据库版本升级,都可能导致Hint失去作用,甚至适得其反。为此,开发人员不得不根据这些变化情况去实施有效的Hint,这将导致昂贵的维护成本。

 

  • 在视图上和视图内谨慎使用Hint

 

由于对视图的访问取决于调用视图的语句环境,Oracle很可能因为上下文环境不一样,对视图的访问路径是不同的,因此在视图上和视图内使用Hint,强制Oracle产生某一种执行计划,很可能并不是最优的执行路径。更何况,Oracle对视图的访问可能是将视图定义与主语句合并,也可能是将主语句的谓词条件推送到视图之中。这些不确定情况,若再加上Hint的使用,将导致更多不可预知的结果。

 

尽管Oracle也推出了Global Table Hint技术,即将语句中表的Hint推送到被访问的视图之中,但本人认为仍然要谨慎使用这样的技术。

 

本人更有这样的观点:对视图本身尤其是多层嵌套视图的使用一定要慎重,因为这都会导致Oracle难以确保最优的执行计划。

 

  • 隐含参数_OPTIMIZER_IGNORE_HINTS参数的使用

 

Oracle推出了一个隐含参数_OPTIMIZER_IGNORE_HINTS,取值为TRUE/FALSE,缺省值是FALSE,也就是说Oracle可以通过将该隐含参数设置为TRUE,使得Oracle优化器忽略语句中所有的Hint。

 

显然,Oracle提供此参数的目的就是在不修改应用前提下,忽略所有Hint,让Oracle优化器自己来选择执行路径。Oracle也是认为在数据库版本升级之后,原有Hint可能不仅起不到好作用,反而会起到反作用了。Oracle也是认为在新版本下,没有这些Hint,Oracle可能会运行得更好。

 

  • 并非一刀切

 

Oracle公司和本文并非一刀切地否定所有Hint的使用,Oracle某些技术的运用还是依赖于Hint的,例如11g新的Result Cache技术的使用等。另外,根据最佳实践经验,通过Hint使用某些技术效果更好。例如通过/*+ parallel */ Hint比11g的自动并行处理技术更为有效。

 

本文最后要总结的是:当年大家被迫采用Hint的一个重要原因是Oracle优化器包括CBO还存在一定缺陷,而现在随着Oracle新版本的不断推出,CBO已经越来越智能、越来越先进了,我们作为用户也要与时俱进,不断适应新技术的发展,并采取合理的技术运用策略。

 

这就是事物的螺旋式上升发展和综合平衡。

最新评论
访客 2017年06月29日

优化前后的执行计划cost值一致,图贴错了?

访客 2017年06月27日

感觉写着写着上厕所了。。突然停止

访客 2017年06月26日

<div class="content"><span class="blue f12">访…

访客 2017年06月23日

为什么小表驱动大表比较快呢?

访客 2017年06月20日

学习

活动预告