clustering factor
通过前面的介绍我们发现index的clustering factor对于index的range scan的cost有很大的影响，从理念上，使用一个数字来描述表相对于索引的数据分布情况是很有效而且很有创造性的，但在我们的实际应用中clustering factor有时候发存在一些问题，我们发现clustering factor有时候（甚至经常）不能真实的index range scan的cost，尽管它真实的反映了表的数据分布。另外我们发现当我们使用某些数据库的feature的时候，会对clustering factor产生负面的影响。下面我们就来讨论这些情况：
实验1：
我们首先看一看在MSSM下，不同的free lists对clustering factor的影响。
1) 我们创建表T1如下:
create table t1(
date_ord date constraint t1_dto_nn not null,
seq_ord number(6) constraint t1_sqo_nn not null,
small_vc varchar2(10)
)
pctfree 90
pctused 10
;
在这里我们使用pctfree 90还是为了通过较小的数据量创建一个比较大的表。在默认情况下，这个表的free lists应该是1：
SQL> select FREELISTS from user_tables where table_name='T1';
FREELISTS
----------
1
2) 我们创建一个sequence：
create sequence t1_seq;
3) 接下来我们创建一个procedure t1_load用来往T1里面插入数据，我们使用5个进程来并发的向t1插入数据，为了保证多个进程能够同时开始，我们使用了dbms_lock。
完成的脚本（base_line.sql）可以在http://fusnow.itpub.net/resource/681/18248 找到。
当所有的load都结束后，我们创建索引，收集统计信息，并用下列SQL来查看表和索引的情况：
select ct, count(*)
from
(
select block, count(*) ct
from
(
select
distinct dbms_rowid.rowid_block_number(rowid) block,
substr(small_vc,1,2)
from t1
)
group by block
)
group by ct
;
CT COUNT(*)
---------- ----------
1 4
4 19
5 754
1 row selected.
create index t1_i1 on t1(date_ord, seq_ord);
Index created.
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
PL/SQL procedure successfully completed.
select
blocks,
num_rows
from
user_tables
where
table_name = 'T1';
BLOCKS NUM_ROWS
---------- ----------
777 26000
1 row selected.
select
index_name, blevel, leaf_blocks, clustering_factor
from
user_indexes
where
table_name = 'T1'
;
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
T1_I1 1 86 1414
1 row selected.
set autotrace traceonly explain
select
count(small_vc)
from
t1
where
date_ord = trunc(sysdate) + 7
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=60 Card=1 Bytes=14)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=60 Card=1000 Bytes=14000)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=5 Card=1000)
我们发现当前表t1有26000行，CLUSTERING_FACTOR=1414，这还是一个比较理想的值。
当我们用不同的session load数据的时候，我们在表里的small_vc做了一个记号用来标志这个session，于是我们从第一条sql的结果知道表t1里面有4个block只被一个session使用过，19个session被4个session使用过，有754个block被全部5个session使用过：
CT COUNT(*)
---------- ----------
1 4
4 19
5 754
我们再运行：
SQL> select rowid, t1.* from t1 where SEQ_ORD between 155 and 166;
ROWID DATE_ORD SEQ_ORD SMALL_VC
------------------ --------------- ---------- ----------
AAAGxEAALAAACg+AAK 04-OCT-06 155 s11
AAAGxEAALAAACg+AAL 04-OCT-06 156 s31
AAAGxEAALAAACg+AAM 04-OCT-06 157 s2155
AAAGxEAALAAACg+AAN 04-OCT-06 158 s41
AAAGxEAALAAACg+AAO 04-OCT-06 159 s12
AAAGxEAALAAACg+AAP 04-OCT-06 160 s2156
AAAGxEAALAAACg+AAQ 04-OCT-06 161 s51
AAAGxEAALAAACg+AAR 04-OCT-06 162 s32
AAAGxEAALAAACg+AAS 04-OCT-06 163 s42
AAAGxEAALAAACg+AAT 04-OCT-06 164 s13
AAAGxEAALAAACg+AAU 04-OCT-06 165 s2157
AAAGxEAALAAACg+AAV 04-OCT-06 166 s43
由于我们知道rowid的结构是这样的：
object_id First six letters (AAAGxE)
Relative file_id Next three letters (AAL)
Block within file Next six letters (AAACg+)
Row within block Last three letters (AAK, AAL, AAM ...)
所以我们知道这是同一个block上的数据。
在t1里面存在大量的这样的block：根据SEQ_ORD和DATE_ORD我们知道是几乎在同一个时间被load进来的，根据SMALL_VC我们知道是被不同的session load进来的。
因此我们完全可以想象在向这个block里load数据的时候，会发生buffer busy wait的争用，这种争用的原因在于我们只有一个free list，从而导致我们的不同的session会在同一时间往同一个block里面load数据。
为了解决这个问题，DBA经常采用增加free list的方法（这也是Oracle performance tuning文档上的推荐），一般free list的个数应该等同于表上的并发数，于是我们可以在建表的时候加上storage (freelists 5)这个参数。
修改完建表参数后我们重建建表测试结果如下：
select ct, count(*)
from
(
select block, count(*) ct
from
(
select
distinct dbms_rowid.rowid_block_number(rowid) block,
substr(small_vc,1,1)
from t1
)
group by block
)
group by ct
;
CT COUNT(*)
---------- ----------
1 455
2 289
1 row selected.
create index t1_i1 on t1(date_ord, seq_ord);
Index created.
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
PL/SQL procedure successfully completed.
BLOCKS NUM_ROWS
---------- ----------
749 26000
1 row selected.
select
blocks,
num_rows
from
user_tables
where
table_name = 'T1';
select
index_name, blevel, leaf_blocks, clustering_factor
from
user_indexes
where
table_name = 'T1'
;
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
T1_I1 1 86 24528
1 row selected.
set autotrace traceonly explain
select
count(small_vc)
from
t1
where
date_ord = trunc(sysdate) + 7
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=115 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=115 Card=1000 Bytes=13000)
这一次我们发现大部分的T1里的block只被1个session使用过，说明Insert的争用大大减小，但随之而来的CLUSTERING_FACTOR的变化却是令人沮丧的，CLUSTERING_FACTOR=24528，几乎和表的行数差不多。这是一个很差的值，直接导致了最后的查询使用了全表扫描而没有使用索引。
当我们使用一个free list的时候，会使不同的做insert的session获取同一个block的机会很高，但当我们使用多个free list的时候，这种比率就会很小，不同的session很有可能使用不同的free list上的Block，（具体的规则可以参考MetaLink note 1029850.6，应该是mod(process_id, freelist count) + 1），从而数据就会分布的相当散，从而导致一个很大的CLUSTERING_FACTOR，下图描述了多个free list下索引和表的关系：
如果我们仔细观察上面这个图，我们发现当我们按照索引的顺序读表的时候，加入我们关心的是值为1~10的值，虽然我们需要在表上“跳跃”多次，但跳来跳去我们都只是在block1和block3之间切换而已，对于CBO来说，每次切换当作是一次物理的IO，但我们知道当我们第一次读取block1和block3的时候我们会把这两个block cache到内存里，所以当我们再次读取block1和block2的时候，已经不是一个physical IO，而是直接从内存读取的logical IO，花的时间远比想象的要少。这说明其实CLUSTERING_FACTOR的计算是有问题的，这个问题在于Oracle计算CLUSTERING_FACTOR的时候只比较跳跃时新的block是不是和原来的block一样，但是不知道这个新的Block是不是以前被访问过了。我们会在后面介绍这个问题解决思路。
如果我们把表不建立在MSSM下,而是建立在ASSM管理的表空间下,会怎么样呢? 下面就是多次测试的结果：
| ASSM (case 1) |
ASSM (case 2) |
MSSM free lists=5 (case 1) |
MSSM free lists=5 (case 2) |
|
| block被多少个session使用？ | block 个数 | block 个数 | block 个数 | block 个数 |
| 1 | 361 | 393 | 745 | 447 |
| 2 | 217 | 258 | 297 | |
| 3 | 97 | 37 | ||
| 4 | 38 | 33 | ||
| 5 | 12 | 6 | ||
| Total_blocks | 725 | 727 | 745 | 744 |
这里我们可以看到MSSM可以相对彻底（也不能保证是完全的）的避免buffer busy wait的争用，但也会产生相对大的CLUSTERING_FACTOR, ASSM比较中庸，在一定程度避免buffer busy wait，CLUSTERING_FACTOR仍然不理想（多次测试都是大概21000左右），但比MSSM好一点。
实际上ASSM相对于MSSM而言尽管技术实现上不一样了，但本质上还是会给不同的session分配不同的block，从而在很高的压力数据load中，恶化CLUSTERING_FACTOR也是不可避免的。
实验2：
在第二个实验里，我们看一下Reverse Key Index对CLUSTERING_FACTOR的影响。
在我们前面的测试里，不管使用了ASSM还是MSSM，不管是设置了多少个free list，如果我们是先创建索引然后再load数据，我们总是会在index遇到严重的buffer busy wait的问题，因为不同的进程总是会在相同的时间修改同一个index block, 为了解决这个问题，我们有时候会选择反键索引。
反键索引就是把索引中的各个键的字节翻转，比如我们查询T1的数据：
select
dump(date_ord,16) date_dump,
dump(seq_ord,16) seq_dump
from t1
where date_ord = to_date('04-OCT-06')
and seq_ord = 39
;
DATE_DUMP SEQ_DUMP
----------------------------------- -----------------------------------
Typ=12 Len=7: 78,6a,a,4,1,1,1 Typ=2 Len=2: c1,28
当我们把这些键值反转后：
select
dump(reverse(date_ord),16) date_dump,
dump(reverse(seq_ord),16) seq_dump
from t1
where date_ord = to_date('04-OCT-06')
and seq_ord = 39
;
DATE_DUMP SEQ_DUMP
----------------------------------- -----------------------------------
Typ=12 Len=7: 1,1,1,4,a,6a,78 Typ=2 Len=2: 28,c1
通过键值反转，本来在索引上相邻的键值就会被分布到不同的index leaf block，从而减少了index leaf block上的争用。
反键索引对CLUSTERING_FACTOR的影响可以用如下测试看到：
对于MSSM下的free lists=1的表t1，我们记得当表t1有26000行时，CLUSTERING_FACTOR=1414，现在我们在t1上作如下测试：
SQL> alter index t1_i1 rebuild reverse;
Index altered.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 't1',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 1'
8 );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select
2 index_name, blevel, leaf_blocks, clustering_factor
3 from
4 user_indexes
5 where
6 table_name = 'T1'
7 ;
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
T1_I1 1 86 25973
SQL> set autotrace traceonly exp
SQL> select
2 count(small_vc)
3 from
4 t1
5 where
6 date_ord = trunc(sysdate) + 7
7 ;
COUNT(SMALL_VC)
---------------
1000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=114 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=114 Card=1000 Bytes=13000)
我们发现尽管我们对表本身没有做任何改动，但由于改变了索引的结构，CLUSTERING_FACTOR受到了很大影响。
浙公网安备 33010602011771号