Oracle 実行計画の取得方法の選択について

概要

Oracleで実行計画を取得する方法をまとめました。
※普段は、SQLチューニング依頼を受けた場合にはDBMS_XPLAN.DISPLAY_CURSORを使用しています。

方法

EXPLAIN PLAN+DBMS_XPLAN
AUTOTRACE(SQL*Plus)
DBMS_XPLANパッケージ(CURSOR/AWR)
動的パフォーマンスビュー(V$SQL_PLAN/V$SQL/V$SQL_TEXT)
SQLトレース+tkprof
リアルタイムSQL監視

上記5にはさらに以下があります。

SQL_TRACE(11gR1-)
10046トレース
DBMS_MONITOR

取得方法まとめ

方法		取得工数(手間)	システム負荷	実行統計	Actual値の取得	ライセンス要否
EXPLAIN PLAN		○	○	×	Estimate	不要
AUTOTRACE		○	○	○	Actual	不要
DBMS_XPLAN		○	○	○	Actual	要(※Diagnostic Packライセンス)
V$ビュー		○	○	×	Actual	不要
SQLトレース	SQL_TRACE(11gR1-)	△	△	○	Actual	不要
	EVENT10046	△	△	○	Actual	不要
	DBMS_MONITOR	△	△	○	Actual	要(※Tuning Packライセンス)
リアルタイムSQL監視		○	○	○	Actual	要(※Tuning Packライセンス)

補足

Actual(実行):
   過去、SQL実行で実際に使用された実行計画・実行統計を表示
Estimate(見積):
   Oracleが予測で見積もった実行計画・実行統計を表示。
   そのため、実行時の実行計画と異なる可能性あり

なお、DBMS_XPLANは、利用方法によってEstimate/Actualが異なります。

パターン1.EXPLAINとの併用実行計画:Estimate/実行統計:Actual
パターン2.SQL実行後の取得実行計画:Actual/実行統計:Estimate
パターン3.STATISTICS_LEVEL=ALL+format句にALLSTATS LAST
実行計画:Actual/実行統計:Estimate&Actual

その他の取得方法

STASPACK/AWR SQLレポート
SQLHC
SQLTXPLAIN(SQLT)
EVENT10053→※CBOトレースですが、実行計画情報も含まれます

...他多数

メリット/デメリット

No	取得方法	メリット/デメリット
1	EXPLAIN+DBMS_XPLAN	<メリット> SQLを実行しない(PARSEのみ)ため、PLANを即時に確認可能 Client側(SQL*Plus等)のみで作業が完結可能 PLANの詳細情報の確認可能(パラレル・クエリ等) バインド変数を設定しなくてもPLANの確認が可能 DMLであっても実行せずにPLANの取得可 <デメリット> EXPLAINで確認したPLANと実行時のPLANに差異がある場合がある
2	AUTOTRACE	<メリット> PLANの取得と実行統計の取得がSQLPLUSのみで完結するため取得工数が少ない <デメリット> PLANの確認は、SQL完了まで待つ必要がある SQLPlusでのみの使用となる PLANの出力は、内部的にはEXPLAIN PLANが実行されているため実行時のPLANと異なる場合がある「SET AUTOTRACE TRACEONLY」を実行しても(あくまでSQL*Plusの表示上の範囲のため)SQLが実行されてしまう
3	DBMS_XPLAN	<メリット> EstimateとActualの双方が取得可能(format句による) 事前設定が少ないため、比較的に簡易に取得可能「Predicate Information」等の追加情報が得られる <デメリット> CURSORの場合、共有プール上に存在しない場合、情報が取得できない
4	V$ビュー	<メリット> (権限が付与されていれば)簡易に取れるため、取得工数が少ない <デメリット> 実行統計は取得できない
5	SQLトレース	<メリット> 出力されるPLANが実行時のPLAN(Estimateではない) PLANと併せて実行統計(経過時間/ブロック読込量/待機イベント)も取得可能 <デメリット> PLANの出力は、SQLが完了するまで待つ必要がある SQLコマンドとOSコマンド(tkprof)が混在するため手順が増える
6	リアルタイムSQL監視	<メリット> 取得に伴うオーバーヘッドが少ない GUIから容易にボトルネックとなっている処理が特定できる SQLの終了待たず、実行中にPLANや実行統計が確認可能 <デメリット> (MONITORヒント)を付与していない場合、5秒以内のSQLは監視にかからない