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

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

前回マルチブロックサイズのメリットである大きな表をキャッシュしたままに
できることを検証した。
大きなサイズのマルチブロックサイズを適用した場合、キャッシュしたままに
できるメリットだけではなく、Logical I/Oを大きく減らすことができる。
以下検証を通して実証する。

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

◆検証
ブロックサイズ:2KBのテーブルとブロックサイズ:32KBのテーブルをFull Scanし、
Logical I/Oを比較する。

◇2KBブロックサイズのテーブルをFull Scan

SQL> select /* melmag5 */ count(*) from tpc2.stock_2k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:00.93

◇32KBブロックサイズのテーブルをFull Scan

SQL> select /* melmag5 */ count(*) from tpc2.stock_32k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:00.28

◇v$sqlにてLogical I/Oを比較する。

SQL> select 	sql_text,
        executions,
        disk_reads,
        buffer_gets,
        cpu_time/1000000,
        elapsed_time/1000000
    from v$sql where sql_text like 'select /* melmag5 */%';

SQL_TEXT
-----------------------------------------------------------------------
select /* melmag5 */ count(*) from tpc2.stock_2k
select /* melmag5 */ count(*) from tpc2.stock_32k

EXECUTIONS DISK_READS BUFFER_GETS CPU_TIME/1000000 ELAPSED_TIME/1000000
---------- ---------- ----------- ---------------- --------------------
         1          0      100012          .890353              .890353
         1          0        5332          .251871              .264053

上記buffer_gets列がLogical I/O回数に相当する。
2KBと32KBのブロックサイズで比較すると約18倍もの差がある。
Logical I/OはCPUのオーバーへッドを多く必要とする処理のひとつである。
cpu_time列(v$sqlのcpu_time列はマイクロ秒単位のため1000000で除算しまし
た)を確認すると約3.5倍もの差があった。
CPU使用率が高いサイトでマルチブロックサイズを適用することは、とても有
効なチューニングであることが実証できた。

今までマルチブロックサイズのメリットに焦点を当てて検証してきた。
では大きなブロックサイズを使用した場合のデメリットはないのだろうか。

◇デメリット１
～1レコードのみにアクセスする必要があるような処理の場合～
1レコードをインデックススキャンするような処理の場合、少ないブロックリ
ードにて処理が行われる。Oracleはブロック単位でアクセスをするためにブロ
ックサイズが大きく1ブロックに複数レコードが格納されているような場合、1
レコードを読込むための転送量が増えてしまい、Disk I/Oでのボトルネックと
なる。

◇デメリット２
～同一ブロックに格納されているレコードに対する同時更新処理が多い場合～
ITLエントリ待ちが発生し易くなる。ITLエントリとは、実際に更新されるブロ
ックの内部にトランザクション(ITL)エントリという、ブロックを更新するト
ランザクションの情報を保持する領域である。ブロック毎のITLエントリ数はテ
ーブル作成や変更時のパラメタinitransで指定できる。initransで指定したITL
エントリ数を使い果たすとmaxtransを最大エントリ数としてブロック内に動的
に領域を確保する。しかしながら、pctfreeが足りないなどの理由により、動的
に確保することもできなくなってITLエントリ待ちに陥る場合がある。
ブロックサイズが大きいと1ブロック当りのレコード数が多いためinitransパラ
メータを意識してテーブルの作成もしくは変更が必要になる場合がある。

◆まとめ
大きなテーブルのFull Scanや大きなインデックスに対するレンジスキャンは
大きなマルチブロックサイズを適用するとCPUやDisk I/Oのオーバーヘッドを
削減できる場合がある。反対にプライマリキーなどを使用して1レコードのみに
アクセスするような場合は大きなマルチブロックサイズはI/Oの転送量が増えて
しまい却って逆効果である。
オブジェクトに対するアクセスを分析し、ブロックサイズの指定を検討するこ
とで大きなパフォーマンスアップが得られることを今回までの検証で判った。

季節の変り目の茅ヶ崎にて

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