Oracle 11g検証 Advanced Compression その８
＜Oracle 11g検証 Advanced Compression その８＞
ペンネーム： クリープ
先週は、お盆休みの為当メルマガはお休みでした。
一週空いてしまうと、どのような検証をしたか忘れていると思いますので（自
分を含む）、ここで改めて前回までの検証の流れを振り返っておきましょう。
今回のテーマであるAdvanced Compressionとは、テーブルに格納されるデータ
を圧縮する、という機能です。
テーブルデータを圧縮する機能は10gにもありましたが、10gまでの圧縮機能は
大量データを一括でロードするAPPEND ヒントを用いたようなInsert処理でし
か圧縮されませんでした。つまり、通常のInsertやUpdateでは圧縮されません
でした。
Advanced Compressionでは、通常の更新処理でも圧縮されるようになっていま
す。
前回までの検証では、通常のInsert処理を実行した時のサイズと、圧縮される
タイミングについて検証してきました。
今週からは、Advanced CompressionテーブルにUpdate処理を実行した時の検証
をしていきましょう。
■■■■■概要■■■■■
１．Advanced CompressionテーブルへのUpdate検証
２．検証環境作成
３．Update処理を実行した時のサイズ比較
４．テーブルサイズの拡張と行移行
■環境
RedHat Enterprise Linux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6 – Production
１．Advanced CompressionテーブルへのUpdate検証
前回まではAdvanced CompressionテーブルへのInsert検証を実施しました。
その結果、通常のInsert処理でも圧縮されるが、圧縮されるタイミングは行デ
ータの空き領域がなくなった時、ということがわかりました。
では、Updateではどうでしょうか？通常のUpdate実行時でも圧縮されるのでし
ょうか？また、圧縮されるタイミングはInsert時と同様、行データの空き領域
がなくなった時でしょうか？
さらに、11gより前の圧縮方式では圧縮データをUpdateすると行移行が発生し
ていましたが（当メルマガの第2回参照）、Advanced Compressionテーブルに
対してUpdateしても行移行は発生してしまうのでしょうか？
検証して確認してみることにしましょう。
２．検証環境作成
検証は、通常テーブル、ダイレクト圧縮テーブル（11gより前の圧縮方式）、
Advanced Compressionテーブル（11gの圧縮方式）のデータをUpdateで更新し
て、そのサイズや圧縮タイミングなどを確認してみることにします。
まずは、環境作成！
・通常テーブル
SQL> create table comp_table_normal 2 ( seq_no number, msg varchar2(10), primary key(seq_no) ); Table created.
・ダイレクト圧縮テーブル（11gより前の圧縮方式）
SQL> create table comp_table_direct 2 ( seq_no number, msg varchar2(10), primary key(seq_no) ) 3 compress for direct_load operations; Table created.
・Advanced Compressionテーブル（11gの圧縮方式）
SQL> create table comp_table_advanced 2 ( seq_no number, msg varchar2(10), primary key(seq_no) ) 3 compress for all operations; Table created.
・データ作成
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into comp_table_normal
4 values( i, '0000000000' );
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> insert /*+ append */ into comp_table_direct
2 select * from comp_table_normal;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into comp_table_advanced
4 values( i, '0000000000' );
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('TEST');
PL/SQL procedure successfully completed.
通常テーブル、ダイレクト圧縮テーブル（11gより前の圧縮方式）、Advanced
Compressionテーブル（11gの圧縮方式）を作成し、各テーブルに10万件のデー
タを格納しました。
３．Update処理を実行した時のサイズ比較
それでは、各テーブルにUpdateを実行してそのサイズを比較してみることにし
ましょう。Updateする前のサイズは以下の通りでした。
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( 'COMP_TABLE_NORMAL', 5 'COMP_TABLE_DIRECT', 6 'COMP_TABLE_ADVANCED' ) 7 and name = 'db_block_size'; TABLE_NAME BLOCK_SIZE size(KB) ------------------------------ -------------------- ---------- COMP_TABLE_NORMAL 2048 2974 COMP_TABLE_DIRECT 2048 1362 COMP_TABLE_ADVANCED 2048 1966
通常テーブルが最も大きく、次にAdvanced Compressionテーブル、そして最も
小さいのがダイレクト圧縮テーブルとなっています。
では、全テーブルにUpdateを実行して、サイズを確認してみることにしましょ
う。Updateする値は、Update前のサイズと同じ10bytesの値を更新しています。
つまり、通常のテーブルであれば、テーブルのサイズが変わることはないサイ
ズになります。それでは実行！
SQL> begin 2 for i in 1 .. 100000 loop 3 update comp_table_normal set msg='1111111111' where seq_no = i; 4 update comp_table_direct set msg='1111111111' where seq_no = i; 5 update comp_table_advanced set msg='1111111111' where seq_no = i; 6 commit; 7 end loop; 8 end; 9 / PL/SQL procedure successfully completed.
Updateが完了しました。さて、テーブルのサイズはどう変化しているのでしょ
うか？
今回の検証で実行したUpdate処理ではInsertで空きがないブロックのデータを
更新しています。この為、予想としては、Update処理を実行するたびに圧縮処
理が実行されてサイズはほとんど大きくならない、と考えられます。
それでは、各テーブルのサイズを見てみることにしましょう。
まずは、通常テーブルとダイレクト圧縮テーブルから確認！
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( 'COMP_TABLE_NORMAL',
5 'COMP_TABLE_DIRECT' )
6 and name = 'db_block_size';
TABLE_NAME BLOCK_SIZE size(KB)
------------------------------ -------------------- ----------
COMP_TABLE_NORMAL 2048 2974
COMP_TABLE_DIRECT 2048 7088
通常テーブル（COMP_TABLE_NORMAL）はUpdate前と同じサイズ、ダイレクト圧
縮テーブル（COMP_TABLE_DIRECT）は、Update前より約5倍拡張しています。
圧縮されていたデータがUpdateにより非圧縮データになっただけであれば、通
常テーブルのサイズと同じになるはずです。ですが、実際は通常テーブルより
大幅にサイズが大きくなっています。この点については、第2回で検証し、原
因が行移行であることを確認しました。
↓詳細は第2回参照
https://old.insight-tec.com/mailmagazine/ora3/vol393.html
では、Advanced Compressionテーブルを確認してみることにしましょう。
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 = 'COMP_TABLE_ADVANCED' 5 and name = 'db_block_size'; TABLE_NAME BLOCK_SIZE size(KB) ------------------------------ -------------------- ---------- COMP_TABLE_ADVANCED 2048 7006
Update前の1966bytesから7006bytesと、3倍以上に拡張しています。
ということは、Advanced Compressionテーブルもダイレクト圧縮テーブル同様、
データを更新した時に行移行が発生してしまっているということでしょうか？
４．テーブルサイズの拡張と行移行
Advanced Compressionテーブルでも行移行が発生しているかどうか、確認して
みることにしましょう。
確認方法は、第2回で実行した通りANALYZEコマンドを実行し、user_tablesの
chain_cnt列を参照します。chain_cntでは行移行した行の行数を確認すること
ができます。
それでは、ANALYZEコマンドを実行！
SQL> analyze table comp_table_normal compute statistics; Table analyzed. SQL> analyze table comp_table_direct compute statistics; Table analyzed. SQL> analyze table comp_table_advanced compute statistics; Table analyzed. SQL> select t.table_name, num_rows, chain_cnt 2 from sys.user_tables t,v$parameter p 3 where table_name In( 'COMP_TABLE_NORMAL', 4 'COMP_TABLE_DIRECT', 5 'COMP_TABLE_ADVANCED' ) 6 and name = 'db_block_size'; TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- COMP_TABLE_NORMAL 100000 0 COMP_TABLE_DIRECT 100000 91840 COMP_TABLE_ADVANCED 100000 82594
やはり、行移行が発生していました。ダイレクト圧縮テーブル、Advanced
Compressionテーブルともに10万件中8万件以上で行移行が発生しています。こ
のことから、行移行がテーブルサイズを大きくした原因と考えられます。
それにしても、圧縮テーブルのサイズは通常テーブルに比べて2倍以上とかな
り大きくなっています。これは、行移行のみが原因なのでしょうか？もしかし
たら、他に何か原因があるのではないでしょうか？
そもそも、ちゃんと圧縮されていればこのようなサイズにはならなかったはず
なのです。一体圧縮テーブルで何が起こっているのでしょうか？
これらについては次週検証していくことにしましょう。
それでは、また来週！
故障したパソコンを修理したら別の箇所が壊れて返ってきました。。。
恵比寿にて