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

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

▼ 前回のおさらい

小さなテーブルを作成し、1ブロック(2048Byte)にぎりぎり収まるよう10レ
コードをInsertしました。
その後、1つのレコードに対して約2倍の長さとなる更新を実施し、行移行を
発生させました。
さらに、ブロック・ダンプを取得して行移行が発生している状況を細かく見
てみました。

○ まとめ

 ・ORDER句なしの単純な問い合わせだと、行移行したレコードは別のブロッ
  クに存在しているので、一連のレコードが返される順序が変わる。
 ・行移行が発生しても rowid は変わらない。(索引はそのまま使える。)
 ・行移行が発生すると、移行元ブロックに nrid という移行先ブロックを指
  し示すポインタを残す。
 ・移行先ブロックに更新後のレコードが書き込まれるが、移行元ブロックか
  らは元のレコードは削除されない。(moveではなくcopyのイメージ)
 ・移行先ブロックには、移行元ブロックを指し示す hrid というポインタが
  存在する。(実際の検索等で使用されるかは不明)

▼ 行移行はパフォーマンスにどれくらい影響があるか?

インデックス検索では rowid を特定してブロックにアクセスしますが、行
移行が発生している状態でのアクセス・パスを実行計画を使って簡単に説明し
ます。

  -----------------------------------------------------------
  | Id  | Operation                   | Name        | Rows  |
  -----------------------------------------------------------
  |   0 | SELECT STATEMENT            |             | 10000 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| LARGE_TBL00 | 10000 |
  |*  2 |   INDEX RANGE SCAN          | IDX_RNO00   | 10000 |
  -----------------------------------------------------------

  1. B*ツリー・インデックスをたどって、条件値に合致したリーフ・ブロック
     に含まれる rowid を特定する。
  2. この場合、単一でなく複数レコードが対象であれば
     「INDEX RANGE SCAN」となる。(Id = 2)
  3. 特定された rowid を基に実表の該当するブロックにアクセスする。
  4. 実際のレコードは nrid が指す別のブロックに存在するので、そのブロッ
     クにアクセスして最終的に必要なデータを得る。(Id = 1)
  5. INDEX RANGE SCAN の場合、3-4を行数分繰り返す。

この 4. の分が、行移行が発生していない状態に比べて必要になるのでロス
となります。

▼ PCTFREEを変えてみる

行移行発生の確率を低減させるためには、更新でレコードが長くなる分を考
慮して「PCTFREE」という表属性を検討する必要があります。

マニュアルには以下の記述があります。

 「移行行を回避するには、PCTFREEを増やします。ブロック内に使用可能な
  空き領域を多く残しておくと、行の拡張に対処できます。」

              - Oracle Databaseパフォーマンス・チューニング・ガイド
                                               11gリリース2(11.2) -

そこで、PCTFREEを変えた3つのテーブルを作成します。各表のカラムは
「その1」で作成した時と同じです。

  TABLE_NAME       PCT_FREE
  -------------- ----------
  LARGE_TBL00             0
  LARGE_TBL10            10  (デフォルト値)
  LARGE_TBL20            20

さらに各表の RNO列に対してインデックスを作成します。

  CREATE UNIQUE INDEX IDX_RNO00 ON LARGE_TBL00 (RNO)
  TABLESPACE LG_TS_2K;

表領域はサイズのみ大きくしただけで、基本的に同じパラメータです。
(ブロックサイズは2KB)

TABLESPACE_NAME  BLOCK_SIZE INITIAL_EXTENT ALLOCATIO SEGMEN
---------------- ---------- -------------- --------- ------
LG_TS_2K               2048          10240 UNIFORM   AUTO

▼ 10万行 Insert し、1万行ずつ Update する。

まず最初に、以下のようにLOOP文を使って10万件のデータをInsertします。

  BEGIN
    FOR i IN 1..100000 LOOP
      DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));

    END LOOP;
    COMMIT;
  END;
  /

次に、以下のUPDATE文で全体の1割ずつを更新していきます。

  UPDATE LARGE_TBL&PCT SET
   COL0=RPAD(COL0,32,'U')
  ,COL1=RPAD(COL1,32,'U')
  ,COL2=RPAD(COL2,32,'U')
  ,COL3=RPAD(COL3,32,'U')
  ,COL4=RPAD(COL4,32,'U')
  ,COL5=RPAD(COL5,32,'U')
  ,COL6=RPAD(COL6,32,'U')
  ,COL7=RPAD(COL7,32,'U')
  ,COL8=RPAD(COL8,32,'U')
  ,COL9=RPAD(COL9,32,'U')
  WHERE MOD(TO_NUMBER(RNO),10) = &LEFT;
  COMMIT
  /

&PCT には(00,10,20)が入り更新対象のテーブルを特定します。
&EFT には RNO(一意のレコード場号)を10で割った余りを指定します。
0~9を指定することによって、1万件ずつ更新します。

▼ 行移行発生と確保されるブロック数の推移

各処理の直後にテーブル(およびインデックス)の統計情報を取得し、平均
レコード長と共に各テーブルの行移行行を集計したものが以下になります。

   %  AVG_ROW_LEN  PCTFREE=0  PCTFREE=10  PCTFREE=20   (1)
  --- -----------  ---------  ----------  ----------  -----
    0         190          0           0           0    -
   10         207     10,000           0           0    -
   20         223     10,000       8,888           0    -
   30         239     20,000      11,111       7,500   267%
   40         255     20,000      17,777      10,000   200%
   50         272     30,000      22,222      17,500   171%
   60         288     30,000      26,666      20,000   150%
   70         304     40,000      33,333      27,500   145%
   80         320     40,000      35,555      30,000   133%
   90         337     50,000      44,444      37,500   133%
  100         353     50,000      44,444      37,500   133%

  (1) --- PCTFREE=0 と PCTFREE=20 の割合

全体の2割が更新されていても、PCTFREE=20であれば行移行は発生していな
いことがわかります。(PCTFREE=0では10,000行発生)
さらに、更新率が高くなると比率はだんだん少なくなるものの、ブロック
空き領域が多いほど行移行の発生が少ないことは明らかです。

次に、テーブル領域として確保されているブロック数の推移を見てみます。

   %  AVG_ROW_LEN  PCTFREE=0  PCTFREE=10  PCTFREE=20   (2)
  --- -----------  ---------  ----------  ----------  -----
    0         190     10,215      11,350      12,765    89%
   10         207     12,255      11,350      12,765    89%
   20         223     12,260      13,615      12,765   107%
   30         239     14,290      14,180      14,675    97%
   40         255     14,290      15,880      15,310   104%
   50         272     16,330      17,010      17,220    99%
   60         288     16,330      18,140      17,860   102%
   70         304     18,365      19,840      19,765   100%
   80         320     18,365      20,405      20,405   100%
   90         337     20,405      22,670      22,315   102%
  100         353     20,405      22,670      22,315   102%
  --- -----------  ---------  ----------  ----------   ----
  (3)        186%       200%        200%        175%

  (2) --- PCTFREE=10 と PCTFREE=20 の割合
  (3) --- 更新率0% と 100% の割合

PCTFREEが大きいほど1ブロックに格納される行数が少なくなるため、同じ10
万行を格納するために必要なブロック数は多くなりますが、行移行によって増
加するブロックもあるため、PCTFREE=10(デフォルト)の場合は、20に比べて
むしろブロック数が多くなることもあります。(ブロック数と全件検索のパ
フォーマンスの関係については一番最後で述べます。

▼ 論理読み込みブロック数の違い

それでは、以下の要領で全体の1割にあたる10,000件のレコードにアクセス
します。確実にインデックス検索を行うためにヒント句を指定しています。

SQL> SELECT /*+ INDEX (LARGE_TBL00 IDX_RNO00) */ * FROM LARGE_TBL00
2 WHERE RNO BETWEEN ‘0000000000000001’ AND ‘0000000000010000’;

10000行が選択されました。

実行計画
————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)|
————————————————————————
| 0 | SELECT STATEMENT | | 10000 | 3152 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| LARGE_TBL00 | 10000 | 3152 (1)|
|* 2 | INDEX RANGE SCAN | IDX_RNO00 | 10000 | 150 (0)|
————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – access("RNO">=’0000000000000001′ AND "RNO" LARGE_TBL10 > LARGE_TBL20

長くなってしまいましたがいかがだったでしょうか?
次回は、行移行が発生してしまった状態をどのように解消することができる
のかを検証する予定です。

P.S. 最近 Twitter始めました(@databaseman)。まだ初心者ですが検証の裏
話をつぶやいているかもしれません。

iPadが欲しくてたまらない!!

恵比寿より