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

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

今回から、Full Scanを如何に速くするかに焦点を当てて連載する。
Full Scanを速くするにはIndexを作成すればよいという話題は巷に満ちあふれ
ているので当メルマガでは他のアプローチから展開していきたい。

まず、Full Scanを速くするには初期化パラメタ”db_file_multiblock_read_count”
を変更することから推奨したい。
初期化パラメタによるチューニングはお手軽に実施可能なチューニングだから
だ。”db_file_multiblock_read_count”にはFull Scan時に1回のI/O操作で読み
取られる最大ブロック数を指定する。

◆検証１
“db_file_multiblock_read_count”の値を変更した場合のFull Scanのレスポン
スを比較する

◆環境
HP-UX hp11i B.11.11
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – 64bit

◆Case1 db_file_multiblock_read_count=1

SQL> alter session set db_file_multiblock_read_count=1;
SQL> select /* 1blk */ count(*) from tpc2.stock2;

Elapsed: 00:00:14.61

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45771 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'STOCK2' (TABLE) (Cost=45771 Card=492068)

◆Case2 db_file_multiblock_read_count=8

SQL> alter session set db_file_multiblock_read_count=8;
SQL> select /* 8blk */ count(*) from tpc2.stock2;

Elapsed: 00:00:08.81

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10479 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'STOCK2' (TABLE) (Cost=10479 Card=492068)

◆Case3 db_file_multiblock_read_count=128

SQL> alter session set db_file_multiblock_read_count=128;
SQL> select /* 128blk */ count(*) from tpc2.stock2;

Elapsed: 00:00:05.66

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

◆Case4 db_file_multiblock_read_count=256

SQL> alter session set db_file_multiblock_read_count=256;
SQL> select /* 256blk */ count(*) from tpc2.stock2;

Elapsed: 00:00:05.69

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

当然ながら”db_file_multiblock_read_count”の値が大きいほどパフォーマン
スは向上する。しかしながら、”db_file_multiblock_read_count=128″でレス
ポンス時間は頭打ちになってしまった。
どうやら、”db_file_multiblock_read_count”には上限があるようだ。

◆検証２
“db_file_multiblock_read_count”の上限値を調査する

SQL> alter session set db_file_multiblock_read_count=999;
SQL> sho parameter db_file_multiblock_read_count

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- -------
db_file_multiblock_read_count        integer                256

“db_file_multiblock_read_count=999″と明示的に設定しても”256″で頭打ちに
なっている。Oracle社のサポートページで確認すると、UNIXで9iR2以上の環境
において、”db_file_multiblock_read_count”の上限値は以下の公式による。

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

最大I/OサイズはOS環境に依存し、UNIXの場合は”1MB”である。
弊社テストではLinux,Windows環境においても”1MB”であった。
今回のテスト環境では”db_block_size=4KB”であるので、”db_file_multiblock_read_count”
は 1MB / 4KB = “256” が上限値である。
しかしながら、今回の検証では”128″でレスポンスは頭打ちになってしまって
いる。次回はこの謎を解明したい。

そろそろウチワ片手にお仕事。茅ヶ崎にて。

読者からのFull Scanを速くする秘策を募集します。
随時、メルマガで取り上げていきますのでよろしくお願いします。

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