GAGA LIFE.

DBAブログ -APとロジックとAKがある-

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は監視にかからない

参考資料

--EXPLAIN PLAN
EXPLAIN PLANの結果(PLAN_TABLEに書き込まれた実行計画)を整形して表示する方法について。(KROWN:71144) (Doc ID 1723960.1)

--AUTOTRACE
SQL*Plusでの実行計画取得方法(AUTOTRACE)(KROWN:11431) (Doc ID 1702212.1)
AUTOTRACEによる実行計画の出力レイアウトの変更方法(KROWN:49782) (Doc ID 1717389.1)

--DBMS_XPLAN
SQLの実行計画に実行統計を併記して表示する方法(DBMS_XPLAN.DISPLAY_CURSOR)(KROWN:141531) (Doc ID 1749298.1)
フォーマットされた実行計画を取得する方法 - 推奨される方法 (Doc ID 1988062.1)

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

--SQLトレース
パフォーマンス問題を調査するための10046イベントトレース(SQL_TRACE)の収集方法 (Doc ID 1551360.1)
SQLトレース(10046), TKProf と Explain Plan - 概要リファレンス (Doc ID 2397512.1)
処理を実行中のプロセスに対してSQL_TRACE(event 10046)を仕掛ける方法(KROWN:27131) (Doc ID 1708850.1)
[マスターノート] パラレル・クエリに対して SQL トレースを取得する方法(KROWN:152602) (Doc ID 1755435.1)
DBリンク先でのSQLトレース(event 10046)の取得方法(KROWN:56392) (Doc ID 1719733.1)

--リアルタイムSQL監視
リアルタイム SQL 監視の使用方法(KROWN:143337) (Doc ID 1750191.1)
http://www.oracle.com/technetwork/jp/ondemand/db-basic/d-16-ssqltuning-1448439-ja.pdf

--その他
STATSPACK を使用した実行計画の確認方法(KROWN:83426) (Doc ID 1726691.1)
event 10053 トレースの取得方法(KROWN:124903) (Doc ID 1739345.1)
AWR から SQL 文の実行計画を抽出する方法(KROWN:120012) (Doc ID 1734928.1)
SQL チューニング・ヘルスチェック・スクリプト (SQLHC) (Doc ID 1542531.1)
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition

Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition

Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition

 

 

--全体
Expert Oracle SQL 

Expert Oracle SQL: Optimization, Deployment, and Statistics

Expert Oracle SQL: Optimization, Deployment, and Statistics

 

 Cost-Based Oracle Fundamentals 

Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)

Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)

 

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