oracle数据库优化器hint,【SQL执行计划】优化器相关Hint

嘉宾介绍：

在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设置为1execdbms_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 优化最佳实践》第二章。如何加入"云和恩墨大讲堂"微信群