Oracle 11g検証 Advanced Compression その７
＜Oracle 11g検証 Advanced Compression その７＞
ペンネーム： クリープ
先週は、Advanced Compressionテーブルのサイズがダイレクト圧縮テーブルよ
り大きくなる原因を調査してました。結果、Advanced Compressionテーブ
ルはあるしきい値を超えたタイミングでデータが圧縮される為、一部のデータ
が圧縮されていないことが影響してサイズが大きくなっていることが判りまし
た。
では、この圧縮されるタイミングを制御しているしきい値とはどのようなもの
なのでしょうか！？
検証で確認してみることにしましょう。
■■■■■概要■■■■■
１．圧縮されるタイミングを制御するしきい値とは？
２．何行Insertした時に圧縮されるのか？
３．ダンプ・ファイル比較
４．圧縮処理が実行されるしきい値の正体
■環境
RedHat Enterprise Linux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6 – Production
１．圧縮されるタイミングを制御するしきい値とは？
それでは早速、データが圧縮されるタイミングを制御しているしきい値につい
て、検証で明らかにしていくことにしましょう。
検証方法ですが、まずはテーブルにデータをInsertした後に毎回ダンプファイ
ルを出力して、何行目をInsertした時に圧縮処理が実行されるのかを確認しま
す。
そして、圧縮される前と後のダンプを比較して、その差異からしきい値となる
情報が出力されていないか確認してみることにします。
まずは、何行Insertした時に圧縮されるのか？について確認してみましょう。
２．何行Insertした時に圧縮されるのか？
ということで、まずは環境を作成します。test_thresholdというAdvanced
Compressionテーブルを作成してこのテーブルにInsert処理を実行します。
Insertは、1ブロックに格納されていた行数である148行を挿入し、各行の
Insert後にダンプを取得します
※格納されていた行数については、第3回メルマガ参照
https://old.insight-tec.com/mailmagazine/ora3/vol394.html
そして、ダンプファイルから圧縮テーブルの特徴であるbindmpやシンボル表な
どの記載があるダンプファイルをピックアップして、どの行から圧縮されたの
かを特定してみましょう。では早速作成開始！
SQL> create table test_threshold 2 ( seq_no number, msg varchar2(10), primary key(seq_no) ) 3 compress for all operations; Table created. SQL> select segment_name,file_id,block_id,blocks 2 from dba_extents 3 where segment_name = 'TEST_THRESHOLD' 4 and owner='TEST' 5 and extent_id=0; SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS ------------------------------------ ---------- ---------- ---------- TEST_THRESHOLD 4 5409 32 SQL> begin 2 for i in 1 .. 148 loop 3 insert into test_threshold values( i, '0000000000' ); 4 execute immediate 5 'alter session set tracefile_identifier = ' || i; 6 execute immediate 7 'alter system dump datafile 4 block min 5409 block max 5441'; 8 commit; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed.
ダンプファイルは1行ごとにファイル名を異なったものにする為、
tracefile_identifierを指定して実行します。
これで、 1行insertする度にダンプ・ファイルが作成され、合計 148個のダン
プ・ファイルが作成されます。
出力されたダンプ・ファイルを確認したところ、90行目と91行目のデータブロ
ックに関する情報に明らかな差異が見られました。
▼90行目Insert後のダンプ・ファイル
1 ===============
2 tsiz: 0x798
3 hsiz: 0xc6
4 pbl: 0x208e3864
5 76543210
6 flag=--------
7 ntab=1
8 nrow=90
9 frre=-1
10 fsbo=0xc6
11 fseo=0x19e
12 avsp=0xd8
13 tosp=0xd8
:
: (省略)
:
14 block_row_dump:
15 tab 0, row 0, @0x787
16 tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
17 col 0: [ 2] c1 02
18 col 1: [10] 30 30 30 30 30 30 30 30 30 30
19 tab 0, row 1, @0x776
20 tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
21 col 0: [ 2] c1 03
22 col 1: [10] 30 30 30 30 30 30 30 30 30 30
▼91行目Insert後のダンプ・ファイル
1 ===============
2 tsiz: 0x798
3 hsiz: 0xdc
4 pbl: 0x208e3864
5 76543210
6 flag=-0------
7 ntab=2
8 nrow=92
9 frre=-1
10 fsbo=0xdc
11 fseo=0x4a9
12 avsp=0x373
13 tosp=0x373
:
: (省略)
:
14 block_row_dump:
15 tab 0, row 0, @0x78b
16 tl: 13 fb: --H-FL-- lb: 0x0 cc: 1
17 col 0: [10] 30 30 30 30 30 30 30 30 30 30
18 bindmp: 00 5a d2 30 30 30 30 30 30 30 30 30 30
19 tab 1, row 0, @0x783
20 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
21 col 0: [ 2] c1 02
22 col 1: [10] 30 30 30 30 30 30 30 30 30 30
23 bindmp: 2c 00 02 02 c9 c1 02 00
24 tab 1, row 1, @0x77b
25 tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
26 col 0: [ 2] c1 03
27 col 1: [10] 30 30 30 30 30 30 30 30 30 30
28 bindmp: 2c 00 02 02 c9 c1 03 00
「91行目Insert後のダンプ・ファイル」を見ると、15行目は「tab 0」であり、
19行目は「tab 1」となっています。
この「tab 0」は圧縮されたデータが格納されているシンボル表です。
※シンボル表については、第4回メルマガ参照
https://old.insight-tec.com/mailmagazine/ora3/vol395.html
また、18行目、23行目、28行目にbindmpが出力されており、前回までに確認し
たAdvanced Compressionテーブルのダンプと同じような構成になっていること
がわかります。
これらのことから、91行目をInsertした時に、データが圧縮されたということ
になります。
では、何故91行目をInsert時に圧縮されたのでしょうか？
圧縮された原因を特定する為に、90行目と91行目Insert時のダンプ・ファイル
を比較してみることにしましょう。
３．ダンプ・ファイル比較
それでは、ダンプ・ファイルを比較してみましょう。
90行目と91行目のダンプ・ファイルで値が異なる箇所を抜粋してみると、以下
のような項目でした。
行 90行目ダンプ 91行目ダンプ
----- -------------- -------------
3 hsiz: 0xc6 hsiz: 0xdc
6 flag=-------- flag=-0------
7 ntab=1 ntab=2
8 nrow=90 nrow=92
10 fsbo=0xc6 fsbo=0xdc
11 fseo=0x19e fseo=0x4a9
12 avsp=0xd8 avsp=0x373
13 tosp=0xd8 tosp=0x373
※データブロックに関する箇所のみ比較して抜粋しています
これらの中に、何かしきい値になりえる情報が格納されている可能性が高そう
です。
ということで、各項目を１つずつ見ていくことにしましょう。
まずは「hsiz」ですが、これはデータヘッダーサイズのことです。90行目は
0xc6（198）で91行目は0xdc（220）とサイズが22bytes増加していることがわ
かります。
と、これだけだとよくわからないので、この前後の流れも少し追ってみること
にしましょう。
▼85行目から95行目の推移
行 hsiz flag ntab nrow fsbo fseo avsp tosp -- -------- -------- ---- ---- -------- ---------- --------- --------- 85 0xbc:188 -------- 1 85 0xbc:188 0x1f3: 499 0x137:311 0x137:311 86 0xbe:190 -------- 1 86 0xbe:190 0x1e2: 482 0x124:292 0x124:292 87 0xc0:192 -------- 1 87 0xc0:192 0x1d1: 465 0x111:273 0x111:273 88 0xc2:194 -------- 1 88 0xc2:194 0x1c0: 448 0xfe :254 0xfe :254 89 0xc4:196 -------- 1 89 0xc4:196 0x1af: 431 0xeb :235 0xeb :235 90 0xc6:198 -------- 1 90 0xc6:198 0x19e: 414 0xd8 :216 0xd8 :216 ------------------------------圧縮後---------------------------------- 91 0xdc:220 -0------ 2 92 0xdc:220 0x4a9:1193 0x373:883 0x373:883 92 0xde:222 -0------ 2 93 0xde:222 0x497:1175 0x35f:863 0x35f:863 93 0xe0:224 -0------ 2 94 0xe0:224 0x485:1157 0x34b:843 0x34b:843 94 0xe2:226 -0------ 2 95 0xe2:226 0x473:1139 0x337:823 0x337:823 95 0xe4:228 -0------ 2 96 0xe4:228 0x461:1121 0x323:803 0x323:803
※「:の右の数字は16進数の値を10進数に変換した値」
圧縮された行から前後5行の推移を集計してみました。
これを見ると、hsizは2bytesずつ値が増加していて、圧縮されるタイミングで
はさらに20bytes増加していることがわかります。
つまり、1行Insertする毎にブロックのヘッダーに2bytesの情報が格納され、
圧縮が実行された時に20bytes余計に情報が書き込まれていることがわかりま
す。
これは、圧縮時にデータヘッダーへの書き込みが行われただけなので、この項
目は圧縮のタイミングを制御しているしきい値とは関係なさそうです。
次に、「flag」ですが、これは圧縮されたブロックであることを「-0——」
で表しているだけのようなのでこれもしきい値とは関係ないでしょう。
「ntab」はテーブルの数、「nrow」は格納されている行数なので、こちらも圧
縮のトリガーになることはなさそうです。
そして、残りの「fsbo、fseo、avsp、tosp」ですが、これらは全て空き領域に
関する情報になります。
「fsbo」 …Free Space Begin Offset
「fseo」 …Free Space End Offset
「avsp」 …Available free Space
「tosp」 …Total free Space
「avsp」、「tosp」はブロックの空き領域のサイズを表しており、「fsbo」か
ら「fseo」までの間に空きがあることを表しています。「avsp」と「tosp」の
違いは、「avsp」は現時点で使用可能な空き領域で、「tosp」はDELETE処理を
実行してCOMMITされてない領域も含んだ空き領域になります。今回の検証では
トランザクションを保持した状態ではない為、「avsp」と「tosp」は同じ値と
なります。
４．圧縮処理が実行されるしきい値の正体
ここで注目すべきは、「avsp」の値です。
90行目では216bytesの空きがあるにも関わらず、91行目をInsertした時に圧縮
が実行されて883bytesまでサイズが拡張されています。
216bytesに対して19bytesのInsertを実行したら圧縮された。。。200bytesの
空きを確保してその空きを超えるようなInsertをした時に圧縮される、という
ことでしょうか？
あれ？200bytesって、もしかして！？
この環境での1ブロックのサイズは2048bytes、200bytesは1ブロックの約10%に
相当。。。
ということは、これはもしかしてPCTFREEで確保された10%の空き領域というこ
とではないでしょうか？
PCTFREEとはUPDATE時に行のサイズが拡張した時の為にブロック内に確保され
た空き領域で、デフォルトで10%です。今回の検証環境ではブロックサイズが
2048bytesなので204bytesの空きが確保されていることになります。
つまり、PCTFREEの領域を除いた空き領域がなくなった時に圧縮処理が実行さ
れている、ということではないでしょうか？
このことを裏付ける為に、PCTFREEのサイズを20%に変更して確認してみること
にしましょう。
2048bytesの20%ということは409bytesで、この値を下回った際に圧縮処理が実
行されるはずです。
SQL> create table test_threshold_20 2 ( seq_no number, msg varchar2(10), primary key(seq_no) ) 3 compress for all operations pctfree 20; Table created. SQL> select segment_name,file_id,block_id,blocks 2 from dba_extents 3 where segment_name = 'TEST_THRESHOLD_20' 4 and owner='TEST' 5 and extent_id=0; SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS ------------------------------------ ---------- ---------- ---------- TEST_THRESHOLD 4 5473 32 SQL> begin 2 for i in 1 .. 148 loop 3 insert into test_threshold_20 values( i, '0000000000' ); 4 execute immediate 4 'alter session set tracefile_identifier = ' || i; 5 execute immediate 5 'alter system dump datafile 4 block min 5473 block max 5505'; 6 commit; 7 end loop; 8 end; 9 / PL/SQL procedure successfully completed.
▼圧縮前後のavspの値推移
(81行目Insert時に圧縮された為、75行目から85行目を抜粋)
行 avsp -- --------- 75 0x1f5:501 76 0x1e2:482 77 0x1cf:463 78 0x1bc:444 79 0x1a9:425 80 0x196:406 81 0x3e1:993 82 0x3cd:973 83 0x3b9:953 84 0x3a5:933 85 0x391:913
あれっ！？
409bytesを下回ったら圧縮されると想定してたのに、実際には406bytesでも圧
縮されていませんでした。何で409bytesを下回ってしまったんでしょうか！？
これは、実はPCTFREEの算出方法が関係しています。
先の計算では、ブロックサイズに対して10%を計算してPCTFREEのサイズを算出
していました。
PCTFREEの空き領域は正確に計算する場合データが格納される領域を基に計算
する必要があります。データが格納される領域とは、ブロックのヘッダー情報
を含まない領域のことです。
では、ブロックヘッダーとはどのくらいのサイズなのでしょうか？
以下計算式でブロック・ヘッダーのサイズを計算してみましょう。
▼ブロック・ヘッダー計算式
SQL> select type, type_size
2 from v$type_size
3 where type in ('KCBH', 'UB4', 'KTBBH', 'KTBIT', 'KDBH');
TYPE TYPE_SIZE
------------- ----------
KCBH 20 共通ヘッダ
UB4 4 フッターに使用
KTBBH 48 固定トランザクションヘッダ
KTBIT 24 可変トランザクションヘッダに使用
KDBH 14 データヘッダ
ブロックヘッダー = KCBH + UB4 + KTBBH + (INITRANS-1) * KTBIT + KDBH
= 20 + 4 + 48 + (1-1) * 24 + 14
= 86
上記計算式から、86bytesがブロック・ヘッダーとなり、それ以外の1962bytes
がデータが格納される領域となります。
つまり、PCTFREEの空き領域の算出は1962bytesに対して計算すべきであり、
PCTFREEが10%なら196bytes、PCTFREEが20%なら392bytesがPCTFREEの空き領域
ということになります。
そして、このPCTFREEの空き領域を引いたデータ領域（10%なら1766bytes、
20%なら1569bytes）が圧縮を制御するしきい値で、この空きを超えてデータを
格納しようとした時に圧縮が実行される、ということになります。
以上、圧縮されるタイミングについて検証してきました。
やっと長い間かかっていたAdvanced Compressionテーブルへの通常INSERT検証
が終わりました。
来週はお盆の為お休みです。再来週はAdvanced Compressionテーブルへの
UPDATE処理の検証を実施する予定です。
それでは再来週まで。
お盆休みで検証してたこと忘れちゃいそう
恵比寿にて