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

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

Full Scanを速くするテクニックとして9i新機能であるマルチブロックサイズ
を取り上げる。
9i以前のバージョンでは、インスタンス作成時に指定したdb_block_size初期
化パラメータは変更できない。
9iからも同じくdb_block_sizeの変更はできないが、表領域にブロックサイズ
を指定し、ブロックサイズ別のバッファキャッシュを指定することで、1つの
インスタンス内で複数のブロックサイズを使用できるようになった。

ブロックサイズが大きくなれば1I/Oの転送量は増えるので、パフォーマンス
は向上するはずだ。早速検証してみよう。

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

◆検証１
検証環境である”TPC2.STOCK2″のblock sizeを確認し、”TPC2.STOCK2″をFull
Scanする。

SQL> select name,value from v$parameter
     where name in ('db_file_multiblock_read_count','db_block_size');

NAME                           VALUE
------------------------------ ------
db_block_size                  4096
db_file_multiblock_read_count  16

SQL> select ts.block_size from dba_tables tb,dba_tablespaces ts
     where tb.tablespace_name=ts.tablespace_name
     and tb.owner='TPC
     and tb.table_name='STOCK2';

BLOCK_SIZE
----------
      4096

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

Elapsed: 00:00:09.42

◆検証２
ブロックサイズ=32Kの表領域を作成し”TPC2.STOCK2″を移動、”TPC2.STOCK2″を
Full Scanする。

SQL> alter system set db_32k_cache_size=200M;
SQL> create tablespace tbs32k datafile 'tbs32k.dbf' size 200M blocksize 32K;
SQL> alter table tpc2.stock2 move tablespace tbs32k;
SQL> select /* tbs32k */ count(*) from tpc2.stock2;

Elapsed: 00:00:10.56

なんか思ったほど速くならない、というか若干遅くなってるくらい。
先週行ったのと同じ手法でv$active_session_historyビューにより、1回のI/O
による最大読込みブロック数を確認する。
(先週のメルマガはこちら↓)
Full Scanを速くしちゃう　その２

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 /* tbs32k */ count(*) from tpc2.stock2'
     order by p3 desc;

EVENT                             P1         P2         P3
------------------------- ---------- ---------- ----------
db file scattered read            11       5303          2
db file scattered read            11       5087          2
db file scattered read            11       4524          2
db file scattered read            11       4116          2
db file scattered read            11       3579          2
db file scattered read            11       3220          2
db file scattered read            11       2953          2
db file scattered read            11       2366          2
db file scattered read            11       2047          2
db file scattered read            11       3212          2

P3列は読込みブロック数を表す。
db_file_multiblock_read_count=16なのにも関わらず、2ブロックしか読み込
まれていないようだ。
念のためdb_file_multiblock_read_countを大きく設定して再度実行してみる。

◆検証３
db_file_multiblock_read_count=256を設定し、ブロックサイズ=32KBの表領域
に格納されている”TPC2.STOCK2″をFull Scanする。

SQL> alter session set db_file_multiblock_read_count=256;
SQL> select /* tbs32k_2 */ count(*) from tpc2.stock2;
Elapsed: 00:00:08.96

速くなった。
1I/Oによる読込みブロック数を確認して見る。

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 /* tbs32k_2 */ count(*) from tpc2.stock2'
     order by p3 desc;

EVENT                             P1         P2         P3
------------------------- ---------- ---------- ----------
db file scattered read            11       4613         32
db file scattered read            11       1797         32
db file scattered read            11        869         32
db file scattered read            11        261         32
db file scattered read            11       4357         32
db file scattered read            11         69         32
db file scattered read            11       2538         27
db file scattered read            11        459         26
db file scattered read            11       2027         26

1I/Oによる最大読込みブロック数は”32″となり、1I/Oによる最大読込みブロッ
クの上限値”1MB”まで読み込むことができた。

32ブロック * 32KB ブロックサイズ = 1024KB

◆まとめ
マルチブロックサイズの表領域に格納される表をFull Scanした場合、表領域
のブロックサイズ * db_file_multiblock_read_countではなく、
db_block_size * db_file_multiblock_read_count が最大I/Oサイズとなった。
マルチブロックサイズを使用する場合には注意が必要だ。

ビーチサンダルでクール・ビズの茅ヶ崎にて

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