Full Scanを速くしちゃう　その２

＜Full Scanを速くしちゃう　その２＞
ペンネーム：グリーンペペ

前回”db_file_multiblock_read_count=128″でパフォーマンスが頭打ちになっ
た件を検証する。

◆おさらい
初期化パラメータ”db_file_multiblock_read_count”には以下公式による上限
値がある。

db_file_multiblock_read_count =< 最大I/Oサイズ / db_block_size

UNIX環境の場合の最大I/Oサイズは”1MB”である。
よって”db_block_size=4KB”の場合、”db_file_multiblock_read_count=256″が
上限であるが、パフォーマンスは”128″で頭打ちになった。

◆環境
HP-UX hp11i B.11.11
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – 64bit
初期化パラメータdb_block_size=4KB

◆検証１
v$active_session_historyビューにより、1回のI/Oによる最大読込みブロック
数を見極める。v$active_session_historyビューの詳細についてはこちら↓
https://old.insight-tec.com/mailmagazine/ora3/vol217.html

SQL> alter session set db_file_multiblock_read_count=256;

SQL> select /* 256blk */ count(*) from tpc2.stock2;

SQL> select a.event,a.p1,a.p2,a.p3 from v$active_session_history a,v$sql s
     where a.sql_id=s.sql_id and
     a.event='db file scattered read' and
   　s.sql_text='select /* 256blk */ count(*) from tpc2.stock2'
     order by p3 desc;

EVENT                             P1         P2         P3
------------------------- ---------- ---------- ----------
db file scattered read             8      44945        128
db file scattered read             8      43665        128
db file scattered read             8      41745        128
db file scattered read             8      40977        128
db file scattered read             8      38033        128
db file scattered read             8      35473        128
db file scattered read             8      33937        128
db file scattered read             8      32657        128
db file scattered read             8      30609        128
db file scattered read             8      29073        128
===以下略===

wait event='db file scattered read'のパラメータに注目して頂きたい。
各パラメータは下記を表す。

p1=データファイルid
p2=ブロック番号
p3=読込みブロック数

今回の検証環境では"db_file_multiblock_read_count"初期化パラメタは"256"
を設定しているが、実際の1回のI/Oによる最大読込みブロック数は"128"であ
った。つまり、"128"以上の指定は今回の検証環境は無効なのである。
無効であるばかりでなく、パフォーマンスに悪影響を与える場合がある。
前回の検証の実行計画に注目して頂きたい。

◆検証２
"db_file_multiblock_read_count"設定値が違う際のコストを比較する

◇Case1 db_file_multiblock_read_count=128

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5753 Card=1)
  SORT (AGGREGATE)
    TABLE ACCESS (FULL) OF 'STOCK2' (TABLE) (Cost=5753 Card=492068)

◇Case2 db_file_multiblock_read_count=256

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5594 Card=1)
  SORT (AGGREGATE)
    TABLE ACCESS (FULL) OF 'STOCK2' (TABLE) (Cost=5594 Card=492068)

Costパラメータを見ると”db_file_multiblock_read_count=256″の場合の方が
コストが低い。
つまり、Full Scanのコストが誤って低く見積もられ、オプティマイザがIndex
ScanではなくFull Scanを選択してしまう可能性がある。

◆まとめ
Full Scanを速くするには初期化パラメタ”db_file_multiblock_read_count”の
変更は有効なチューニングである。
しかしながら、実際の1回のI/Oによる最大読込みブロック数を超えて設定して
しまうとオプティマイザがFull Scanのコストを誤って低く見積もってしまい、
思いがけないパフォーマンス劣化を招く惧れがある。
貴社の環境でも、”db_file_multiblock_read_count”の上限値を見極め、有効
にチューニングすることをお勧めしたい。

ビール片手にお仕事（希望）茅ヶ崎にて

Full Scanを速くしちゃう その２