行移行・行連鎖に関する検証 その１
＜行移行・行連鎖に関する検証 その１＞
ペンネーム： ミラニスタ
11g新機能についての検証がしばらく続きましたが、今回から「行移行・行
連鎖」を取り上げます。
▼ 行移行・行連鎖とは？
行移行：1つのデータ・ブロックに収まっていた行が更新されて、行全体の長
さが増加したが、更新後の行を保持する十分な空き領域がない場合
（中略）
行全体が新しいブロックに収まることを前提として、行全体が新しい
データ・ブロックに移動されます。
移行された行の元の行断片には、行の移行先の新しいブロックへのポ
インタまたは「転送先アドレス」が含まれます。そのため、移動され
た行のROWIDは変わりません。
行連鎖：最初に行を挿入するとき、大きすぎて1つのデータ・ブロック内に収
まらない場合
（中略）
その行のデータは、セグメント用に確保された1つ以上のデータ・ブ
ロックの連鎖に格納されます。多くの場合、行連鎖は、行が大きい
場合に発生します。
- Oracle Database概要 11gリリース 2（11.2）-
行移行・行連鎖が発生すると、発生していない状態と比べて無駄なブロック
I/Oが多くなりパフォーマンスに影響を与えます。
特に、行移行は時間が経つにつれて発生の度合いが進むため、気が付かない
間にだんだんパフォーマンスが悪化するようなことがよくあります。
行移行が発生している状況を把握し適切なメンテナンスを実施すれば、SQL
文をチューニングすることなしに性能を改善することができます。
▼ なぜ今、行移行・行連鎖？
優れたデータベースとはどんなデータベースでしょうか？正解は1つではな
いと思いますが、筆者は「性能の経年劣化が少ない」データベースであると考
えます。
ビジネス規模の拡大と共にデータ量は増えていくのが普通ですが、5年経っ
てもカット・オーバー直後の性能が維持されているとすれば、投資に十分見
合った成果が得られていると言えるのではないでしょうか？
11gに至るまでの進化の中で、Oracleデータベースでは性能を維持するため
の様々な自動化機能が実現されてきました。
しかし、決められた大きさの入れ物（データ・ブロック）に可変長のデータ
を格納したり、そのデータを更新したりするということはなかなか自動的に最
適化することが難しい分野です。なぜならデータの形というのは千差万別です
べてのパターンに対応することが不可能だからです。
従って、行移行・行連鎖に関しては、適切な設計あるいは設定で予防的に対
応することが必要です。
行移行：更新で長くなるレコード長を見越して適切なPCTFREEを設定する。
行連鎖：1つのブロックでレコードを格納できるよう適切なブロック・サイズ
を選定する。
これらが、行移行・行連鎖に対する教科書的な処方箋ですが、筆者の経験で
はデータベースを構築する人（DBA）と利用する人（開発者）が明確に分かれ
ている場合ほど、PCTFREEをデフォルトの10%としている等の傾向が強く、行移
行による性能劣化に無頓着なケースが多いように思います。
優れたデータベースを構築・運用するためには、DBA・開発者という垣根を
超える必要性を最近特に感じます。ということでこのテーマを選ぶに至りまし
た。
この検証シリーズでは、どのようなメカニズムで行移行・行連鎖が発生し、
どのように解消すればよいのか、そしてその際の注意すべき点は何かというこ
とについて確認していきます。
▼ まずは行移行を発生させてみる
今回検証を行う環境は以下のとおりです。
Oracle Database 10g Release 10.2.0.4.0 – 64bit Production
Linux 2.6.18-164.el5
検証の方針としては
1) なるべく小さくて行移行が発生しやすいテーブルを作成する。 2) 1ブロックにぎりぎり収まるように複数行のレコードをInsertする。 3) ブロック内の空き容量を超える、レコードを長くするUpdateを、特定の1行 に対して実行する。（行移行の発生） 4) 行移行の発生前後でブロック・ダンプを取得し、どんな変化が生じたかを 確認する。 5) 解消のためのメンテナンスを行った後、同様にブロック・ダンプを取得し て変化を確認する。
ということにします。
▼ 小さいテーブルの作成
1. 小さい表領域の作成（管理者ユーザにて実施）
ブロックレベルで確認しやすくするため、検証は最小のブロックサイズ、す
なわち DB_BLOCK_SIZE=2KB（2048byte）の表領域を作成し、その中に可能な限
り最小のテーブルを作成してみます。
DB_BLOCK_SIZEのデフォルト値は8KBですが、Oracle 9i以降であれば1つの
データベース内に複数のブロックサイズを混在させることが可能です。
2KBの表領域を作成する前に、DB_2K_CACHE_SIZEパラメータの状況を確認し、
「0」であれば適当な大きさに変更します。
SQL> show parameter db_2k_cache_size NAME TYPE VALUE ------------------- ----------- -------- db_2k_cache_size big integer 0 SQL> ALTER SYSTEM SET DB_2K_CACHE_SIZE = 208M; System altered. SQL> show parameter db_2k_cache_size NAME TYPE VALUE ------------------- ----------- -------- db_2k_cache_size big integer 208M
次に、BLOCKSIZE=2KBの表領域「TS_2K」を作成します。
エクステント管理は「UNIFORM, SIZE=10K」とします。
SQL> CREATE TABLESPACE TS_2K
2 DATAFILE '/opt/app/oracle/oradata/orcl/ts_2k.dbf' SIZE 64K REUSE
3 BLOCKSIZE 2K
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10K
5 SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
表領域作成後の状況を確認します。
SQL> SELECT TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,
2 EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
3 FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TS_2K';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT EXTENT_MAN ALLOCATIO SEGMEN
---------------- ---------- -------------- ---------- --------- ------
TS_2K 2048 10240 LOCAL UNIFORM AUTO
2. 検証用ユーザの作成
1.で作成した表領域を使用するユーザ「RM」(Row Migration：行移行 から)
を作成します。
SQL> CREATE USER rm IDENTIFIED BY rm DEFAULT TABLESPACE TS_2K
2 TEMPORARY TABLESPACE TEMP;
User created.
SQL> GRANT DBA TO rm;
Grant succeeded.
3. 小さいテーブルの作成（RMユーザにて実施）
今回作成する”小さいテーブル”は、16byteの行番号固定長カラムと10個の
最大400byteの可変長カラムから構成されるテーブルで、理屈上は1レコードの
最大長が4016byteとなり1ブロック（2048byte）に収まらないようになってい
ます。
実際の作成スクリプトは以下となりますが、ポイントは表領域「TS_2K」に
作成することと、「PCTFREE 0」としていることです。
SQL> CREATE TABLE SMALL_TBL
2 (
3 RNO CHAR(16)
4 ,COL0 VARCHAR2(400)
5 ,COL1 VARCHAR2(400)
6 ,COL2 VARCHAR2(400)
7 ,COL3 VARCHAR2(400)
8 ,COL4 VARCHAR2(400)
9 ,COL5 VARCHAR2(400)
10 ,COL6 VARCHAR2(400)
11 ,COL7 VARCHAR2(400)
12 ,COL8 VARCHAR2(400)
13 ,COL9 VARCHAR2(400)
14 )
15 PCTFREE 0
16 TABLESPACE TS_2K;
Table created.
さらに、RNO列にインデックスも作成しておきましょう。
SQL> CREATE UNIQUE INDEX IDX_RNO ON SMALL_TBL (RNO)
2 TABLESPACE TS_2K;
Index created.
そして、行移行・行連鎖の確認を行うために必要なCHAINED_ROWS表を以下の
要領で作成します。
SQL> @?/rdbms/admin/utlchain.sql Table created.
4. 状況の確認
検証環境の準備は整いましたが、後でブロック･ダンプを取得するために必
要な情報を確認しましょう。
まずは、DBA_SEGMENTSとDBA_EXTENTSからセグメント（テーブルおよびイン
デックス）を構成するブロックの情報を取得します。
ます。
SQL> SELECT SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME IN ('SMALL_TBL','IDX_RNO');
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
-------------- ----------- ------------ ------ ------- --------
IDX_RNO 5 11 10240 5 1
SMALL_TBL 5 6 10240 5 1
SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME,FILE_ID,BLOCK_ID,BYTES,BLOCKS
2 FROM DBA_EXTENTS
3 WHERE SEGMENT_NAME IN ('SMALL_TBL','IDX_RNO') ;
SEGMENT_NAME TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------ ---------------- -------- ---------- ------ --------
IDX_RNO TS_2K 5 9 10240 5
SMALL_TBL TS_2K 5 4 10240 5
「SMALL_TBL」「IDX_RNO」いずれのセグメントも、FILE_ID=5のデータファイ
ルに含まれ、それぞれBLOCK_ID=4あるいは9から5ブロック分の連続した領域と
して割り当てられています。
次に、Analyze文を2種類実行します。
1つ目はテーブルの完全な統計情報を取得、2つ目は行移行・行連鎖の発生状
況の調査を行うためのAnalyze文です。
SQL> ANALYZE TABLE SMALL_TBL COMPUTE STATISTICS;
Table analyzed.
SQL> ANALYZE TABLE SMALL_TBL LIST CHAINED ROWS;
Table analyzed.
Analyze文実行の後は、USER_TABLESから現状を確認します。
まだ、1行もInsertしていないので表示は以下の通りとなります。
SQL> SELECT TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT,
2 AVG_ROW_LEN,TO_CHAR(LAST_ANALYZED,'HH24:MI') LAST_ANALYZED
3 FROM USER_TABLES
4 WHERE TABLE_NAME='SMALL_TBL';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN LAST_
----------- -------- ------ ------------ --------- ----------- -----
SMALL_TBL 0 0 5 0 0 17:46
これで、検証の準備が整いました。
次回は、実際に行移行を発生させてみます。
週末、鎌倉・鶴岡八幡宮の大銀杏が倒れた跡を見に行きました。寂しい。。。
恵比寿より