備忘録：　ORACLE の行連鎖を調査する方法

DBA のお仕事の一つに、定期的に ORACLE の統計情報の取得とパフォーマンス解析を実施ってのがあります。うちのシステムは DBA の監視部分を外注にだしているのですが、その定期レポートで行連鎖を頻発しているテーブルがあるんですが・・・と報告がありました。

この時点で、該当するテーブル名が容易に推測できたのですが、念のため調査。ORACLE には、行移行・行連鎖した行を ANALYZE コマンドでデータディクショナリに抽出・保存する方法が用意されています。具体的には以下の通りの手順。

- スポンサーリンク -

  1. CHAINED_ROWS テーブルの定義（初回のみ実施）
    sqlplus sys/パスワード as sysdba
@$ORACLE_HOME/rdbms/admin/utlchain.sql
truncate table chained_rows;
  2. 行連鎖、行移行に関する統計情報の取得
    ANALYZE TABLE スキーマ名.テーブル名 LIST CHAINED ROWS INTO CHAINED_ROWS;

    いちいちテーブル毎に発行するのがメンドウなら、こんな感じで。 

    DECLARE
BEGIN
    FOR CUR IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='スキーマ名') LOOP
        EXECUTE IMMEDIATE 'ANALYZE TABLE スキーマ名.' ||CUR.TABLE_NAME||' LIST CHAIN ROWS INTO CHAINED_ROWS';
    END LOOP;
END;
/
    このコマンドの実行によって、SQL の最適化時に利用されるコストに関する統計情報は更新されないので安心してこの SQL を発行してＯＫです。
  3. 取得した行連鎖、行移行に関する統計情報の閲覧
    SELECT COUNT(*),TABLE_NAME FROM CHAINED_ROWS GROUP BY TABLE_NAME;
    これでこんな感じの結果が出てくると思います。 
      COUNT(*) TABLE_NAME
---------- ------------------------------
        10 テーブル１
      6671 テーブル２

行移行と行連鎖の違い

ここら辺の知識に関しては、「SHIFT the ORACLE」のサイトがうまくまとまってます。引用させて頂くと、

行移行

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

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

※ 行移行は完全な移動ではないので ROWID は変更されていない点がポイント。ROWID が変更されると、テーブル所有のすべてのインデックスの ROWID を更新しなければならない。そのためテーブルのデータの更新だけに比べての数倍のコストが必要になる。

ROW MOVEMENT 指定によりパーティション移動を伴う更新の場合には格納先と同時に ROWID も変更される。(インデックスの更新は、データの更新にくらべて、およそインデックス数 × 2 のコストが余計に必要)

行移行が発生しているレコードの読み込みに最低 2 ブロック必要になるので効率が悪い 。空き領域は テーブルの PCTFREE (PCTUSED) パラメータ で管理されているので、そのチューニングを検討する。

行連鎖

データ挿入時に発生する現象

行連鎖とは 1 レコードの実サイズがデータブロックのデータ領域を超える場合(LONGを使用しているなど)に発生する。データは 1 ブロックに収まらずに、2 つ以上の行断片(レコードピース)に分割して格納される。行連鎖による行断片の連鎖の状態は、行ディレクトリに連鎖先のROWIDを格納する一方向の線形リストで管理されている。

行連鎖が発生したレコードの読み込みに最低 2 ブロック必要になるので効率が悪く、データブロックサイズを大きくすること(またはテーブル分割)を検討する。

との違いがあります。行連鎖も行移行も最低２ブロック以上のアクセスが必要なため、パフォーマンスが劣化する”悪”として広く知られてます。意外と知られていないのは、ブロック内連鎖ってヤツです。

ブロック内連鎖

カラムを 256 個以上もつテーブルの場合も、1 行断片(レコードピース)に格納できない。 (行断片の仕様)
しかし同一ブロックに次の行断片を格納できる十分な空き領域が存在すればブロック内行連鎖により同一データブロックに格納される。ブロック内行連鎖状態の場合には、ブロックI/Oによるロスは発生しないためレスポンスに大きな影響はない。
カラム数が非常に多いということは、データの伸張にともなって行移行を誘発しやすい。伸張したときに同一ブロックに格納できない場合には、複数データブロックに分かれて格納されるため全体の応答速度が徐々に、そして確実に悪化していく OLTP 向けには極力控えた方が良い。

また、カラム数を 256 個以上もつテーブルは表の圧縮がサポートされていない。ALTER TABLE 〜 MOVE COMPRESS を実行してもエラーにはならない。そしてディクショナリも圧縮済になるものの実際には圧縮されていない。

※その他、「レコードピース」という概念を知っておいた方がよいです。SHIFT the ORACLE ってサイト、ホント良くできてます。

残念ながら、ANALYZE コマンドで取得できる行移行・行連鎖に関する統計情報は、上記のいずれであるかを判別することはできません。

さて、話は初めの方に戻って、今回の原因は一番最後にご紹介したブロック内連鎖でおさまらずに行連鎖する場合だったわけです。とある要件を実現するため、あえて正規化を崩して 256 カラムを超えるテーブルが１つあるんです。当然、そいつに対するインサートが大量に実行されると、ブロック内連鎖で対応しきれず行連鎖が発生し、それがレポーティングされていたわけですね。

マシンスペックも共有ディスクの性能も５，６年前と比較すると急激にあがっていることもあり、今から設計するなら正規化の手法にきっちりと従ってＤＢ設計するのが吉と思われます。それでも対応できないときに初めて正規化を崩すと。。。

まぁ今も昔もこの手法でＤＢ設計してきましたが、マシンスペックの向上と同時に、ＡＪＡＸ等の技術のようにリクエスト数も飛躍的にあがってきてる今日この頃なので、今風な実装をするとパフォーマンス重視になるのは結局は同じかもねー。

- スポンサーリンク -

関連する記事＆スポンサーリンク