eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Gtalk升级也疯狂 | Blog首页 | Oracle初学者入门指南-How to get Oracle Patch? »

db_file_multiblock_read_count and Oracle IO size

(把以前写的一点简单东西转在Blog上)

初始化参数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最大所能读取得数据量: 

我们可以看到,在以上测试平台中,Oracle最多每次IO能够读取128个Block,由于block_size为8k,也就是每次最多读取了1M数据.

$ 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
$

 

系统平台为:

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

当然具体的,Oracle一次IO能读取多少block还和很多因素有关,比如存储是否连续,磁盘是否经过条带等方式划分,并且Oracle的
单次IO读取不能跨越Extent边界等.某些平台还和操作系统的参数设置有关.

大家可以测试一下不同的平台,Oracle的单次IO最多可以读取的Block数量.


历史上的今天...
    >> 2010-12-14文章:

无觅

By eygle on 2005-12-14 15:11 | Comments (1) | Internal | 580 |

1 Comment

补充一点。在9i允许多个不同block大小的表空间上作测试,可以发现不同的表空间FTS时候读取的block数十不一样的,但是字节数总是一样。所以其实db_file_multiblock_read_count 并不是限定了FTS时读取的确切block数,而是限定了读取的字节数。
也就是如果system表空间的block size是8K,db_file_multiblock_read_count =16,那么其实是限定了一次读取8K*16=128K字节。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com