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

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

前回はFull Scanを速くするために、32Kブロックサイズに変更して検証してみ
た。だが、32kブロックサイズをマルチブロックサイズとして採用した場合の
真のメリットはディスクI/Oのオーバーヘッド云々ということではない。
マルチブロックサイズのアーキテクチャを使用することで大きな表を全てメモ
リ上にキャッシュしてしまうことが可能になる。
defaultバッファキャッシュのみの構成の場合は、大きな表をバッファ上に全
てキャッシュしておくことは非現実的だ。
なぜなら、Oracleのアーキテクチャーとしてdb_cache_size * 2%を超えるよう
な大きな表をFull Scanした場合は、LRUリストの最後方に置かれ長くキャッシ
ュされることはない。
よって、大きな表のFull Scanの場合は毎実行のたびにディスクI/Oが発生して
しまう。仮にcacheオプション付きで表の属性を変更し、ブロックをキャッシ
ュに残るようにしたとしたら、他の表のブロックがキャッシュから追い出され
てしまうため、非効率的だ。
しかし、バッファキャッシュが別れていればどうだろうか。
大きい表専用のバッファキャッシュとして32kバッファキャッシュを使用すれ
ば例えLRUリストの最後方に置かれようが、追い出される心配はない。
もちろん、大きな表を格納できるだけの物理メモリを用意する必要がある。
32-bitOSの場合はSGAのサイズに制限があるので難しいかもしれない。

◆検証１
大きな表をFull Scan後、どれくらいのブロックがdefaultバッファキャッシュ
上にキャッシュされているか検証する。

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

まず、テスト対象の大きな表である”TPC2.STOCK2″の現在のHWMブロックサイ
ズを調べるため、統計情報を取得

SQL> exec dbms_stats.gather_table_stats('TPC2','STOCK2');

PL/SQL procedure successfully completed.

現在の”TPC2.STOCK2″のHWMサイズを計算する
[/sql]

SQL> select t.blocks * p.value /1024 /1024 from dba_tables t, v$parameter p
where t.owner=’TPC2′ and t.table_name=’STOCK2′ and p.name=’db_block_size’;

T.BLOCKS*P.VALUE/1024/1024
————————–
179.070313

[/sql]

“TPC2.STOCK2″をFull Scan

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

v$bhビューを用いてバッファキャッシュにキャッシュされている”TPC2.STOCK2″
のサイズを求める

SQL> select a.cnt * p.value /1024 /1024
     from v$parameter p,
    (select count(b.objd) cnt
        from dba_objects o , v$bh b
            where 	o.data_object_id=b.objd
        and 	o.owner='TPC2'
        and 	o.object_name='STOCK2') a
     where p.name='db_block_size';

A.CNT*P.VALUE/1024/1024
-----------------------
               29.34375

“TPC2.STOCK2″のHWM(ハイウォーターマーク)ブロックは179MBであるが、バッ
ファキャッシュ上に残留しているのは僅か29MBであった。

◆検証２
32kブロックサイズに大きな表を移動し、Full Scanを実行。
32kバッファキャッシュ上にどれくらいのブロックがキャッシュされているか
検証する。

32k表領域へ移動

SQL> alter table tpc2.stock2 move tablespace tbs32k;

Table altered.

再度HWMブロック取得のため統計情報取得

SQL> exec dbms_stats.gather_table_stats('TPC2','STOCK2');

PL/SQL procedure successfully completed.

HWMサイズを計算する

SQL> select t.blocks * 32 /1024 from dba_tables t
     where t.owner='TPC2' and t.table_name='STOCK2';

T.BLOCKS*32/1024
----------------
          166.25

Full Scan実行

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

32kバッファキャッシュにキャッシュされているサイズを求める

SQL> select count(b.objd) * 32 / 1024
        from dba_objects o , v$bh b
            where 	o.data_object_id=b.objd
        and 	o.owner='TPC2'
        and 	o.object_name='STOCK2'

COUNT(B.OBJD)*32/1024
---------------------
            166.28125

HWMブロックサイズ166MBが、そのままキャッシュされていることが確認できた。

今週はここまで

梅雨はどこへ行ったんだろうの茅ヶ崎にて

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