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

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

▼ 行連鎖とは?

前回まで、行移行について検証しましたが、今回は行連鎖について見ていき
ましょう。
ところで、行連鎖とはどのような現象だったでしょうか?
その1で紹介しましたが、念のため再度マニュアルからの抜粋を掲載します。

行連鎖:最初に行を挿入するとき、大きすぎて1つのデータ・ブロック内に収
        まらない場合
        (中略)
        その行のデータは、セグメント用に確保された1つ以上のデータ・ブ
        ロックの連鎖に格納されます。多くの場合、行連鎖は、行が大きい
        場合に発生します。

                         - Oracle Database概要 11gリリース 2(11.2)-

行移行は、最初発生していなくても行の長さを長くするような更新があると
発生し、ブロック空き領域(PCTFREE)を適切に設定すれば発生する確率をあ
る程度抑えることができました。
また、定期的にメンテナンスを行うことによって解消できることも確認しま
した。

一方、行連鎖は「設計の問題」とも言えます。行の長さに対して小さすぎる
ブロック・サイズを選択してしまうと、”必ず”行連鎖が発生してしまいます。
エクスポート/インポートなどのメンテナンスを行っても解消されるわけで
はありません。

今回は、あえて行連鎖が発生するような状況を作り、ブロック・ダンプから
どのように行連鎖が発生しているかを確認してみます。

▼ 行連鎖を発生させる

それでは、以下のような2KBの表領域に作成されたSMALL_TBL表に対し、2KBを
超える長さの行を挿入してみます。

  TABLESPACE_NAME  BLOCK_SIZE INITIAL_EXTENT EXTENT_MAN ALLOCATIO SEGMEN
  ---------------- ---------- -------------- ---------- --------- ------
  TS_2K                  2048          40960 LOCAL      UNIFORM   AUTO

○ SMALL_TBL表

   Name    Type
   ------- ---------------
   RNO     CHAR(16)
   COL0    VARCHAR2(400)
   COL1    VARCHAR2(400)
   COL2    VARCHAR2(400)
   COL3    VARCHAR2(400)
   COL4    VARCHAR2(400)
   COL5    VARCHAR2(400)
   COL6    VARCHAR2(400)
   COL7    VARCHAR2(400)
   COL8    VARCHAR2(400)
   COL9    VARCHAR2(400)

○ Insert文

  INSERT INTO SMALL_TBL (RNO,COL0,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9)
  VALUES (lpad( '1',16,'0'),lpad('0',240,'0'),lpad('1',240,'1'),lpad('2',240,'2'),
                            lpad('3',240,'3'),lpad('4',240,'4'),lpad('5',240,'5'),
                            lpad('6',240,'6'),lpad('7',240,'7'),lpad('8',240,'8'),
                            lpad('9',240,'9'));

COL0~9にはlpad関数で指定した桁数(今回は240byte)の文字列を作成し挿
入します。この要領で4レコード挿入します。

  SQL> SELECT * FROM SMALL_TBL;

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

  0000000000000002 0000000000000000 1111111111111111 2222222222222222
                   0000000000000000 1111111111111111 2222222222222222
                   ................ ................ ................

統計情報を取得すると、(平均)レコード長が2436byteでブロックサイズ2KB
よりも大きいため、4行とも行連鎖となっていることがわかります。

  TABLE_NAME   NUM_ROWS  BLOCKS  PCT_FREE  CHAIN_CNT AVG_ROW_LEN LAST_
  ----------- --------- ------- --------- ---------- ----------- -----
  SMALL_TBL           4       7         0          4        2436 17:14
                    ^^^                          ^^^       ^^^^^

セグメントの状況を確認すると、SMALL_TBL表に関しては 2KB*5ブロックの
エクステント2つから構成されていることがわかります。

  SEGMENT_NAME TABLESPACE_NAME  FILE_ID  BLOCK_ID   BYTES   BLOCKS
  ------------ --------------- -------- --------- ------- --------
  SMALL_TBL    TS_2K                  6         4   10240        5
  IDX_RNO      TS_2K                  6         9   10240        5
  SMALL_TBL    TS_2K                  6        19   10240        5

さらに、以下のSQL文によりrowidの情報から各レコードがどのブロックに格
納されているかを確認します。ROW#というのは各ブロック内でのレコード順序
(0始まり)です。

  SELECT
   RNO
  ,ROWID
  ,DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'RM','SMALL_TBL') "FILE#"
  ,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) "BLOCK#"
  ,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) "ROW#"
  FROM RM.SMALL_TBL

  RNO              ROWID                FILE#     BLOCK#    ROW#
  ---------------- ------------------ ------- ---------- -------
  0000000000000001 AAAUcLAAGAAAAAIAAA       6          8       0
  0000000000000002 AAAUcLAAGAAAAAIAAB       6          8       1
  0000000000000003 AAAUcLAAGAAAAAVAAA       6         21       0
  0000000000000004 AAAUcLAAGAAAAAVAAB       6         21       1

FILE#とBLOCK#がわかれば、以下のファンクションによりデータ・ブロック・
アドレス(DBA)がわかります。これをさらに16進数に変換したのが以下の結果
です。これは後でブロック・ダンプを見る際に必要な情報です。

  SELECT DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(FILE#,BLOCK#) "DBA" FROM DUAL

       FILE#     BLOCK#   DBA(dec) DBA(hex)
  ---------- ---------- ---------- -----------
           6          8   25165832 0x1800008

       FILE#     BLOCK#   DBA(dec) DBA(hex)
  ---------- ---------- ---------- -----------
           6         21   25165845 0x1800015

▼ 行連鎖が発生した状態のブロック・ダンプを見る!

それでは、この状態でブロック・ダンプを取得して、行連鎖がどのように発
生しているかを見てみましょう。

  ALTER SYSTEM DUMP DATAFILE 6 BLOCK MIN 4 BLOCK MAX 25

どのように行が連鎖しているかわからないので、念のため範囲を広くしてダ
ンプを取得しています。

ダンプ・ファイルは非常に長いので、後の説明で最小限必要な分だけを抜粋
し、行番号つきのテキストをJPEGファイルにしてみました。(それでも12ペー
ジになりました。辛抱してお付き合いください。)

vol446_chain_blkdmp_01.jpg
vol446_chain_blkdmp_02.jpg
vol446_chain_blkdmp_03.jpg
vol446_chain_blkdmp_04.jpg
vol446_chain_blkdmp_05.jpg
vol446_chain_blkdmp_06.jpg
vol446_chain_blkdmp_07.jpg
vol446_chain_blkdmp_08.jpg
vol446_chain_blkdmp_09.jpg
vol446_chain_blkdmp_10.jpg
vol446_chain_blkdmp_11.jpg
vol446_chain_blkdmp_12.jpg

RNO=0000000000000001のデータが格納されている、DBA: 0x1800008 のブロッ
クから見ていきましょう。
(1)~(7)はポイントになる箇所で補足説明を加えています。

(215-236行目)
  bdba: 0x01800008                     --(1)データ・ブロック・アドレス
  data_block_dump,data header at 0x73004064
  ===============
  tsiz: 0x798
  hsiz: 0x16
  pbl: 0x73004064
       76543210
  flag=--------
  ntab=1
  nrow=2                               --(2)ブロックに含まれる行の数
  frre=-1
  fsbo=0x16
  fseo=0x1be
  avsp=0x1a8
  tosp=0x1a8
  0xe:pti[0]	nrow=2	offs=0
  0x12:pri[0]	offs=0x4ab
  0x14:pri[1]	offs=0x1be
  block_row_dump:
  tab 0, row 0, @0x4ab        --(3)row:ブロック内の行位置(0始まり)
  tl: 749 fb: --H-F--- lb: 0x1  cc: 4   --(4)tl:行断片の長さ
  nrid:  0x01800007.0               --(5)fb:行断片の状況を示すフラグ
                                    --(6)cc:列数
(271-273行目)
  tab 0, row 1, @0x1be
  tl: 749 fb: --H-F--- lb: 0x1  cc: 4
  nrid:  0x01800013.0             --(7)nrid:次のブロックへのポインタ

行連鎖とは1行が複数に分割されていますので、分割された断片を「行断片」
と呼びます。

4行分のデータについて格納状況をまとめたものが、下の表になります。
(#は行断片毎に便宜的に付けた番号)

  +-+----------+-------+------+-----+--------+-----+----------+
  |#|(1)bdba   |(2)nrow|(3)row|(4)tl|(5)fb   |(6)cc|(7)nrid   |
  +-+----------+-------+------+-----+--------+-----+----------+
  |1|0x01800007|     1 |   0  |1,690|-----L--|   7 |     -    |
  +-+----------+-------+------+-----+--------+-----+----------+
  |2|0x01800008|     2 |   0  |  749|--H-F---|   4 |0x01800007|
  |3|          |       |   1  |  749|--H-F---|   4 |0x01800013|
  +-+----------+-------+------+-----+--------+-----+----------+
  |4|0x01800013|     1 |   0  |1,690|-----L--|   7 |     -    |
  +-+----------+-------+------+-----+--------+-----+----------+
  |5|0x01800014|     1 |   0  |1,690|-----L--|   7 |     -    |
  +-+----------+-------+------+-----+--------+-----+----------+
  |6|0x01800015|     2 |   0  |  749|--H-F---|   4 |0x01800014|
  |7|          |       |   1  |  749|--H-F---|   4 |0x01800016|
  +-+----------+-------+------+-----+--------+-----+----------+
  |8|0x01800016|     1 |   0  |1,690|-----L--|   7 |     -    |
  +-+----------+-------+------+-----+--------+-----+----------+

fbは(H),(F),(L)が揃って1つの行になります。もし行連鎖が発生していなけ
ればfbは「–H-FL–」となりますが、「–H-F—」の行断片に「—–L–」の
行断片が連鎖して1つの行が完成します。
行断片毎のtlを合計すると 749 + 1690 = 2439 となり、先に確認した
AVG_ROW_LEN = 2436 とほぼ同じになっています。

2つの行断片が連鎖して1つの行となっている組み合わせは以下のとおりです。

  RNO              どの行断片が連鎖しているか?
  ---------------- ------------------------------------
  0000000000000001 0x01800008  -->  0x01800007 (#2,#1)
  0000000000000002 0x01800008  -->  0x01800013 (#3,#4)
  0000000000000003 0x01800015  -->  0x01800007 (#6,#5)
  0000000000000004 0x01800015  -->  0x01800007 (#7,#8)

今回、わかりやすくするために単純化した例で検証してみましたが、行連鎖
のイメージご理解いただけましたでしょうか?
必ずしもアドレスの若いブロックから順番にデータが格納されていくわけで
はないことは、検証を通して実感できた次第です。

こうして見ると、Oracleでは物理的(または論理的)にブロックを読み取っ
た後に、ブロックから必要なデータを取り出すという過程でけっこう面倒な操
作をしているのだと実感します。
行移行、行連鎖はその面倒な操作に、輪をかけて足を引っ張るようなものだ
と思います。

PCTFREEを適切に設定する、適切なブロックサイズを選定する、という設計
段階からできる配慮と、無駄なI/Oの状況を監視し、正しくメンテナンスする
という運用は、地味なようですがとても大切です。
コンサルタントとして多くの現場を見てきましたが、行移行・行連鎖が発生
していないという環境は目にしたことがありません。逆にメンテナンスをした
ことでパフォーマンスが改善したという例も数多く見てきました。

行移行・行連鎖について関心を持っていただけると幸いです。

このシリーズはこれで終わりです。
次回からのテーマは「ひ・み・つ」だそうです。お楽しみに!!

恵比寿より