行移行・行連鎖に関する検証 その５
＜行移行・行連鎖に関する検証 その５＞
ペンネーム： ミラニスタ
▼ 前回のおさらい
PCTFREEパラメータが0,10,20と異なるが、その他の条件が全く同じ10万件の
3テーブルを作成し、それぞれ1万件ずつレコード長を長くする更新を行った場
合の、更新率（10-100%）に伴う行移行発生の推移を観察しました。
また、1万件（全体の10%）のデータを取得するインデックス検索によって生
じる論理読込ブロック数の更新率に伴う推移を確認しました。
1. PCTFREEが大きくなると1ブロックに格納できるレコード数が少なくなるの で更新が全くない状態では、PCTFREE=0のケースが10万件の格納に必要なブ ロック数が最も少なくて済む。 2. PCTFREEを大きめに設定しておけば、ある程度更新があっても行移行が発生 しない可能性がある。（行移行発生の確率を低減させることができる。） 3. インデックス検索の場合、行移行発生の度合いによって論理読込ブロック 数（およびコスト）に変化をもたらす。つまりパフォーマンスに影響を与 える。 4. 全件検索（Full Scan）の場合、論理（物理）読込ブロック数・コストは、 確保されたブロック数（エクステント数）に相関しているが、行移行発生 の度合いとは特に関係がない。
▼ データベース単位で行移行・行連鎖行へのアクセス頻度を調べるには？
V$SYSSTATを参照することによって得るシステム統計情報の中には、行移行・
行連鎖に関するものがあります。
具体的には以下のSQL文によって、インスタンス起動後に発生した行移行・
行連鎖行へのアクセス数を把握することができます。
SQL> SELECT CLASS,VALUE FROM V$SYSSTAT
2 WHERE NAME='table fetch continued row';
CLASS VALUE
---------- ----------
64 16473 （CLASS=64 は SQLクラス）
マニュアルには「フェッチ中に検出された連鎖または移行行の数」とありま
すが、前回までに検証したように rowid → nrid というアクセス、つまりイン
デックス検索の中でのみ検出される値のため、行移行においては全件検索とは
関係のないことに注意してください。
このSQL文を1日おきに実行し、差分を計算すれば1日で発生したアクセス行数
がわかります。
当社が開発・販売しているPerformance Insightでは、以下のように1時間ご
との行移行行へのアクセス回数をグラフにして表示しています。
--------------------------------------------------------------------- 1時間ごとの行移行行へのアクセス回数 ----------------------------------- 01/08 03|======== 2717 01/08 04|===== 1873 01/08 05|======== 2782 01/08 06|===== 1876 01/08 07|====== 2101 01/08 08|========= 2964 01/08 09|=========== 3785 01/08 10|=========== 3647 01/08 11|============ 3939 01/08 12|======== 2701 01/08 13|============= 4339 01/08 14|========= 2963 01/08 15|===================== 6959 01/08 16|==============================10058 01/08 17|===================================11615 01/08 18|==================== 6744 01/08 19|================== 5933 01/08 20|================= 5813 01/08 21|============================================================19688 01/08 22|==============================================================20201 01/08 23|===========================================================19394 01/09 00|================================10574 01/09 01|========================= 8218 01/09 02|============ 4078 --------+------------------------------+------------------------------| (=) MIN: 1873(01/08 04) MAX: 20201(01/08 22) AVG: 6873 ---------------------------------------------------------------------
この例のように1時間で数万回に及ぶことも少なくありません。皆さんが管理
されているデータベースではいかがでしょうか？是非確認してみてください。
▼ 行移行をどのように解消させるか？
マニュアル等を見ると行移行は「エクスポート/インポートで解消できる。」
と書いてあります。早速やってみましょう。
初期状態： TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_ ----------- --------- ------- --------- ---------- ----------- ----- LARGE_TBL00 100000 20003 0 50000 353 10:28 SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS ---------------- ----------- ------------ ---------- ---------- LARGE_TBL00 7 34 20405 4081 IDX_RNO00 7 39 1550 310
評価SQL文：10万件のうち1万件を取得（インデックス検索）
SELECT /*+ INDEX (LARGE_TBL00 IDX_RNO00) */ * FROM LARGE_TBL00 WHERE RNO BETWEEN '0000000000000001' AND '0000000000010000';
このSQL文を実行し「consistent gets」の値がどのようになるか確認します。
統計 -------------------------------- 12140 consistent gets ←初期状態
○方法１：エクスポート/インポート
1-1) PCTFREEはそのままでExport→Drop Table→Import
TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_ ----------- --------- ------- --------- ---------- ----------- ----- LARGE_TBL00 100000 20003 0 0 350 10:34 ^^^行移行解消 SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS ---------------- ----------- ------------ ---------- ---------- LARGE_TBL00 7 34 20405 4081 ^^^^^^^^^^^^^^^^ ↑メンテナンス前と同じ IDX_RNO00 7 64874 1725 345 ^^^^^^^^^^^^^^^^^^^^^^^^^^^ ↑再作成された 統計 -------------------------------- 10822 consistent gets ←メンテナンス前に比べ11%削減された！！
1-2) PCTFREEを20に変更後は1-1)と同じ
SQL> ALTER TABLE LARGE_TBL00 PCTFREE 20;
表が変更されました。
TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_
----------- --------- ------- --------- ---------- ----------- -----
LARGE_TBL00 100000 25000 20 0 350 10:51
^^^変更 ^^^行移行解消
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------------- ----------- ------------ ---------- ----------
LARGE_TBL00 7 34 25495 5099
^^^^^^^^^^^^^^^^
↑25%増加
IDX_RNO00 7 115004 1725 345
統計
--------------------------------
10822 consistent gets ←論理読込ブロック数は 1-1)と同じ
○方法２：ALTER TABLE … MOVE を使う。
エクスポート/インポートはテーブルの削除が必要ですしちょっと大がかり
な感じがします。もっと簡単な方法はないでしょうか？
実は、行移行はテーブルを移動することでも解消することができます。
今回はあえて同じ表領域の中でテーブルを移動させます。その前に十分な空
きがあるかを確認しておきます。
SQL> SELECT SUM(BLOCKS) FREE_BLOCKS FROM DBA_FREE_SPACE
2 WHERE TABLESPACE_NAME = 'LG_TS_2K';
FREE_BLOCKS
-----------
190665
念のためメンテナンス前の状態も確認しておきます。
TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_ ----------- --------- ------- --------- ---------- ----------- ----- LARGE_TBL00 100000 20003 0 50000 353 11:35 SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS ---------------- ----------- ------------ ---------- ---------- LARGE_TBL00 7 34 20405 4081 IDX_RNO00 7 39 1550 310
2-1) PCTFREEはそのままでTable Move
それでは、テーブルを移動してみましょう。（移動先表領域を省略すると同
じ表領域の中で移動します。）
SQL> ALTER TABLE LARGE_TBL00 MOVE; 表が変更されました。
ちゃんと移動したかどうかは、DBA_SEGMENTSを参照すればわかります。
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS ---------------- ----------- ------------ ---------- ---------- IDX_RNO00 7 69 1725 345 LARGE_TBL00 7 73204 20405 4081
テーブルのHEADER_BLOCKに注目してください。全く異なるBLOCK_IDとなって
いることから移動を判断することができます。また当然ながらエクステント数
は変わっていません。
行移行が解消できたかどうか統計情報を取得して確認します。
SQL> ANALYZE TABLE LARGE_TBL00 COMPUTE STATISTICS; ANALYZE TABLE LARGE_TBL00 COMPUTE STATISTICS * 行1でエラーが発生しました。: ORA-01502: 索引'RM1.IDX_RNO00'またはそのパーティションが使用不可の状態です。
おっと！エラーになってしまいました。テーブルの移動で行のrowidが変わっ
たため、古いrowid情報を保持しているインデックスが無効になってしまったよ
うです。インデックスをリビルドして再取得しましょう。
SQL> ALTER INDEX IDX_RNO00 REBUILD ONLINE;
索引が変更されました。
SQL> ANALYZE TABLE LARGE_TBL00 COMPUTE STATISTICS;
表が分析されました。
TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_
----------- --------- ------- --------- ---------- ----------- -----
LARGE_TBL00 100000 20402 0 0 350 11:43
^^^
行移行が解消されました！！
統計
--------------------------------
10822 consistent gets
論理読込ブロック数は、エクスポート/インポートの場合と全く同じです。
2-2) PCTFREEを20に変更後は2-1)と同じ
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS ---------------- ----------- ------------ ---------- ---------- IDX_RNO00 7 39 1550 310 LARGE_TBL00 7 73204 25495 5099 TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_ ----------- --------- ------- --------- ---------- ----------- ----- LARGE_TBL00 100000 25495 20 0 350 12:46 統計 -------------------------------- 10822 consistent gets
エクスポート/インポートもテーブルの移動も結果は同じになりました。
前者の方がきれいに行移行が解消できそうなイメージですが、実際は同じで
した。メンテナンスの手間やサービス影響度を考えるとテーブル移動の方がお
勧めです。
○方法３：CHAINED_ROWS表を使う
ANALYZE TABLE LIST CHAINED ROWS;
を実行すると、CHAINED_ROWS表に行移行が発生している行のrowidが格納され
ます。（ANALYZEですが、実行計画に影響を与える統計情報は変更されません。）
格納された様子は以下の要領で確認します。
SQL> SELECT OWNER_NAME,TABLE_NAME,HEAD_ROWID,
2 TO_CHAR(ANALYZE_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS') ANALYZE_TIMESTAMP
3 FROM CHAINED_ROWS;
OWNER_NAME TABLE_NAME HEAD_ROWID ANALYZE_TIMESTAMP
----------- -------------- ------------------ -------------------
RM1 LARGE_TBL00 AAAUG6AAHAAAAAjAAB 2010/05/06 16:26:17
RM1 LARGE_TBL00 AAAUG6AAHAAAAAjAAD 2010/05/06 16:26:17
RM1 LARGE_TBL00 AAAUG6AAHAAAAAjAAF 2010/05/06 16:26:17
.................................................................
この情報を利用して、行移行を解消するための方法を以下に紹介します。
（PCTFREEはそのまま）
1. 行移行が発生しているレコードをワーク表に書き出す。
SQL> CREATE TABLE LARGE_TBL00_WORK AS
2 SELECT * FROM LARGE_TBL00 WHERE ROWID IN (
3 SELECT HEAD_ROWID FROM CHAINED_ROWS
4 WHERE OWNER_NAME='RM1' AND TABLE_NAME='LARGE_TBL00');
表が作成されました。
念のため、行移行が発生している件数を確認しておきます。
SQL> SELECT COUNT(*) FROM LARGE_TBL00_WORK;
COUNT(*)
----------
50000
2. 元表から行移行が発生しているレコードを削除する。
SQL> DELETE FROM LARGE_TBL00 WHERE ROWID IN (
2 SELECT HEAD_ROWID FROM CHAINED_ROWS
3 WHERE OWNER_NAME='RM1' AND TABLE_NAME='LARGE_TBL00');
50000行が削除されました。
SQL> COMMIT;
コミットが完了しました。
3. ワーク表から元表にレコードを戻す。
SQL> INSERT INTO LARGE_TBL00 SELECT * FROM LARGE_TBL00_WORK;
50000行が作成されました。
SQL> SELECT COUNT(*) FROM LARGE_TBL00;
COUNT(*)
----------
100000
SQL> COMMIT;
コミットが完了しました。
TABLE_NAME NUM_ROWS BLOCKS PCT_FREE CHAIN_CNT AVG_ROW_LEN LAST_
----------- ---------- ------- --------- ---------- ----------- -----
LARGE_TBL00 100000 20003 0 0 350 13:00
^^^行移行解消！
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------------- ----------- ------------ ---------- ----------
LARGE_TBL00 7 34 20405 4081
IDX_RNO00 7 39 1550 310
統計
--------------------------------
10807 consistent gets
論理読込ブロック数は、方法１,２に比べ若干小さくなりました。
4. ワーク表を削除する。
SQL> DROP TABLE LARGE_TBL00_WORK; 表が削除されました。
▼ まとめ
行移行は前回ご紹介したようにPCTFREEを適切に設定しておけば、発生する
確率を抑えることができます。しかし、運用を長く続けているとそれでも次第
に発生する行移行によって、インデックス検索の負荷がだんだん大きくなって
しまい、CPU負荷の増大から処理遅延を引き起こしてしまいます。
行移行が発生したとしても、今回ご紹介した３つの方法のいずれかで解消す
ることができます。
どの方法でも大差ありませんが、実行の容易さから「方法２ テーブルの移動」
がお勧めです。（インデックスのリビルドを忘れないように注意！！）
筆者が以前経験した例では、行移行を地道に解消していったら、SQL文を改
修することなく夜間バッチ処理時間を3割以上短縮することができたというこ
ともありました。行移行侮るなかれ！です。
来週は、行連鎖を検証してこのシリーズを終わりたいと思います。
ミラノが本拠地のインテル、CL制覇で３冠達成！！日本代表は不安だ～
恵比寿より