使用 db_file_multiblock_read_count测试Oracle在不同系统中的IO能
2024-08-29 13:30:24
供稿：网友
link:
http://www.eygle.com/faq/db_file_multiblock_read_count&oracleio.htm
初始化参数db_file_multiblock_read_count 影响oracle在执行全表扫描时一次读取的block的数量.
db_file_multiblock_read_count的设置要受os最大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.
我们可以通过db_file_multiblock_read_count来测试oracle在不同系统下，单次io最大所能读取得数据量：
$ sqlplus "/ as sysdba"
sql*plus: release 10.1.0.2.0 - production on wed aug 11 23:43:52 2004
copyright (c) 1982, 2004, oracle. all rights reserved.
connected to:
oracle database 10g enterprise edition release 10.1.0.2.0 - 64bit production
with the partitioning, olap and data mining options
sys as sysdba on 11-aug-04 >show parameter read_count
name type value
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
sys 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_count
name type value
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
sys 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(*)
----------
73192
sys as sysdba on 12-aug-04 >@gettrace
trace_file_name
--------------------------------------------------------------------------------
/opt/oracle/soft/eygle_ora_24432.trc
$ cat /opt/oracle/soft/eygle_ora_24432.trc|grep sca
wait #26: nam='db file scattered read' ela= 18267 p1=10 p2=10 p3=128
wait #26: nam='db file scattered read' ela= 8836 p1=10 p2=138 p3=127
wait #26: nam='db file scattered read' ela= 8923 p1=10 p2=265 p3=128
wait #26: nam='db file scattered read' ela= 8853 p1=10 p2=393 p3=128
wait #26: nam='db file scattered read' ela= 8985 p1=10 p2=521 p3=128
wait #26: nam='db file scattered read' ela= 8997 p1=10 p2=649 p3=128
wait #26: nam='db file scattered read' ela= 9096 p1=10 p2=777 p3=128
wait #26: nam='db file scattered read' ela= 583 p1=10 p2=905 p3=12
$
我们可以看到，在以上测试平台中，oracle最多每次io能够读取128个block，由于block_size为8k,也就是每次最多读取了1m数据.
系统平台为:
$ uname -a
sunos billing 5.8 generic_108528-23 sun4u sparc sunw,ultra-4
当然具体的，oracle一次io能读取多少block还和很多因素有关，比如存储是否连续，磁盘是否经过条带等方式划分，并且oracle的
单次io读取不能跨越extent边界等.某些平台还和操作系统的参数设置有关.
大家可以测试一下不同的平台，oracle的单次io最多可以读取的block数量.
