津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第32回 SQL統計と実行計画の出力について

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、台風による自然災害のニュースが多いですが大丈夫でしょうか。急に寒くなってきましたので体調にも気をつけてください。
今回は、SQL統計と実行計画の出力について少し説明します。SQLのパフォーマンス問題について、これまでいろいろと説明してきましたが、内容を少し整理してみましたので、参考にしてください。

1. SQL統計による問題分析について

まずは、SQL統計を使用したSQLチューニングについて説明しましょう。
SQLの実行計画については、いろいろと説明してきましたが、問題のSQLが特定されているのは少なく、一般的には非常に多くの中から問題のSQLを特定する必要があります(これが非常に難しいと感じている方が多いのではないでしょうか)。第3回の「Statspackから探る、パフォーマンス問題の原因特定方法」はインスタンス・チューニングまでの内容でしたので、続きとしてパフォーマンス問題のSQL特定方法について説明します(待機イベントからSQL統計を使用して、問題のSQLを特定する方法になります)。
今回はStatspackより便利なAWRを使用します(SQLを確認するときに、HTML 形式の”Complete List of SQL Text”セクションでSQLの全文を確認できます)。Oracle Database 11gR2になってAWRの情報が増えているので、分析するのに非常に便利になってきていますが、正しい見方を分かっていないと、かえって問題を見つけるのが大変になります。効果的に分析できるツールなどもあるので、それの紹介もしようと思います。

(1)SQLの問題に関係するイベント
SQLの問題に関係するイベントについて説明します。
第3回で説明したように、最初にTop 5イベントを見て問題を切り分け、インスタンスとSQLのどちらの問題なのか判断する必要がありますが、ここではSQLの問題を特定するイベントについて取り上げます。一般的に、データベースが遅く、以下のようなCPU、I/Oなどのイベント(待機イベント)の割合が多いと、SQLの問題になる場合が多いです。そのため、SQLを特定するには、このイベントに関連することを知っておく必要があります。

① CPU時間(CPU Time)
CPU Time(サーバー・プロセスのCPU時間)の割合が多い場合には、何も待機していない状態なので、見た目には問題ないように思えますが、無駄に(必要のない行にアクセスして)CPUを使用している可能性があります。そのため、処理行数が多い場合は、効果的な実行計画かを確認することが必要です(これは今までいろいろ説明してきた内容ですよね)。過度なSQLの解析(ライブラリ・キャッシュの競合)などでもCPUを消費する場合があります。

② ユーザI/O
以下のようなサーバー・プロセスの物理I/Oの割合が多い場合は、最適なアクセス方法でない(効率の悪い索引の使用や全表スキャンなど)、TEMPを使用している、またはリソース不足(ストレージ性能の不足、低いキャッシュ・ヒット率)の可能性があります。
・db file sequential read/db file scattered read(バッファ・キャッシュ経由のI/O)
・direct path read/direct path write(ダイレクトI/O)
・direct path read temp/direct path write temp(TEMPへのI/O)
これも論理I/Oまたは処理行数が多い場合には、実行計画を変更することで改善できる場合があります(今まで説明したように、索引、パーティション、表結合の順番などのI/Oを削減することを検討します)。
実行計画で改善できない場合には、TEMP領域の使用については第14回で説明したPGAが足りないからなので、PGAを増やすか索引を使用してPGAを使用しないようにする必要があります(DBブロックのI/Oについては第26回の「I/O周りについて(2)」を参照してください)。

③ ライブラリ・キャッシュ
ライブラリ・キャッシュ(共有カーソルなど)の競合が多い場合には、ハード・パース(SQLの解析)や子カーソルが多いSQLが存在している可能性があります。多くのSQLで同一のカーソルを使用することは、ハード・パースとライブラリ・キャッシュ(共有プール)の使用量を削減することで性能が向上しますが、逆に同一SQLに対する共有カーソルの競合が増加する場合もあります(なので競合関係は難しいのですが、あまり難しく考えずに改善できるところから行うようにすることです)。
これの代表的な待機イベントには、共有カーソル(cursor: mutex .. / cursor: pin ..)とライブラリ・キャッシュ・オブジェクト(library cache: mutex ..)があります。第7回の「共有プールについて」では、待機イベントまで説明していなかったので、以下でこの代表的な待機イベントについて説明します。

待機イベント 意味
cursor: mutex X カーソル関連mutex(cursor mutex)を排他モードで待機している。
この排他モードは、新しい子カーソルの登録(ハード・パース)のときに獲得する。
cursor: mutex S カーソル関連mutex(cursor mutex)を共有モードで待機している。
この共有モードは、既存の子カーソルを使用できるか確認するときに獲得する。
cursor: pin X カーソル関連mutex(cursor pin mutex)を排他モードで待機している。
この排他モードは、カーソルの作成/更新(ハード・パース)のときに獲得する。
cursor: pin S カーソル関連mutex(cursor pin mutex)を共有モードで待機している。
この共有モードは、カーソルが存在しているとき参照するために獲得する(参照カウンタを更新するため)。この処理は瞬時だが、同じカーソルに対する同時実行数が多くなると待ちとして現れる。
cursor: pin S wait on X カーソル関連mutex(cursor pin mutex)を排他モード保持中に対して共有モードで待機している(一般的なロック待ち)。
SQL実行中にDDLを実行する(共有カーソルのINVALID)や自動共有メモリ管理で共有プールが削減されるときなどはこの待機になる場合がある。
library cache: mutex X ライブラリ・キャッシュ・オブジェクト関連のmutex(以前のlatch: library cache)を排他モードで待機している。
library cache: mutex S ライブラリ・キャッシュ・オブジェクト関連のmutex(以前のlatch: library cache)を共有モードで待機している。

このようにSQLの解析時の待機イベントなので、実行計画の変更では改善できません(例えば、子カーソルが多くなると、待機イベント”cursor: mutex S”と”cursor: mutex X”の発生する可能性が増えますが、これを改善するには子カーソル数の削減をする必要があります)。チューニングの詳細については、第7回の「共有プールについて」を参照してください。

④ クラスタ待機時間
以下のようなRACキャッシュ・フュージョンによる競合が多い場合には、ブロック競合が多いということになります。これも実行計画の変更では改善できません。これについては、キャッシュ・フュージョンの説明をしないと分からないので、別の機会とします。
・gc buffer busy ..
・gc cr ..
・gc current ..

(2)SQL統計
次に、問題の待機イベントからSQL統計を使用してSQLを特定します。
SQL統計とは、SQLの実行に関係する統計の値が多いSQL情報を出力してくれます。いろいろなカテゴリに基づいてサブセクションがありますが、それぞれで出力される項目が異なります(以下 はSQL ordered by Elapsed Timeの例です)。

tsushima-32-2

左端にそれぞれのカテゴリの基準項目が出力され、これの全体の比率が”%Total”となります(Version Count以外に出力します)。基本は、基準項目の上位のSQL(%Totalの高いSQL)を確認して問題のSQLを特定します。ただし、Executions(実行回数)が多いと1回あたりの平均値(.. per Exec)が少ない可能性があり、実行計画では改善されない場合があります(そのような場合には、SQLの呼び出される方法を検討して、少なくできないか検討する必要があります)。この実行回数は、SQL ordered by CPU TimeからReadsまでのサブセクションに出力されます。SQL統計には、以下のサブセクションがありますが、意味と使用方法について簡単に説明します。

SQL統計 意味と使用方法
SQL ordered by Elapsed Time 経過時間が長いSQL(項目の中の%CPUと%IOで、CPUとI/Oのどちらの割合が大きいか確認できる)
時間が掛っているSQLなので改善の対象になるが、これだけで最も影響が大きいかまでは判断できない(待機の割合が多いSQLを探す必要がある)。
SQL ordered by CPU Time サーバー・プロセスのCPU時間が多いSQL(経過時間に対する比率が%CPU)
CPU Timeの割合が多い場合には、これと処理行数が上位のSQLの実行計画を確認する(処理行数を削減することで改善できるかを確認する)。
SQL ordered by User I/O Wait Time サーバー・プロセスのI/O待機時間が長いSQL(経過時間に対する比率が%IO)
TEMPのI/O時間の割合が多い場合には、これの上位のSQLを確認する(実行計画のどこでTEMPを使用しているかは、後で説明するDBMS_XPLAN.DISPLAY_CURSOR関数やリアルタイムSQL監視で確認できる)。
SQL ordered by Gets 論理I/O(DBバッファ・キャッシュ経由の論理I/O)が多いSQL
バッファ・キャッシュ経由のI/Oが多い場合は、これの値も確認して、キャッシュ・ヒット率の問題で時間が長いのではないことを確認する。
SQL ordered by Reads すべて(フラッシュ・キャッシュ+非フラッシュ・キャッシュ)のDBブロックの読込みが多いSQL
DBブロックのI/Oが多い場合は、これとSQL ordered by User I/O Wait Timeが上位のSQLの実行計画を確認する(I/O時間だけでなく読込み数も見ることで、読込み数を削減することで改善できるかを確認する)。
SQL ordered by Physical Reads
(UnOptimized)
非フラッシュ・キャッシュからのDBブロックの読込みが多いSQL(Oracle Database 11gR2から)
フラッシュ・キャッシュがない場合はSQL ordered by Readsと同じ。
SQL ordered by Executions 実行回数が多いSQL(全体と1回あたりの処理行数が分かる)
CPU TimeやダイレクトI/Oが多い場合には、これの処理行数が多いSQLも確認する。
SQL ordered by Parse Calls 解析コール数(ハード及びソフト・パース)が多いSQL
cursor: pin Sが多い場合には、ソフト・パースが多いことなので、これの上位のSQLを確認して、同時実行数の削減を検討する。cursor: pin X/cursor: mutex X/library cache: mutex ..が多い場合には、ハード・パースが多いことなので、これとSQL ordered by Versoion Countが上位のSQLを確認して、ハード・パースの数を削減する(共有メモリを増やす、カーソルの共有化などを行う)。
SQL ordered by Sharable Memory 共有メモリ使用量が多いSQL(デフォルトは1Mバイト以上)
複雑なSQLや子カーソルが多いために多く消費しているので、共有メモリが不足するような場合には削減を検討する。
SQL ordered by Version Count 子カーソル(同一SQLで実行計画が異なる)が多いSQL
cursor: mutex ../library cache: mutex ..が多い場合は、これの上位のSQLを確認して、子カーソルの削減を検討する。
SQL ordered by Cluster Wait Time クラスタ待機時間が多いSQL(RACキャッシュ・フュージョン関係)
DBブロック競合なので、無駄なブロックにアクセスしない、パーティション化などを行う。

それぞれのサブセクションには、関連がある場合が多いので、一つのサブセクションだけで決定しないようにしてください。これは原因の可能性が複数あるので、関係するサブセクションを見て特定する必要があるからです(ただし、それぞれのサブセクションで同じSQLを探すのは大変な作業です。そのため、後から説明するAWR Formatterなどが欲しい訳です)。特定のSQLが問題でない場合もあるので、SQLが問題と決めつけると、本当の問題を見つけられない場合があります(例えば、ユーザI/O時間の割合が多いのは、キャッシュ・ヒット率やストレージ性能の問題なのかもしれません)。
PL/SQLの場合は、その中のすべてのSQLによって使用されるリソースが含まれます。そして、個々のSQLもレポートされるため、”%Total”の総計が100を超える可能性があるので、注意してください。

(3)AWR Formatterの紹介
知っている方も多いと思いますが、念のためにAWR Formatterツールを紹介します。
AWR Formatter は、AWRを分かり易くフォーマットしてくれる、chromeのplugin機能になります。これで分析すると楽になるので使用してみてください(以下はSQL統計の出力例で、必要な項目をマージして表示しています)。詳細は、//tylermuth.wordpress.com/2011/04/20/awr-formatter/を参照してください。

tsushima-32-4

2. 実行計画の出力について

最後に、今まで説明していなかった実行計画の出力について説明しましょう。
今まで、実行計画の見方をいろいろ説明してきたように、問題のあったSQLをチューニングするには、実行計画を出力する必要があります。その実行計画を出力するには、以下のような様々なツールがありますが、上手く使えていますでしょうか。このようなツールの使い方も Oracle技術者には重要なことなので、上手くツールを使うことが(効果的に調査することが)できるようになってください。
・AUTOTRACE(SQL*Plus)
・EXPLAN PLAN
・SQLトレース
・実行計画のキャッシュ機能
AUTOTRACEとEXPLAN PLANは、SQLを特定してから実行して調べるツールなので、使うのが限られます(最近は、あまり使用しないと思います)。やはり、運用している環境で動作が遅くなったSQLを特定して、チューニングするのが一般的だからです。そのため、問題のSQLを特定して再度実行するというのは難しいものです。また、実行する環境が異なると同じ実行計画にならない場合などもあります。そのため、SQLトレースを使用して実際の実行計画を出力するなどが必要になりましたが、これは余分な負荷が掛るため頻繁に行えないなど、これまでは実行計画の分析を難しくしていました。そこで、実行したときの実行計画をそのまま使用するのが最適ということで、Oracle9iから提供されたのが実行計画のキャッシュ機能です。この機能を使用して、様々な方法で実行計画を出力できるようになっています。

(1)実行計画のキャッシュ機能
これまで部分的な説明しかしていなかったので、ここで実行計画のキャッシュ機能について簡単に説明します。
実行計画のキャッシュ機能は、Oracle9iから提供された機能で、共有SQL領域(ライブラリ・キャッシュ)に存在する間は実行計画の情報が、動的パフォーマンス・ビュー”V$SQL_PLAN”に格納される機能です。そのため、共有SQL領域に存在するSQLは、実行計画を簡単に出力することができるので、問題が発生してからSQLを分析するときなどに非常に便利です。DBMS_XPLAN.DISPLAY_CURSOR関数(Oracle Database 10gから)やリアルタイムSQL監視(Oracle Database 11gから)、パフォーマンス統計(AWRやStatspackのスナップショット)への出力などで使用しています。これによって再実行しなくてもパフォーマンス統計があれば、いつでも実行計画を作成することが可能になっています。ここでは、DBMS_XPLAN.DISPLAY_CURSOR関数とパフォーマンス統計からの出力について簡単に説明します。

・DBMS_XPLAN.DISPLAY_CURSOR関数
第27回で少し説明しましたが、以下のように実行計画を出力することができます(sql_idを省略すると最後に実行したSQLが対象になります)。GATHER_PLAN_STATISTICSヒントを使用しているのは、実行時の統計を出力するためです(これは、初期化パラメータSTATISTICS_LEVELをALLするば必要ありません)。

tsushima-32-5

formatパラメータで出力する内容を指定します。この例では、typical(デフォルトで最も一般的な情報)、allstats(IOSTATSとMEMSTATSのことで、実行時のI/O統計とメモリ統計が含みます)、last(最後の実行に対する統計情報)を指定しています。上記のように様々な情報を出力するので、分析がし易くなっています。以下のように1つのSQLで指定することも可能です。

tsushima-32-6

・パフォーマンス統計からの出力
パフォーマンス統計に情報を出力することで(Statspackはレベル6以上のスナップショットが必要)、第4回で説明したようにスクリプトを使用して、後から実行計画を出力することが可能です。ここでは、第4回で説明していなかったAWRのスナップショットから出力する方法を載せておきます。Oracle Database 10gR2から以下のスクリプト"awrsqrpt.sql"を実行して、対象のSnapshotIDとSQL_ID(SQL統計で特定したSQL)を入力することで、出力することができます。

tsushima-32-7

3. おわりに

今回はSQL統計と実行計画の出力について説明しましたが、少しは参考になりましたでしょうか。また機会があれば他のことについても説明したいと思います。これからもよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。

津島博士のパフォーマンス講座 indexページ▶▶