Oracle 11g検証 Advanced Compression その１０
＜Oracle 11g検証 Advanced Compression その１０＞
ペンネーム： クリープ
先週は、Advanced CompressionテーブルにUpdateした時のテーブルのサイズが
拡張した原因について調査し、行移行が関係していることがわかりました。
そして、行移行先のブロックには、2048bytesのうち1129bytesしか格納されて
おらず、残りの919bytesに何が格納されているのかわからないまま前回は終わ
りました。
今週は、この919bytesに何が格納されているかについて確認します。その後で、
何故Updateで圧縮されなかったのかについての検証を実施して最後を締めくく
りたいと思います。
■■■■■概要■■■■■
１．919bytesの正体
２．Updateでサイズが拡張した原因まとめ
３．Updateで圧縮されなかった原因
４．Update処理でも圧縮される？
５．Advanced Compressionまとめ
１．919bytesの正体
それでは早速、919bytesに何が格納されているかについて調査していくことに
しましょう。
とはいうものの、ダンプに出力されていた行情報には何か余計に情報が格納さ
れているような記述は見当たりません。
ということは、ヘッダーに何か格納されているのでしょうか？
っと、ブロックダンプのヘッダー部分を眺めていると、不自然な箇所を発見し
ました。
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.004.000100ff 0x00c128af.01b9.01 C--- 0 scn 0x0000.0057dfd6
0x02 0x0008.002.00010103 0x00c12a88.01c4.12 C--- 0 scn 0x0000.0057dfd8
0x03 0x000a.004.0000fe29 0x00c124c2.01ed.08 C--- 0 scn 0x0000.0057dfda
:
: (省略)
:
0x23 0x0004.004.0000fd4a 0x00c11e02.01c3.01 C--- 0 scn 0x0000.0057e01a
0x24 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
Itlが「0x01」から「0x24」まで合計36行取得されています。
ItlとはInterested Transaction Listの略で、トランザクションがブロック内
のデータを更新する際に更新情報を格納する為に必要な領域のことを言います。
つまり、トランザクションごとに確保される領域なのですが、何故か36行もあ
ります。
この環境にアクセスしているのはクリープ以外いません。
試しに別テーブルを作成し、1行ずつ行移行を発生させてダンプを取得してみ
た所、Itlが1つずつ増加していくのが確認できました。
⇒テストの詳細は以下を参照
■Itl増加テスト
create table comp_table_advanced_test
( seq_no number, msg varchar2(10), primary key(seq_no) )
compress for all operations;
begin
for i in 1 .. 100000 loop
insert into comp_table_advanced
values( i, '0000000000' );
commit;
end loop;
end;
/
update comp_table_advanced_test set msg='1111111111' where seq_no = 1;
alter system dump datafile x block x;
update comp_table_advanced_test set msg='1111111111' where seq_no = 2;
alter system dump datafile x block x;
:
:
:
このことから、行移行が1つ発生するごとにItlが1つ確保されるようです。
そして、このItlが、サイズ拡張に影響してそうです。
では、Itlの情報1行に対してどれぐらいの領域を確保しているのでしょうか？
Itlについて調査してみると、以下マニュアルにItlのサイズに関する記載があ
りました。
▼Oracle Database 概要（11gリリース1）より
====================================================================== トランザクション・エントリに必要な領域は、オペレーティング・システムに よって異なります。ただし、多くのオペレーティング・システムでは、トラン ザクション・エントリのために約23 バイトが必要です。 ======================================================================
ここでいうトランザクション・エントリがItlのことで、Itlが1つにつき
約23bytes確保されるということになります。
ちなみに、マニュアルにはオペレーティング・システムで異なる、とあります
が、今回検証しているLinux環境では24bytesになります。
この値は、ブロックヘッダーに関する以下情報から確認することができます。
TYPE TYPE_SIZE ------------- ---------- KCBH 20 UB4 4 KTBBH 48 KTBIT 24 KDBH 14
ここに「KTBIT」という項目があり、この24bytesがトランザクション・エント
リで確保される領域になります。
つまり、Itlで確保された領域は、
24bytes * 36行 = 864
で、このブロックには864bytesがItlの領域として確保されている、というこ
とがわかりました。
つまり、行移行するだけで1行あたり24bytesも無駄に領域を使用してしまう、
ということになり、このことがテーブルサイズを3倍以上に拡張した要因とい
えます。
ちなみに、不明だった919bytesのうち864bytesについてはわかりましたが、残
りの55bytesは何が格納されているのでしょうか？
これについては、おそらく、Itlに関連する情報が格納されていると類推して
ますが、残念ながら確認することができませんでした。
２．Updateでサイズが拡張した原因まとめ
以上をまとめると、Advanced Compressionに対するUpdate処理によりサイズが
拡張してしまった原因は大きく以下3つによることがわかりました。
・圧縮データから非圧縮データへ変換
・行移行の発生によるブロックの移行
・行移行時に使用されるItl
このことはつまり、Advanced Compressionテーブルに対してUpdate処理は実行
しない方がよい、ということが言えます。
３．Updateで圧縮されなかった原因
では、何故Advanced Compressionテーブルに対するUpdate処理で圧縮が実行さ
れなかったのでしょうか？
Oracleのサポート情報をみると、Update処理で圧縮されないのはOracle11.1.0.6
の仕様（もしくは不具合）とありました。つまり、Update処理では現行の最新
バージョンである11.1.0.6では圧縮されません。
現在、これは、サポート情報では調査中というステータスになっていますが、
（KROWN #127414）。もし仕様ということであれば、トランザクション処理など
でこの圧縮テーブルを利用するようなケースは激減してしまいます。
仕様ではなく不具合ということで次バージョンで改善されることを祈りましょ
う。
これで、Update処理では圧縮されない原因がわかりましたが、それでは本当に
Updateで圧縮されないのでしょうか？
実は、Update実行時に空き領域（PCTFREE以外）がなくなった場合、Insert処理
同様圧縮処理が実行されるようなことはないのでしょうか？
念のため検証で確認してみることにしましょう。
４．Update処理でも圧縮される？
それでは、検証してみることにしましょう。
今回の検証は、PCTFREE以外の空き領域を残した状態でデータをInsertします。
その後で、空きがなくなるようなUpdate処理を実行してデータが圧縮されるか
どうかを確認してみることにします。
それでは、ブロックを空き領域がなくなる直前の状態にしてみましょう。
作成方法ですが、前回の検証で、以下テーブルに91行目をInsertした時点で圧
縮処理が実行されました。このテーブルに90行のデータを格納すれば、空き領
域がなくなる直前の状態ということになります。この後にUpdate処理で空き領
域を更新して、圧縮処理が実行されるかどうかを確認してみましょう。
SQL> create table comp_table_advanced 2 ( seq_no number, msg varchar2(10), primary key(seq_no) ) 3 compress for all operations;
ちなみに、上記テーブルではmsgカラムのサイズが10bytesです。今回Insertし
たデータも10bytesなのでUpdateでサイズを大きくすることができません。です
ので、msgのサイズを10bytesから20bytesに変更してUpdateでサイズが拡張する
ようなテーブルで作成しましょう。
それでは、環境作成！
SQL> create table comp_table_update_test 2 ( seq_no number, msg varchar2(20), primary key(seq_no) ) 3 compress for all operations; SQL> begin 2 for i in 1 .. 90 loop 3 insert into comp_table_update_test 4 values( i, '0000000000' ); 5 commit; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed.
テーブルが作成されました。またデータも90行挿入しています。ブロックダン
プを取得してブロックの空きを見てみましょう。
SQL> select segment_name,file_id,block_id,blocks 2 from dba_extents 3 where segment_name = 'COMP_TABLE_UPDATE_TEST' 4 and owner='TEST' 5 and extent_id=0; SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS ------------------------------------ ---------- ---------- ---------- COMP_TABLE_UPDATE_TEST 4 26977 32 SQL> alter system dump datafile 4 block min 26977 block max 27009; System altered.
・ダンプファイル
1 data_block_dump,data header at 0x261a5864 2 =============== 3 tsiz: 0x798 4 hsiz: 0xc6 5 pbl: 0x261a5864 6 76543210 7 flag=-------- 8 ntab=1 9 nrow=90 10 frre=-1 11 fsbo=0xc6 12 fseo=0x19e 13 avsp=0xd8 14 tosp=0xd8
ダンプファイルを見ると13行目にavspが0xd8とあります。avspは空き領域のサ
イズなので、10進数に変換すると216bytesの空きがあることになり、当メルマ
ガ第7回の圧縮直前のサイズと同じになります。
この時は、216bytesの空きに対して17bytesの行を挿入した時に圧縮処理が実
行されています。ですので、10bytesの文字列を20bytesに変更する行を2行実
行して20bytes拡張させます。これで、データの空き領域がなくなりますので、
圧縮処理が実行されるかもしれません。
ということで確認してみることにしましょう。
それでは実行！
SQL> update comp_table_update_test 2 set msg = '00000000000000000000' 3 where seq_no In( 1, 2 ); 2 row updated. SQL> commit; Commit complete. SQL> alter system dump datafile 4 block min 26977 block max 27009; System altered.
・ダンプファイルのavsp
1 data_block_dump,data header at 0x261a5864 2 =============== 3 tsiz: 0x798 4 hsiz: 0xc6 5 pbl: 0x261a5864 6 76543210 7 flag=-------- 8 ntab=1 9 nrow=90 10 frre=-1 11 fsbo=0xc6 12 fseo=0x168 13 avsp=0xc4 14 tosp=0xc4
avspをみると、0xc4と10進数で196bytesになってしまいました。また、行デー
タをみても「tab 0」のみで「tab 1」が存在していないことから、圧縮されな
かったようです。
1 tab 0, row 0, @0x77d 2 tl: 27 fb: --H-FL-- lb: 0x0 cc: 2 3 col 0: [ 2] c1 02 4 col 1: [20] 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 : : : 5 tab 0, row 89, @0xe7 6 tl: 17 fb: --H-FL-- lb: 0x0 cc: 2 7 col 0: [ 2] c1 5b 8 col 1: [10] 30 30 30 30 30 30 30 30 30 30 9 end_of_block_dump
やはりUpdateでは圧縮されないのでしょうか？
もう少し多くの行をUpdateしてみたら圧縮されたりして！？
さらに、追加で10行をUpdateしてみることにします。
SQL> update comp_table_update_test 2 set msg = '00000000000000000000' 3 where seq_no >= 3 4 and seq_no commit; Commit complete. SQL> alter system dump datafile 4 block min 26977 block max 27009; System altered.
・ダンプファイルのavsp
1 data_block_dump,data header at 0x23df606 2 =============== 3 tsiz: 0x798 4 hsiz: 0xc6 5 pbl: 0x23df6064 6 76543210 7 flag=-------- 8 ntab=1 9 nrow=90 10 frre=-1 11 fsbo=0xc6 12 fseo=0xf3 13 avsp=0x60 14 tosp=0x60
avspが0x60、10進数で96bytesまで小さくなってしまいました。やはり圧縮さ
れないようです。
ついでに96bytesの空きがなくなるようにさらに10行をUpdateしてみましょう。
SQL> update comp_table_update_test 2 set msg = '00000000000000000000' 3 where seq_no >= 14 4 and seq_no commit; Commit complete. SQL> alter system dump datafile 4 block min 26977 block max 27009; System altered.
・ダンプファイルのavsp
1 data_block_dump,data header at 0x2600f864
2 ===============
3 tsiz: 0x798
4 hsiz: 0xc6
5 pbl: 0x2600f864
6 76543210
7 flag=--------
8 ntab=1
9 nrow=90
10 frre=-1
11 fsbo=0xc6
12 fseo=0xcc
13 avsp=0x6
14 tosp=0xe
・行情報
1 tab 0, row 22, @0x55a
2 tl: 9 fb: --H----- lb: 0x1 cc: 0
3 nrid: 0x01006973.0
4 tab 0, row 23, @0x549
nridという行の出力から行移行が発生してしまい、圧縮は実行されませんでし
た。
やはり、仕様！？によりUpdateでは圧縮されないようです。
５．Advanced Compressionまとめ
以上、全10回に渡ってAdvanced Compressionの検証を行ってきました。
今回の検証で、通常のInsertに関しては問題なく圧縮されているので非常に有
効ですが、Updateで圧縮されないのでは、使用されるシーンが大幅に限られて
しまいます。
11.1.0.7以降でUpdateでも圧縮されることを祈りつつAdvanced Compressionの
検証を終わりにします。
Updateで圧縮されるようになったらまたお会いしましょう！
Oracle 11.1.0.7はいつリリースされるのでしょうか。。。
恵比寿にて