db_file_multiblock_read_count 是 Full table scan or index fast full scan 时候oracle一次 read 请求的最大block数量

一般oltp：
db_file_multiblock_read_count × db_block_size = 256K

data ware:

db_file_multiblock_read_count × db_block_size = 1M

与操作系统,应用类型都有关系的,oltp型的可以小点8
,数据仓库型的可以大点16--32
最好还是做一下测试,

Oracle支持最大的 db_file_multiblock_read_count 是128K
一般不建议设置这么大 ， Oracle建议通常不要超过32K， 而且，如果db_file_multiblock_read_count 设置过大会使全表扫描的成本降低，Oracle会更倾向于使用全表扫描而不是索引,db_file_multiblock_read_count 参数设置影响全表扫描的性能 .

为了提高效率，OS一般都不是以OS BLOCK SIZE来作一次IO，而是在一次IO中读取一定数量的OS BLOCK，在unix平台，一般OS IO为64k（这个跟平台有关系，不同的平台是不一样的。）如果ORACLE的BLOCK SIZE>OS的IO，则意味着如果oracle需要从datafile中读取一个oracle block，则需要OS两次或者更多次的IO，这显然会影响系统的效率。

1.创建测试表
create table big_table as select * from all_objects;

2.获取等待时间
create or replace procedure full_scan_big_table(p_dbmrc in number)
as
l_cnt number;
begin
execute immediate 'alter tablespace users offline';
execute immediate 'alter tablespace users online';

execute immediate 'alter session set db_file_multiblock_read_count=' || p_dbmrc;

execute immediate 'alter session set events ''10046 trace name context forever, level 12'' ';

execute immediate 'select /*+ FULL(bt_mbrc_'|| p_dbmrc || ') */ count(*) from big_table bt_mbrc_'||p_dbmrc into l_cnt;

execute immediate 'alter session set events ''10046 trace name context off'' ';

end;

执行如下脚本，对trc文件运行tkprof，可以得到不同db_file_multiblock_read_count设置时执行类似
select /*+ FULL(b) */ count(*) from big_table b;查询的等待时间：
begin
for i in 0..9
loop
full_scan_big_table(power(2,i));
end loop;
end;

3.测试查询成本

set autotrace traceonly explain

alter session set db_file_multiblock_read_count=4;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：
SQL> alter session set db_file_multiblock_read_count=4;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 280 (6)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 280 (6)| 00:00:04 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=8;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果:
SQL> alter session set db_file_multiblock_read_count=8;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 (8)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 207 (8)| 00:00:03 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=16;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：
SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 170 (9)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 170 (9)| 00:00:03 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=32;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：

SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 152 (10)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 152 (10)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=64;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果：

SQL> alter session set db_file_multiblock_read_count=64;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 143 (11)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 143 (11)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=128;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果:

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 138 (11)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 138 (11)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=256;
select /*+ FULL(b) */ count(*) from big_table b;

执行结果:

SQL> alter session set db_file_multiblock_read_count=256;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 138 (11)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 138 (11)| 00:00:02 |
------------------------------------------------------------------------

alter session set db_file_multiblock_read_count=512;
select /*+ FULL(b) */ count(*) from big_table b;

SQL> alter session set db_file_multiblock_read_count=512;

Session altered.

SQL> select /*+ FULL(b) */ count(*) from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3812947622

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 138 (11)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 51035 | 138 (11)| 00:00:02 |
------------------------------------------------------------------------

观察不同db_file_multiblock_read_count参数设置下：查询成本/1000+等待时间
可以得出：最好的db_file_multiblock_read_count值应该为16，该参数取值的阈
值依赖于操作系统和硬件，应该对不同的操作系统和硬件，多作测试