测量一次I/0最多能读多少块

db_file_multiblock_read_count指定了一次最多能够读取的block数，当然这个值也是和操作系统相关的，ORACLE文档说，大多数的平台，一次I/O一般读1M的数据(This value is platform-dependent and is 1MB for most platforms),如果你的数据库的block size 为8，那么你一次就可以读128个块。现在我来测试一下，我的笔记本电脑是不是 most platforms.

实验步骤：
SQL> create tablespace io_test datafile 'C:/oracle/product/10.2.0/oradata/ROBINSON/DATAFILE/io.dbf' size 20M uniform size 5m;   ----因为ORACLE不会垮extents 读的，所以我将一个区设置为5M，也就是640个blocks。
Tablespace created
SQL> create table test tablespace io_test as select * from dba_objects ;
Table created
SQL> select segment_name,extent_id,blocks from user_extents where segment_name='TEST';

SEGMENT_NAME                                                                      EXTENT_ID     BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
TEST                                                                                      0        640
TEST                                                                                      1        640
SQL> alter system set db_file_multiblock_read_count=128;  
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter session set events '10046 trace name context forever,level 8';  ---level 8 能够显示wait events.
会话已更改。
SQL> select count(*) from test;

  COUNT(*)
----------
     49962
SQL> alter system set events '10046 trace name context off';
系统已更改。
部分的跟踪文件
WAIT #2: nam='db file scattered read' ela= 35621 file#=8 block#=23 blocks=128 obj#=52657 tim=22327072307
WAIT #2: nam='db file scattered read' ela= 30495 file#=8 block#=153 blocks=128 obj#=52657 tim=22327103766
WAIT #2: nam='db file scattered read' ela= 35334 file#=8 block#=296 blocks=128 obj#=52657 tim=22327139930
WAIT #2: nam='db file scattered read' ela= 37814 file#=8 block#=424 blocks=128 obj#=52657 tim=22327179334
WAIT #2: nam='db file scattered read' ela= 21943 file#=8 block#=581 blocks=68 obj#=52657 tim=22327203010
WAIT #2: nam='db file scattered read' ela= 14812 file#=8 block#=664 blocks=54 obj#=52657 tim=22327218891

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup ;
ORACLE 例程已经启动。

Total System Global Area  268435456 bytes
Fixed Size                  1290112 bytes
Variable Size             205521024 bytes
Database Buffers           54525952 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
数据库已经打开。

SQL> SELECT PHYRDS,PHYBLKRD FROM V$FILESTAT WHERE FILE#=8;

    PHYRDS   PHYBLKRD
---------- ----------
         4          4

SQL> select count(*) from test;

  COUNT(*)
----------
     96485

SQL> SELECT PHYRDS,PHYBLKRD FROM V$FILESTAT WHERE FILE#=8;

    PHYRDS   PHYBLKRD
---------- ----------
        22       1424

SQL> select 1420/18 from dual;

   1420/18
----------
78.8888888

由此可见，我的笔记本确实能够一次性读128个block.从统计信息上面看，平均一次能够读取78个block.