设为首页 收藏本站
查看: 1133|回复: 0

[经验分享] oracle 集群因子

[复制链接]
累计签到:1 天
连续签到:1 天
发表于 2018-9-6 07:19:19 | 显示全部楼层 |阅读模式
  集群因子(CLUSTERING FACTOR)
  

官方解释:index clustering factor  


  
A measure of the row order in>  
The more order that exists in row storage for this value, the lower the clustering factor.
  
对于此值,行存储中存在的顺序越多,聚类因子越低。
  

  索引idx_id的叶子块中有序的存储了索引的键值以及键值对应行所在的ROWID:
  示例:
  create table scott.test as select * from dba_object;
  create index scott.idx_object_id on scott.test(object_id);
  SCOTT@PROD>select owner,index_name,table_name,clustering_factor from dba_indexes where table_name='TEST';
  OWNER                          INDEX_NAME                     TABLE_NAME                     CLUSTERING_FACTOR

  SCOTT                         >  SCOTT@PROD>select count(*) from scott.test;

COUNT(*)
  

75275  

  *** 索引idx_id的叶子块中有序的存储了索引的键值以及键值对应行所在的ROWID(默认情况下索引是升序的):
  SCOTT@PROD> select * from (
  2   select object_id, rowid
  3     from test
  4    where object_id is not null
  5    order by object_id) where rownumselect sum(case
  2               when block#1 = block#2 and file#1 = file#2 then
  3                0
  4               else
  5                1
  6             end) CLUSTERING_FACTOR
  7    from (select dbms_rowid.rowid_relative_fno(rowid) file#1,
  8    lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,
  dbms_rowid.rowid_block_number(rowid) block#1,
  10    lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2
  11            from test
  12           where object_id is not null);

CLUSTERING_FACTOR
  

         1192  

  和上面的一样
  表的总块数为:
  select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from scott.test;
  SCOTT@PROD>select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from scott.test;
  

BLOCKS  

  

  1072   

  集群因子非常接近表的总块儿数的
  实验:
  set arraysize 5000
  set autot trace
  select * from scott.test where object_id < 1000;
  SCOTT@PROD>select * from scott.test where object_id < 1000;
  997 rows selected.

Execution Plan
  Plan hash value: 985375477

|>
|   0 | SELECT STATEMENT            |               |   970 | 94090 |    20   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   970 | 94090 |    20   (0)| 00:00:01 |

  |*  2 |   INDEX RANGE SCAN          |>Predicate Information (identified by operation>  2 - access(&quot;OBJECT_ID&quot;  92831  bytes sent via SQL*Net to client
  523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  997  rows processed
  

  解析:返回997条数据,(index range scan) 返回一条数据要回表一次,也就意味着要回表997次,但是逻辑读consistent gets(21个)
  因为集群因子很小,所以它回表逻辑读很小。
  实验:现在新建一个测试表test2,并且对数据随机排序:
  

create table scott.test2 as select * from scott.test order by dbms_random.value;  
create index scott.idx_id2 on scott.test2(object_id);
  

  
查看idx_id2的集群因子:
  

  
select owner,index_name,table_name,clustering_factor from dba_indexes where owner='SCOTT' and table_name='TEST2';
  

  SCOTT@PROD>select owner,index_name,table_name,clustering_factor from dba_indexes where owner='SCOTT' and table_name='TEST2';
  OWNER                          INDEX_NAME                     TABLE_NAME                     CLUSTERING_FACTOR

  SCOTT                         >  集群因子非常大,接近于表的总行数(75275)
  再来看看相同的SQL,它的执行执行计划还有逻辑读...
  set arraysize 5000
  set autot trace
  select * from scott.test2 where object_id < 1000;
  SCOTT@PROD>set arraysize 5000
  SCOTT@PROD>set autot trace
  SCOTT@PROD>select * from scott.test2 where object_id < 1000;
  997 rows selected.

Execution Plan
  Plan hash value: 300966803

|>
|   0 | SELECT STATEMENT  |       |   997 |   201K|   180   (1)| 00:00:03 |
  |*  1 |  TABLE ACCESS FULL| TEST2 |   997 |   201K|   180   (1)| 00:00:03 |

Predicate Information (identified by operation>  1 - filter(&quot;OBJECT_ID&quot;  71501  bytes sent via SQL*Net to client
  523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  997  rows processed
  

  解析:同样是返回997条数据,但是它的逻辑读(consistent gets 1177个),差不多每一行都要对应一个块儿。集群因子越大它回表要更多的逻辑读,以及物理读
  结论:
  通过上面实验得知,集群因子太大会严重影响索引回表的性能。
  集群因子究竟影响的是什么性能呢?集群因子影响的是索引回表的物理I/O次数。假设索引范围扫描返回了1000行数据,
  假设buffer cache中没有缓存表的数据块,假设这1000行数据都在同一个数据块中,那么回表需要耗费的物理I/O就只需要一个,
  假设这1000行数据都在不同的数据块中,那么回表就需要耗费1000个物理I/O。因此,集群因子影响索引回表的物理I/O次数。
  

  请注意,不要尝试重建索引来降低集群因子,那根本没用,因为表中的数据顺序始终没变。  唯一能降低集群因子的办法就是根据索引列排序对表进行重建
  (create table new_table as select * from old_table order by 索引列),但是这在实际操作中是不可取的,因为你无法照顾到每一个索引。
  

  思考?
  怎么才能避免集群因子对SQL查询性能产生影响呢?其实前文已经有了答案,集群因子只影响索引范围扫描和索引全扫描,
  当索引范围扫描,索引全扫描不回表或者返回数据量很少的时候,不管集群因子多大,对SQL查询性能几乎没有任何影响。
  再次强调一遍,在进行SQL优化的时候,往往会建立合适的组合索引消除回表,或者建立组合索引尽量减少回表次数。
  如果无法避免回表,怎么做才能消除回表对SQL查询性能产生影响呢?当我们把表中所有的数据块缓存在buffer cache中,
  这个时候不管集群因子多大,对SQL查询性能也没有多大影响,因为这时不需要物理I/O,数据块全在内存中访问速度是非常快的。



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.iyunv.com/thread-563743-1-1.html 上篇帖子: Oracle专题11之PLSQL入门 下篇帖子: oracle导出结果集为xml格式文件
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表