作者介绍
罗敏，从事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已经越来越智能、越来越先进了，我们作为用户也要与时俱进，不断适应新技术的发展，并采取合理的技术运用策略。
这就是事物的螺旋式上升发展和综合平衡。
本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2016-10-12
相关资源：Oracle核心技术中文版