リアルタイムSQL監視

Transcription

1 2009 年 12 月

2 はじめに Oracle Database 11g で導入されたは リソース消費量が多く長時間実行される SQL 文やパラレル SQL 文で発生するランタイム パフォーマンスの問題を極めて効果的に特定する手段です Oracle Enterprise Manager(Oracle EM) のインタラクティブな画面に SQL 実行の詳細が表示されますが この情報を取得するのに使用されるのが 新しい詳細な SQL 統計です この統計は自動的に追跡されるようになっており 本番システムのパフォーマンスを低下させることがありません 実行計画の各ステップの統計は キー パフォーマンス メトリック別に追跡されます メトリックには 経過時間 CPU 時間 読取りと書込みの回数 I/O 待機時間 およびその他の各種待機時間などがあります これによりデータベース管理者は 以前よりも格段に詳細な SQL 実行を分析してから 長時間実行されている SQL を強制終了させるか 完了させるか チューニングを要請するかを判断できます 実装の詳細 Oracle Database の機能では 実行中の SQL 文のパフォーマンスを監視できます デフォルトでは SQL 文がパラレル実行された場合 または 1 度の実行で CPU 時間と I/O 時間を合計 5 秒以上消費した場合に リアルタイム SQL 監視機能が自動的に開始されます V$ ビューこの機能をサポートするために V$SQL_MONITORビューとV$SQL_PLAN_MONITOR ビューという 2 つの新しいビューがOracle Database 11gに導入されました 1 これらの新しいビューを次のビューと組み合わせて使用すると 監視中の実行に関する追加情報が取得できます V$ACTIVE_SESSION_HISTORY V$SESSION_LONGOPS 1 SQL 監視の使用方法としてはあまり簡単な方法ではありませんが Oracle Enterprise Manager のアクティブ レポートおよび SQL 監視のセクションで同じデータのグラフィカル表示も確認してください 2 ページ

3 V$SQL V$SQL_PLAN V$SQL_MONITOR ビューには V$SQL で使用できる統計のサブセットが含まれます ただし V$SQL とは異なり 監視統計は複数の実行を累計したものではありません V$SQL_MONITOR の 1 つのエントリには SQL 文の 1 回の実行に特化した統計が保持されます 同じ SQL 文の 2 つの実行が監視されている場合は 2 つの実行それぞれについて別々のエントリが V$SQL_MONITOR に作成されます 同じ SQL 文の 2 つの実行を一意に識別するために 実行キーと呼ばれる複合キーが生成されます この実行キーは 3 つの属性で構成されており 各属性は V$SQL_MONITOR の 1 つの列に対応しています SQL 文を識別するための SQL 識別子 (SQL_ID) 実行開始タイムスタンプ (SQL_EXEC_START) この主キーを確実に一意なものにするために内部的に生成される識別子 (SQL_EXEC_ID) SQL 監視データの保存監視が開始されると 動的パフォーマンス ビュー V$SQL_MONITOR にエントリが 1 つ追加されます 当該実行について収集されたキー パフォーマンス メトリック ( 経過時間 CPU 時間 読取りと書込みの回数 I/O 待機時間 およびその他の各種待機時間 ) は このエントリで追跡されます これらの統計は 文が実行されている間 ほぼリアルタイム (1 秒に 1 回 ) でリフレッシュされます 実行が終了すると 監視情報は少なくとも 5 分間 V$SQL_MONITOR ビューに保存されます SGA に保存されている SQL 監視データがカーソルのエージングにより V$SQL から削除されることはありませんが データが存在するのはサイズに制限のあるインメモリ バッファ内であるため 最終的には 監視中の新しい文の統計によって上書きされます そのため 長時間実行されている SQL 文やパラレル問合せがシステム上に多数存在する場合は すべての SQL 文が監視される保証はなくなります その理由は 2 つあります インメモリ バッファの領域に制限があるためと 監視リストに含まれる SQL 文は必ず 5 分間保存する必要があるためです ただし 事実上 このようなことは発生しないようになっています SQL 計画の監視では 監視中の SQL 文の実行計画に含まれる各操作の統計も監視されます このデータは V$SQL_PLAN_MONITOR ビューに表示されます V$SQL_MONITOR ビューと同様に V$SQL_PLAN_MONITOR の統計は SQL 文が実行されている間 1 秒間隔で更新されます これらの統計も 実行の終了後 少なくとも 5 分間保存されます V$SQL_PLAN_MONITOR には 監視されている各 SQL 文に対するエントリが複数保持されます 各エントリは 文の実行計画に含まれる 1 つの操作に対応します 3 ページ

4 パラレル実行の監視パラレル問合せ DML 文および DDL 文の監視は 実行が開始されると同時に自動的に開始されます パラレル実行に関与している各プロセスの監視情報は 別々のエントリとして V$SQL_MONITOR ビューと V$SQL_PLAN_MONITOR ビューに記録されます V$SQL_MONITOR には パラレル実行コーディネータ プロセス用のエントリが 1 つと パラレル実行サーバー プロセス用のエントリがプロセスごとに1 つあります 各エントリに対応するエントリが V$SQL_PLAN_MONITOR に保持されます SQL 文のパラレル実行に割り当てられたプロセスは同じ実行のために協調して動作するため これらのエントリには同じ実行キー (SQL_ID SQL_EXEC_START および SQL_EXEC_ID からなる複合キー ) が割り当てられます したがって 実行キーで集計すれば 1 つのパラレル実行の統計全体を確認できます Oracle Real Application Clusters(Oracle RAC) システムでのパラレル実行はクロス インスタンスになるため V$ ビューには現在のインスタンス上で実行されたプロセスだけが表示されます クロス インスタンス統計を確認するには GV$ ビューを使用する必要があります アクティブ レポート Oracle Database 11g Release 2 以降 SQL 監視のレポートにアクティブ レポート機能が加わりました アクティブ レポートは オフライン分析に使用できるインタラクティブなレポートです この機能には Oracle Enterprise Manager のライブ表示と同じレベルの双方向性があり さまざまなレベルの詳細情報にドリルダウンできます たとえば SQL 監視アクティブ レポートには 返された行の予想数と実際の数が実行計画のステップごとに表示されます この情報は オプティマイザが特定の実行計画を選択した理由を理解するうえで欠かせない場合があります アクティブ レポートは単一の HTML ファイルとして保存したり 後から参照できるようにアーカイブしたりすることができます また 詳細な分析をしてもらうために たとえばパフォーマンスの問題に詳しい社内の人材やオラクルのサポート担当者に電子メールで送信するなど しかるべき人に転送することもできます 図 1:SQL 監視アクティブ レポートアクティブ レポートを受け取った人は Oracle Enterprise Manager や Oracle Database がインストールされていなくてもレポートを表示できます アクティブ レポートは SQL Monitoring Execution Details ページの上部にある新しいボタン コントロー 4 ページ

5 ルを使用する方法か DBMS_SQLTUNE パッケージに含まれる REPORT_SQL_ MONITOR() ファンクションを呼び出す方法で生成できます Oracle Enterprise ManagerのSQL 監視ワークフロー Oracle Database 11g の最初のリリースに含まれる SQL 監視は コマンドラインで使用するものでした 実行中のアクティブな SQL を監視するためのグラフィカル ユーザー インタフェースが初めて登場したのは Grid Control と Database Control においてです 基本的には 前述した GV$ のデータが集計されて Oracle Enterprise Manager の使い勝手の良いインタラクティブな画面に表示されます Oracle EM では SQL 監視データを 3 つの異なるビューで表示します 1 番目のもっとも包括的なビューは システム全体のビューを表示するものでもあり Performance ページの下部にあるリンクからアクセスできます 図 2:SQL Monitoring のリンク このリンクをクリックすると Monitored SQL Executions ページが開き そのシステ ム上で監視中または監視が終了したすべての SQL 文のリストが表示されます 図 3:Monitored SQL Executions ページこのページの各行は 監視中または監視が終了した SQL 実行のインスタンスを示します デフォルトでは 最初にもっとも新しい実行から順番に行が表に表示され 常に最初の数行には進行中の SQL 実行が表示されます このページに表示される情報は 各実行に関する重要なデータのみで SQL の ID とテキスト パラレル DOP などのグローバル情報と ステータス 開始時刻 終了時刻 継続時間 データベース 5 ページ

6 時間の内訳 IO 統計といった実行に関する統計です このページは定期的にリフレッシュされ 進行中の実行の統計が更新されたり 新しく監視が始まった実行のためのエントリが追加されたりします エントリをクリックすると Monitored SQL Executions Detail ページにドリルダウンし 特定の SQL 実行に関する追加情報が表示されます 選択した実行がまだ進行中の場合は 実行統計をリアルタイムで更新できます また 計画に含まれる操作のうち その時点で実行されているものを確認できます さらに SQL に関する多くの有益な情報を表示できます 表示できるのは データベース時間の内訳 CPU I/O の読取り / 書込みの内訳といった実行の SQL レベルの統計 問合せの並列度 (DOP) 実行の開始時刻および終了時刻などです 図 4:Monitored SQL Execution Details ページ 2 つ目のビューは SQL 文のさまざまなインスタンスの SQL 監視統計です このビューは SQL Details ページを使用して表示されます 新しい SQL Monitoring タブがこのページに導入されました このタブには その特定の SQL 文についてこれまでに監視が行われたすべての実行のリストが含まれます 6 ページ

7 図 5:SQL Details ページ 3 つ目のビューは 特定のセッションの SQL 監視統計です このビューは Session Details ページを使用して表示されます SQL Details ページと同様に Session Details ページにも新しい SQL Monitoring タブが追加されました このタブには 監視対象の SQL 文のうち そのセッションで実行されたものがすべて含まれます Session Details ページの Kill Session ボタンを使用すれば このページからセッション全体を強制終了させることができるため SQL 文が暴走してシステム リソースが独占され 他のすべてのユーザーに悪影響が出ているような状況をすばやく解決する手段として使用することもできます 図 6:Session Details ページ SQL 文に関する情報がさらに必要な場合は メインの Monitored SQL Executions ページから SQL Details ページと Session Details ページにアクセスすることもできます このように SQL Monitoring データの 3 つのビューはすべて Oracle EM 内で相互にリンクされています 7 ページ

8 SYSTEM SQL SESSION の順に SQL 監視のデータベース アクティビティを表示する方法は この機能で新しく追加されたものではありません これは Oracle Database 10g Release 1 以降の Oracle EM の GUI で使用されている主要な手順であって ここに SQL 監視が完全に統合されただけです MONITORED SQL EXECUTION DETAILS ページ ここでは Oracle Enterprise Manager で新たに使用できるようになったリアルタイム SQL 監視ユーザー インタフェースのさまざまなコンポーネントについて 複雑なパラレル問合せの詳しい例を使用して説明します この内容は データベース管理者の方に 監視対象の SQL 文に関する豊富なランタイム データを探索する方法を学んでいただくことを目的としています Monitored SQL Execution Details ページは Monitored SQL Executions ページから個々の SQL 文をドリルダウンすると表示されます 画面の最上部 ページ タイトルの横に このページで監視されている SQL 文のステータス アイコンがあります SQL 文のステータスを示すアイコンには Done Running Failed の 3 種類があります アイコンの上にマウスを移動すると アイコンの説明が表示されます 図 7: 監視対象の SQL 文のステータスまた SQL が失敗した場合は アイコン ツールチップを使用して Oracle エラー番号とエラー メッセージを表示できるようになっています この拡張機能は Oracle Database 11g Release 2 以降の SQL 監視に導入されています 次は SQL ID です SQL ID の上にマウス ポインタを置くと 実行中の問合せに対応した SQL テキストが表示されます 8 ページ

9 図 8: 監視対象の SQL 文の SQL テキスト本番稼働中のシステム上で SQL ID リンクをクリックすると SQL Details ページが表示され その SQL に関する履歴情報だけでなくリアルタイムの情報も表示されます SQL 文が長い場合や SQL 監視の UI からクリップボードに SQL テキストをコピーする場合は SQL ID の横にある情報アイコンをクリックします 図 9: 監視対象の SQL 文の SQL テキスト SQL Text ウィンドウに SQL テキストの全文と 実行に使用されたバインド変数に関する情報および変数の値が表示されます バインド情報は 別のタブで SQL Text ウィンドウに表示されます バインド変数に関する情報を知っておくことは重要です というのも SQL の条件の選択性がその値により変化する可能性があるためであり ひいては Oracle オプティマイザにより選択される実行計画と実行統計との両方に影響を与えることになるためです バインド変数を表示する拡張機能は Oracle Database 11g Release 2 以降の SQL 監視に導入されています 9 ページ

10 Overview ボックスの次の項目は "Parallel" です この項目は問合せの並列度を示します この例では 問合せの DOP が 16 になっています 図 10: 監視対象の SQL 文の並列度この特定の問合せは単一インスタンスの Oracle Database で実行されました そのため Parallel 行に 1 つの数字だけが表示されています 複数インスタンスをサポートする Oracle RAC データベースの場合は この問合せの処理に関与するインスタンスの数が DOP アイコンの横に表示されます 図 11: 監視対象の SQL 文の並列度 16 という並列度は この問合せの実行に割り当てられたパラレル サーバーが 16 台だけだということを必ずしも意味しません 実際には パラレル実行サーバー 1 組当たりに割り当てられたパラレル実行サーバーの数を示しています Oracle では 単純な問合せの処理には 1 組だけが割り当てられますが 複雑な問合せにはそれぞれ 2 組の DOP プロセスが割り当てられます 後者の場合 同じ DOP を使用する理由 ( つまり この例でいえば 32 ではなく 16 を使用する理由 ) は たとえ 32 台のパラレル実行サーバーが割り当てられているとしても 高速化が効率的に行われるのは 32 台ではなく最大 16 台だからです このような状態になるのは パラレル実行の通常のメカニズムによります つまり 2 組のパラレル サーバーがデータをパイプラインで交換するとき プロデューサまたはコンシューマのいずれかが順番でアクティブになるためです 前者の場合の例としては select * from table_name などの単純な問合せが考えられます この場合は 表スキャンに必要なパラレル サーバーは 1 組だけです 通常 1 つの操作を並列して実行するというレベルで並列化が発生します ( この例では 表スキャン ) 複雑な問合せの場合は 通常 1 つ以上のパイプラインが関与します たとえば 前述の単純な選択問合せの結果を使用して集計計算をするとします この場合は 全表スキャン操作で取得された行が GROUP BY 操作に割り当てられた 2 組目のパラレル サーバーにパイプラインで送信されます このようにして 1 組目 ( プロデューサ ) から送信されてきたデータが 2 組目 ( コンシューマ ) に再配布されます SQL 計画の図にマークで示した TABLE ACCESS 操作と GROUP BY 操作をご覧ください 赤い矢印は 2 組のパラレル サーバーの間で発生するデータ通信を表しています 10 ページ

11 図 12:SQL 実行計画の操作と SQL テキストとの関係 "Monitored SQL Execution Details" ページの下部にはいくつかのタブがあります デフォルトで表示される最初のタブには 監視されている文の実行計画に関する統計が行単位で表示されます 図 13: 監視対象の SQL 実行の計画操作最初のいくつかの列 (Operation Name Estimated Rows and Cost) には 実行計画に関するオプティマイザの情報が表示されます Name 列には 計画操作に関連付けられているオブジェクト名が表示されます たとえば TABLE ACCESS 操作には LINEITEM という名前の表が関連付けられています :TQ10000 のようなオブジェクト名は パラレル表のキューの名前を表しています 表のキューというのは パラレル問合せの実行期間中に作成される一時的なインメモリ オブジェクトで 2 組のパラレル サーバーの間 ( この例では :TQ10000) または 1 組のパラレル サーバーと問合せコーディネータの間 ( この例では :TQ10001) でパイプラインを使用して行を交換するメカニズムの基盤です Estimated Rows 列には 特定の計画操作の入力になると予測された行数が表示されます この数字は 文のコンパイル時に Oracle Optimizer が予測した値です 11 ページ

12 同様に Cost 列には 特定の操作から派生するサブツリー全体についてオプティマイザが予測したコストが表示されます コストは内部的な単位で表現されます Timeline から始まる残りの列には 計画の各行について SQL 監視で収集されたランタイム統計が表示されます また 問合せがパラレル実行された場合は 各操作を実行したパラレル エンティティの種類も示されます この情報は 操作名の左側の小さいアイコンで表示されます 表示されるアイコンの種類は 3 つあり 例にすべて表示されています 操作を実行しているパラレル問合せコーディネータは明るい緑色のアイコン 1 組目のパラレル サーバーは青色のアイコン 2 組目のパラレル サーバーはオレンジ色のアイコンで示されます この例では オレンジ色のアイコンで示されている 2 組目のパラレル サーバーが LINEITEM 表をパラレルでスキャンします この組に含まれる各サーバーが表のブロックのサブセットをスキャンし 選択した行のサブセットを事前集計します 事前集計が完了すると 事前集計された行は青色のアイコンで示される 1 組目のパラレル サーバーに送信され ここで最終的な集計がパラレル実行されます 行は GROUP BY キーの値に基づいて 2 組のサーバーの間でパイプラインを使用して再配布されます Timeline 列には 各計画操作について 問合せの実行の開始時刻を基準にした相対的な開始時刻と 実際の継続時間が表示されます 問合せの合計ランタイムは列のヘッダーに表示されます ( この例では 232 秒です ) マウス ポインタを Timeline のバーの上に置くと 相対的な開始時刻だけでなく 各操作の継続時間を数値で表した情報も確認できます 図 14:SQL 実行の計画操作のタイムライン計画の行は 問合せの実行の各段階でアクティブになったりそうでなくなったりします たとえば ネステッド ループ結合の右側の操作は複数回実行されるため SQL の実行中はビジー状態になったりアイドル状態になったりします Timeline 列には 初めて操作がアクティブになった時刻から最後にアクティブだった時刻までの アイドル期間を含む合計継続時間が表示されます 操作の実行が 1 秒未満の場合 タイムラインは表示されません Executions 列には 計画の各行が実行された回数が表示されます この例では PX COODINATOR という操作が 33 回実行されたのが分かります これは このパラレル実行に関与するすべてのプロセスが この操作を実行するためです 2 組のパラレル サーバーを使用しており 1 組は問合せコーディネータ プロセスで使用しているため パラレル問合せを実行しているプロセスの合計数は DOP 2 です 12 ページ

13 この例では DOP が 16 ですが ある特定の操作を実行しているプロセスは 33 です 計画に含まれる他の操作は 1 組または 2 組のサーバーを使用してパラレル実行されるため Executions 列には 16 という この問合せの DOP と同じ値が表示されます Table Access 操作は 1,296 回実行されているため ここでは例外です このような値になるのは パラレル サーバーが LINEITEM 表の異なるブロック範囲をスキャンするたびに PX BLOCK ITERATOR 操作によりこの操作が再起動されるためです 実際 ブロック範囲がスキャンされるたびに PX BLOCK ITERATOR 操作は次のブロック範囲をスキャンするようにパラレル コーディネータ プロセスから要求されます 次の範囲が取得されると その新しいブロック範囲に対してもう一度 TABLE ACCESS 操作が実行されます 簡単な計算により この TABLE ACCESS 操作の粒度が分かります 合計実行回数は 1,296 で そのすべてで 247GB のデータがスキャンされています したがって TABLE ACCESS 操作を 1 回実行するたびに 平均 200MB 弱のデータがスキャンされたことになります また 各パラレル実行サーバーが TABLE ACCESS 操作を実行した回数は 1, で 平均 81 回になります 表は サーバー プロセスの合計数をはるかに超える多数のブロック範囲に分割され スキャン操作に関与しているパラレル サーバーの間で動的にロードバランシングされます この仕組みがあるため 一部に他より処理の速いパラレル サーバーがあったとしても 各サーバーの処理はほぼ同時に終了します 図 15:Executions 列の数値 Actual Rows 列には 計画操作により実際に取得された行数が表示されます この行数には 対応する計画操作に関与したすべてのパラレル サーバーによる処理分が累積されています この数値は その操作のすべての実行についても累積されています Actual Rows 列と Estimated Rows 列の値は異なる可能性があり その理由としては次の 2 つが挙げられます 1 つは オプティマイザが作成するのは単なる予測であり 実際のランタイム値とは異なる可能性があることです もう 1 つの理由は オプティマイザが作成した予測は 1 回の実行を基準にしたものであって 累積されていない場合があることです これの良い例がネステッド ループ結合操作です この操作では 左側の行数と同じ回数だけ右側の操作が再実行されます オプティマイザは右側のツリーを 1 回だけ実行した場合の行数を予測しますが SQL 監視では累積された数が表示されます したがって 2 つの数値に差が出ます そのため 実際に行数と予測行数を比較することは 大きな差異のあるオプティマイザ予測を検出するのにとても役立ちます このような差異があると 最適ではない計画が選択される可能性があります 適切でない予測を修正するには 問合せでアクセスされる元のデータベース オブジェクトの統計が必ず最新の状態になっているようにします 13 ページ

14 その他の適切でない予測が原因で最適ではない実行計画をオプティマイザが選択しているとすれば 多くの場合 SQL Tuning Advisor を実行することで解決できます SQL Tuning Advisor は SQL プロファイルを推奨することで 最適ではない実行計画を正しくします 図 16: 予測行数と実際の行数の関係 この例で分かるように オプティマイザの予測に大きな差異はありません Memory (Max) 列には 特定の計画操作のすべての実行で使用されたメモリの最大量の合計が表示されます この例では HASH GROUP BY 操作で合計 13MB のメモリが消費されています (" 図 17: 最大メモリ列 " の赤いボックスで示している部分 ) 図 17: 最大メモリ列この操作は " 図 17: 最大メモリ列 " に表示されているように 16 回実行されています この例では メモリ内で集計が実行され 全体で 13MB が使用されました パラレル サーバー 1 台当たり 1MB 弱ということになります 計画の最初の方の段階でメモリが消費されていませんが それは 多くの操作が 1 度に 1 行ずつデータを処理しているためです したがって 操作に必要なランタイム メモリが非常に少なくなり SQL 監視にはレポートされません SQL 監視にレポートされるのは 作業領域で消費される PGA メモリのみであるため ハッシュ結合 ハッシュ グループ化 ソート ビットマップ マージ ビットマップ索引作成のみがレポートされます Memory (Max) 列は 問合せの実行中は単に "Memory" と呼ばれます この場合 この列には 特定の時点で操作が消費しているメモリ量が表示されます 問合せが完了すると 消費されたメモリの最大値が表示されます Temp (Max) 列には 一時領域に関連付けられている計画操作が消費した一時領域の量が表示されます 値が NULL 以外の場合は この操作によりディスク I/O が発生したことを示します pga_aggregate_target パラメータの値をチューニングすることで ディスクへの I/O を回避できる場合があります このパラメータのチューニング方法の詳細は Oracle のチューニング ガイドを参照してください Temp (Max) 列は 問合せの実行中は単に "Temp" と呼ばれます この場合 この列には 特定の時点で操作が消費している一時領域の量が表示されます 14 ページ

15 次の IO Requests 列には 計画操作当たりの読取り / 書込み要求数が表示されます この例では すべての IO は LINEITEM 表をスキャンする TABLE ACCESS 操作により実行されたことが分かります マウス カーソルを IO バーの上に置くと追加情報が表示されるようになっており 要求数と実際に読み取られたバイト数の他に IO 操作の平均サイズ ( この例では 863K バイト ) が表示されます 図 18:IO Requests 列コンテキスト メニューの一番上の項目を選択することで IO 要求数とバイト数を切り替えることもできます コンテキスト メニューを表示するには マウスを右クリックします Cell Offload Efficiency 列は Exadata ストレージを使用している場合は IO 効率係数と関係があります この例では 77% の改善が達成されていますが これは ディスクから読み取られた 247GB のデータのうち 58GB のみが IO インターコネクト経由で送信されているためです この値は 減少係数の 77% と対応しています この効率化が実現するのは 通常 射影と選択がオフロードされ Exadata ストレージ セルにより直接評価されることによります そのため データベース サーバーに戻されるのは関連性のある行と列だけになり IO 帯域幅が節約されます オフロードの最適化が実行されるのは 全表スキャンと索引高速完全スキャンについてのみです 図 19:Cell Offload Efficiency 列この列は Oracle Exadata V1 に対してワークロードが実行されている場合にのみレンダリングされます 次の CPU Activity % 列は 各操作が消費している CPU リソースの割合を示します この割合は GV$ACTIVE_SESSION_HISTORY( 略称 :ASH) を問い合わせ すべての CPU サンプルに対して各操作の CPU サンプルがどのくらいあるかをカウントして求めます この例では TABLE ACCESS が消費している CPU リソースは 4.65% のみで 残りのほとんどすべて (95%) を HASH GROUP BY が消費しています CPU Activity の位置にマウスを置くと CPU アクティビティに関する追加情報が表示されます 選択した操作について取得された CPU の ASH サンプルの数が表示されます パラレル問合せの場合は 各パラレル サーバーがアイドル待機状態ではないと想定して 毎秒サンプリングされる点に注意してください このような理由 15 ページ

16 により この例では 実行時間が 240 秒足らずの問合せによるサンプルが 2,912 件と大量になっています ASH の予測の精度は 対象の文について観察されたサンプルの合計数により異なりますが 合計サンプル数は合計実行時間に比例します したがって 実行時間が長い問合せの方が 計画行別リソース予測内訳の精度は高くなります また ASH に含まれる CPU サンプルは 操作が CPU 上にあったか CPU を待機している状態だった ( つまり OS スケジューラにより実行キューに配置されていた ) ことを意味している点にも注意してください 図 20:CPU Activity 列この例では ほとんどすべての CPU アクティビティが HASH GROUP BY 集計ステップで発生しています 最後の列 Wait Activity % は 問合せについての待機アクティビティの分布を示しています たとえば TABLE ACCESS 操作では Exadata セルからデータが送られてきてからでなければこのデータを処理できないため ユーザー I/O 待ちが発生します 図 21:Wait Activity 列この例では すべての待機アクティビティが表スキャン レベルで発生しているため まっさきに気付くのは この大きい待機アクティビティ バーのチューニングが必要だということです ただし 適切なところにチューニング作業を集中させるには 大局的に検討する必要があります ユーザー I/O 待機のチューニング作業に取りかかる前に CPU 待機とユーザー I/O 待機が互いにどのように測定されているかを把握しておく必要があります そのためには データベース時間の内訳を調査するのが正しいやり方です データベース時間の大半が CPU で発生している場合は CPU を最適化するのが理にかなっています 16 ページ

17 図 22:Monitored SQL Execution Details ページの Activity タブ Activity タブは Monitored SQL Execution Details ページの 3 つ目のタブで このタブにはアクティブ セッションと時間を対比させたグラフで ASH データが視覚的に表示されます ここには Plan Statistics タブで使われている色とまったく同じ色で表示されますが Waits などの色は異なるディメンションに沿って表示されます Activity タブは 問合せアクティビティが時間の経過とともにどのように分散するかを確認するのに役立ちます また パラレル問合せの場合は SQL が完全に並列化されなかった期間を探すのに役立ちます このタブを見て最初に気付くおもな点は CPU 時間が I/O 待機を大きく超えていることです したがって 集中的にチューニングする必要があるのは I/O ではなく もっとも重大な待機クラスです 検討する必要があるもう 1 つの重要な要素は Maximum CPU 線です この線は データベース インスタンスが稼働しているホスト上に存在する使用可能な CPU の数を示しています この例では 8CPU のシステムを使用しているため Maximum CPU 線は Active Sessions が 8 の位置に引かれています 明るい緑色の領域のうち この線より上にある領域では 処理に CPU リソースが使えるようになるまで待機する時間が発生します この問合せに対して 16 のパラレル プロセスが割り当てられていますが 同時に CPU を消費できるプロセスは 8 つのみで 残りの 8 つは CPU リソースが使用できるようになるまで待機します この例では 完全に CPU でボトルネックが発生しています そのため この問合せを高速化するには SQL 実行計画を変更するか CPU リソースを増やすしかありません これらの問題点と Plan Statistics タブとを照らし合わせることにより この問合せが費やしている時間の大半は 部分集計レベルの CPU アクティビティであるという結論が得られます 表スキャンで 17 億行が取得されますが 事前集計操作の結果は 54 行だけです この問合せはもともとリソース消費量が多く 17 億行を集計するのに大量の CPU リソースが使用されているため 実行速度を上げるには CPU の数を増やすか高速な CPU にすることが必要になると考えられます または マテリアライズド ビューを作成し 集計を事前に計算しておくこともできます この新しいアクセス パスを作成すれば 実行時間は目に見えて大幅に改善されるでしょう 図 23: 実際の行数と予測行数の関係 17 ページ

18 ここまでに説明したとおり インタラクティブなユーザー インタフェースには データベース管理者が問題のある SQL 文を分析するときに活用できる有益な情報が驚くほどたくさん含まれています SQL 監視のユースケースここでは 実行時にパフォーマンスの問題が発生した SQL 文の 5 種類のユースケースを紹介します を使用すれば これらのユースケースのいずれについても 問題の根本原因を見つけることができます 複雑な問合せ 大きな計画のもっとも一般的な適用例の 1 つが 非常に大きな計画を伴う SQL 文です SQL 実行計画が大規模になり 複雑すぎて解釈できなくなると どのように文が実行されるのか どこでリソースが消費されるのか おもに何がボトルネックなる可能性があるのかを理解する試みは極めて煩雑になり 手に負えないタスクになる可能性があります 大きな計画を伴う SQL 文の例について検討しましょう 図 24: 長い SQL の計画 18 ページ

19 " 図 24: 長い SQL の計画 " は SQL 文に対して Explain Plan を実行して潜在的な問題を検出する必要があるデータベース管理者にとっては難しいシナリオです 計画がこのように大きい場合は データベース管理者をきちんと支援する必要が出てくるのは明らかです を使用すれば 計画の中で重要な部分 つまりもっともリソース消費量が多い計画操作を確認できます " 図 24: 長い SQL の計画 " を見ると 重要であることを示す色の帯が横に表示されている計画操作はほんのわずかであることが確認できます 実行時間が費やされているのはこれらの操作であるため 特にこれらに注意する必要があります 視覚的にさらに分かりやすくするために 計画のいくつかのステップを 1 行にまとめることができます ( 図 25 の SORT AGGREGATION の操作を参照してください ) マウスの 1 回のクリックで " 図 24: 長い SQL の計画 " の状態から 図 25 に示すような必須の部分にまで 計画を縮小できました ここでは TABLE ACCESS FULL 操作が CPU リソースのほとんどを使用して ORDERS 表 LINEITEM 表の順に処理していることが分かります そのため これに続くチューニング作業では これらの全表スキャンを少ないリソースで高速に実行できるようにするか または SQL を修正するかアクセス構造を改善して全表スキャンが使用されないようにすることを集中して行います 図 25: 同じ大きな計画を SQL 監視でまとめたもの有効活用されていない索引 2 つ目のユースケースでは を使用して 索引が十分活用されていない事例を診断します この例は 一見したところ 索引がある場合はそれを使用することで実行計画を改善できるように見えます ところが SQL 文のランタイム統計を調べたところ 表アクセスを高速化するための索引の選択が違うところに現れています ( 図 26 参照 ) Monitored SQL Execution Details ページのリソース消費量の部分を見ると ほとんどの CPU リソース (55%) が INDEX RANGE SCAN 操作に消費されていることが分かります この操作をよく調べると 問合せで必要となるわずかなフィルタ処理に索引が使用されているだけであることが分かります つまり 88 万行が索引から取得され 選択されたのはそのうちわずか 6 万件です ここでデータベース管理者に気付いていただきたいのは この問合せがおもに CPU を消費していること そしてそのほとんどを INDEX RANGE SCAN 操作が消費していること それに フィル 19 ページ

20 タ処理のほとんどはランダム I/O によって表から行が取得された後に実行されてい るに過ぎないということです 図 26: 不十分な索引計画 図 27: 上図の問合せの中で索引を使用する計画操作の部分を拡大した図あまり時間をかけなくても 図 26 および図 27 の SQL 文の問題には オプティマイザによる索引活用計画が不十分である (" 選択的ではない索引 " を使用している ) ことが関係しているのを突き止めることができました 索引が実際にはこの問合せのパフォーマンスを低下させていることが分かると この SQL 問合せをチューニングする担当者は 計画の該当する部分に焦点を合わせてチューニング作業を行うことができます 部分的に並列化されているSQLの検出繰り返しになりますが SQL 監視の対象となっていたもっとも問題が大きい SQL 問合せは 長時間実行されている SQL 文で 2 番目はパラレル SQL 問合せです では パラレル問合せがどのように実行されるか 特に 問合せを実行しているパラレル サーバー間にどのようにワークロードが分散されるかを調べることができます 20 ページ

21 図 28: 部分的に並列化されている問合せ 図 29: 部分的に並列化されている問合せの拡大図図 28 の例は 2 つのインスタンス上で 8 つの DOP を使用して実行されている問合せを示しています 上から下に見ていくと 最初に問合せコーディネータ ( 明るい緑色のアイコン ) があり 次に 1 組のパラレル サーバー ( 青色のアイコン ) があり もう 1 組のパラレル サーバー ( オレンジ色のアイコン ) と交代でアクティブになります リソース消費量の部分を見ると この問合せが消費している CPU 全体の 82% は 計画に含まれる 1 つの操作が消費していることが分かります この操作は 4,002,000 行を戻した TABLE ACCESS FULL 操作で この操作は連続して実行されました この全表スキャンは パラレル実行されていればもっと短時間で完了したはずであるため 部分的に並列化された問合せの例であることがはっきりしています Monitored SQL Execution Details ページの Parallel タブ ( 図 30 および図 31 参照 ) は パラレル実行に関与したサーバーごとのリソース消費量を示しています パラレル サーバーの組ごとに集計した作業量を比較すれば パラレル問合せコーディネータがあとどれだけの作業を実行する必要があったのかを すぐに確認できます このような状況は明らかに異常です 問合せコーディネータは 作業のほとんどを実行する代わりに パラレル実行サーバー間のワークロード分散を調整する必要があるため 作業量はわずかになるはずです このような状況を検出したら SQL チューニングを担当するデータベース管理者は 問合せを完全に並列化するアクション プランを実施します 21 ページ

22 この例の場合は LINEITEM 表をパラレルに変更する (alter table LINEITEM parallel を実行する ) だけでかまいません 図 30: 部分的に並列化されている問合せの パラレル サーバー全体のリソース消費量 図 31: 部分的に並列化されている問合せの パラレル サーバー全体のリソース消費量の拡 大図 パラレルDMLの有効化次もパラレル実行の例です パラレル実行されると思っていた SQL 文が 実際にはほとんど順次実行になっており パラレル サーバーが 1 組だけと問合せコーディネータが 1 つ使用されている例です このユースケースでは 理由が何であれ 問合せの DOP は強制的に 4 になるため (FORCE PARALLEL QUERY PARALLEL 4) すべての文が 4 方向にパラレル実行されるはずです ところが LINEITEM 表に対する Insert 文は DML 文であり DML 文はデフォルトではパラレル実行されないため ロード操作がここでのボトルネックになります Plan Statistics タブ ( 図 32 参照 ) から調査すれば 作業がパラレル コーディネータとパラレル サーバー セットの間で均等に分散されていない様子を確認できます CPU アクティビティのほとんどは COUNT STOPKEY についての LOAD AS SELECT 22 ページ

23 操作と PX COORDINATOR 操作で発生しています 明らかなボトルネックを作りだ している問合せコーディネータが これら両方の操作を実行しているのです 図 32: 問合せの DOP が強制された DML 文次の図 33 および図 34 は パラレル コーディネータの作業量が圧倒的に多く Parallel Set 1 よりほぼ 3 倍多い作業を実行している様子を拡大して表示しているだけです チューニング作業は この DML 文がパラレル実行されるようにすることに集中する必要があることが この時点ではっきりします これは セッションのパラレル DML を有効化し (alter session enable parallel DML) LINEITEM 表が確実にパラレル化されるようにする (alter table lineitem parallel) だけで実現できます 図 33: 問合せの DOP が強制された DML 文の Parallel タブ 23 ページ

24 図 34: 問合せの DOP が強制された DML 文の Parallel タブの拡大図 PQサーバー グループ内の偏りの検出最後に紹介する例も パラレル問合せに関係しています 実際 SQL 監視ではこれまでにはなかった方法でパラレル SQL 文を実行中に監視できます そのため 旧リリースの Oracle Database で実行していた同等のタスクと比べて短時間でチューニング作業が完了します 図 35:PQ の偏りが進んだ問合せ 図 36:PQ の偏りが進んだ問合せの拡大図 24 ページ

25 計画の調査では 問合せがパラレル実行されたことを確認した後は 通常 Parallel タブに切り替えて 問合せの並列度に関するさまざまな側面についてさらに調査します ここで すべてのパラレル セットを展開すると 各セットに含まれるパラレル サーバー間のワークロードの分散状況を確認できます 平均すると 均等に分散されているように見えますが Prallel Set 1 を調べてみると Parallel Server 3 は Parallel Server 2 の 2 倍 Parallel Server 4 の 8 倍の作業量をこなしている様子が確認できます この作業の分散状況は均等ではありません もっと均等に分散されるようにチューニングする必要があります 図 37:Parallel タブ :PQ の偏りが進んだ問合せ 図 38:Parallel タブ :PQ の偏りが進んだ問合せの拡大図 ここでは Activity タブと Parallel タブをチューニング作業に使用する方法を説明します Activity タブのグラフでは システムがしばらくアイドル状態になったと思われる 2 カ所のすき間以外は アクティビティが均等になっているように見えます この問題を解決するには 2 つのことに注意する必要があります 1 つ目は Parallel タブと Plan Statistics タブを相互に関連付ける必要があることです 25 ページ

26 ユーザーはこのようにして問題を診断します Parallel タブでは 1 組のサーバー セットに偏りがあります つまり 1 つのサーバーが他の 3 つのサーバーより多くの作業を実行しています ( 図 37 参照 ) その組の色( 青色 ) を元に計画を見て 計画のどのステップがそのパラレル セットで実行されているかを見つける必要があります 2 つ目は 偏りが発生していたときのすき間は Activity タブから見つけられるということです 見つけたら 問合せの実行期間の中ですき間が発生した時点と Plan Statistics タブのタイムライン情報に基づいて 問題が発生した具体的な行ソースを見つけることができます この例では GROUP BY の 1 つの値がほとんどの行に含まれ GROUP BY キーの値が偏っているため GROUP BY レベルで偏りが発生します 特定の 1 つ 1 つの値を 1 つの実行サーバーだけで処理するため ほとんどの行をこのサーバーで受信して集計することになり この独特の偏りが発生します この問合せのチューニング作業は少し面倒です しかしながら データベース管理者が SQL 監視というツールを持っていなかったら このチューニング タスクはほとんど実行不可能でしょう このようなケースでは 問合せを記述し直して 集計処理も UNION ALL ビューに含めるようにする必要があるため 簡単な解決方法がなかなか見つからないかもしれません それでも SQL 監視を使用すれば 少なくとも問題の根本原因を突き止めることはできます 図 39:Activity タブ :PQ の偏りが進んだ問合せ 結論によってインタラクティブに公開されるランタイム統計は豊富にあります これらのデータは 実行時間の長い SQL 文やパラレル SQL 文の監視に使用できます 本書では この機能の中心概念を説明し Oracle Enterprise Manager から提供されるグラフィカル インタフェースを紹介し 重要かつ困難な SQL チューニング タスクの解決を を使用して支援できるユースケースをいくつか説明しました 読者の方々が Oracle Database 11g のこの素晴らしい新機能を使い始めるときに本書が役立てば幸いです 26 ページ

27 ライセンス情報 機能のライセンスは Oracle Tuning Pack に含まれています ( /options.htm - CIHFIHFG) 参考資料 [ 参考ドキュメント ] Oracle Databaseリファレンス 11gリリース 1(11.1) ( instance_tune.htm - CACGEEIF) 27 ページ

28 付録 A:SQL 監視のコマンドラインの使用方法 この付録では Oracle Enterprise Manager のユーザー インタフェースを使用できな いユーザーのために コマンドライン API を介して SQL 監視を使用する方法につ いて簡単に説明します Oracle EM の SQL 監視画面にあるすべてのインタラクティ ブ機能が使用できるわけではありませんが コマンドライン インタフェースを使用してもデータの抽出は実行できます SQL 監視レポートを生成するには DBMS_SQLTUNE パッケージに含まれる REPORT_ SQL_MONITOR ファクションを実行します variable my_rept CLOB; BEGIN :my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR(); END; / print :my_rept DBMS_SQLTUNE.REPORT_SQL_MONITOR ファンクションにいくつかの入力パラメータを渡して 実行 レポートの詳細レベル レポートのタイプ ('TEXT' 'HTML' または 'XML') を指定できます 例に示したように何もパラメータを指定しなければ 監視された最後の実行に関するテキスト レポートがデフォルトで生成されます 次に示すのは テキストベースの SQL 監視レポートのサンプルです 28 ページ

29 このサンプル レポートについて簡単に説明します このレポートの Global Information セクションにある Status フィールドは この文がまだ実行中であることを示しています Time Active(s) 列は この操作がアクティブ状態だった期間を示しており アクティブ状態だった最初と最後の時刻のデルタを秒単位で表しています Start Active 列は 実行計画の中でその操作が開始された時刻を SQL 文の実行開始時間と比較して秒単位で表示します このレポートでは ID5 の索引高速完全スキャンが 1 番目に開始され (Start Active の表示が "+1s") 最初の 82 秒間 実行されました Starts 列は 実行計画の当該操作が実行された回数を示します Rows (Actual) 列は取得された行数を示し Rows (Estim) 列はオプティマイザに見積もられたカーディナリティを示します Memory 列および Temp 列は 実行計画内の各操作で消費されたメモリ量と一時領域を示しています Activity (percent) 列および Activity Detail (sample #) 列は V$SQL_PLAN_MONITOR ビューと V$ACTIVE_SESSION_HISTORY ビューを結合して作成されます Activity (percent) は 実行計画の各操作で消費されたデータベース時間の割合を示します Activity Detail (sample#) は そのアクティビティの性質 (CPU 待機イベントなど) を示します このレポートでは データベース時間のほとんど (36.68%) を ID8 の操作 (ORDERS に対する TABLE ACCESS FULL) が消費していることがこの列で示されています このアクティビティは 73 のサンプル ( ) で構成されており その過半数がダイレクト パス読取りによるアクティビティで ( サンプル数 42) 約 3 分の 1 が CPU のアクティビティです ( サンプル数 28) 最後の Progress 列は V$SESSION_LONGOPS ビューから取得した操作の進捗監視情報を示しています このレポートでは ハッシュ結合操作が 87% 完了していることが示されています 29 ページ

30 2009 年 12 月著者 :Sergey Koltakov 共著者 :Benoit Dageville Peter Belknap Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. 海外からのお問い合わせ窓口 : 電話 : ファクシミリ : Copyright 2009, Oracle and/or its affiliates. All rights reserved. 本文書は情報提供のみを目的として提供されており ここに記載される内容は予告なく変更されることがあります 本文書は一切間違いがないことを保証するものではなく さらに 口述による明示または法律による黙示を問わず 特定の目的に対する商品性もしくは適合性についての黙示的な保証を含み いかなる他の保証や条件も提供するものではありません オラクル社は本文書に関するいかなる法的責任も明確に否認し 本文書によって直接的または間接的に確立される契約義務はないものとします 本文書はオラクル社の書面による許可を前もって得ることなく いかなる目的のためにも 電子または印刷を含むいかなる形式や手段によっても再作成または送信することはできません Oracle は米国 Oracle Corporation およびその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標です 0408