在一次性能调优交流中，听到某专家介绍，在缺省db_block_size=8K的环境下，增加额外db block size=32K的表空间，然后把需要进行全表扫描的表(FTS)或索引(FIS)移到该表空间上，能提高全表/索引扫描的I/O性能。
咋听起来，好像挺有道理，db block size增加了，每个block包含的数据增加了， 在db_file_multiblock_read_count不变的情况下，db_block_size * db_file_multiblock_count 值增大，每次I/O读取的数据增加，所以I/O性能提高了。事实上这个是错误的观念。
在jonathan lewis "CBO Foundation" 第二章节对tablescan介绍了很清楚，以下是基于AIX5.3平台下的oracle 10.2.0.1环境下进行测试：
一、基本介绍
1、FTS Cost = 1 + HWM/dbf_mbrc =>dbf_mbrc=HWM/(cost-1)
2、缺省db_file_multiblock_read_count=16, db_block_size=8
3、创建test_32K的表空间，表空间的段管理是手工方式
SQL>alter system set db_cache_size =3034M; (减少)
SQL>alter system set dba_32k_cache_size=512M;
SQL>create tablespace test_32K datafile
'/home/XXXX/oracle/oradataXXXX/test_32K' size 200M
blocksize 32K segment space management manual;
二、实验
1、在正常的block size =8k创建表t1
SQL>create table t1
pctfree 99
pctused 1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000
)
select
/*+ ordered use_nl(v2) */
rownum id,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;
2、对表t1进行统计分析
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 1'
);
end;
/
3、计算该平台不同的db_file_multiblock_read_count所对应着dbf_mbrc值
alter session set events '10053 trace name context forever, level 2';
alter session set db_file_multiblock_read_count=2;
select /*+ nocpu_costing */ count(*) from t1;
-- Cost_io: 3836
-- #Blks: 10143
-- adjusted dbf_mbrc=HWM/(cost-1)=10143/(3836-1)=2.645
alter session set db_file_multiblock_read_count=4;
select /*+ nocpu_costing */ count(*) from t1;
-- Cost_io: 2431
-- #Blks: 10143
-- adjusted dbf_mbrc= 10143/(2431-1)=4.174
alter session set db_file_multiblock_read_count=8;
select /*+ nocpu_costing */ count(*) from t1;
--Cost_io: 1541
--#Blks: 10143
--adjusted dbf_mbrc= 10143/(1541-1)=6.586
alter session set db_file_multiblock_read_count=16;
select /*+ nocpu_costing */ count(*) from t1;
--Cost_io: 977
--#Blks: 10143
----adjusted dbf_mbrc= 10143/(977-1)=10.392
alter session set db_file_multiblock_read_count=32;
select /*+ nocpu_costing */ count(*) from t1;
--Cost_io: 620
--#Blks: 10143
----adjusted dbf_mbrc= 10143/(620-1)=16.386
alter session set events '10053 trace name context off';
db_file_multiblock_read_count Adjusted dbf_mbrc
2 2.645
4 4.174
8 6.586
16 10.392
32 16.386
一次的I/O读取的大小=8K*16=128K
4、在db_block_size=32K的表空间创建测试表t1_32k
SQL> create table t1_32k
pctfree 99
pctused 1
tablespace test_32K
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000
)
select
/*+ ordered use_nl(v2) */
rownum id,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;
5、对t1_32k进行统计分析
6、计算全表扫描下的dbf_mrbc值
alter session set db_file_multiblock_read_count=16;
alter session set events '10053 trace name context forever, level 2';
select /*+ nocpu_costing */ count(*) from t1_32K;
alter session set events '10053 trace name context off';
--Cost_io: 1199
--#Blks: 5000
--adjusted dbf_mbrc= 5000/(1199-1)=4.174
另外，设置不同db block size主要的目的是为了数据迁移，并不是用于提高性能。
更新：
浏览 http://richardfoote.wordpress.com/2008/03/20/store-indexes-in-a-larger-block-tablespace-the-multiblock-read-myth-part-ii-the-fly/ 中的comment部分，发现Richard Foote和Jonathan Lewis赞成将索引迁移更大的Block Size 表空间不会提高性能，Donald K.Burleson则反之。争论相当激烈。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/354732/viewspace-610755/ ，如需转载，请注明出处，否则将追究法律责任。
