2024.05.28
SQLチューニング 2nd Season（第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つを提供します。
■ 基本的な使い方は次の通りです。
[ TEXT ]
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>&sql_id , report_level=>'ALL' , type=>'TEXT' )
FROM dual ;
[ HTML ]
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>&sql_id , report_level=>'ALL' , type=>'HTML' )
FROM dual ;
[ ACTIVE ]
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>&sql_id , report_level=>'ALL' , type=>'ACTIVE' )
FROM dual ;
■ 次に各タイプの特徴や長所と短所についてご紹介します。
3.3.1.1 TEXT TYPE
TEXT TYPEは DBMS_SQLTUNE.REPORT_SQL_MONITOR の最も基本的な出力方式です。
このオプションはDBMS_XPLANと比較していくつかの長所と短所があります。
実際に出力されたデータを見てみましょう。
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SH (132:277)
SQL ID : c0y4tsbab59f4
SQL Execution ID : 16777216
Execution Started : 07/20/2015 12:41:07
First Refresh Time : 07/20/2015 12:41:07
Last Refresh Time : 07/20/2015 12:41:18
Duration : 13s
Module/Action : SH@ORCL1:132/Trial Until 2015-12-31
Service : orcl.168.1.58
Program : LitePlus.exe
Global Stats
=================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
=================================================================================
| 117 | 83 | 0.14 | 0.05 | 33 | 96030 | 859 | 750MB |
=================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=4)
==================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
==================================================================================================================================
| PX Coordinator | QC | | 0.18 | 0.11 | 0.01 | 0.05 | 0.01 | 381 | 91 | 1MB | |
| p000 | Set 1 | 1 | 29 | 17 | 0.01 | | 12 | 22354 | 179 | 175MB | |
| p001 | Set 1 | 2 | 29 | 23 | 0.03 | | 5.67 | 26744 | 215 | 210MB | |
| p002 | Set 1 | 3 | 29 | 21 | 0.01 | | 7.99 | 21342 | 172 | 168MB | |
| p003 | Set 1 | 4 | 29 | 21 | 0.07 | | 8.12 | 25209 | 202 | 197MB | |
==================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1103069905)
=============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
=============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | 1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | 5 | | 59 | 472KB | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | | 4 | | | | | | | |
| -> 4 | SORT AGGREGATE | | 1 | | 30 | +1 | 4 | 0 | | | | 69.23 | Cpu (36) | |
| -> 5 | HASH JOIN | | 821M | 40852 | 29 | +2 | 4 | 158M | | | 30M | 13.46 | Cpu (7) | |
| 6 | BUFFER SORT | | | | 1 | +2 | 4 | 328K | | | | | | |
| 7 | PART JOIN FILTER CREATE | :BF0000 | 82112 | 31 | 1 | +2 | 4 | 328K | | | | | | |
| 8 | PX RECEIVE | | 82112 | 31 | 1 | +2 | 4 | 328K | | | | | | |
| 9 | PX SEND BROADCAST | :TQ10000 | 82112 | 31 | 1 | +7 | 1 | 328K | | | | | | |
| 10 t
| -> 13 | PX BLOCK ITERATOR | | 18M | 39718 | 29 | +2 | 4 | 3M | | | | 3.85 | Cpu (2) | |
| -> 14 | TABLE ACCESS FULL | PARTITION_TEST2 | 18M | 39718 | 30 | +1 | 17 | 3M | 768 | 749MB | | 13.46 | Cpu (7) | 78% |
=============================================================================================================================================================================================
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の形で表示されるため、競合発生の有無を一目で確認できます。
Procedureは、特定の業務を処理するためにロジックをSQLと制御文を使って実装したものです。
業務処理に使用されるデータによって複数のテーブルを参照する必要があるため、様々なSQLが使用されます。
このように様々なSQLの中で『 どのSQLで性能低下が発生したのか？ 』『 どのような理由で性能が遅くなったのか？ 』を
把握することが、Procedureのようなプログラムの性能改善を進めるにあたっての最初に取り組むべきポイントです。
Procedureを実行したSQLをACTIVE TypeでDBMS_SQLTUNE.REPORT_SQL_MONITORを確認すると、上の画像のように
Procedure全体の実行時間で各SQLが実行された時間をTime Chartで一目で確認することができます。
最後にDBMS_SQLTUNE.REPORT_SQL_MONITORを使うことで得られるメリットは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（第14回） 終
次回ブログテーマ
SQLチューニングブログ 2nd Season（第15回）
「object情報分析及び活用方法」について
■ データベース運用のことなら日本エクセムにお任せください ■
当社の最新情報はSNSでも配信中（フォローをお願いします）
掲載内容についてのお問い合わせは
私たちは、日本のITインフラにおける
プロジェクト運営～システム運用 の安定化と効率化を推進します。
日本エクセム株式会社
営業推進部 まで
✉ sales@ex-em.co.jp
