使用 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數量.
