link:http://www.eygle.com/faq/db_file_multiblock_read_count&OracleIO.htm初始化參數(shù)db_file_multiblock_read_count 影響Oracle在執(zhí)行全表掃描時(shí)一次讀取的block的數(shù)量. db_file_multiblock_read_count的設(shè)置要受OS最大IO能力影響，也就是說，假如 你系統(tǒng)的硬件IO能力有限，即使設(shè)置再大的db_file_multiblock_read_count也是沒有用 的。理論上，最大db_file_multiblock_read_count和系統(tǒng)IO能力應(yīng)該有如下關(guān)系: Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size當(dāng)然這個(gè)Max(db_file_multiblock_read_count)還要受Oracle的限制，目前Oracle所支持的最大db_file_multiblock_read_count 值為128.我們可以通過db_file_multiblock_read_count來測(cè)試Oracle在不同系統(tǒng)下，單次IO最大所能讀取得數(shù)據(jù)量：$ sqlplus '/ as sysdba'SQL*Plus: Release 10.1.0.2.0 - ProdUCtion on Wed Aug 11 23:43:52 2004Copyright (c) 1982, 2004, Oracle.; All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSYS AS SYSDBA on 11-AUG-04 >show parameter read_countNAME TYPE;;;;;VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count;;;;;integer;;16SYS AS SYSDBA on 11-AUG-04 >create tablespace dfmbrc 2; datafile '/opt/oracle/oradata/eygle/dfmbrc.dbf' 3; size 20M extent management local uniform size 2M;Tablespace created.SYS AS SYSDBA on 11-AUG-04 >create table t tablespace dfmbrc as select * from dba_objects;Table created.SYS AS SYSDBA on 11-AUG-04 >insert into t select * from t;9149 rows created.SYS AS SYSDBA on 11-AUG-04 >/18298 rows created.SYS AS SYSDBA on 11-AUG-04 >/36596 rows created.SYS AS SYSDBA on 11-AUG-04 >commit;Commit complete.SYS AS SYSDBA on 11-AUG-04 >alter session set db_file_multiblock_read_count=1000;Session altered.SYS AS SYSDBA on 12-AUG-04 >show parameter read_countNAMETYPE; ;;;;VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count;;;;;integer;;;;;128SYS AS SYSDBA on 11-AUG-04 >alter session set events '10046 trace name context forever,level 12';Session altered.SYS AS SYSDBA on 11-AUG-04 >alter system flush buffer_cache;System altered.SYS AS SYSDBA on 11-AUG-04 >select count(*) from t; COUNT(*)---------- 73192SYS AS SYSDBA on 12-AUG-04 >@gettraceTRACE_FILE_NAME--------------------------------------------------------------------------------/opt/oracle/soft/eygle_ora_24432.trc$ cat /opt/oracle/soft/eygle_ora_24432.trcgrep scaWAIT #26: nam='db file scattered read' ela= 18267 p1=10 p2=10 p3=128WAIT #26: nam='db file scattered read' ela= 8836 p1=10 p2=138 p3=127WAIT #26: nam='db file scattered read' ela= 8923 p1=10 p2=265 p3=128WAIT #26: nam='db file scattered read' ela= 8853 p1=10 p2=393 p3=128WAIT #26: nam='db file scattered read' ela= 8985 p1=10 p2=521 p3=128WAIT #26: nam='db file scattered read' ela= 8997 p1=10 p2=649 p3=128WAIT #26: nam='db file scattered read' ela= 9096 p1=10 p2=777 p3=128WAIT #26: nam='db file scattered read' ela= 583 p1=10 p2=905 p3=12$我們可以看到，在以上測(cè)試平臺(tái)中，Oracle最多每次IO能夠讀取128個(gè)Block，由于block_size為8k,也就是每次最多讀取了1M數(shù)據(jù).系統(tǒng)平臺(tái)為: $ uname -aSunOS billing 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-4當(dāng)然具體的，Oracle一次IO能讀取多少block還和很多因素有關(guān)，比如存儲(chǔ)是否連續(xù)，磁盤是否經(jīng)過條帶等方式劃分，并且Oracle的單次IO讀取不能跨越Extent邊界等.某些平臺(tái)還和操作系統(tǒng)的參數(shù)設(shè)置有關(guān).大家可以測(cè)試一下不同的平臺(tái)，Oracle的單次IO最多可以讀取的Block數(shù)量.