待ちイベントに関する検証 その8

<待ちイベントに関する検証 その8>
ペンネーム: ダーリン

【db_file_sequential_read/db_file_scattered_read】

先週はディスク I/O にまつわる待ちイベントに着目してみました。
今週もこの点についてもう少し見て行きます

まずは、先週取得したトレースファイルをもう一度眺めてみましょう。
ただし、”db file *** read” の部分だけ抜き出します。

------- ここから -----------------------------------------------------

$ grep "db file .* read" ora102d_ora_21787.trc

WAIT #5: nam='db file sequential read' ... block#=44179 blocks=1 ...
WAIT #5: nam='db file scattered read' ... block#=44180 blocks=5 ...
WAIT #5: nam='db file scattered read' ... block#=44185 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44194 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=44201 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44210 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=44217 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44226 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=44233 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44242 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=44249 blocks=8 ...
..........  <省略> .................

------- ここまで -----------------------------------------------------

先週見てきたとおり、”db file sequential read” の待ちイベントが発生して
いるときは “blocks=1″ で示されるとおり、単一ブロックを読み込んでいます。
一方、”db file scattered read” の待ちイベントが発生しているときは、複
数ブロックが読み込まれています。

なるほど最初を除けば 7 or 8 ブロックの読み込みですね。
7 ブロックか 8 ブロック….ん?

複数ブロックの読み込みと言えば、初期化パラメータの
“db_file_multiblock_read_count” が思い浮かびます。今の設定を確認してみ
ると、

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_file_multiblock_read_count        integer     16

ということですが、んー、なんでだ?
連続で 16 ブロック読み込むはずなのに。

トレースファイルをもうちょっと見てみましょう。
よーく見てみると、、

------- ここから -----------------------------------------------------

..........  <前略> .................
WAIT #5: nam='db file scattered read' ... block#=44249 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44258 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=44265 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44274 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=44281 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=44290 blocks=7 ...
WAIT #5: nam='db file scattered read' ... block#=50185 blocks=8 ...
WAIT #5: nam='db file scattered read' ... block#=50315 blocks=16 ...
WAIT #5: nam='db file scattered read' ... block#=50331 blocks=16 ...
WAIT #5: nam='db file scattered read' ... block#=50347 blocks=16 ...
WAIT #5: nam='db file scattered read' ... block#=50363 blocks=16 ...
WAIT #5: nam='db file scattered read' ... block#=50379 blocks=16 ...
WAIT #5: nam='db file scattered read' ... block#=50395 blocks=16 ...
WAIT #5: nam='db file scattered read' ... block#=50411 blocks=16 ...
..........  <省略> .................

------- ここまで -----------------------------------------------------

おっと、途中から 16 ブロックの読み込みになっています。
どうやら、この境目に何かありそうです。

というわけで、DBA_EXTENTS を見てみるとその理由がわかります。

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,EXTENT_ID
  2       , FILE_ID,BLOCK_ID,BYTES,BLOCKS
  3  from dba_extents
  4  where owner = 'TTT'
  5  and segment_name = 'EMP_TEST'
  6  order by extent_id ,block_id;

OWNER  SEGMENT_NAME  EXTENT_ID FILE_ID BLOCK_ID    BYTES   BLOCKS
------ ------------ ---------- ------- -------- -------- --------
TTT    EMP_TEST              0      15    44177    65536        8
TTT    EMP_TEST              1      15    44185    65536        8
TTT    EMP_TEST              2      15    44193    65536        8
 :        :                  :       :     :        :           :
TTT    EMP_TEST             11      15    44265    65536        8
TTT    EMP_TEST             12      15    44273    65536        8
TTT    EMP_TEST             13      15    44281    65536        8
TTT    EMP_TEST             14      15    44289    65536        8
TTT    EMP_TEST             15      15    50185    65536        8
TTT    EMP_TEST             16      15    50313  1048576      128
TTT    EMP_TEST             17      15    50441  1048576      128
TTT    EMP_TEST             18      15    50569  1048576      128
TTT    EMP_TEST             19      15    50697  1048576      128

20 rows selected.

トレースからは、”block#=50315″ の箇所から読み込み数が 16 に増えている
ことがわかります。 また、 DBA_EXTENTS からは BLOCK_ID=50313 のエクステ
ント以降エクステントのサイズが、1MB に増えていることが確認できます。と
いうわけで、複数ブロックの読み込みは、エクステントのサイズにも影響を受
けていたと言うわけです。

Oracle の I/O 単位は基本的に Oracle ブロックなので、最小 1 ブロックで
アクセスされます。でも、1 ブロックずつアクセスしているとトンでもなく
I/O に時間がかかってしまうことは容易に想像できます。
そこで、連続しているブロックの一括読み込みとなるわけですが、上記のトレ
ースの結果をみると、エクステントの壁は越えられなかったと言うことですね。
エクステント内のブロックは連続していることが保証されるのですが、エクス
テントをまたいでしまうと連続しているかどうかは時の運ということになるの
でこれはやむを得ません。
実際には EXTENT_ID=0 から 15 までのブロックは連続しているんですけどね。

Oracle で連続ブロック複数読み込みの I/O 単位を最適化するにはどうすれば
よいかというと、下記の URL が参考になります。

<Full Scanを速くしちゃう その1>
https://www.insight-tec.com/mailmagazine/ora3/vol249.html

OS 環境に依存するもののおおむね 1MB が最大 I/O ということです。
また、これを考慮して初期化パラメータ “db_file_multiblock_read_count”
を設定するほうがよさそうです。

これを踏まえて、かつ、複数ブロック読み込みもエクステントの壁を越えられ
ないことを考慮すると、エクステントのサイズを UNIFORM SIZE で 1MB に設
定し、かつ初期化パラメータ “db_file_multiblock_read_count” を 1MB 読み
込めるように設定するというアプローチがリーズナブルだと思えますねぇ。

今週はここまで。

Insight World 2007 はお楽しみいただけましたか?
来年もまた会場でお会いしましょう。
See You Next Year!!
茅ヶ崎にて