ASM を味わう ~ DISK の I/O をもっと偏らせてみる ~ その12

<ASM を味わう ~ DISK の I/O をもっと偏らせてみる ~ その12>
ペンネーム:ダーリン

今回は DISK I/O をもう少しきっちり偏らせてみます。
まず、データがどのエクステントに何レコード格納されているか確認します。
前回 31 エクステント作成されていたことは確認済みです。

SQL> select segment_name segment, segment_type seg_type, tablespace_name
  2       , extent_id, block_id, bytes, blocks
  3    from dba_extents
  4   where owner = 'DARLING'
  5     and segment_name = 'TBL001'
SQL> /

SEGMENT SEG_TYPE TABLESPACE  EXTENT_ID BLOCK_ID      BYTES     BLOCKS
------- -------- ---------- ---------- -------- ---------- ----------
TBL001  TABLE    TEST001             0        9    1048576        128
TBL001  TABLE    TEST001             1      137    1048576        128
TBL001  TABLE    TEST001             2      265    1048576        128
TBL001  TABLE    TEST001             3      393    1048576        128
TBL001  TABLE    TEST001             4      521    1048576        128
.............
TBL001  TABLE    TEST001            28     3593    1048576        128
TBL001  TABLE    TEST001            29     3721    1048576        128
TBL001  TABLE    TEST001            30     3849    1048576        128

31 rows selected.

で、それぞれに何件のレコードが存在しているかというと、

SQL> select a.extent_id
  2       , count(1)
  3    from dba_extents a
  4       , darling.tbl001 b
  5   where dbms_rowid.rowid_block_number(b.rowid) >= a.block_id
  6     and dbms_rowid.rowid_block_number(b.rowid) < a.block_id + a.blocks
  7     and a.owner = 'DARLING'
  8     and a.segment_name = 'TBL001'
  9   group by a.extent_id;

 EXTENT_ID   COUNT(1)
---------- ----------
         0        124
         1        126
         2        126
         3        126
         4        126
         5        126
         6        126
         7        126
      ....       ....
        23        126
        24        126
        25        126
        26        126
        27        126
        28        126
        29        126
        30         62

31 rows selected.

ということで、やはり 128 レコードずつ格納されてはいなかったようです。
ちなみに、EXTENT_ID = 0 の 124 というのは、1 EXTENT 128 BLOCK のうち、
4 BLOCK が SEGMENT HEADER および EXTENT HEADER となり EXTENT 内のブロ
ックの情報を格納しているため、データが 124 BLOCK 分しか格納されていな
いことを意味しています。同様に、EXTENT_ID = 1 以降のエクステントにつ
いても EXTENT HEADERとして 2 BLOCK 必要なため、データが 126 BLOCK分格
納されています。よって、1 BLOCK に 128 レコード入りきらずに、はみ出し
た分が、31 番目のエクステントに 62 レコード格納されていたことが確認で
きました。

では、次に、どのエクステントにどのレコードが格納されているかを確認しま
しょう。

  1  select extent_id,count(*),min(min_c1),max(max_c1) from (
  2  select min(b.c1) over (partition by a.extent_id) min_c1
  3        ,max(b.c1) over (partition by a.extent_id) max_c1
  4        ,a.extent_id
  5  from dba_extents a
  6     , darling.tbl001 b
  7  where dbms_rowid.rowid_block_number(b.rowid) >= a.block_id
  8    and dbms_rowid.rowid_block_number(b.rowid) < a.block_id + a.blocks
  9    and a.segment_name = 'TBL001'
 10  )
 11* group by extent_id
SQL> /

 EXTENT_ID   COUNT(*) MIN(MIN_C1) MAX(MAX_C1)
---------- ---------- ----------- -----------
         0        124           1         124
         1        126         125         250
         2        126         251         376
         3        126         377         502
         4        126         503         628
         5        126         629         754
      ....       ....       .....       .....
        25        126        3149        3274
        26        126        3275        3400
        27        126        3401        3526
        28        126        3527        3652
        29        126        3653        3778
        30         62        3779        3840

31 rows selected.

問題なく順番に格納されているようですね。

では、改めて偏り検索をやってみます。
今回は、キーを直接指定して、SQL 一発で実行してみます。

  1 select /*+ index(b ui_tbl001) */
  2         dbms_rowid.rowid_block_number(b.rowid) blk_num
  3       , b.c2
  4    from darling.tbl001 b
  5   where (   ( b.c1 >=    1 and b.c1 <=  124 )
  6          or ( b.c1 >=  377 and b.c1 <=  502 )
  7          or ( b.c1 >=  755 and b.c1 <=  880 )
  8          or ( b.c1 >= 1133 and b.c1 <= 1258 )
  9          or ( b.c1 >= 1511 and b.c1 <= 1636 )
 10          or ( b.c1 >= 1889 and b.c1 <= 2014 )
 11          or ( b.c1 >= 2267 and b.c1 <= 2392 )
 12          or ( b.c1 >= 2645 and b.c1 <= 2770 )
 13          or ( b.c1 >= 3032 and b.c1 <= 3148 )
 14          or ( b.c1 >= 3401 and b.c1 <= 3526 )
 15          or ( b.c1 >= 3779 and b.c1 <= 3840 ))
SQL> /

1311 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=49 Card=1341 Bytes=2711502)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TBL001' (TABLE) (Cost=49Card=1341 Bytes=2711502)
   2    1     INDEX (FULL SCAN) OF 'UI_TBL001' (INDEX (UNIQUE)) (Cost=26 Card=88)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1408  consistent gets
       1320  physical reads
          0  redo size
    5285567  bytes sent via SQL*Net to client
       1469  bytes received via SQL*Net from client
         89  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1311  rows processed

上記の SQL は EXTENT_IDが 0,3,6,….,30 のデータを検索しようと言う意図
があります。それぞれのエクステントにどのレコードが格納されているかを調
べたのはここでキーを指定したかったためです。インデックス検索させている
ので、テーブルには ROWID でアクセスしていることが確認できます。
さて、では、各 DISK の I/O はどうなっているでしょうか。ASM インスタンス
の V$ASM_DISK を確認してみます。

検索前
~~~~~~~~~~~~~

NAME       PATH                 READS     WRITES BYTES_READ BYTES_WRITTEN
---------- --------------- ---------- ---------- ---------- -------------
DISK_003   /dev/raw/raw5         3157        897  179957760      64230400
DISK_002   /dev/raw/raw4        11426        904  259727360      65050112
DISK_001   /dev/raw/raw3         1872      14939  149909504     121750016

検索後
~~~~~~~~~~~~~

NAME       PATH                 READS     WRITES BYTES_READ BYTES_WRITTEN
---------- --------------- ---------- ---------- ---------- -------------
DISK_003   /dev/raw/raw5         3265        899  181452800      64246784
DISK_002   /dev/raw/raw4        12650        904  269750272      65050112
DISK_001   /dev/raw/raw3         1887      14948  150888448     121786880
           検索前   検索後  差分
           ------   ------  -----
DISK_001     1872     1887     15
DISK_002    11426    12650   1224
DISK_003     3157     3265    108

SQL の統計情報からは 1320 回の Physical Read が発生している様子が確認
できました。一方 ASM からは、3 つの DISK に対して 15、1224、108 回の読
み込みが発生している様子が確認できました。

3 の倍数に当たるエクステントにのみアクセスするようにしてみたのですが、
必ずしも特定の DISK にだけアクセスが発生するわけではないようですね。と
いうことは、データをインサートする時、それぞれのエクステントの順番にデ
ータは格納されるものの、ASM 管理されている DISK へは必ずしもラウンドロ
ビン方式でエクステントが配置されているわけではないようですね。

ちゃむが沖縄にいた。なんで!?茅ヶ崎にて