Oracle:集群因子

什么是集群因子（Clustering Factor）呢？集群因子是通过一个索引扫描一张表时需要访问的表的数据块的数量。

集群因子的计算方法如下：

(1) 扫描一个索引；

(2) 比较某行的ROWID和前一行的ROWID，如果这两个ROWID不属于同一个数据块，那么集群因子增加1；

(3) 整个索引扫描完毕后，就得到了该索引的集群因子。

以上面集群因子的计算方式可以看出，集群因子反映了索引范围扫描可能带来的对整个表访问过程的IO开销情况，如果集群因子接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。如果集群因子接近于行的数量，那说明这张表不是按索引字段顺序存储的。在计算索引访问成本时，集群因子十分有用。集群因子乘以选择性参数就是访问索引的开销。（是优化器决定是否用索引的关键因素）

官方说明：

----row存储的越有序，clustering factor的值越低

----当clustering factor很高时，说明index entry (rowid) 是随机指向一些block的，在一个大的index range scan时，这样为了读取这些rowid指向的block，就需要一次又一次重复的去读这些block。

----当clustering factor值低时，说明index keys (rowid) 是指向的记录是存储在相同的block里，这样去读row时，只需要在同一个block里读取就可以了，这样减少重复读取blocks的次数

Index Clustering Factor说明

简单的说，Index Clustering Factor是通过一个索引扫描一张表，需要访问的表的数据块的数量，即对I/O的影响，也代表索引键存储位置是否有序。

(1)、如果越有序，即相邻的键值存储在相同的block，那么这时候Clustering Factor的值就越低；

(2)、如果不是很有序，即键值是随机的存储在block上，这样在读取键值时，可能就需要一次又一次的去访问相同的block，从而增加了I/O。

Clustering Factor的计算方式如下：

(1)、扫描一个索引(large index range scan)；

(2)、比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1；

(3)、整个索引扫描完毕后，就得到了该索引的clustering factor。

如果clustering factor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。

如果clustering factor接近于行的数量，那说明这张表不是按索引字段顺序存储的。

在计算索引访问成本的时候，这个值十分有用。Clustering Factor乘以选择性参数(selectivity)就是访问索引的开销。

如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

测试

产生问题

----查看一下数据库的版本----
sql>select*fromv$versionwhererownum=1;

BANNER
--------------------------------------------------------------------------------
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production

----创建一张测试表jack----
sql>createtablejackasselect*fromdba_objectswhere1=2;

Tablecreated.

----将数据无序的插入jack表中----
sql>begin
2foriin1..10loop
3insert/*+append*/intojackselect*fromdba_objectsorderbyi;
4commit;
5endloop;
6end;
7/

PL/sqlproceduresuccessfullycompleted.

sql>selectcount(*)fromjack;

COUNT(*)
----------
725460

----查看一下表的大小-----
sql>setwrapoff
sql>colownerfora10;
sql>colsegment_namefora15;
sql>selectsegment_name,blocks,extents,bytes/1024/1024||'M'"size"fromuser_segmentswheresegment_name='JACK';

SEGMENT_NAMEBLOCKSEXTENTSsize
-----------------------------------------
JACK112648288M

----在object_id上创建索引----
sql>createindexjack_indonjack(object_id);

Indexcreated.

----查看一下索引的大小----
sql>selectsegment_name,segment_type,bytes/1024/1024||'M'"size"fromuser_segmentswheresegment_name='JACK_IND';

SEGMENT_NAMESEGMENT_TYPEBLOCKSEXTENTSsize
-----------------------------------------------------------
JACK_INDINDEX16642813M
----在没有收集相关的统计信息之前，查看一下indexclusteringfactor----
sql>selectindex_name,clustering_factor,num_rowsfromuser_indexeswhereindex_name='JACK_IND';

INDEX_NAMECLUSTERING_FACTORNUM_ROWS
------------------------------------------
JACK_IND725460725460

----简单的收集一下统计信息----
sql>execdbms_stats.gather_table_stats(user,'jack',cascade=>true);

PL/sqlproceduresuccessfullycompleted.

----再次查看indexclusteringfactor----
sql>selectindex_name,num_rowsfromuser_indexeswhereindex_name='JACK_IND';

INDEX_NAMECLUSTERING_FACTORNUM_ROWS
-----------------------------------------
JACK_IND725460725460----显然统计信息收集前和后，clusteringfactor值不变，说在创建索引的时候，会收集表中的数据真正的行数。并且这里的clusteringfactor等num_rows，也说明表的clusteringfactor是无序的。

----查看一个确定值，然后查看执行计划----
sql>explainplanforselect*fromjackwhereobject_id=1501;

Explained.

sql>select*fromtable(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Planhashvalue:2860868395

--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Ti
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||10|970|13(0)|00
|1|TABLEACCESSBYINDEXROWID|JACK|10|970|13(0)|00
|*2|INDEXRANGESCAN|JACK_IND|10||3(0)|00
--------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


2-access("OBJECT_ID"=1501)

14rowsselected.----在这里走了索引，cost为13.

sql>altersystemflushbuffer_cache;

Systemaltered.

sql>setautotracetraceonly;
----查询一个范围的执行计划----
sql>select*fromjackwhereobject_id>1000andobject_id<2000;

9880rowsselected.


ExecutionPlan
----------------------------------------------------------
Planhashvalue:949574992

--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
--------------------------------------------------------------------------
|0|SELECTSTATEMENT||9657|914K|1824(1)|00:00:22|
|*1|TABLEACCESSFULL|JACK|9657|914K|1824(1)|00:00:22|
--------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

1-filter("OBJECT_ID"<2000AND"OBJECT_ID">1000)


Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
10993consistentgets
10340physicalreads
0redosize
471945bytessentviasql*Nettoclient
7657bytesreceivedviasql*Netfromclient
660sql*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
9880rowsprocessed----注意，object_id上是有索引的，但是这里并没有使用索引，而是使用了全表扫描。

sql>altersystemflushbuffer_cache;

Systemaltered.

----强制走索引，查看执行计划----
sql>select/*+index(jackjack_ind)*/*fromjackwhereobject_id>1000andobject_id<2000;

9880rowsselected.


ExecutionPlan
----------------------------------------------------------
Planhashvalue:2860868395

----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||9657|914K|9683(1)|00:01:57|
|1|TABLEACCESSBYINDEXROWID|JACK|9657|914K|9683(1)|00:01:57|
|*2|INDEXRANGESCAN|JACK_IND|9657||24(0)|00:00:01|
----------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

2-access("OBJECT_ID">1000AND"OBJECT_ID"<2000)


Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
10561consistentgets
164physicalreads
0redosize
988947bytessentviasql*Nettoclient
7657bytesreceivedviasql*Netfromclient
660sql*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
9880rowsprocessed
----强制走索引之后，使用了indexrangescan,但是cost变成了9683，而全表扫描时是1824.
----还有比较一下两次查询中物理读的情况：全表扫描的物理读明显比索引的要高很多，但是Oracle却没有使用索引。
----因此Oracle认为走索引的Cost比走全表扫描大，而是大N倍，CBO是基于Cost来决定执行计划的。
----由此得出，对于索引的Cost，Oracle是根据clusteringfactor参数来计算的，而该实验中的clusteringfactor参数是很高的，数据存储无序。这就造成了Oracle认为走索引的cost比全表扫描的大。

解决问题：

----通过上面的分析，可以看出，要降低clusteringfactor才能解决问题，而要解决clusteringfactor，就需要重新对表的存储位置进行排序。----
----重建jakc表----
sql>createtableechoasselect*fromjackwhere1=0;

Tablecreated.

sql>insert/*+append*/intoechoselect*fromjackorderbyobject_id;

725460rowscreated.

sql>commit;

Commitcomplete.

sql>truncatetablejack;

Tabletruncated.

sql>insert/*+append*/intojackselect*fromecho;

725460rowscreated.

sql>commit;

Commitcomplete.

----查看表和索引的信息----
sql>selectsegment_name,bytes/1024/1024||'M'"size"fromuser_segmentswheresegment_name='JACK';

SEGMENT_NAMEBLOCKSEXTENTSsize
--------------------------------------------
JACK112648288M

sql>selectsegment_name,bytes/1024/1024||'M'"size"fromuser_segmentswheresegment_name='JACK_IND';

SEGMENT_NAMESEGMENT_TYPEBLOCKSEXTENTSsize
---------------------------------------------------------------
JACK_INDINDEX15362712M

sql>selectindex_name,num_rowsfromuser_indexeswhereindex_name='JACK_IND';

INDEX_NAMECLUSTERING_FACTORNUM_ROWS
----------------------------------------
JACK_IND725460725460

----对索引进行rebuild----
sql>alterindexjack_indrebuild;

Indexaltered.

----查看clusterfactor----
sql>selectindex_name,num_rowsfromuser_indexeswhereindex_name='JACK_IND';

INDEX_NAMECLUSTERING_FACTORNUM_ROWS
------------------------------------------
JACK_IND10327725460------注意这里的Factor，已经变成10327，我们收集一下表的统计信息，然后与表的block进行一次比较。

sql>execdbms_stats.gather_table_stats(user,cascade=>true);

PL/sqlproceduresuccessfullycompleted.

sql>selectblocksfromdba_tableswheretable_name='JACK';

BLOCKS
----------
10474----表jack实际使用的block是10474，clusteringfactor是10327基本还是比较接近了，这也说明相邻的row是存储在相同的block里。

sql>selectindex_name,num_rowsfromuser_indexeswhereindex_name='JACK_IND';

INDEX_NAMECLUSTERING_FACTORNUM_ROWS
---------------------------------------------------------
JACK_IND10327725460

sql>altersystemflushbuffer_cache;

Systemaltered.

sql>setautotracetraceonly;
----再次查看之前sql的执行计划----
sql>select*fromjackwhereobject_id>1000andobject_id<2000;

9880rowsselected.


ExecutionPlan
----------------------------------------------------------
Planhashvalue:2860868395

----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||9657|914K|162(0)|00:00:02|
|1|TABLEACCESSBYINDEXROWID|JACK|9657|914K|162(0)|00:00:02|
|*2|INDEXRANGESCAN|JACK_IND|9657||24(0)|00:00:01|
----------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):
---------------------------------------------------

2-access("OBJECT_ID">1000AND"OBJECT_ID"<2000)


Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
1457consistentgets
151physicalreads
0redosize
988947bytessentviasql*Nettoclient
7657bytesreceivedviasql*Netfromclient
660sql*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
9880rowsprocessed
----注意这里的cost已经降到了162，性能提升还是非常明显。

小结

通过以上说明和测试，可以看到clustering factor也是索引健康的一个重要判断的标准。其值越低越好。它会影响CBO选择正确的执行计划。但是注意一点，clustering factor总是趋势与不断恶化的。

原文链接：https://www.f2er.com/oracle/208697.html

猜你在找的Oracle相关文章