【PL/SQL】実行計画と統計情報でSQL性能診断する方法｜DBMS_XPLAN・A-Rows・SQLモニタ

SQLが遅いときに、最初からインデックス追加やSQL書き換えに進むと、原因を外すことがあります。性能診断で最初に見るべきなのは、実際に使われた実行計画、実行時の行数、オプティマイザ統計、バインド値、実行環境の差です。特にEXPLAIN PLANだけで判断すると、実行時に選ばれた計画と違うものを見てしまうことがあります。

この記事では、PL/SQLとOracle SQLの性能診断を、DBMS_XPLAN.DISPLAY_CURSOR、A-Rows/E-Rows、SQLモニタ、統計情報、バインド変数から順に確認する手順で整理します。大量データ処理のコミット設計は 大量データ処理のコミット頻度とUNDO最適化、運用監視は DBMS_APPLICATION_INFOで運用観測性を底上げ と合わせて確認すると効果的です。

まずは診断の順番を決める

性能診断は、思いつきでSQLを書き換えるのではなく、同じ順番で確認すると原因を外しにくくなります。最初に実行済み計画を取り、推定行数と実測行数の差を見て、統計情報やバインド値を確認し、最後に対策を検証します。

EXPLAIN PLANよりDISPLAY_CURSORを優先する

EXPLAIN PLANは推定計画を見るには便利ですが、実行時に使われた計画と一致しないことがあります。本番障害や遅延調査では、実行済みSQLのsql_idとchild_numberをもとに、DBMS_XPLAN.DISPLAY_CURSORで確認するのが基本です。

-- 直前に実行したSQL_IDを確認
SELECT prev_sql_id
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV','SID');

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

ALLSTATS LASTを付けると、実際に処理した行数やバッファ取得量を見られます。+PEEKED_BINDSは、バインド値によって計画が変わるSQLの確認に役立ちます。

実測値を付けて再実行する

対象SQLを再実行できる状況なら、実測統計を明示的に収集します。セッション全体で有効にするか、対象SQLだけヒントで有効にするかを選びます。

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

-- SQL単位で実行時統計を収集
SELECT /*+ GATHER_PLAN_STATISTICS */
       *
FROM sales
WHERE sales_date >= :from_date
  AND sales_date <  :to_date;

本番で重いSQLを再実行する場合は、影響範囲を確認してから行います。再現環境で実行できるなら、バインド値、件数、統計、初期化パラメータを本番に近づけます。

E-RowsとA-Rowsの差を見る

実行計画で重要なのは、どのオペレーションで推定行数と実測行数がずれているかです。E-Rowsが10なのにA-Rowsが100000なら、オプティマイザは実際よりかなり少ない件数だと見積もっています。この差が結合順序、結合方式、Temp使用量、索引選択に影響します。

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

たとえば、あるフィルタ条件でE-Rows = 10、A-Rows = 100000になっているなら、列の偏り、古い統計、ヒストグラム不足、関数適用による索引利用不可などを疑います。逆にE-Rowsが大きすぎる場合は、不要なフルスキャンやハッシュ結合が選ばれる原因になります。

バインド変数と子カーソルを確認する

同じSQLでも、バインド値によって選択性が大きく変わると、複数のchild cursorが作られることがあります。特定のバインド値だけ遅い場合は、child cursor、peeked binds、bind sensitive / bind aware の状態を確認します。

SELECT
  sql_id,
  child_number,
  plan_hash_value,
  executions,
  is_bind_sensitive,
  is_bind_aware,
  is_shareable
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => :sql_id,
  cursor_child_no => :child_no,
  format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
));

バインド値によるばらつきが大きい列には、ヒストグラムが効く場合があります。ただし、不要なヒストグラムが計画を不安定にすることもあるため、頻出条件とデータ分布を見て判断します。

SQLモニタで長時間SQLを確認する

長時間実行、並列実行、Temp使用、ソートやハッシュ結合が重いSQLは、SQLモニタが有効です。実行中または直近のSQLについて、各ステップの経過時間、I/O、Temp、並列度を確認できます。

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id       => :sql_id,
         report_level => 'ALL',
         type         => 'TEXT'
       ) AS report_text
FROM dual;

HTMLレポートとして保存したい場合はtype => 'ACTIVE'も選択できます。Tempが増えているなら、結合順序、ハッシュ結合のビルド側、PGA、ソート条件、誤見積りを確認します。

統計情報の健全性を確認する

オプティマイザは統計情報をもとに実行計画を選びます。統計が古い、サンプル率が低い、列の偏りを捉えていない、相関列を見ていない、といった状態だと、推定行数が大きくずれます。

-- 表統計
SELECT
  table_name,
  num_rows,
  blocks,
  sample_size,
  last_analyzed,
  stale_stats
FROM user_tab_statistics
WHERE table_name = 'SALES';

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

-- 拡張統計
SELECT extension_name, extension
FROM user_stat_extensions
WHERE table_name = 'SALES';

条件に複数列の相関がある場合、単独列の統計だけでは見積もりが外れることがあります。その場合は列グループの拡張統計を検討します。

保留統計を安全に検証する

統計をいきなり本番公開すると、別SQLの実行計画まで変わることがあります。安全に試すには、対象テーブルのPUBLISH preferenceを一時的にFALSEにし、保留統計として収集してから、検証セッションでoptimizer_use_pending_statisticsを有効にします。

-- 1. 新しい統計をすぐ公開しない
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => USER,
    tabname => 'SALES',
    pname   => 'PUBLISH',
    pvalue  => 'FALSE'
  );
END;
/

-- 2. 統計を収集する。PUBLISH=FALSEのため保留統計になる
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => USER,
    tabname    => 'SALES',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade    => TRUE
  );
END;
/

-- 3. 検証セッションだけ保留統計を使う
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;

-- 4. 問題なければ公開する
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS(
    ownname => USER,
    tabname => 'SALES'
  );
END;
/

-- 5. 運用方針に応じて自動公開へ戻す
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => USER,
    tabname => 'SALES',
    pname   => 'PUBLISH',
    pvalue  => 'TRUE'
  );
END;
/

GATHER_TABLE_STATSのoptionsに存在しない値を入れて保留統計を作るのではなく、PUBLISH preferenceで公開制御するのが安全です。統計収集そのものの詳細は DBMS_STATSによる統計情報の手動収集・管理完全ガイド も参考になります。

統計だけで直らない場合の打ち手

統計を整えても計画が安定しない場合は、SQL書き換え、索引設計、パーティション、プランベースライン、SQLプロファイルを検討します。ただし、プラン固定は根本対策ではなく、急場をしのぐ手段として扱います。

BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => :sql_id,
    fixed  => 'YES'
  );
END;
/

大量データの更新や集計で遅い場合は、SQL単体だけでなく、コミット間隔、UNDO、チャンク分割、パーティション運用も関係します。パーティション運用は パーティションテーブル運用を自動化する方法、処理ステータス管理は 処理ステータス管理テーブル設計 と合わせて考えます。

権限と環境差を確認する

V$SQL、V$SESSION、V$SQL_PLAN_STATISTICS_ALL、SQLモニタは、環境によって参照権限が必要です。開発環境で見えるSQLが本番で見えない場合、権限不足やビューの違いを確認します。また、テストと本番で初期化パラメータや統計が違うと、同じSQLでも計画が変わります。

SELECT name, value
FROM v$parameter
WHERE name IN (
  'optimizer_features_enable',
  'optimizer_mode',
  'parallel_degree_policy',
  'cursor_sharing',
  'result_cache_mode'
)
ORDER BY name;

権限設計は AUTHIDと権限管理の設計、動的SQLの安全性は 動的SQLのセキュアな書き方 と合わせて確認すると安全です。

診断ワークフローを定型化する

性能診断は、毎回違う見方をすると属人化します。障害対応や定期点検で使えるように、採取、照合、対策、検証の流れを定型化しておきます。

やってはいけない性能診断

最後に、避けたい診断パターンを整理します。性能改善は、変更すれば何かが速くなる一方で、別SQLを遅くすることもあります。

まとめ

PL/SQLとOracle SQLの性能診断では、実際に使われた実行計画と実測行数を起点にします。DBMS_XPLAN.DISPLAY_CURSORで実行済み計画を取り、E-RowsとA-Rowsの差を見て、統計情報、バインド値、パラメータ、SQLモニタを順に確認します。

特に重要なのは、推定と実測の差を見つけること、統計情報を安全に検証してから公開すること、対策後に同じ指標で再測定することです。闇雲なインデックス追加ではなく、採取、照合、対策、検証の流れで進めると、性能改善の再現性が上がります。