DB_FILE_MULTIBLOCK_READ_COUNTのI/O操作検証
初期化パラメータ DB_FILE_MULTIBLOCK_READ_COUNT

このパラメータには、順次スキャン中に1回のI/O操作で読み取られるブロックの最大数を指定する。
DB_FILE_MULTIBLOCK_READ_COUNTの値は走査するエクステントのブロック数より大きい場合でも、複数のエクステントを跨ってブロックを読み込むことはできない。

検証ステップ1: ブロックサイズ及びDB_FILE_MULTIBLOCK_READ_COUNTの設定を確認
PROD(JASON)> show parameter db_file_multi

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_multiblock_read_count        integer                           16
PROD(JASON)> show parameter db_block_size

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_size                        integer                           8192
PROD(JASON)>

ブロックサイズは8K。db_file_multiblock_read_countは16個を設定されている。
検証ステップ2: エクステントが64K均一の表領域を作成し、検証表を作成する。
PROD(JASON)> create tablespace tbs_test datafile '/u02/app/oracle/oradata/PROD/tbs_test01.dbf' 
    size 20M extent management local uniform size 64K;     <<- 64K均一サイズのエクステント、因みにエクステント毎に8ブロック

Tablespace created.

PROD(JASON)> create table t_obj tablespace tbs_test as select * from dba_objects;

Table created.

PROD(JASON)>

PROD(JASON)> select file_id,block_id,blocks from dba_extents where segment_name = 'T_OBJ';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        31          9          8
        31         17          8
        31         25          8
        31         33          8
        31         41          8
        31         49          8
        31         57          8
        31         65          8
        31         73          8
        31         81          8
        31         89          8
        31         97          8
        31        105          8
        31        113          8
        31        121          8
        31        129          8
        31        137          8
        31        145          8

18 rows selected.

PROD(JASON)>

dba_extentsの問い合わせによって各エクステントに8個のブロックが割り当てられていることを確認できた。
検証ステップ3: 10046イベントでT_OBJ表に対する問い合わせをトレースする
PROD(JASON)> alter session set events '10046 trace name context forever,level 12';

Session altered.

PROD(JASON)> select count(*) from t_obj;

  COUNT(*)
----------
     10242

PROD(JASON)> alter session set events '10046 trace name context off';

Session altered.

PROD(JASON)>

PROD(JASON)> 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
/

TRACE_FILE_NAME
------------------------------------------------------------------------------------------------------------------
/u01/oracle/app/admin/PROD/udump/PROD_ora_23255.trc

PROD(JASON)>
検証ステップ4: トレースファイルから全表走査によって発生する「db file scattered read」を抽出する

トレース情報中のblocksが読込みブロック数になる。
DB_FILE_MULTIBLOCK_READ_COUNTが16に設定されているのに毎回読み込んだブロック数が最大8個と成った。因みにエクステントを跨ってブロックを読み込むことができない。

[admin oracle()] cat /u01/oracle/app/admin/PROD/udump/PROD_ora_23255.trc | grep scatt
WAIT #3: nam='db file scattered read' ela= 6452 file#=31 block#=19 blocks=6 obj#=1 tim=1235358657290124
WAIT #3: nam='db file scattered read' ela= 3095 file#=31 block#=26 blocks=7 obj#=1 tim=1235358657293515
WAIT #3: nam='db file scattered read' ela= 11401 file#=31 block#=33 blocks=8 obj#=1 tim=1235358657305164
WAIT #3: nam='db file scattered read' ela= 3584 file#=31 block#=51 blocks=6 obj#=1 tim=1235358657309010
WAIT #3: nam='db file scattered read' ela= 897 file#=31 block#=58 blocks=7 obj#=1 tim=1235358657310135
WAIT #3: nam='db file scattered read' ela= 13634 file#=31 block#=65 blocks=8 obj#=1 tim=1235358657324125
WAIT #3: nam='db file scattered read' ela= 170 file#=31 block#=76 blocks=5 obj#=1 tim=1235358657324908
WAIT #3: nam='db file scattered read' ela= 1118 file#=31 block#=82 blocks=7 obj#=1 tim=1235358657326347
WAIT #3: nam='db file scattered read' ela= 57 file#=31 block#=91 blocks=6 obj#=1 tim=1235358657326850
WAIT #3: nam='db file scattered read' ela= 687 file#=31 block#=97 blocks=8 obj#=1 tim=1235358657327945
WAIT #3: nam='db file scattered read' ela= 4343 file#=31 block#=114 blocks=7 obj#=1 tim=1235358657332847
WAIT #3: nam='db file scattered read' ela= 83 file#=31 block#=122 blocks=7 obj#=1 tim=1235358657333376
WAIT #3: nam='db file scattered read' ela= 681 file#=31 block#=129 blocks=8 obj#=1 tim=1235358657334518
WAIT #3: nam='db file scattered read' ela= 76 file#=31 block#=138 blocks=7 obj#=1 tim=1235358657334990
WAIT #3: nam='db file scattered read' ela= 40 file#=31 block#=146 blocks=3 obj#=1 tim=1235358657335357
WAIT #4: nam='db file scattered read' ela= 8349 file#=31 block#=12 blocks=4 obj#=11214 tim=1235358657345208
WAIT #4: nam='db file scattered read' ela= 31 file#=31 block#=17 blocks=2 obj#=11214 tim=1235358657345489
WAIT #4: nam='db file scattered read' ela= 89 file#=31 block#=42 blocks=2 obj#=11214 tim=1235358657346095
WAIT #4: nam='db file scattered read' ela= 586 file#=31 block#=45 blocks=4 obj#=11214 tim=1235358657346798
WAIT #4: nam='db file scattered read' ela= 20 file#=31 block#=49 blocks=2 obj#=11214 tim=1235358657346955
WAIT #4: nam='db file scattered read' ela= 30 file#=31 block#=74 blocks=2 obj#=11214 tim=1235358657347464
WAIT #4: nam='db file scattered read' ela= 49 file#=31 block#=106 blocks=4 obj#=11214 tim=1235358657348229
WAIT #4: nam='db file scattered read' ela= 14711 file#=31 block#=111 blocks=2 obj#=11214 tim=1235358657363073
[admin oracle()]
検証ステップ5: エクステントが256K均一表領域を作り直し、上記のテストを行う
PROD(JASON)> drop tablespace tbs_test including contents and datafiles;

Tablespace dropped.

PROD(JASON)>

PROD(JASON)> create tablespace tbs_test datafile '/u02/app/oracle/oradata/PROD/tbs_test01.dbf' 
    size 20M extent management local uniform size 256K;

Tablespace created.

PROD(JASON)> create table t_obj tablespace tbs_test as select * from dba_objects;

Table created.

PROD(JASON)> select file_id,block_id,blocks from dba_extents where segment_name = 'T_OBJ';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        31          9         32
        31         41         32
        31         73         32
        31        105         32
        31        137         32

PROD(JASON)> alter session set events '10046 trace name context forever,level 12';

Session altered.

PROD(JASON)> select count(*) from t_obj;

  COUNT(*)
----------
     10242

PROD(JASON)> alter session set events '10046 trace name context off';

Session altered.

PROD(JASON)> 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
/

TRACE_FILE_NAME
-----------------------------------------------------------------------------------------------------------------
/u01/oracle/app/admin/PROD/udump/PROD_ora_25938.trc

[admin oracle()] cat /u01/oracle/app/admin/PROD/udump/PROD_ora_25938.trc | grep scatt
WAIT #1: nam='db file scattered read' ela= 360 file#=31 block#=15 blocks=16 obj#=3 tim=1235359245743983
WAIT #1: nam='db file scattered read' ela= 231 file#=31 block#=32 blocks=9 obj#=3 tim=1235359245745151
WAIT #1: nam='db file scattered read' ela= 297 file#=31 block#=43 blocks=16 obj#=3 tim=1235359245746088
WAIT #1: nam='db file scattered read' ela= 179 file#=31 block#=61 blocks=12 obj#=3 tim=1235359245746771
WAIT #1: nam='db file scattered read' ela= 259 file#=31 block#=75 blocks=16 obj#=3 tim=1235359245747512
WAIT #1: nam='db file scattered read' ela= 206 file#=31 block#=93 blocks=12 obj#=3 tim=1235359245748035
WAIT #1: nam='db file scattered read' ela= 213 file#=31 block#=106 blocks=16 obj#=3 tim=1235359245748631
WAIT #1: nam='db file scattered read' ela= 204 file#=31 block#=124 blocks=13 obj#=3 tim=1235359245749185
WAIT #1: nam='db file scattered read' ela= 113 file#=31 block#=141 blocks=7 obj#=3 tim=1235359245749656
WAIT #4: nam='db file scattered read' ela= 53 file#=31 block#=13 blocks=2 obj#=11216 tim=1235359245751087
WAIT #4: nam='db file scattered read' ela= 45 file#=31 block#=59 blocks=2 obj#=11216 tim=1235359245752191
WAIT #4: nam='db file scattered read' ela= 34 file#=31 block#=91 blocks=2 obj#=11216 tim=1235359245752753
WAIT #4: nam='db file scattered read' ela= 45 file#=31 block#=122 blocks=2 obj#=11216 tim=1235359245753322
WAIT #4: nam='db file scattered read' ela= 73 file#=31 block#=137 blocks=4 obj#=11216 tim=1235359245753686
[admin oracle()]

トレース情報によって、一度I/Oで読み込んだブロック数が最大16個になり、I/O回数が64K均一の場合より低くなった。
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License