【PL/SQL】性能診断のための実行計画・統計情報の収集と分析

SQLが遅いと感じたとき、やみくもにインデックスを増やすのではなく、「実行計画」「実行時統計」「オプティマイザ統計」の三点を正しく採取・照合して原因を特定するのが最短です。この記事では、EXPLAIN PLANだけで終わらせない実務的な診断手順を、DBMS_XPLAN・SQLモニタ・各種データディクショナリを使って順に解説します。要点は、計画（Plan）と実測（A-Rows等）の”差”を見ること、そしてその差を生む統計・パラメータ・バインドの前提を確認することです。

まずは実行計画の取得：EXPLAIN PLANよりDISPLAY_CURSORを優先

EXPLAIN PLANは「推定計画」を示すだけで、実行時に選ばれた計画と異なることがあります。実運用の診断では、実際に走ったSQLのchild cursorに紐づく計画と実行時統計をDBMS_XPLAN.DISPLAY_CURSORで取得するのが出発点です。

-- 直前に実行したSQLのSQL_IDを確認（セッション内）
SELECT prev_sql_id FROM v$session
 WHERE sid = SYS_CONTEXT('USERENV','SID');

-- 実行済みSQLの計画と実測を取得（直近のchild）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => :sql_id,
  format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +NOTE'
));

ALLSTATS LASTを付けると、各オペレータで実際に処理した行数（A-Rows）やワーク量が表示されます。推定行数（E-Rows）と大きく乖離している箇所があれば、そこがボトルネックや誤見積りの震源になっている可能性が高いと判断できます。

実測値を付けて再実行：GATHER_PLAN_STATISTICSとヒントの使い分け

「対象SQLをもう一度流してもよい」状況なら、実測の収集を明確に有効化します。セッション単位で統計を付けるか、SQL単位でヒントを使うかを選びます。

-- セッション全体で実行時統計を収集
ALTER SESSION SET statistics_level = ALL;

-- SQL単位（アプリ変更不可でもヒントで付与可能）
SELECT /*+ GATHER_PLAN_STATISTICS */ ...
  FROM ... WHERE ...;

実行後にDISPLAY_CURSORを取ると、バッファ取得量・A-Rows・メモリ/Temp使用量などが具体的に見えるため、闇雲なチューニングを避けられます。

バインド変数とプラン分岐：ピークドバインドと子カーソルの確認

バインド値により選択性が大きく変わるSQLは、child cursorが分岐し、場合によっては悪化した計画を握ることがあります。DISPLAY_CURSORの+PEEKED_BINDSで直近のバインドを確認し、child_numberを指定して比較します。

-- 子カーソル一覧（選択性のばらつきや適用ヒントの差を把握）
SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware
  FROM v$sql WHERE sql_id = :sql_id ORDER BY child_number;

-- 特定のchildを表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id, :child_no,
  'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'));

選択性が尖る列にはヒストグラムが必要か、あるいは安定性を優先してヒストグラムを抑制すべきかを、この時点で仮説立てします。

SQLモニタで長時間・並列・ソート/ハッシュ重い処理を可視化

長く走る単発ジョブや並列実行は、SQLモニタのレポートが有効です。リアルタイムに各ステップの経過時間・I/O・Temp使用を把握できます。

-- 直近の高コストSQLに対するHTMLレポート（CLOB）
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id => :sql_id,
         report_level => 'ALL',
         type => 'ACTIVE'
       ) AS html
  FROM dual;

Tempが膨れているならソート/ハッシュの溢れ、PGA不足、JOIN順序の問題、カーディナリティ誤見積りなどが疑えます。

統計情報の健全性：表・列・ヒストグラム・拡張統計を点検

オプティマイザの推定は統計に依存します。古い・偏りを捉えていない・相関を無視している統計は誤見積りの温床です。基本点検は次の順で進めます。

-- 表の最終収集時刻・サンプル率・行数の確認
SELECT table_name, num_rows, last_analyzed, sample_size
  FROM user_tab_statistics
 WHERE table_name = 'SALES';

-- 列統計とヒストグラムの有無
SELECT column_name, num_distinct, density, histogram, last_analyzed
  FROM user_tab_col_statistics
 WHERE table_name = 'SALES';

-- 相関に効く拡張統計（エクスプレッション/列グループ）の存在
SELECT * FROM user_stat_extensions WHERE table_name = 'SALES';

偏りの強い列に対してヒストグラムが無い、あるいは不要なヒストグラムが誤誘導している、といったケースは珍しくありません。頻出条件に合わせて必要最小限のヒストグラムと、必要に応じた拡張統計（列グループ・式統計）を導入します。

統計の収集・保留・ロールバック：安全な更新フロー

いきなり本番反映せず、PENDING統計で影響を検証し、問題がなければパブリッシュする手順が安全です。

-- 保留統計を使う（セッションまたはシステム）
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;

-- 表統計を収集（必要なら列・インデックスも指定）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SALES',
                                method_opt => 'FOR ALL COLUMNS SIZE AUTO',
                                granularity => 'AUTO',
                                options => 'GATHER PENDING');
END;
/

-- 影響確認後に公開
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS(ownname => USER, tabname => 'SALES');
END;

直近の統計を削除して一つ前へ戻す、特定列のみ再収集する、といった粒度の運用もDBMS_STATSで可能です。

実行計画の安定化を検討：原因が統計でない場合の打ち手

統計健全でもブレが残る場合、プランベースラインやSQLプロファイルの適用を検討します。恒久対策はデータモデル・インデックス・SQL書き換えですが、運用を止めずに急場を凌ぐ選択肢として有効です。

-- 既知の良い実行計画を固定（概要）
BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => :sql_id, fixed => 'YES');
END;

固定は最後の手段とし、根原因（誤見積り・結合順序・結合方法・スカラサブクエリの爆発など）を潰すまでのブリッジとして扱います。

スキャン肥大の原因切り分け：E-RowsとA-Rowsの差を読む

テーブルアクセスでE-Rows≪A-Rowsなら「絞り込み弱い／誤見積り」、ハッシュ結合のビルド側でA-Rowsが想定以上なら「メモリ溢れ→Temp拡大」、ネステッドループの内側でA-Rowsが跳ねていれば「キー選択性の誤認」が疑われます。DISPLAY_CURSORの行ごとの実測に加え、v$sql_plan_statistics_allで細かい数字も掴めます。

SELECT plan_hash_value, id, operation, options,
       cardinality AS e_rows, last_output_rows AS a_rows
  FROM v$sql_plan_statistics_all
 WHERE sql_id = :sql_id AND child_number = :child_no
 ORDER BY id;

パラメータ前提の確認：セッションとシステムの差異を潰す

テストと本番でoptimizer_features_enableや並列度、result_cache、cursor_sharingなどが食い違うと、再現性が崩れます。再現環境ではセッションパラメータを合わせてから診断します。

-- 代表パラメータの差分確認（例）
SELECT name, value FROM v$parameter
 WHERE name IN ('optimizer_features_enable','optimizer_mode',
                'parallel_degree_policy','cursor_sharing','result_cache_mode');

代表的なボトルネック兆候と初動の直し方

実測でTempの急増が見えたら、JOIN方式とソート・ハッシュ領域（PGA）を疑います。NESTED LOOPSの内側が重いときはインデックス/結合順序の見直し、HASH JOINでビルド側が巨大なら駆動表の選び方と結合条件の選択性改善、MERGE/UPDATEでホットブロックならI/Oと分散、ロック待ちが混ざっていれば同時実行制御の再設計が有効です。いずれも「E-RowsとA-Rowsの差」を起点に、該当オペレータ直前の選択性を是正するのが効きます。

診断の定型ワークフロー：採取→照合→対策→検証

対象SQLを実行可能にし、DISPLAY_CURSORで実測付き計画を採取、子カーソルとバインドを確認、関連表・列の統計を点検、必要な統計や拡張統計を追加、再実行でA-Rowsの収束を確認、必要ならベースラインで安定化――という順で進めます。都度、SQLモニタやAWRスナップショット前後の待機差分で副作用を観察すると、変更の妥当性を早く判断できます。

まとめ

性能診断は、実際に使われた実行計画（DISPLAY_CURSOR）と実測（ALLSTATS）、それを支える統計（DBMS_STATS）の三点セットで行うのが最短です。推定と実測の差を見つけ、差を生む前提（統計・バインド・パラメータ）を正すと、計画は自然に良化します。長尺ジョブや並列処理はSQLモニタでボトルネックを特定し、必要最小限の統計・索引・書き換え・プラン安定化を組み合わせて、効果と安定性を両立させていきましょう。