使用 db_file_multiblock_read_count测试Oracle在不同系统中的IO能力

使用 db_file_multiblock_read_count测试Oracle在不同系统中的IO能力 link:

http://www.eygle.com/faq/db_file_multiblock_read_count&OracleIO.htm

即使设置再大的db_file_multiblock_read_count也是没有用的。

Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size

我们可以通过db_file_multiblock_read_count来测试Oracle在不同系统下，单次IO最大所能读取得数据量：

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Aug 11 23:43:52 2004

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

SunOS billing 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-4