使用 db_file_multiblock_read_count測試Oracle在不同系統中的IO能力
2024-08-29 13:48:59
供稿：網友
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.trcgrep 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數量.
