GAGA LIFE.

DBAブログ

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

概要

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

方法

1.EXPLAIN PLAN+DBMS_XPLAN
2.AUTOTRACE(SQL*Plus)
3.DBMS_XPLANパッケージ(CURSOR/AWR)
4.動的パフォーマンスビュー(V$SQL_PLAN/V$SQL/V$SQL_TEXT)
5.SQLトレース+tkprof
   ・SQL_TRACE(11gR1-)
   ・10046
   ・DBMS_MONITOR
6.リアルタイムSQL監視

取得方法まとめ

方法 取得工数(手間) システム負荷 実行統計 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の取得と実行統計の取得がSQL*PLUSのみで完結するため取得工数が少ない
<デメリット>
- PLANの確認は、SQL完了まで待つ必要がある
- SQL*Plusでのみの使用となる
- 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は監視にかからない

参考資料

--V$ビュー
http://odakeiji.blog33.fc2.com/blog-entry-23.html

--リアルタイムSQL監視
http://www.oracle.com/technetwork/jp/ondemand/db-basic/d-16-ssqltuning-1448439-ja.pdf

--その他
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition

--全体
Expert Oracle SQL
Cost-Based Oracle Fundamentals
Pro Oracle SQL, Second Edition
Troubleshooting Oracle Performance, Second Edition
Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2)
http://www.doppo1.net/oracle/tuning/explain-plan.html#2
https://www.ashisuto.co.jp/db_blog/article/20160630_sqltrace.html
http://d.hatena.ne.jp/yohei-a/20160605/1465079548
http://www.intellilink.co.jp/article/column/ora-report20150507.html