【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设置为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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-11-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java达人

Oracle执行计划详解

简介: 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆! +++ 目录 ...

32110
来自专栏乐沙弥的世界

执行计划中各字段各模块描述

      在SQL语句的执行计划中,包含很多字段项和很多模块,其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示,下 面的描述给出了执行计划...

794
来自专栏乐沙弥的世界

Oracle 数据字典和数据字典视图

是元数据的集合,从逻辑上和物理上描述了数据库及内容,存储于SYSTEM与SYSAUX表空间内的若干段。

723
来自专栏乐沙弥的世界

高水位线和全表扫描

   高水位线好比水库中储水的水位线,用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用delete 操作 表记录时,高水位线并不...

732
来自专栏程序猿

Oracle_12C的新特性

这里我们来领略下Tom眼中的12个特性增强: ? #1 Even better PL/SQL from SQL, 直接在SQL中嵌入PL/SQL对象并运行,猜测...

2939
来自专栏SpringBoot 核心技术

第七章:使用QueryDSL与SpringDataJPA实现子查询

1391
来自专栏分布式系统和大数据处理

Hive中分区和分桶的概念和操作

在使用传统的RDBMS数据库(关系数据库),例如MySql时,对于一些大表,我们通常会进行分表操作,以提升查询效率。在Hive中也提供了类似的概念和操作,本文将...

1271
来自专栏数据和云

MySQL:由USE DB堵塞故障引发的思考

遇到故障,我们往往想的是如何解决这个故障,而不是从故障的根本去思考出现这个故障的原因?这样的结果,只能使我们得到了鱼,失去了渔。今天,我们就来分享一个由USE ...

3075
来自专栏用户2442861的专栏

基于bootstrap的web登陆实例

前情提要 之前已经搭好的springMVC+myBatis项目骨架,详情请看–>传送门。

4671
来自专栏数据小魔方

时间&日期函数

今天要跟大家简要介绍一下excel中经常会用到的日期与时间函数! 日期与时间类的函数虽然算所有函数中最难掌握的,但是因为格式众多,形式多样,而且作为其他高级函数...

3197

扫码关注云+社区