Oracle 11g検証 Advanced Compression　その２

＜Oracle 11g検証 Advanced Compression　その２＞
ペンネーム：クリープ

先週は、11g検証と言いながら9iの圧縮機能についておさらいしました。
今週も（しつこく）9iR2でのセグメント圧縮について確認してみましょう。

■■■■■概要■■■■■
１．9iR2セグメント圧縮機能について Part2
２．圧縮データにUPDATE

■環境
RedHatLinux ES4 Update 5
Oracle Database 9i Enterprise Edition Release 9.2.0.8 – Production

１．9iR2セグメント圧縮機能について Part2
さて、9iのセグメント圧縮では、異なるデータが大量に格納されている場合に
圧縮効率が低いこと、通常のINSERT処理ではデータが圧縮されないことが確認
できました。
では、既に圧縮データとして格納されているデータに対してUPDATE処理を実行
した時はどうなるでしょうか？
確認してみることにしましょう。

２．圧縮データにUPDATE
それでは早速、圧縮データに対してUPDATEを実行してみることにします。
今回は圧縮テーブルと非圧縮テーブルを作成し、それぞれのテーブルに対して
UPDATEを実行し、テーブルサイズがどのように変化するか比較してみることに
します。
まずは、環境を作成。

SQL> create table normal_table
　　　　2  ( seq_no number, msg varchar2(10), primary key(seq_no) );

Table created.

SQL> create table comp_table
　　　　2  ( seq_no number, msg varchar2(10), primary key(seq_no) )
　　　　3  compress;

Table created.

SQL> begin
　　　　2  for i in 1 .. 100000 loop
　　　　3     insert into normal_table
　　　　4        values( i, '0000000000' );
　　　　4     commit;
　　　　5  end loop;
　　　　6  end;
　　　　7  /

PL/SQL procedure successfully completed.

SQL> insert /*+ append */ into comp_table
　　　　2    select * from normal_table;
SQL> commit;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select t.table_name, p.value block_size,
　　　　2         t.blocks * p.value/1024 "size(KB)"
　　　　3    from sys.user_tables t,v$parameter p
　　　　4   where table_name In( 'NORMAL_TABLE', 'COMP_TABLE' )
　　　　5     and name = 'db_block_size';

TABLE_NAME                     BLOCK_SIZE             size(KB)
------------------------------ -------------------- ----------
NORMAL_TABLE                   2048                       2436
COMP_TABLE                     2048                       1276

非圧縮テーブルに非圧縮データが10万件、圧縮テーブルに圧縮データが10万件
格納されました。では、この状態で、各テーブルに対して異なる値をUPDATEし
てみましょう。今回の検証では、UPDATEした後の値と前の値でサイズがかわら
ない値を更新することにします。通常のテーブルに対して更新した場合、テー
ブルサイズはかわりません。では、圧縮テーブルの場合はどのような結果にな
るのでしょうか？
早速試してみることにしましょう。

SQL> BEGIN
　　　　2  FOR i IN 1 .. 100000 LOOP
　　　　3   UPDATE NORMAL_TABLE SET MSG= '1111111111' WHERE SEQ_NO = i;
　　　　4   COMMIT;
　　　　5  END LOOP;
　　　　6  END;
　　　　7  /

PL/SQL procedure successfully completed.

SQL> BEGIN
　　　　2  FOR i IN 1 .. 100000 LOOP
　　　　3   UPDATE COMP_TABLE SET MSG= '1111111111' WHERE SEQ_NO = i;
　　　　4   COMMIT;
　　　　5  END LOOP;
　　　　6  END;
　　　　7  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select t.table_name, p.value block_size,
　　　　2         t.blocks * p.value/1024 "size(KB)"
　　　　3    from sys.user_tables t,v$parameter p
　　　　4   where table_name In( 'NORMAL_TABLE', 'COMP_TABLE' )
　　　　5     and name = 'db_block_size';

TABLE_NAME                     BLOCK_SIZE             size(KB)
------------------------------ -------------------- ----------
NORMAL_TABLE                   2048                       2436
COMP_TABLE                     2048                       6672

なんと！圧縮テーブルのサイズが6倍近くになってます！
これは、圧縮データが非圧縮データになってしまった為サイズが大きくなった
と考えられます。でも、それだけで6倍近くも大きくなってしまうのでしょう
か？まさか。。。

SQL> analyze table normal_table compute statistics;

Table analyzed.

SQL> analyze table comp_table compute statistics;

Table analyzed.

SQL> select t.table_name, chain_cnt
　　　　2    from sys.user_tables t,v$parameter p
　　　　3   where table_name In( 'NORMAL_TABLE', 'COMP_TABLE' )
　　　　4     and name = 'db_block_size';

TABLE_NAME            CHAIN_CNT
-------------------- ----------
NORMAL_TABLE                  0
COMP_TABLE                99981

やっぱり！chain_cntに値が格納されていました。
chain_cntとは行移行または行連鎖が発生した行数を格納している項目で、
100000行に対して99981行とほとんどの行で発生していたことがわかります。
っと、ここで行移行・行連鎖について簡単に説明しておきます。

行移行
変更前より大きい値にUPDATEした場合、ブロックに確保されている空き領域を
使用しますがその空き領域が足りなくなった時に新たなブロックに行を移行す
ることをいいます。移行前のブロックにはROWIDが格納されます。

行連鎖
1レコード長がデータブロックサイズを超えてしまった場合に発生します。
今回の検証環境では、1ブロック2048Bytesの為、1行で2048Bytesを超える長さ
の行が格納された時に別のブロックにまたがって値が格納されます。

行移行・行連鎖が発生した場合、通常であれば1ブロックの読み込みで済むと
ころを2ブロック以上読み込む必要がある為、不要なオーバーヘッドが発生し
ます。

通常、同じ長さの値をUPDATEした場合行移行は発生しません。例えば、
varchar2(10)の項目に「0」という文字列が格納されていて、「1」という値を
更新しても、1行の長さは変わらない為サイズは拡張せず行移行も発生しませ
ん。また、「0」という文字列を「1111111111」という値に更新した場合でも
必ず大きくなるわけではなく、ブロックにある空き領域を使用して値を格納し
ます。大量の更新が発生しブロックの空きがなくなった場合、更新前より大き
な値を格納できず行移行が発生します。

今回の検証では、「0000000000」という値に対して、「1111111111」という同
じサイズの値を更新したものの、圧縮されていたデータが非圧縮データとして
格納される為、行のサイズが拡張してしまい行移行が発生した、と考えられま
す。
圧縮データを更新する時は十分に注意しましょう。

ちなみに、CHAIN_CNTの値はDBMS_STATSでは取得されない為、ANALYZEコマンド
を実行する必要がありますので、お気をつけ下さい。

まとめ
１．圧縮データにUPDATEを実行すると、行移行が発生し、基テーブルのサイ
ズを超えて拡張してしまう可能性がある
２．行移行した行を確認する時はANALYZE実行後USER_TABLESのCHAIN_CNTを
参照する

以上、9iR2でのセグメント圧縮について検証してきました。
今回と前回の検証でわかったことは、この9iR2で追加されたセグメント圧縮と
いう機能は、テーブルを全て圧縮テーブルに変更しちゃいましょう！という機
能ではなく、あまり変更されないデータ、例えば過去のデータやデータウエア
ハウスなどのデータを格納する時にサイズを小さくして格納しておく為の機能
、ということができます。

9iR2でのセグメント圧縮についておさらいをしてきましたが、次回はいよいよ
11gのAdvanced Compressionについて検証していきます。
9iR2と比較してどのような点がAdvancedになったのか？これからがいよいよ11g
の検証に入ります。乞うご期待！

7月7日はインサイトの13回目の誕生日。人間なら13歳。そろそろ反抗期！？
恵比寿にて

Oracle 11g検証 Advanced Compression その２