一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

编辑手记:一条SQL的执行计划异常变更,在深入分析的过程中,发现其涉及到的知识点非常之多,于是整个问题都变得错综复杂。前面介绍了绑定变量及其窥探方面的知识,今天来分析聚簇因子。

作者介绍:

刘晨,网名bisal,Oracle 10g/11g OCM,并国内首批Oracle YEP成员,博客:blog.itpub.net/bisal

系列文章回顾:

【性能优化】一个执行计划异常变更的案例(上)

【错综复杂】一个执行计划异常变更的案例(中)

致每一个DBA:

这个案例中涉及到了聚簇因子,这篇文章将会详细介绍,对于这个案例感觉就像打怪,见着真正的大BOSS之前,要经历各种小怪的骚扰。这可能是每一个DBA的必经之路,只有在开始的时候扎实地打小怪,不断梳理知识积累经验,才能够更快地成长,在后面的旅途中更高效低发现和处理BOSS。

严归正传,聚簇因子,ClusteringFactor,听着名字就很高大上,很学术。题外话,记得几年前的一次内部分享,dbsnake介绍一案例的时候,曾问过在场同事其中涉及的一个知识点是什么,如果知道就意味着你对索引的了解很深入,可惜当时没人反应,作为小白的我自然也不知道,当时的这个知识点就是聚簇因子,下来我仔细了解了下,确实这些东东,如果经常用到自然脱口而出,可惜这种机会只能靠自己。

我们先看下官方对CF介绍,

索引聚簇因子衡量的是索引字段存储顺序和表中数据存储顺序的符合程度。两者存储顺序越接近,聚簇因子值就越小。

聚簇因子的用处在于可以粗略估算根据索引回表需要的IO数量。

- 如果CF值高,Oracle执行一个相对较大的索引范围扫描时就会需要相对多的IO数量。这些索引项指向的是随机的表块,数据库为了根据索引检索表中数据,不得不一次又一次地读取相同的数据块。 - 如果CF值低,Oracle执行一个相对较大的索引范围扫描时就会需要相对少的IO数量。这些索引键值可能指向相同的数据块,数据库不需要重复读取同一个数据块。

文中还举了一个例子,如下表EMPLOYEES中数据是按照last name的字母顺序存储的。

如果last name是索引字段,可以看出索引的存储顺序(blockXrowY可以抽象地看作rowid),即连续的几个索引键值指向的是同一个数据块。

如果此时id是索引字段,可以看出连续的几个索引键值对应的可能是不同的数据块,而且有可能几个顺序间隔不多的键值指向的是同一个数据块;

如果这是一个庞大的索引和表,buffer cache再小一些,使用id字段作为检索条件的SQL并发再高一些,很可能之前刚从数据文件中加载至buffer cache,马上就会根据LRU算法age out,但一会又再次加载至buffer cache。

反反复复,各种latch等的资源争用就会累积起来,进而可能对系统性能造成影响。

DBA/ALL/USER_INDEXES视图有一列CLUSTERING_FACTOR,表明该索引的聚簇因子值。

摘自dbsnake书中对于CF值计算算法的叙述

(1) CF初始值是1。 (2) Oracle首先定为至目标索引最左边的叶子块。 (3) 从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描,Oracle比较当前索引行的roid和他之前相邻的索引行的rowid,若这两rowid并不是指向同一个表块,则将聚簇因子值递增1,如果指向同一个rowid,则不改变当前聚簇因子值。比对rowid的时候并不需要回表访问相应的表块。(注:原因就是根据rowid的值是可以计算出block信息) (4) 直到顺序扫描完目标索引所有叶子块的所有索引行。 (5) 扫描操作完成后,聚簇因子当前值就是会被存储在数据字典中,就是上面视图中CLUSTERINGFACTOR列。

说了这么多,CF有什么实际意义?

个人理解,CBO模式的优化器会综合考虑各种因素来判断一条SQL不同执行计划对应的成本值,选择成本值最低的一个执行计划,CF实际影响的是根据索引回表需要的IO数量,自然也在其考虑的范围之内,因此CF值的高低有时会影响CBO对不同执行计划的选择。

实验: 1.创建测试表 测试表有两列NUMBER类型的字段,其中id1是按照顺序存储,id2是无序存储,id1和id2各有一个非唯一索引

2.采集统计信息

DBA/ALL/USER_INDEXES中有一注释:

“Column names followed by an asterisk are populatedonly if you collect statistics on the index using the DBMS_STATS package.”

即使用DBMS_STATS包收集索引统计信息的时候,CLUSTERING_FACTOR才会有值。

从dba_indexes中可以看出id1对应的索引CF只有204,id2对应的索引CF有99481,表的数据量是100000,就是说这个id2中所有叶子块的索引行排列顺序几乎和表中数据存储的顺序完全不一致。

3.CF对执行计划选择的影响

使用id1 between1 and 1000作为检索条件,可以看出使用了id1索引范围扫描,

使用id2 between 1 and 1000作为检索条件,这次却选择了全表扫描,没有选择id2索引扫描。

如果我们强制使用id2索引,无论从Cost,还是consistent gets,都要高于全表扫描

究其原因,还可以参考dbsnake书中对于索引范围扫描的算法,

IRS Cost = I/O Cost + CPU Cost I/O Cost = Index Access Cost + Table Access I/O Cost Index Access Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL) Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS)

我们可以检索视图发现,id1和id2的索引LEAF_BLOCKS等列值均相等,只有CLUSTERING_FACTOR不同,进而可以粗略认为索引范围扫描的成本和聚簇因子的大小成正比。

进而我们可以这么尝试,人为将id2的索引聚簇因子值改为200

可以看出此时选择了id2的索引范围扫描

但相应consistent gets值依旧很大,我猜原因就是计算执行计划成本值,CBO会根据相关统计信息值来计算,我们人为设置了索引的聚簇因子为一个很小的值,计算出来的成本值小于全表扫描,因此选择了使用索引的执行计划,但实际回表等操作需要消耗的资源其实并没有少。

如果要消除聚簇因子的影响,只能对表中数据按照目标索引键值的顺序重新存储,例如,

create tablet1_cf_0 as select * from t1_cf order by id2;

但这么做带来的问题就是,可能id2的聚簇因子下降了,相对id1的聚簇因子上升了,有些顾此失彼的意思。

因此根据实际业务需求,选择正确的表数据组织形式,或者只能通过其他优化方式,来减小聚簇因子的影响。

之前曾发过一个如何让CF值小的讨论帖,有兴趣的朋友可以参考, http://www.itpub.net/thread-1910003-1-1.html

总结:

1.聚簇因子表示索引键值的排列顺序和表中数据排列顺序的相似程度。 2.可以粗略认为索引范围扫描的成本,和聚簇因子的大小成正比,从索引范围扫描的计算方法可以推出这个结论。 3.是否需要重新组织表中数据存储顺序,以降低某一个索引的聚簇因子值,需要结合实际需求来判断,因为若表中存在多个索引,很可能造成顾此失彼的情况。

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

原文发表时间:2017-02-21

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python

自定义 Django的User Model,扩展 AbstractUser类注意事项

本篇主要讨论一下User Model的使用技巧. 注意, 由于Django 1.5之后user model带来了很大的变化, 本篇内容只针对django 1.5...

1012
来自专栏java 成神之路

java.util.Random 实现原理

3005
来自专栏Spark学习技巧

Spark的Ml pipeline

ML pipeline提供了一组统一的高级API,它们构建在 DataFrame之上,可帮助用户创建和调整实用的机器学习pipeline。 一 重要概念 1.1...

3049
来自专栏老九学堂

浅谈计算机中的存储模型(一)物理内存

今天,我们来了解一下计算机中的存储模型,大雄将这部分知识分成了三块,也就是我们会对这部分的知识推送三次。

1074
来自专栏Java3y

数据库原理

什么是数据库? 数据库是一个以某种有组织的方式存储的数据集合。也就是:保存有组织数据的容器(一个文件或一组文件) 为什么我们需要数据库? 毫无疑问,数据库是用来...

3147
来自专栏后端之路

批量查询DB的实现

背景 由于业务量的上涨,门店转入业务数据的需求,催生了一批excel导入系统的需求。但是由于原先excel导入的实现基于行的模式(一行一行导入) 导致系统导入的...

1607
来自专栏大数据架构

Spark SQL / Catalyst 内部原理 与 RBO

从上图可见,无论是直接使用 SQL 语句还是使用 DataFrame,都会经过如下步骤转换成 DAG 对 RDD 的操作

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

圆周率π是怎么算出来的,用程序怎么算

下午在看一个算法的时候,突然看到了一个关于圆周率的问题,如果问你圆的周长怎么算,你肯定毫不犹豫是2πR,但是π是怎么算出来的呢?估计我们都没有想过,所以...

993
来自专栏灯塔大数据

每周学点大数据 | No.21磁盘算法概述

No.21期 磁盘算法概述 Mr. 王:现在我们谈谈磁盘算法的问题。根据你的了解,跟我说说计算机中都采用了哪些种类的存储器? 小可:这个我还是略知一二...

2677
来自专栏Gaussic

CUDA 计时器 原

612

扫码关注云+社区