Oracle SQLチューニング Season2（第14回）第3章 SQL実行情報の分析及び活用方法（3/3）
SQL実行情報の分析及び活用方法 の第3回目は「 DBMS_SQLTUNE情報を活用する 」 について解説していきます。
では、早速始めましょう。
3.3 DBMS_SQLTUNE情報を活用する
Oracle 11gからSQL実行履歴を抽出することができる Real-Time SQL Monitoring という新しい機能が提供されています。
Real-Time SQL Monitoringは、5秒（_SQLMON_THRESHOLDで調整）以上実行されたすべてのSQLの実行情報を収集し、収集された情報をV$SQL_MONITOR, V$SQL_PLAN_MONITOR で照会することができる機能をいいます。
また、Note. SQL Monitoring機能を使う場合には、STATISTICS_LEVELパラメータ設定が「TYPICAL」または「ALL」に設定しておく必要があります。
CONTROL_MANAGEMENT_PACK_ACCESSパラメータが「DIAGNOSTIC+TUNING」に設定されている必要があるため、DIAGNOSTICとTUNING PACKが購入されたEnterprise Edition DBでのみ使用できる機能ですので、注意しましょう。
DBMS_SQLTUNE PackageのREPORT_SQL_MONITORを使用すれば、今までには抽出することができなかった多くの情報が提供され、テキストやHTMLなどの様々な形でデータを照会することができます。
3.3.1 基本的な使い方と特徴
DBMS_SQLTUNE.REPORT_SQL_MONITOR は通常、長時間に渡って実行中のSQLやPARALLEL SQLの実行履歴を把握するためによく使われます。
既存の10046 TraceやDBMS_XPLANでは、SQLの実行が終わった後に実行履歴を抽出する必要があります。
しかし、DBMS_SQLTUNE.REPORT_SQL_MONITORを使えば、実行中のSQLについても現在の進行状況を把握することができるため、とても効率的なチューニングツールであると言えるでしょう。
また、DBMS_SQLTUNE.REPORT_SQL_MONITOR は多くのオプションがあります。
このうち TYPE オプションは出力データの形式を TEXT、HTML、ACTIVEの3つを提供します。
■ 基本的な使い方は次の通りです。
■ 次に各タイプの特徴や長所と短所についてご紹介します。
3.3.1.1 TEXT TYPE
TEXT TYPEは DBMS_SQLTUNE.REPORT_SQL_MONITOR の最も基本的な出力方式です。
このオプションはDBMS_XPLANと比較していくつかの長所と短所があります。
実際に出力されたデータを見てみましょう。
DBMS_XPLANと比較した場合、最も大きな利点は、SQLのバインド値 及び オペレーション別のEvent情報 を確認できる点です。
実行オペレーションのうち『 どの部分で最も多くのリソースを占めているか？ 』を容易に確認することが可能です。
更には、DBMS_SQLTUNE.REPORT_SQL_MONITORはSQLの実行中にも情報照会することができ、SQLが実行された後～現在までのSQLの実行履歴を確認することも可能です。
しかし多くの利点がある反面、残念ながら下記のようないくつかの重要な情報の抽出ができません。
SQLチューニング時に必要なデータのうち、抽出できない情報は次の通りです。
- row source operation別I/O量の不在
- Predicate Informationの不在（Active Optionでは確認可能）
- Pstart,Pstop パーティション関連情報の不在
いくつかの重要な情報が不足している状態にありますが、DBMS_XPLANとV$SQL_PLANなどDictionary Viewを適切に併用することができれば、欠点を補うことができます。
3.3.1.2 HTML TYPE
HTML TypeはTEXT Typeをもっとグラフィカルに表示するという利点があります。（つまり、色で表示された数値です。）
直感的でWait Event情報を%表示で確認することができますが、どのEventを待ってるか？については確認することができないため、ほとんど使われていません。
また直感性と表現される情報のいずれにおいても ACTIVE Type に比べて劣ってしまうのも使われない理由の一つです。
HTML Type抽出結果
3.3.1.3 ACTIVE TYPE
ACTIVE TypeはDBMS_SQLTUNE.REPORT_SQL_MONITORを出力する形の中で最もよく使うTypeです。
特に、PARALLEL Qyeryの結果を確認する時には威力を発揮します。
また、DETAILSタブで区分されたPlan、Parallel、Activity、Metricsの情報は、SQLチューニングを実施する場合において、非常に便利な情報として提供されます。
DBMS_SQLTUNE を使わない時と DBMS_SQLTUNE を使う時では、Parallel QueryやProcedureを使ったプログラムの分析やチューニングは大きく異なります。
DBMS_SQLTUNE.REPORT_SQL_MONITORの使用時に得られるメリットについては、 ACTIVE Type で出力されるデータで説明していきたいと思います。
Activityタブで提供するStat/EventデータはTime Graphの形で表示されるため、競合発生の有無を一目で確認できます。
Active Typeの Time Graph
Procedureは、特定の業務を処理するためにロジックをSQLと制御文を使って実装したものです。
業務処理に使用されるデータによって複数のテーブルを参照する必要があるため、様々なSQLが使用されます。
このように様々なSQLの中で『 どのSQLで性能低下が発生したのか？ 』『 どのような理由で性能が遅くなったのか？ 』を把握することが、Procedureのようなプログラムの性能改善を進めるにあたっての最初に取り組むべきポイントです。
ACTIVE TypeのProcedure出力画面
Procedureを実行したSQLをACTIVE TypeでDBMS_SQLTUNE.REPORT_SQL_MONITORを確認すると、上の画像のようにProcedure全体の実行時間で各SQLが実行された時間をTime Chartで一目で確認することができます。
最後にDBMS_SQLTUNE.REPORT_SQL_MONITORを使うことで得られるメリットはParallel Queryを実行する時です。
ACTIVE TypeのParallel Query出力画面
Parallel Queryの性能改善の始まりは､SQLが正しくParallelで実行されたか？を確認することです。
私たちが、Degree4を与えてParallelで実行したSQLが､ただ実行する時より4倍速くなることを期待します。
しかし、実際のParallel Queryの各Slaveがどのように動作するかを分析してみると、特定のセッションだけ動作したり（または動作しない）、動作してもI/O処理量が非常に少なく、性能が大きく改善されない場合が多いのです。
この場合、適切な改善案を導出する必要がありますが、改善案を導出するために最も重要なことはParallel Queryがどのように実行されているかの明確な分析です。
DBMS_XPLANやDBMS_SQLTUNEがなかったバージョンでは、Parallel Queryの実行履歴を把握するために各Slave Sessionのトレース結果を分析しなければなりませんでしたが、可読性が低いため分析することが難しかったのです。
しかし、Parallel QueryをACTIVE Typeで抽出し、ParallelタブとActivityタブを連携してParallel Queryのデータ処理を担当する各Slaveセッションの分析をすると、より簡単にParallel Queryの性能分析を行うことができます。
DBMS_SQLTUNEの有用性がどれほど素晴らしいかは実際に使ってみないと正確には分かりません。
ここでは簡単に紹介しましたが、このPackageを活用して実行計画を分析してみることをお勧めします。
次回ブログテーマ
SQLチューニングブログ 2nd Season（第15回）
「object情報分析及び活用方法」について
