在多块读的情况下，db_file_multiblock_read_count的值指定了一次最多可读取的数据块数目。实际情况下一次IO很难达到该参数的值。

《性能诊断艺术》里面写着有三中情况会导致一次多块读的数目小于次参数

1、读段头时单块读

2、物理读不能跨越多个区

3、部分数据块已经在高速缓存中，除非是直接读，否则不会重新读取。

因为这个原因在系统统计信息中有MBRC（多块度的平均数）。

db_file_multiblock_read_count值越大，越倾向于多块读操作（FFS和INDEX_FFS）。

ys@BBK10G> create table t as select * from dba_objects;

Table created.

sys@BBK10G> show parameter db_file_m

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count integer 16

sys@BBK10G> set autot trace exp

sys@BBK10G> select * from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 44705 | 7727K| 157 (2)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T | 44705 | 7727K| 157 (2)| 00:00:02 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

sys@BBK10G> alter session set db_file_multiblock_read_count = 32;

Session altered.

sys@BBK10G> select * from t;

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 44705 | 7727K| 139 (2)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T | 44705 | 7727K| 139 (2)| 00:00:02 |

--------------------------------------------------------------------------

sys@BBK10G> alter session set db_file_multiblock_read_count = 128;

Session altered.

sys@BBK10G> select * from t;

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 44705 | 7727K| 125 (2)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T | 44705 | 7727K| 125 (2)| 00:00:02 |

--------------------------------------------------------------------------

可以看出该参数越大，优化器认为全表扫描的代价（Cost）越小。

ITPub博客

db_file_multiblock_read_count