Oracle中db_file_multiblock_read_count参数探究

  • PiscesCanon

    2017-06-03 01:50:10
  • Oracle
  • 原创
2017.04.19的实验探究，这里补上
前言：
对于该参数首次遇到的地方是关于证明"一个查询执行后、抓取记录时，抓取到哪条记录，才会去读相应的块。Oracle并不预先读取所有的块，构造一个结果集，然后从结果集中返回所查询的记录"的时候，该参数造成了一定的影响。有时间再将该实验整理。
拓展来源http://www.itpub.net/thread-950711-1-1.html
这里研究一下db_file_multiblock_read_count的作用。

网上的资料：
Oracle在读取数据时一次读取的最大block的数量。
理论上，最大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.

Oracle 初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) 默认值一般是比较低的，在进行一些比较大的数据操作的时候，恰当的调整当前 Session 的 MBRC 的值可能会在 IO 上节省一点时间。(这里抛出一个问题，数据泵之类的工具导出数据时怎么设置session层面的参数)。
DB_FILE_MULTIBLOCK_READ 这个参数的值并不是可以无限大, 大多数平台下的 Oracle 都是 128。一般 Oracle 的 Block Size 是 8K 。128*8K=1M 。 这个 1M 是大多数操作系统一次最大 I/O 的限制。前面的限制要从这个 1M 推回去，初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT 的最大值之所以定为 128 ，也是一个比较保守的策略。

操作系统环境：
  1. [oracle@oracle ~]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle ~]$ lsb_release -a
  4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID: RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:        6.5
  8. Codename:       Santiago

数据库版本：
  1. SYS@proc> select * from v$version where rownum<=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

构造测试数据：
  1. SYS@proc> drop table t purge;

  3. Table dropped.

  5. SYS@proc> create table t as select * from dba_objects where rownum<=1200;

  7. Table created.

  9. SYS@proc> alter table t move tablespace test;

  11. Table altered.

  13. SYS@proc> select dbms_rowid.rowid_block_number(rowid) block#,min(rownum),max(rownum) from t group by dbms_rowid.rowid_block_number(rowid) order by dbms_rowid.rowid_block_number(rowid);

  15.     BLOCK# MIN(ROWNUM) MAX(ROWNUM)
  16. ---------- ----------- -----------
  17.        171           1          88
  18.        172          89         171
  19.        173         172         251
  20.        174         252         329
  21.        175         330         407
  22.        176         408         487
  23.        177         488         567
  24.        178         568         646
  25.        179         647         724
  26.        180         725         798
  27.        181         799         873

  29.     BLOCK# MIN(ROWNUM) MAX(ROWNUM)
  30. ---------- ----------- -----------
  31.        182         874         946
  32.        183         947        1022
  33.        185        1023        1104
  34.        186        1105        1179
  35.        187        1180        1200

  37. 16 rows selected.

  39. SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='T';

  41.  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
  42. ---------- ---------- ---------- ----------
  43.          0          6        168          8       --168 169 170 171 172 173 174 175
  44.          1          6        176          8       --176 177 178 179 180 181 182 183
  45.          2          6        184          8       --184 185 186 187 188 189 190 191

实验过程：
  1. SYS@proc> show parameter multibl

  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_file_multiblock_read_count        integer     128

  7. SYS@proc> alter system set db_file_multiblock_read_count=8;

  9. System altered.

  11. SYS@proc> analyze table t compute statistics;   --避免动态采样的影响，详情影响结果见http://blog.itpub.net/30174570/viewspace-2140240/

  13. Table analyzed.

  15. SYS@proc> alter system flush buffer_cache;

  17. System altered.

  19. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  21. no rows selected

  23. SYS@proc> select count(*) from t where rownum<=171;

  25.   COUNT(*)
  26. ----------
  27.        171

  29. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  31.      FILE#     DBABLK      STATE
  32. ---------- ---------- ----------
  33.          6        170          1
  34.          6        171          1
  35.          6        172          1
  36.          6        173          1
  37.          6        174          1
  38.          6        175          1

  40. 6 rows selected.
现在分析一下结果为什么是6个块：
1.select count(*) from t where rownum<=171该语句只读取了t表的前两个块171和172；
2.db_file_multiblock_read_count=8表明读取数据时Oracle一次读取的数据块会达到最大8个
3.170块并不包含实际数据，但是应该包含关于T表一些信息，所以每次都会读取。（待探究） 。因为不是读取数据过程，170块不算第一个块。
4.171,172自然不用说。这是读取的第一、二个块。
5.由于db_file_multiblock_read_count影响，接下去应该读取多6个块，跟前边两个块凑足8个。
 但是这里却只读了173,174,175三个块。原因见6。
6.实际上描述是“在读取数据时一次读取的最大block的数量”，这里达不到8个是因为另外一个因素，读取多余的块的时候遇到区的尽头就停止读取，受到区范围的影响。
 从上边实验数据知道，0号区的块是168 169 170 171 172 173 174 175，刚好175是0号区最后一个块，所以此次只读了6个块。
从10046结果看：
  1. WAIT #139854147886688: nam='Disk file operations I/O' ela= 454 FileOperation=2 fileno=6 filetype=2 obj#=89310 tim=1482082121461330
  2. WAIT #139854147886688: nam='db file sequential read' ela= 21 file#=6 block#=170 blocks=1 obj#=89310 tim=1482082121461405
  3. WAIT #139854147886688: nam='db file scattered read' ela= 41 file#=6 block#=171 blocks=5 obj#=89310 tim=1482082121461586
oracle分两次io读取，第一次获取必要信息，第二次才是获取数据时的io，受到区范围的影响，第二次io读取了5个块。

网友到这里若是看得懂，那么将db_file_multiblock_read_count设置为4，结果应该读取哪几个块。
我们先分析在验证：
170 171 172 173 174
应该读取上述5个块。

验证过程：
  1. SYS@proc> alter system set db_file_multiblock_read_count=4;

  3. System altered.

  5. SYS@proc> alter system flush buffer_cache;

  7. System altered.

  9. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  11. no rows selected

  13. SYS@proc> select count(*) from t where rownum<=171;

  15.   COUNT(*)
  16. ----------
  17.        171

  19. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='T') and state<>0 order by dbablk;

  21.      FILE#     DBABLK      STATE
  22. ---------- ---------- ----------
  23.          6        170          1
  24.          6        171          1
  25.          6        172          1
  26.          6        173          1
  27.          6        174          1

遗留或拓展问题：
1.数据泵之类的工具导出数据时怎么设置session层面的参数。
2.上述实验过程分析中的第3点，每次读取多余的不包含数据的块，技术原理以及细节，已解决，详情见http://blog.itpub.net/30174570/viewspace-2140813/。
请使用浏览器的分享功能分享到微信等