節約プログラマー雑記

最近、Oracleを使っているシステムで、稼働してから2年ほど問題無く動いていたのですが、最近遅くなってきたという問い合わせを受けました。
ただ、SQLの観点から調査してみても、実行計画は特に問題が無く、実際に開発環境でも問題なく処理できていたため、正直原因が良くわからずしばらく苦しんでいました。

その後、まさかとは思っていたのですが、行移行の観点から調べてみると、行移行が自分のシステムで発生していることが判明し、対処することとなりました。

今回は自分の戒めを込めて、その時の調査方法と対処、予防策を書いていきたいと思います。

1. 行移行とは

そもそも行移行とはなんぞやという話もあるかと思います。

簡単に言うと、データ更新時にデータブロック内にデータが収まらなくなり、行が移動した結果、複数のデータブロックを読み込むようになる事象であり、その結果データの読み込みに時間がかかるようになってしまいます。

また、Oracleに詳しい他のサイトでは次のように説明されています。

データ更新時に発生する現象

行移行とは、データレコードを更新するときにブロック内の空き領域を使用してもデータブロック内に収まらず 実データの行断片(レコードピース)を、すべて、別のデータブロックへ引越している状態をさす。
移行先の ROWID を行ディレクトリに格納する線形リストで管理している。

※ 行移行は完全な移動ではないので ROWID は変更されていない点がポイント。

（参考）SHIFT THE Oracle 行移行とは

2. 調査方法

行連鎖・行移行が発生しているかについては、USER_TABLESからチェックをしました。「SELECT TABLE_NAME, CHAIN_CNT FROM USER_TABLES」を実行すると、テーブル名と行移行・行連鎖の件数が出力され、問題が起きているテーブルは、ピンクセルのように0より大きい数が表示されます。

3. 対処

これで、問題のテーブルがわかりましたので、対処をしていきます。今回実施した方法は2点あり、1つは「データの再登録」、もう1つは「pcfreeによる行移行の抑制」になります。

「データの再登録」については、簡単で単純にテーブルをTRUNCATEして、データの入れ直しを行います。
私の場合は、下記のような形で一度一時テーブルを作成して退避した後、データの入れ直しを行いました。

データの入れ直しが終わり、再度USER_TABLEからデータを確認すると「CHAIN_CNT」が0になっていることが確認できました。

データの再登録

-- テーブルの一時退避
CREATE TABLE TEMP_TABLE
AS SELECT * FROM TABLE_D
/
-- 現行テーブルの削除
TRUNCATE TABLE TABLE_D
/
-- 入れ直し
INSERT INTO TABLE_D SELECT * FROM TEMP_TABLE
/
-- 一時テーブルの削除
DROP TABLE TEMP_TABLE PURGE
/

そしてもう1つの「pcfreeによる行移行の抑制」ですが、これはテーブル定義のパラメータを変更して、データブロック内の更新用の空き領域をデフォルトの10よりも大きくすることで、行移行が発生しづらくするチューニングになります。

ただ、空き領域を増やすことで、テーブルに必要なデータブロック数も増えてしまい、結果としてテーブルのサイズそのものが増えてしまうので、その点は注意が必要になります。

pcfreeによる行移行の抑制

CREATE TABLE TABLE_D
(
   COLUMN_A VARCHAR2(10),
   COLUMN_B VARCHAR2(10)
)
PCTFREE 40 --ここをデフォルトの10から40に変更

この2点を実施後、システムの応答時間が正常になり、今のところは行移行も再発しなくなりました。

もしOracleを利用しているシステムで応答時間が遅くなったという方がいましたら、参考になれば幸いです。