Oracle Database 11g： リアルタイムSQL監視



Oracle Database 11gで導入されたリアルタイムSQL監視機能を利用すると、大量のリソースを消費する長時間実行SQL文やパラレルSQL文に起因する実行時のパフォーマンス問題を極めて効果的に特定できます。 Oracle Enterprise Managerのインタラクティブな画面には、新しいファイングレインSQL統計機能を使用して収集されたSQL実行の詳細データが表示されます。この統計情報は、一切の設定を追加することなく収集され、本番システムのパフォーマンスに影響を与えることもありません。 統計情報は、経過時間、CPU時間、読取りと書込みの回数、I/O待機時間、その他の各種待機時間などの主要なパフォーマンス指標ごとに、実行計画の各ステップで追跡されます。 この情報を利用することで、DBAは今までよりずっと詳しくSQL実行を分析し、監視したSQL文に対して最適なチューニング戦略を決定できます。

SQL監視アクティブ・レポートは、Oracle Enterprise ManagerのUIテクノロジーを使用した新しいタイプのインタラクティブ・レポートであり、OTNにホストされています。 その他のアクティブ・レポートとして、 SQL Performance Analyzerと SQL Detailsも提供されています。



アクティブ・レポートの例

シンプルなハッシュ結合（ビルド中）
シンプルなハッシュ結合（プローブ中）
シンプルなハッシュ結合（完了）
ライト・ディープ・ハッシュ結合
Create IndexのDDL
Create Table as Selectの失敗
パラレル問合せのダウングレード
PL/SQLを使用したSQL実行
バインドを使用したSQL

よくある質問

回答

リアルタイムSQL監視のアクティブ・レポートを生成する方法を教えてください。

SQL監視アクティブ・レポートは、Oracle Enterprise ManagerのライブUIで詳細なSQL監視レポートを表示している間に直接生成できます。 ページ右上にあるsaveボタンまたはsend e-mailボタンを使用すると、SQL監視詳細ページをアクティブ・レポートとして保存したり、電子メールで送信したりすることができます。 または、PL/SQLプロシージャであるdbms_sqltune.report_sql_monitor()のレポート・タイプに"active"を指定して起動することで、コマンドラインから直接アクティブ・レポートを生成することもできます。 次のSQL*Plusスクリプトの例は、Oracleが最後に監視した文に対してアクティブ・レポートを生成する方法を示しています。

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off

出力されたsqlmon_active.htmlファイルからヘッダー（ファイルの1行目）と最終行（spool off）を削除すると、結果のhtmlファイルを任意のブラウザで表示できます。 アクティブ・レポート・コードをロードするため、このブラウザからOTNへの接続を確保する必要があります。

 

実行中のSQLに対してアクティブ・レポートを生成できますか。

はい、できます。アクティブ・レポートには、レポート生成時における問合せ実行のスナップショットが表示されます。 たとえば、次のアクティブ・レポートには、実行中のシンプルなハッシュ結合問合せが表示されています（アクティブ・レポートの表示は こちら）。 "EXECUTING"ステータス（最上部の緑色のアイコンを参照）によって、レポート生成時にSQL文が実行中であったことが示されています。 また、計画統計タブで左側に緑色の矢印が表示されている計画ステップは、レポート生成時にアクティブであったことを表します。 この例では、ハッシュ結合とそのビルド入力（顧客表の全表スキャン）がアクティブになっています。 また、Oracle Enterprise ManagerのライブUIと同様に、顧客表のスキャンは55%完了しており、残りはわずか33秒（関連するツールチップを参照）であることが分かります。

 

次のSQL監視アクティブ・レポートでアクティビティ・タブを選択すると、CPUアクティビティがCPUの最大数を超えています。これはなぜですか（アクティブ・レポートの表示は こちら）。

このSQL文は、32ウェイのOracle RACクラスタ上でパラレル実行されています。 使用されている並列度（DOP）はデフォルトであり、64（CPU数の2倍）となります。 この文は非常に多くのCPUを消費するため、PXサーバーの大部分はCPUに費やされ、待機イベントの待機はほとんど発生しません。 64のPXサーバーに対してCPUは32個しかないため、PXサーバーはCPUを使用しているか、またはOSスケジューラによるスケジューリングを待機している状態（実行キュー内）にあります。 GV$ACTIVE_SESSION_HISTORYから取得したアクティビティがCPUになっていても、実際はCPU使用中のケースとCPU待機中のケースの両方が含まれます。

 

上記と同じアクティブ・レポートにおいて、データベース時間（概要セクションの"時間と待機の統計"を参照）のうち"その他"の時間が高い割合（16時間33分の総データベース時間に対して、7時間17分）を示しています。"その他"のデータベース時間とは何ですか（アクティブ・レポートの表示は こちら）。

その他のデータベース時間とは、実行キュー時間（システムがCPUの限界に達している場合にプロセスがCPU使用を待機する時間）または待機クラスが"その他"であるイベントを待機する時間です。 待機アクティビティの棒グラフにその他の待機クラスを持つイベントの待機は含まれておらず、またデータベース時間の半分はCPUリソースの待機である（質問1の回答を参照）ことがアクティビティ・グラフから明らかに分かることから、ここでの回答は前者（実行キュー時間）になります。

 

計画統計タブには"IOリクエスト"という列があり、各処理で実行されたI/O操作の数が表示されています。たとえば、全表スキャン処理では836,020のI/Oが実施されています。DSS問合せでは、IOリクエスト数ではなくIOバイト数を表示した方が良い場合がありますが、バイト数を表示することは可能ですか（アクティブ・レポートの表示は こちら）。

はい、バイト数を表示できます。 まず、棒グラフのツールチップにはバイト数とリクエスト数の両方が表示されます。 たとえば、スキャン処理のツールチップには457GBの表示があります。 また、ツールチップには平均I/Oサイズも表示されており、ここでは約1/2メガバイトです。 さらに、計画統計表に関連付けられたコンテキスト・メニューを使用して（表内の任意の行を右クリックし、"Toggle IO Bytes"を選択）、IOリクエスト数とIOバイト数の表示を切り替えることもできます。

 

期間がわずか16分であるのに、なぜデータベース時間に16.6時間（アクティブ・レポートの表示は こちら）もかかっているのですか。

これは、問合せがパラレル実行されているためです。 SQL監視レポートの概要セクションには、すべてのPXサーバーと問合せコーディネータ間で累積された合計データベース時間が表示されます。 パラレル・タブには、このデータベース時間に対するプロセスごとの内訳が表示されています（プロセス・レベルのデータベース時間を表示するには、ツリー・ビューをすべて展開する必要があります）。 ここでは、データベース時間が期間に非常に近い値を示しています。 実際、データベース時間と期間の比率を使用して、並列度と比較したパラレル問合せの効率を算出することができます。 たとえば、この例ではデータベース時間が59,589秒であり、期間は959秒です。 この2つの値の比率は62であり、並列度の64に極めて近い値になっています。 言い換えると、この問合せをパラレル実行すると、同じ実行計画をシリアル実行するよりも62倍速く実行できます。 問合せをシリアル実行していたら、期間は62時間を超えていたでしょう。

 

この例のようにSQL文をパラレル実行すると、計画統計表の各操作の左側にアイコンが表示されます。このアイコンは緑色の1人の人物か青色またはオレンジ色の複数の人物を表しており、同じアイコンがパラレル・タブにも表示されます。このアイコンは何を意味していますか（アクティブ・レポートの表示は こちら）。

このアイコンは、実行計画のどの処理でどの種類のプロセスが実行されたかを理解する際に役立ちます。 それぞれのアイコンは、パラレル処理の種類を記号化したものです。 緑色のアイコンは問合せコーディネータを表しており、理想的には実行計画の"PX COORDINATOR"ステップのみを実行します（計画のルート・ノードはSQL文の種類を表しているため、ここでは考慮に入れません）。 残りの2つのアイコンはパラレル実行サーバーのセットを表します。 青色のアイコンは第1セットを表し、オレンジ色のアイコンは第2セットを表します。第2セットは、実行計画でパラレル・パイプラインが必要とされる場合にのみ割り当てられます。 実際、Oracleのパラレル実行モデルでは、1つのセット（第1セットまたは第2セット）に含まれるすべてのPXサーバー・プロセスが1つの実行計画に割り当てられるため、この計画に含まれる各処理のパラレル実行が可能になります。 これは、イントラ・オペレーション並列化と呼ばれます。 さらに、複数のパラレル実行サーバー・セットを使用して、計画内の2つの処理を同時に実行することもできます。この場合、データは1つの処理から別の処理へと流れます。 これは、インター・オペレーション並列化と呼ばれます。

 

このアクティブ・レポートでは並列度が5として表示されていますが（概要セクションの"パラレル"ラベルのついたアイコンを参照）、パラレル・タブを確認すると、インスタンス2に10（セットごとに5つ）のプロセスが割り当てられています。この場合、なぜ並列度は10ではなく5なのですか（アクティブ・レポートの表示は こちら）。

並列度は、イントラ・オペレーション並列化における並列度を表します。 したがって、並列度は1つのセットに含まれるPXサーバー・プロセスの数として定義されており（2つのセットには常に同じ数のプロセスが含まれます）、この例では5になります。 この理由は、パラレル実行による高速化を実現するのはイントラ・オペレーション並列化であるためです。インター・オペレーション並列化は中間的な結果セットが余分にマテリアライズされることを回避するために使用されています。 このため、ここでの高速化は最大でも5倍であり、10倍にはなりません。

 

リアルタイムSQL監視を使用して無名PL/SQLブロックを監視できますか。

はい、できます。RDBMS 11.2以降ではすべてのカーソル実行が監視可能であり、PL/SQLコールや無名ブロックを表すカーソルもこれに含まれています。 次のアクティブ・レポート（アクティブ・レポートの参照は こちら）を例として参照してください。 その他のSQL文と同様に、レポートの概要セクションには実行に関する一般情報と統計情報が表示されています。 詳細セクションに表示されているのは、アクティビティ・タブのみです。 このタブには、PL/SQLカーソルが実行された際のセッション・アクティビティが表示されています。 監視対象のカーソルがアクティブになると、アクティビティはその種類（例：CPU、イベントXYZの待機）ごとにラベル付けされます。 しかし、監視されている文とは別の文がアクティブになると、このアクティビティのラベルには実行された文のSQL IDが設定されます。 この例では、PL/SQLの最初のフェーズはCPU（約20秒）であり、その後に4つの再帰的SQL文が実行されていることが分かります。 それぞれのSQL IDは順に、56ppx29nq4jzj、gqm4ga8tnja1r、gh9szbs0nd9ut、そして最後に2xjt9pa9hjc6aとなります。 最後の文（SQL ID：2xjt9pa9hjc6a）にもっとも多くの実行時間が費やされており、問合せ時間の半分以上を占めています。 アクティブ・レポートではなくOracle Enterprise ManagerのライブUIを使用している場合、これらのSQL IDのいずれかをクリックすると、SQLの詳細にドリルダウンして、SQLのテキストや監視情報（実行が監視されている場合）などSQL文に関する詳しい情報を入手できます。

 

このアクティブ・レポートでは、並列度を示すアイコンに下向きの赤い矢印が表示されています。これはどういう意味ですか（アクティブ・レポートの表示は こちら）。

この矢印は、並列度がダウングレードされたことを表します。言い換えると、リクエストされた数よりも少ない数のパラレル実行サーバーがSQL文の実行に割り当てられたことを示します。 パラレル・アイコンのツールチップには、ダウングレードに関してさらに詳しい情報が表示されます。 この例では、並列度が60%ダウングレードされています。リクエストされた10という数に対して、割り当てられたPXサーバーの数はわずか4です。 パラレル実行サーバーの数が"parallel_max_servers"で指定された制限に達した場合や、パラメータ"parallel_adaptive_multi_user"にtrueが設定されており、サーバー負荷が過剰に高い場合、並列度はダウングレードされる可能性があります。

 

監視されているSQL文でエラーが発生した場合はどうなりますか。

エラー番号とエラー・メッセージが保存され、実行ステータスがエラー（赤色のXアイコンによって示されます）に設定されます。 次のアクティブ・レポート（アクティブ・レポートの参照は こちら）を例として参照してください。 また、エラーが発生した時点までに累積されたすべての統計情報は保持されます。 この例では、700万行を挿入した後でINSERT文が失敗しています。

 

クロス・インスタンス実行の場合、パラレル・アイコンには並列度とともに使用されたインスタンスの数が任意で表示されます。 このアクティブ・レポートのパラレル・アイコンのように、使用されたインスタンス数に1が表示されている場合、何を意味しますか。SQL文がクロス・インスタンスで実行されているのに、インスタンス数が1であるのはなぜですか（アクティブ・レポートの表示は こちら）。

問合せコーディネータがリモート・インスタンスから1つ以上のPXサーバーを割り当てた場合、SQL文はクロス・インスタンス実行されます。 レポートに表示されるインスタンス数は、PXサーバーの割当てに使用されたインスタンスの総数です。 パラレル・タブから分かるとおり、問合せコーディネータがインスタンス1で実行され、すべてのPXサーバーはインスタンス2に割り当てられています。このように、パラレル問合せ自体はクロス・インスタンスであっても、すべてのPXサーバーは1つのインスタンスで実行されているため、問合せをパラレル化するために使用されたインスタンス数は1になります（コーディネータは数に含めません）。

 

監視対象のSQL文のバインド変数の値を特定できますか。

はい、できます。 このアクティブ・レポート（アクティブ・レポートの表示は こちら）のSQL文には、バインド変数の値が含まれています。 バインド変数を表示するには、SQL IDの横にある円形の"i"アイコンをクリックします。SQLテキストを表示したウィンドウが開き、SQL文にバインド変数が含まれる場合、そのタイプ、位置、値も一緒に表示されます。