专栏首页数据和云一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

一个执行计划异常变更的案例 - 外传之聚簇因子(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),作者:刘晨

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何理解并正确使用MySql索引

    索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,本文主要介绍在MySql数据库中索引类型,以及如何创建出更加合...

    数据和云
  • MySQL史上最全性能优化方式

    MySQL有哪些性能优化方式?这个问题可以涉及到 MySQL 的很多核心知识,就像要考你计算机网络的知识时,问你“输入URL回车之后,究竟发生了什么”一样,看看...

    数据和云
  • 两千字揭密 MySQL 8.0.19 三大索引新功能:隐藏索引,降序索引,函数索引

    导读:本文详细介绍 MySQL 8.0.19 三大索引新功能,隐藏索引,降序索引,函数索引,结合其他同仁的技术应用案例,进一步进行验证改编,最后总结心得,希望对...

    数据和云
  • 《Oracle Concept》第三章 - 7

    按照《Oracle Conecpt》的结构一起了解Oracle数据库,这是学习Oracle从入门到精通的基础。

    bisal
  • MySQL专题- 数据库索引使用场景&注意事项

    用户1081422
  • SQL Sever索引

    表的索引与字典中的索引非常相似。它可以极大地提高查询的速度。对一个较大的表来说,通过加索引,一个通常要花费几个小时来完成的查询只要几分钟就可以完成。(对于包含索...

    张善友
  • 分库分表后的索引问题

    最近遇到一个慢sql,在排查过程中发现和分库分表后的索引设置有关系,总结了下问题。

    方丈的寺院
  • MySQL 性能优化--优化数据库结构之优化数据大小

    l 尽可能使用最效率(最小)的数据类型。比如,使用更小的整型以便于获取更小的表。相比INT,MEDIUMINT 通常是个更好的选择,因为MEDIUMIN...

    授客
  • Phoenix快速入门系列(3) | 一文教你如何在Phoenix中创建 HBase 二级索引

      在前面的学习中, 我们知道 HBase 只能通过 rowkey 进行搜索, 一般把 rowkey 称作一级索引. 在很长的一段时间里 HBase 就只支持一...

    不温卜火
  • 数据库索引

    索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库...

    Kevin_Zhang

扫码关注云+社区

领取腾讯云代金券