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，该参数取值的阈
值依赖于操作系统和硬件，应该对不同的操作系统和硬件，多作测试
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60217/viewspace-916242/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/60217/viewspace-916242/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
