【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 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

通过shell脚本抓取awr报告中的问题sql(r6笔记第78天)

awr报告中的sql明细部分基本必看的部分,尤其是SQL Order by Elapsed time这个部分,能够很清晰的看到哪些sql语句占用了较多的DB t...

2483
来自专栏电光石火

用mysqlslap进行mysql压力测试

mysqlslap是一个mysql官方提供的压力测试工具,通过模拟多个并发客户端访问mysql来执行测试,使用起来非常的简单。通过mysqlslap--help...

18710
来自专栏杨建荣的学习笔记

使用shell脚本查看数据库负载情况(81天)

平时在查看数据库的问题时,会有种迷茫的感觉,如果没有任何人反馈问题,基本上没有主动查找问题的方向,awr,ash都是在得知问题发生的时间段或者一些时间戳来从历史...

28112
来自专栏别先生

实验手册——搜狗搜索日志分析系统

前奏:请提前搭建好你的集群和必要的软件:hadoop + jdk + hive + Sqoop + HBase; 数据(链接是网友的,感谢,感谢,感谢。里面有测...

6147
来自专栏北京马哥教育

MySQL 超级入门教程(内含资源福利)

运维行业正在变革,推荐阅读:30万年薪Linux运维工程师成长魔法 MySQL简介 1、什么是数据库 ? 数据库(Database)是按照数据结构来组织、存储...

3708
来自专栏Albert陈凯

2018-07-12 Oracle for update和for update nowait的区别Oracle for update和for update nowait的区别

原版排版太难看了看着闹眼睛。 https://www.cnblogs.com/quanweiru/archive/2012/11/09/2762223.htm...

662
来自专栏数据和云

经典案例:如何优化Oracle使用DBlink的SQL语句

作者介绍 赵全文 就职于太极计算机股份有限公司,在中央电化教育馆做Oracle DBA的驻场运维工作。具有3年左右的Oracle工作经验,目前擅长Oracle数...

3199
来自专栏idba

MySQL 5.7 新特性之四

本系列文章基于5.7.20 版本讲述MySQL的新特性。从安装,文件结构,SQL ,优化 ,运维层面 复制,GITD等几个方面展开介绍5.7 的新特性和功能。同...

522
来自专栏数据和云

极限优化:从75到2000,由技能到性能提升岂止20倍

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

2184
来自专栏乐沙弥的世界

ERROR 1160 (08S01) : Got an error writing communication packets

    应用程序端碰到错误提示,MySQLNonTransientConnectionException:Got an error writing commun...

700

扫描关注云+社区