当前位置:首页 > 开发 > 数据库 > 正文

db_file_multiblock_read_count详解(原创)

发表于: 2012-05-15   作者:czmmiao   来源:转载   浏览次数:
摘要: db_file_multiblock_read_count 初始化参数db_file_multiblock_read_count 影响Oracle在执行连续的数据库扫描时,一次I/O允许读取的最大数据块数,这个参数的设置可能影响到CBO的执行计划选择。但db_file_multiblock_read_count的设置要受系统最大IO能力影响,也就是说,如果你系统的硬件IO能力有限,即使设置再大

db_file_multiblock_read_count
初始化参数db_file_multiblock_read_count 影响Oracle在执行连续的数据库扫描时,一次I/O允许读取的最大数据块数,这个参数的设置可能影响到CBO的执行计划选择。但db_file_multiblock_read_count的设置要受系统最大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.
在Oracle10gR2之后的版本(10gR2和11g)中,Oracle数据库已经可以根据系统的IO能力以及Buffer Cache的大小来动态调整该参数值,Oracle建议不要显式设置该参数值。
为什么物理读不和理论值不一样
理论上I/O次数应为
表大小/(db_file_multiblock_read_count*db_block_size)
然而实际上I/O既有可能大于这个值,也有可能小于它,下面为实验过程

SQL> create table t (x int,y int);  
Table created.
SQL> insert into t values(1,1);
1 row created.
SQL> insert into t values(2,2);
1 row created.
将每个数据块的记录收缩到最小,这里每个数据块将只存储两行数据。
SQL> alter table t minimize records_per_block;
Table altered.
SQL> insert into t select rownum+2,1 from dba_objects where rownum<=254;
254 rows created.
SQL> create index t_idx on t(x);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T');
PL/SQL procedure successfully completed.
查询数据的分布情况,这里数据共分布在128个数据块中
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                128
SQL> alter system flush buffer_cache;
System altered.
SQL> show parameter multiblock           
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_multiblock_read_count        integer                           16
将数据块间隔的读入内存中
SQL> declare
  2   l_y number;
  3  begin
  4   for i in 1..64
  5   loop
  6   select y into l_y from t where x=i*4;
  7   end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> set autotrace traceonly statistics
可以看到一次全表扫描需要103次的物理读
SQL> select * from t;
256 rows selected.
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        179  consistent gets
        103  physical reads
          0  redo size
       4818  bytes sent via SQL*Net to client
        572  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed
查看10046产生的trace文件,可以看到每个wait#等待事件中,blocks都等于1,说明每次只读取一个数据块
SQL> !grep sequential /u01/app/admin/orcl/udump/orcl_ora_3535.trc
WAIT #30: nam='db file sequential read' ela= 287 file#=1 block#=1778 blocks=1 obj#=53617 tim=1305732442708769
WAIT #32: nam='db file sequential read' ela= 90 file#=1 block#=51 blocks=1 obj#=53617 tim=1305732442741712
WAIT #32: nam='db file sequential read' ela= 19 file#=1 block#=38 blocks=1 obj#=53617 tim=1305732442741863
...............省略若干行.................
WAIT #32: nam='db file sequential read' ela= 84 file#=1 block#=181 blocks=1 obj#=53617 tim=1305732442809561
WAIT #33: nam='db file sequential read' ela= 81 file#=1 block#=27267 blocks=1 obj#=53617 tim=1305732442813358

经上例可以知道,当没有连续的数据块可供读取时,无论db_file_multiblock_read_count设为何值,每次也只能读取一个数据块。

db_file_multiblock_read_count对CBO是怎样的影响

由于多块读取只发生在FTS(Full Table Scan)和INDEX_FFS(Index Fast Full Scan)当db_file_multiblock_read_count的值越大时,FFS或者INDEX_FFS的成本也就越低,执行计划就越倾向于使用FTS或者INDEX_FFS,见下例

SQL> set autotrace trace exp
SQL> alter session set db_file_multiblock_read_count=4;
Session altered.
SQL> select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 |  1536 |    69   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |   256 |  1536 |    69   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> alter system set db_file_multiblock_read_count=8;
Session altered.
SQL> select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 |  1536 |    51   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |   256 |  1536 |    51   (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> alter system set db_file_multiblock_read_count=128;
Session altered.
SQL>  select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 |  1536 |    33   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |   256 |  1536 |    33   (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> alter system set db_file_multiblock_read_count=129;
Session altered.
SQL>  select * from hr.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 |  1536 |    33   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |   256 |  1536 |    33   (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> show parameter multi
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_multiblock_read_count        integer                           128
SQL> show parameter db_block_size
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_size                        integer                           8192

可以看到,db_file_multiblock_read_count的值越大,在进行FTS时cost值越小,那为什么db_file_multiblock_read_count没有超过128呢,通常来讲,操作系统一次最大 I/O通常都是1M,那么1MB/db_block_size=max(db_file_multiblock_read_count),我的系统db_block_size是8k,故计算得max(db_file_multiblock_read_count)为128。有一种说法是受Oracle的限制,目前Oracle所支持的最大db_file_multiblock_read_count 值为128.这里笔者没有经过具体测试,也没见过官方的文档不敢下结论。读者可以自行在db_block_size为4k或者更小的数据库上进行测试。


参考至:《让Oracle跑得更快》谭怀远著

http://blog.chinaunix.net/uid-57865-id-2090667.html
http://www.eygle.com/archives/2009/03/db_file_multiblock_read_count_auto.html
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com

db_file_multiblock_read_count详解(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
LUN概述 LUN的全称是Logical Unit Number,也就是逻辑单元号,是SCSI中的概念。 我们用Target ID(也
WebLogic Server 中的JDBC概述 在 WebLogic Server 中,您可以配置数据库连接,方法是先配置 JDBC
AIX服务概述 在Windows中,服务标志着一个组件、一套应用、一套进程结构,这是大家所熟悉的。在AIX
DRBD概述 Distributed Replicated Block Device(DRBD)是一种基于软件的,无共享,复制的存储解决方
简单介绍一下有关Oracle集群环境中日志体系结构,在遇到问题时,可以快速查找所需的日志文件,及时
Directory Services(目录服务) 我们知道,当局域网的规模变的越来越大时,为了方便主机管理,我们使
服务器日志 每个 WebLogic Server 实例将来自子系统和应用程序的所有消息写入位于本地主机上的服务
GPT概述 全局唯一标识分区表(GUID Partition Table,缩写:GPT)是一个实体硬盘的分区结构。它是可
Memcache的使用和协议分析详解 作者:heiyeluren 博客:http://blog.csdn.net/heiyeshuwu 时间:200
Memcache的使用和协议分析详解 作者:heiyeluren 博客:http://blog.csdn.net/heiyeshuwu 时间:200
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号