行移行・行連鎖に関する検証 その４
＜行移行・行連鎖に関する検証 その４＞
ペンネーム： ミラニスタ
▼ 前回のおさらい
小さなテーブルを作成し、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つのテーブルを作成します。各表のカラムは
「その１」で作成した時と同じです。
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が欲しくてたまらない！！
恵比寿より