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

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

▼ 前回のおさらい

 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-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)と同じ

○方法2: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

 エクスポート/インポートもテーブルの移動も結果は同じになりました。
 前者の方がきれいに行移行が解消できそうなイメージですが、実際は同じで
した。メンテナンスの手間やサービス影響度を考えるとテーブル移動の方がお
勧めです。

○方法3: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

 論理読込ブロック数は、方法1,2に比べ若干小さくなりました。

4. ワーク表を削除する。

  SQL> DROP TABLE LARGE_TBL00_WORK;
  
  表が削除されました。

▼ まとめ

 行移行は前回ご紹介したようにPCTFREEを適切に設定しておけば、発生する
確率を抑えることができます。しかし、運用を長く続けているとそれでも次第
に発生する行移行によって、インデックス検索の負荷がだんだん大きくなって
しまい、CPU負荷の増大から処理遅延を引き起こしてしまいます。
 行移行が発生したとしても、今回ご紹介した3つの方法のいずれかで解消す
ることができます。
 どの方法でも大差ありませんが、実行の容易さから「方法2 テーブルの移動」
がお勧めです。(インデックスのリビルドを忘れないように注意!!)
 筆者が以前経験した例では、行移行を地道に解消していったら、SQL文を改
修することなく夜間バッチ処理時間を3割以上短縮することができたというこ
ともありました。行移行侮るなかれ!です。

 来週は、行連鎖を検証してこのシリーズを終わりたいと思います。

ミラノが本拠地のインテル、CL制覇で3冠達成!!日本代表は不安だ~

恵比寿より