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

相关文章

来自专栏文武兼修ing——机器学习与IC设计

流水线式p2p接口的分析与实现

P2P接口是一种双向握手接口,传输的前级和后级各提供一个数据有效信号valid和忙信号busy信号,只有当两个信号达成某种指定情况时,握手完成,数据传输完成,否...

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

清理session的小插曲(r4笔记第95天)

前几天在做一次巡检的时候,通过top发现有3个进程占用的时间很长,之前也碰到过几次这种情况,但是排查发现是由于监控程序在运行,算是虚惊一场。 今天看到这些进程的...

3207
来自专栏企鹅号快讯

无惧双十二Or 黑五,这些 MySQL 性能调优技巧看过来

摘要:针对购物旺季网站流量会对数据库造成的压力,作者给出了 MySQL 性能调优的一些技巧,这些技巧极具参考价值,通过这些调优,可以有效避免因为流量过大造成服务...

1749
来自专栏乐沙弥的世界

快速体验mongoDB分片

1、mongodb分片的实质是将数据分散到不同的物理机器,以分散IO,提供并发与吞吐量 2、mongodb分片依赖于片键,即任意一个需要开启的集合都需要创建...

692
来自专栏数据和云

数据恢复:隐含参数_minimum_giga_scn被废弃后如何调SCN

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 在很多数据恢复的场景中,我们可能需要调节SCN,但是一个重要的隐含参数...

27710
来自专栏用户画像

5.1 I/O 管理知识框架

632
来自专栏技术分享

Redis 数据结构与内存管理策略(下)

Redis 数据结构与内存管理策略(下) 标签: Redis Redis数据结构 Redis内存管理策略 Redis数据类型 Redis类型映射 作者:王清培...

2838
来自专栏蓝天

LINUX内存高,触发OOM-KILLER问题解决

Linux alarm 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:58:04 EST 2007 i686 i686 i386

612
来自专栏zingpLiu

Linux内存信息查看——free命令

  free 命令可以显示系统已用和空闲的内存情况。包括物理内存、交互区内存(swap)和内核缓冲区内存(buffer)。共享内存将被忽略。在Linux系统监控...

652
来自专栏SAP最佳业务实践

SAP最佳业务实践:SD–可退回包装物销售(120)-5托盘退货

一、 VA01创建托盘退货订单 在此活动中,您可以为托盘输入退货销售订单。 如果您不使用精益仓库管理 (WM),请通过如下路径选择存储地点不使用精益仓库管理进行...

2613

扫码关注云+社区