行移行・行連鎖に関する検証 その3

<行移行・行連鎖に関する検証 その3>
ペンネーム: ミラニスタ

▼ 前回のおさらい

小さなテーブルを作成し、1ブロック(2048Byte)にぎりぎり収まるよう10レ
コードをInsertしました。

SQL> SELECT * FROM SMALL_TBL;

RNO              COL0             COL1             COL2
---------------- ---------------- ---------------- ----------------
COL3             COL4             COL5             COL6
---------------- ---------------- ---------------- ----------------
COL7             COL8             COL9
---------------- ---------------- ----------------
R000000000000001 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999

R000000000000002 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999
.....................................................................

R000000000000010 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999

10 rows selected.

(図1)

各行のRowidと、どのデータ・ブロックに格納されているかを確認します。

SQL> SELECT
2   RNO
3  ,ROWID
4  ,DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'RM','SMALL_TBL') "FILE#"
5  ,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) "BLOCK#"
6  FROM RM.SMALL_TBL;

RNO              ROWID                   FILE#     BLOCK#
---------------- ------------------ ---------- ----------
R000000000000001 AAAToaAAGAAAAAHAAA          6          7
R000000000000002 AAAToaAAGAAAAAHAAB          6          7
R000000000000003 AAAToaAAGAAAAAHAAC          6          7
R000000000000004 AAAToaAAGAAAAAHAAD          6          7
R000000000000005 AAAToaAAGAAAAAHAAE          6          7 ← 5行目
R000000000000006 AAAToaAAGAAAAAHAAF          6          7
R000000000000007 AAAToaAAGAAAAAHAAG          6          7
R000000000000008 AAAToaAAGAAAAAHAAH          6          7
R000000000000009 AAAToaAAGAAAAAHAAI          6          7
R000000000000010 AAAToaAAGAAAAAHAAJ          6          7

10行が選択されました。

以下のSQL文で、該当データ・ブロックのブロック・ダンプ取得します。

SQL> ALTER SYSTEM DUMP DATAFILE 6 BLOCK 7;

システムが変更されました。

ブロック・ダンプそのものは以下のリンクを参照してください。

vol442_blk_dmp_bfo_upd.txt

*前回までの検証環境が使えなくなってしまったので、今回から
Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
2.6.18-164.el5
の環境で検証を行っています。

▼ 行移行を発生させてみる。

ブロックにぎっしり書き込まれたレコードのちょうど真ん中あたりの行
(RNO=R000000000000005)を長くするような更新をさせてみて、どのように
行移行するかを見てみます。
具体的には、列COL0~COL9を以下のSQL文で16Byteから倍の32Byteに更新し
てみます。

(図2)

SQL> UPDATE SMALL_TBL SET
2   COL0=RPAD(COL0,32,'U')
3  ,COL1=RPAD(COL1,32,'U')
4  ,COL2=RPAD(COL2,32,'U')
5  ,COL3=RPAD(COL3,32,'U')
6  ,COL4=RPAD(COL4,32,'U')
7  ,COL5=RPAD(COL5,32,'U')
8  ,COL6=RPAD(COL6,32,'U')
9  ,COL7=RPAD(COL7,32,'U')
10 ,COL8=RPAD(COL8,32,'U')
11 ,COL9=RPAD(COL9,32,'U')
12 WHERE RNO='R000000000000005';

1行が更新されました。

SQL> COMMIT;

コミットが完了しました。

統計情報を取得して、行移行の発生を確認します。

TABLE_NAME   NUM_ROWS  BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN LAST_
----------- --------- ------- ------------ ---------- ----------- -----
SMALL_TBL          10       2            3          1         207 16:42

CHAIN_CNTが 0→1 になりました。行移行が発生したようです。

▼ データを確認する。

まず初めに単純な問い合わせを実行してみます。

SQL> SELECT * FROM SMALL_TBL;

RNO              COL0             COL1             COL2
---------------- ---------------- ---------------- ----------------
COL3             COL4             COL5             COL6
---------------- ---------------- ---------------- ----------------
COL7             COL8             COL9
---------------- ---------------- ----------------
R000000000000001 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999

....................................................................

R000000000000004 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999

(更新前 RNO=R000000000000005 はここにあった)

R000000000000006 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999

....................................................................

R000000000000010 0000000000000000 1111111111111111 2222222222222222
3333333333333333 4444444444444444 5555555555555555 6666666666666666
7777777777777777 8888888888888888 9999999999999999

(更新後、問い合わせの一番最後になった)

R000000000000005 0000000000000000 1111111111111111 2222222222222222
UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU
3333333333333333 4444444444444444 5555555555555555 6666666666666666
UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU
7777777777777777 8888888888888888 9999999999999999
UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU UUUUUUUUUUUUUUUU

10行が選択されました。

なるほど、RNO=R000000000000005 は 5行目にあったのですが、10行目になっ
ています。

同様に、Rowidとブロック番号がどうなったかを確認してみましょう。

RNO              ROWID                   FILE#     BLOCK#
---------------- ------------------ ---------- ----------
R000000000000001 AAAToaAAGAAAAAHAAA          6          7
R000000000000002 AAAToaAAGAAAAAHAAB          6          7
R000000000000003 AAAToaAAGAAAAAHAAC          6          7
R000000000000004 AAAToaAAGAAAAAHAAD          6          7
R000000000000006 AAAToaAAGAAAAAHAAF          6          7
R000000000000007 AAAToaAAGAAAAAHAAG          6          7
R000000000000008 AAAToaAAGAAAAAHAAH          6          7
R000000000000009 AAAToaAAGAAAAAHAAI          6          7
R000000000000010 AAAToaAAGAAAAAHAAJ          6          7
R000000000000005 AAAToaAAGAAAAAHAAE          6          7 ← 10行目
↑                    ↑
Rowidは同じ     ブロック番号も同じ
10行が選択されました。

普通、単純な問い合わせを行うと Rowid の順番に行が返されるのですが、
RNO=R000000000000005 だけ例外的に順番が変わっています。恐らくこれが行
移行の発生している現象と思われます。

▼ ブロック・ダンプを確認する。

ブロック・ダンプを確認すれば、行移行の正体がもっとよくわかるはずです。
隣のブロックに行が移行していることが考えられるので、今度はブロック番
号の範囲を指定してダンプを取得してみます。

SQL> ALTER SYSTEM DUMP DATAFILE 6 BLOCK MIN 7 BLOCK MAX 8;

システムが変更されました。

実際のブロック・ダンプの内容は以下のリンクを参照してください。
(行番号が表示できるエディタ等にコピー&ペーストしていただくとわかりや
すいと思います。)

vol442_blk_dmp_aft_upd.txt

まずは、論理ダンプの確認から。。。

ブロック・ダンプは長いので行番号でポイントを示します。

(258-264行目)
......................................................................
tab 0, row 4, @0x3e2
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x01800008.0
tab 0, row 5, @0x324
tl: 190 fb: --H-FL-- lb: 0x0  cc: 11
col  0: [16]  52 30 30 30 30 30 30 30 30 30 30 30 30 30 30 36
col  1: [16]  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
......................................................................

「row 4」すなわち5行目だけが、他の行と違っています。
1. tl:(1行の長さ)が 190 → 9 に。cc:(column count?)が11→0に。
2. fb:(?)が「--H-FL--」→「--H-----」に変化。
3. nrid: が出現。値は「0x01800008.0」。

行データが別のブロックに移ってしまったために、このブロックにおける1行
の長さが9バイトになってしまったようです。カラムの数も0になってしまいま
した。
fbというのは、行の状態をフラグで表現しているようです。(Flag Byte?)
行移行前はヘッダー(H)、行の先頭(F)そして行の終わり(L)が1行に収
まっていたのが、行移行後はヘッダーを残して(F)と(L)が別のブロックに
移動してしまったのでしょう。
nridは恐らく「next(or new) record(or row) id」か何かのことでしょう。
つまりrowidは変えずに(行移行している)次のブロックを指し示すポインタ
と思われます。
「0x01800008」で検索してみると、

(630行目)
......................................................................
BH (0x837fac78) file#: 6 rdba: 0x01800008 (6/8) class: 1 ba: 0x83771000
......................................................................
(690-714行目)
......................................................................
bdba: 0x01800008
data_block_dump,data header at 0x8377107c
===============
tsiz: 0x780
hsiz: 0x14
pbl: 0x8377107c
76543210
flag=--------
ntab=1
nrow=1
(中略)
block_row_dump:
tab 0, row 0, @0x61c
tl: 356 fb: ----FL-- lb: 0x1  cc: 11  --(1)
hrid: 0x01800007.4  --(2)
col  0: [16]  52 30 30 30 30 30 30 30 30 30 30 30 30 30 30 35
col  1: [32]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 55 55 55 55 55 55 55 55 55
55 55 55 55 55 55 55
......................................................................

という箇所にたどり着きました。

(1)レコード長が長く更新され(16+32*10=336byte+α)、実際には tl=356と
なりました。フラグの状態も「—-FL–」が確認できました。(709行目)
(2)移行元を示すポインタらしきhrid(header record id?)が「0x01800007.4」
となっていることも確認できます。(710行目)

実際のデータの格納状況はバイナリ・ダンプを見ればわかりますが、紙面が
長くなりますのでここでは割愛させていただきます。
興味深い点としては、行移行といっても移行元のデータを消して移行するの
ではなくそのままにしていることです。(110-122行目)
つまり、行移行したとしても元のブロックの空き領域は変わりません。アク
セスされない無駄な領域が残り続けます。

行移行が発生している状況をミクロ的に見てきましたが、お解かりになりま
したでしょうか?
行移行というのは無駄なI/Oが増えるだけでなく、領域も無駄になってしまう
ようです。「百害あって一利なし」ということでしょうか?

次回も、行移行・行連鎖について深く見ていきます。

暑くなったり、寒くなったりの

恵比寿より