一个执行计划异常变更的案例 - 外传之聚簇因子(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 条评论
登录 后参与评论

相关文章

来自专栏小壮和前端

手写贪吃蛇

1043
来自专栏Android群英传

花式实现图片3D翻转效果

501
来自专栏企鹅号快讯

PYTHON验证身份证号码准确性

  大家每个人都有身份证,但是认真研究过自己那18位居民身份证号码的人可能真的不多,今天就跟大家简单聊一下身份证号码的构成形式及最后一位验证码的验证办法。 最开...

2279
来自专栏大数据文摘

手把手: 如何用R作Polar图等

1585
来自专栏雨过天晴

原 PHP 大数相加求和

1601
来自专栏数据和云

Thinking in SQL系列之数据挖掘Apriori关联分析再现啤酒尿布神话

编辑手记:SQL做为一种编程语言,能够满足各类数据处理的需要,关键就在于算法与思维方式。以SQL会友,希望结交更多的数据库、数据分析领域的朋友。 作者简介:牛超...

3358
来自专栏增长技术

FFmpeg参数详解

-t duration 设置记录时间 hh:mm:ss[.xxx]格式的记录时间也支持

701
来自专栏Grace development

一道看似简单的面试题

这样看似简单的一个面试题, 实际牵出了很多基础知识,本章在为大家补习基础知识的情况下来解答这道题。先亮出答案

332
来自专栏一名叫大蕉的程序员

RSA 这俩世纪最重要的算法之一No.91

本文大概1000字,读完共需5分钟 Diffie–Hellman加密算法的劣势 上一篇文章我们聊到 Diffie–Hellman key exchange ...

2615
来自专栏漫漫全栈路

计算机组成原理-运算方法之数据格式

数据格式 先说下数据格式,在选择计算机数的表示方式时,需要考虑以下几个因数: 要表达的书的类型(小数,整数,实数,复数) 可能遇到的数值范围 数值精度 数据存储...

2616

扫描关注云+社区