Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2583611
  • 博文数量: 323
  • 博客积分: 10211
  • 博客等级: 上将
  • 技术积分: 4934
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-27 14:56
文章分类

全部博文(323)

文章存档

2012年(5)

2011年(3)

2010年(6)

2009年(140)

2008年(169)

分类: Oracle

2009-02-26 17:13:20

初始化参数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.   --跟踪session

SYS AS SYSDBA on 11-AUG-04 >alter system flush buffer_cache;

System altered.   --目的是让oracle直接从磁盘读。

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数量.
 
附加:gettrace脚本
--for UNIX
SELECT       d.VALUE
           || '/'
           || LOWER (RTRIM (i.INSTANCE, CHR (0)))
           || '_ora_'
          || p.spid
           || '.trc' trace_file_name
     FROM (SELECT p.spid
             FROM v$mystat m, v$session s, v$process p
             WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT t.INSTANCE
           FROM v$thread t, v$parameter v
           WHERE v.NAME = 'thread'
             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
        (SELECT VALUE
            FROM v$parameter
          WHERE NAME = 'user_dump_dest') d
 
--for NT
 
SELECT    d.VALUE
           || '\'
           || LOWER (RTRIM (i.INSTANCE, CHR (0)))
           || '_ora_'
           || p.spid
           || '.trc' trace_file_name
      FROM (SELECT p.spid
             FROM v$mystat m, v$session s, v$process p
             WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
          (SELECT t.INSTANCE
             FROM v$thread t, v$parameter v
           WHERE v.NAME = 'thread'
              AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
          (SELECT VALUE
             FROM v$parameter
            WHERE NAME = 'user_dump_dest') d




阅读(972) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~