行移行・行連鎖に関する検証 その６
＜行移行・行連鎖に関する検証 その６＞
ペンネーム： ミラニスタ
▼ 行連鎖とは？
前回まで、行移行について検証しましたが、今回は行連鎖について見ていき
ましょう。
ところで、行連鎖とはどのような現象だったでしょうか？
その１で紹介しましたが、念のため再度マニュアルからの抜粋を掲載します。
行連鎖：最初に行を挿入するとき、大きすぎて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の状況を監視し、正しくメンテナンスする
という運用は、地味なようですがとても大切です。
コンサルタントとして多くの現場を見てきましたが、行移行・行連鎖が発生
していないという環境は目にしたことがありません。逆にメンテナンスをした
ことでパフォーマンスが改善したという例も数多く見てきました。
行移行・行連鎖について関心を持っていただけると幸いです。
このシリーズはこれで終わりです。
次回からのテーマは「ひ・み・つ」だそうです。お楽しみに！！
恵比寿より