【SQL执行计划】优化器相关Hint
编辑手记：韩老师在数据库性能优化方面有很丰富的经验，出版书籍《SQL 优化最佳实践》，感谢韩老师和机械工业出版社的授权，我们会在接下来的每周二分享书中的经典篇章，与大家共同成长。
嘉宾介绍：
在SQL优化中，除了可以通过修改参数的方式干预优化器工作外，还可以使用提示的方式进行干预，而且这种方式更加精准、不影响其他SQL，故使用场景更加广泛。
1. ALL_ROWS
说明：
ALL_ROWS是针对整个目标SQL的Hint，它的含义是让优化器启用CBO，而且在得到目标SQL的执行计划时会选择那些吞吐量最佳的执行路径。这里的“吞吐量最佳”是指资源消耗量（即对I/O、CPU等硬件资源的消耗量）最小，也就是说在ALL_ROWS Hint生效的情况下，优化器会启用CBO而且会依据各个执行路径的资源消耗量来计算它们各自的成本。
ALL_ROWS Hint其实就相当于对目标SQL启用CBO，其优化器为ALL_ROWS。从Oracle 10g开始，ALL_ROWS就是默认的优化器模式。这也意味着自Oracle 10g以来，默认情况下优化器启用的就是CBO，而且会依据各条执行路径的资源消耗量来计算它们各自的成本。
如果在目标SQL中除了ALL_ROWS之外还使用了其他与执行路径、表连接相关的Hint，则优化器会优先考虑ALL_ROWS。
格式：
/*+ ALL_ROWS */
范例：
select /*+ all_rows */ empno,ename,sal,jobfrom emp where empno=7369;
2. FIRST_ROWS(n)
说明：FIRST_ROWS(n)是针对整个目标SQL的Hint，它的含义是让优化器启用CBO模式，而且在得到目标SQL的执行计划时会选择那些得以最快响应并返回头n条记录的执行路径，也就是说在FIRST_ROWS(n) Hint生效的情况下，优化器会启用CBO，而且会依据返回头n条记录的响应时间来决定目标SQL的执行计划。
格式：
/*+ FIRST_ROWS(n) */
范例：
select /*+ first_rows(10) */empno,ename,sal,job from emp where empno=7369;
优化器模式-FIRST_ROWS_n：FIRST_ROWS(n) Hint和优化器模式FIRST_ROWS_n不是一一对应的。优化器模式FIRST_ROWS_n中只能是1、10、100和1000，但FIRST_ROWS(n) Hint中的n可以是除1、10、100和1000之外的所有值。
alter session setoptimizer_mode=first_rows_10;
忽略情况：如果在UPDATE、DELETE或者含如下内容的查询语句中使用了FIRST_ROWS(n) Hint，则该FIRST_ROWS(n) Hint会被Oracle忽略。
集合运算（如UNION、INTERSECT、MINUS、UNION ALL等）
GROUP BY
FOR UPDATE
聚合函数（比如SUM等）
DISTINCT
ORDER BY（对应的排序列上没有索引）
这里优化器会忽略FIRST_ROWS(n) Hint是因为对于上述类型的SQL语言而言，Oracle必须访问所有的行记录后才能返回满足条件的头n行记录，即在上述情形下，使用FIRST_ROWS(n) Hint是没有意义的。
3. RULE
说明：RULE是针对整个SQL的Hint，它表示对目标SQL启用RBO。
格式：
/*+ RULE */
范例：
select /*+ rule */ empno,ename,sal,job fromemp where empno=7369;
RULE与其他Hint：RULE通常不能与除DRIVING_SITE以外的Hint联用，当RULE与除DRIVING_SITE以外的Hint联用时，其他的Hint可能会失效。但是，当RULE和DRIVING_SITE联用时，它自身可能会失效，所以RULE Hint最好是单独使用。
最佳实践：不推荐使用RULE Hint。一是因为Oracle早就不支持RBO了，二是因为启用RBO后优化器在执行目标SQL时选择的执行路径将大大减少，很多执行路径RBO根本就不支持（比如哈希连接），这也就意味着启用RBO后目标SQL跑出正确执行计划的概率将大大降低。
忽略情况：因为很多执行路径RBO根本就不支持，所以即使在目标SQL中使用了RULE Hint，如果出现了如下情况（包括但不限于），RULE Hint依然会被Oracle忽略。
目标SQL除RULE之外还联合使用了其他Hint（比如DRIVING_SITE）。
目标SQL使用了并行执行。
目标SQL所涉及的对象有IOT。
目标SQL所涉及的对象有分区表。
4.测试案例
下面通过一个完整的案例，介绍混合使用各种不同的提示并观察其效果。
准备工作，代码如下：
create table t1 as select * fromdba_objects;
insert into t1 select * from t1;
insert into t1 select * from t1;
commit;
select count(*) from t1; => 292280
构造了一张测试表，数据规模接近30万
create index idx_t1 on t1(object_id);
对OBJECT_ID字段创建了索引
update t1 set object_id=1 whererownum<288280;
commit;
select count(*) from t1 where object_id=1;=> 288279
手动修改了OBJECT_ID的值，将表中绝大多数记录的OBJECT_ID设置为1
execdbms_stats.gather_table_stats(
ownname=>'HF',
tabname=>'T1',
estimate_percent=>100,
method_opt=>'forcolumns size auto object_id',
cascade=>true);
收集表的统计信息，注意此时也收集了相关对象—索引的统计信息
selectclustering_factor from dba_indexeswhere index_name='IDX_T1'; => 4213
查看当前索引的聚簇因子为4213。关于聚簇因子，后面章节有详细说明。这里简单说明一下，聚簇因子反映了索引字段的顺序和表中数据存储的有序关系。聚簇因子越小，说明索引字段顺序与表中数据存储顺序一致性越高；反之，则一致性越低，即越无序
execdbms_stats.set_index_stats(
ownname=>'HF',
indname=>'IDX_T1',
clstfct=>10000,
no_invalidate=>false);
selectclustering_factor from dba_indexeswhere index_name='IDX_T1'; => 10000
这里手动修改了聚簇因子，将其设置为10000。手动修改统计信息，是一种常用的优化手段，可以便于我们分析问题。后面的统计信息的章节会有详细说明
测试SQL-默认情况，具体如下：
在默认情况下，上面的SQL应该是采用的索引扫描。因为上面手工修改了索引的聚簇因子，大大增加了索引扫描的成本。因此这里选择使用了全表扫描。注意此时是使用了CBO，且优化器模式为默认值—ALL_ROWS
测试SQL-first_rows(10)，具体如下：
这里使用了一个提示first_rows(10)，其作用是优先返回10条记录。在使用提示后，Oracle认为此时扫描索引IDX_T1能够以最短的响应时间返回满足上述SQL的where条件“object_id=1”的头10条记录，因此这里使用了索引范围扫描
测试SQL-first_rows(9)，具体如下：
使用提示first_rows(9)，带来的变化就是优化器对基数的估算不同。注意观察执行计划中的Rows部分。从first_rows(10)的12变成了11
测试SQL-all_rows，具体如下：
ALL_ROWS Hint其实就相当于对目标SQL启用CBO且优化器模式为ALL_ROWS，而ALL_ROWS本身就是自10g以来优化器模式的默认设置，即在默认情况下单独使用ALL_ROWS Hint和不使用任何Hint的效果是一样的
测试SQL-rule，具体如下：
注意执行计划中的关键字“rule based...”，并且显示的具体执行步骤中并没有“Cost”列，这说明RULE起作用了(现在用的是RBO)
测试SQL-rule + parallel，具体如下：
输出中包含了“Cost”列，这表示上述SQL在解析时使用的是CBO，这也验证了之前的观点:如果目标SQL使用了并行执行，就意味着其中的RULE Hint会失效，此时Oracle会自动启用CBO
--本文节选自《SQL 优化最佳实践》第二章。
如何加入"云和恩墨大讲堂"微信群
搜索 盖国强（Eygle）微信号：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
关注本微信（OraNews）回复关键字获取
嘉年华PPT，第六届Oracle技术嘉年华PPT；
2016DTCC, 2016数据库大会PPT；
DBALife,"DBA的一天"精品海报大图；
12cArch,“Oracle 12c体系结构”精品海报；
DBA01，《Oracle DBA手记》第一本下载；
YunHe，“云和恩墨大讲堂”案例文档下载；