Oracle中db_file_multiblock_read_count引數探究

PiscesCanon發表於2017-06-03
OracleBloC
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/。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2140241/，如需轉載，請註明出處，否則將追究法律責任。

相關文章