db_file_multiblock_read_count

db_file_multiblock_read_count
初始化参数db_file_multiblock_read_count 影响Oracle在执行连续的数据库扫描时，一次I/O允许读取的最大数据块数，这个参数的设置可能影响到CBO的执行计划选择。但db_file_multiblock_read_count的设置要受系统最大IO能力影响，也就是说，如果你系统的硬件IO能力有限，即使设置再大的db_file_multiblock_read_count也是没有用。
理论上，最大db_file_multiblock_read_count和系统IO能力有如下关系:
Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size
当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制，目前Oracle所支持的最大db_file_multiblock_read_count 值为128.
在Oracle10gR2之后的版本（10gR2和11g）中，Oracle数据库已经可以根据系统的IO能力以及Buffer Cache的大小来动态调整该参数值，Oracle建议不要显式设置该参数值。
为什么物理读不和理论值不一样
理论上I/O次数应为
表大小/(db_file_multiblock_read_count*db_block_size)
然而实际上I/O既有可能大于这个值，也有可能小于它，下面为实验过程

SQL> create table t (x int,y int);
Table created.
SQL> insert into t values(1,1);
1 row created.
SQL> insert into t values(2,2);
1 row created.
将每个数据块的记录收缩到最小，这里每个数据块将只存储两行数据。
SQL> alter table t minimize records_per_block;
Table altered.
SQL> insert into t select rownum+2,1 from dba_objects where rownum<=254;
254 rows created.
SQL> create index t_idx on t(x);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T');
PL/SQL procedure successfully completed.
查询数据的分布情况，这里数据共分布在128个数据块中
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
128
SQL> alter system flush buffer_cache;
System altered.
SQL> show parameter multiblock
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_multiblock_read_count integer 16
将数据块间隔的读入内存中
SQL> declare
2 l_y number;
3 begin
4 for i in 1..64
5 loop
6 select y into l_y from t where x=i*4;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> set autotrace traceonly statistics
可以看到一次全表扫描需要103次的物理读
SQL> select * from t;
256 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
179 consistent gets
103 physical reads
0 redo size
4818 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
查看10046产生的trace文件，可以看到每个wait#等待事件中，blocks都等于1,说明每次只读取一个数据块
SQL> !grep sequential /u01/app/admin/orcl/udump/orcl_ora_3535.trc
WAIT #30: nam='db file sequential read' ela= 287 file#=1 block#=1778 blocks=1 obj#=53617 tim=1305732442708769
WAIT #32: nam='db file sequential read' ela= 90 file#=1 block#=51 blocks=1 obj#=53617 tim=1305732442741712
WAIT #32: nam='db file sequential read' ela= 19 file#=1 block#=38 blocks=1 obj#=53617 tim=1305732442741863
...............省略若干行.................
WAIT #32: nam='db file sequential read' ela= 84 file#=1 block#=181 blocks=1 obj#=53617 tim=1305732442809561
WAIT #33: nam='db file sequential read' ela= 81 file#=1 block#=27267 blocks=1 obj#=53617 tim=1305732442813358
经上例可以知道，当没有连续的数据块可供读取时，无论db_file_multiblock_read_count设为何值，每次也只能读取一个数据块。

db_file_multiblock_read_count对CBO是怎样的影响

由于多块读取只发生在FTS(Full Table Scan)和INDEX_FFS(Index Fast Full Scan)当db_file_multiblock_read_count的值越大时，FFS或者INDEX_FFS的成本也就越低，执行计划就越倾向于使用FTS或者INDEX_FFS，见下例

SQL> set autotrace trace exp
SQL> alter session set db_file_multiblock_read_count=4;
Session altered.
SQL> select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 1536 | 69 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 256 | 1536 | 69 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> alter system set db_file_multiblock_read_count=8;
Session altered.
SQL> select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 1536 | 51 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 256 | 1536 | 51 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> alter system set db_file_multiblock_read_count=128;
Session altered.
SQL> select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 1536 | 33 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 256 | 1536 | 33 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> alter system set db_file_multiblock_read_count=129;
Session altered.
SQL> select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 256 | 1536 | 33 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 256 | 1536 | 33 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> show parameter multi
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_multiblock_read_count integer 128
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_size integer 8192

可以看到，db_file_multiblock_read_count的值越大,在进行FTS时cost值越小，那为什么db_file_multiblock_read_count没有超过128呢，通常来讲，操作系统一次最大 I/O通常都是1M，那么1MB/db_block_size=max(db_file_multiblock_read_count)，我的系统db_block_size是8k，故计算得max(db_file_multiblock_read_count)为128。有一种说法是受Oracle的限制，目前Oracle所支持的最大db_file_multiblock_read_count 值为128.这里笔者没有经过具体测试，也没见过官方的文档不敢下结论。读者可以自行在db_block_size为4k或者更小的数据库上进行测试。