21 データベース操作の監視
この章では、SQLおよびPL/SQLを監視する方法について説明します。
21.1 データベース操作の監視について
SQL監視機能は、STATISTICS_LEVEL初期化パラメータがTYPICAL(デフォルト値)またはALLのいずれかに設定されている場合に、デフォルトで有効になります。
関連項目:
データベース操作の概要は、『Oracle Database概要』を参照してください
21.1.1 データベース操作について
データベース操作とは、一連のデータベース・タスクのことです。一般的なタスクとして、バッチ・ジョブや、抽出、変換およびロード(ETL)処理を行うジョブなどがあります。
データベース操作は単純または複合のいずれかです。
単純データベース操作
単一のSQL文、あるいは単一のPL/SQLサブプログラムはシンプル・データベースの操作です。SQL監視機能が有効な場合は、次のいずれかの条件が満たされるときに、データベースは単純データベース操作を自動的に監視します。
-
SQL文やPL/SQLのサブプログラムが、CPUまたはI/O時間を1回の実行で5秒以上消費した。
-
SQL文がパラレルで実行される。
-
SQL文で
/*+ MONITOR */ヒントを指定している。 -
イベント
sql_monitorで監視対象の文のSQL IDのリストを指定している。たとえば、次の文では、SQL ID5hc07qvt8v737と9ht3ba3arrzt3に対してインスタンス・レベルの監視を強制しています。ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true'
SQL実行計画の各ステップで、データベースはパフォーマンス・メトリック(経過時間、CPU時間、読取りと書込みの数、I/O待機時間など)による統計を追跡します。これらのメトリックは、SQL監視アクティブ・レポートというグラフィカルな対話モード・レポートで使用できます。
複合データベース操作
複合データベース操作は、ユーザーが定義します。これには、プロシージャDBMS_SQL_MONITOR.BEGIN_OPERATIONおよびDBMS_SQL_MONITOR.END_OPERATIONを使用して開始ポイントと終了ポイントが定義されたデータベース・セッションで実行される、すべてのSQL文またはPL/SQLサブプログラムが含まれています。複合データベース操作は、名前と実行IDによって一意に識別され、複数回実行できます。
ノート:
1つのデータベース・セッションは、一度に1つのデータベース操作にのみ関与できます。
Oracle Databaseは、次の条件のいずれかが満たされた場合に、複合操作を自動的に監視します。
-
操作で、5秒以上のCPU時間またはI/O時間が消費された。
-
DBMS_SQL_MONITOR.BEGIN_OPERATIONのFORCED_TRACKINGをYに設定することで操作のトラッキングが強制されている。
ノート:
SQLモニターの概要は、「SQLモニターを最大活用する方法」を参照
21.1.2 データベース操作の監視の目的
単純な操作については、文で時間がかかっている部分を判断する際にリアルタイムSQL監視が役立ちます。
また、完了したばかりの文の時間やリソース使用率の内訳を確認できます。このようにして、特定の操作でコストが高くなる理由をより適切に特定できます。リアルタイムSQL監視のユースケースには次が含まれます。
-
頻繁に実行されるSQL文の実行速度が通常より遅い場合。この問題の根本原因を特定する必要があります。
-
データベース・セッションのパフォーマンスが低下する場合。
-
パラレルSQL文に時間がかかる場合。サーバー・プロセスで作業がどのように分割されているのかを特定する必要があります。
OLTPおよびデータ・ウェアハウス環境では、ジョブによって関連するSQL文が論理的にグループ化されることが多くあります。ジョブは、複数同時セッションに及ぶことが可能です。データベース操作は、一連の文またはプロシージャを名前付きの一意に識別される再実行可能な単位として処理できるようにすることにより、リアルタイムSQL監視を拡張します。操作のユースケースには次が含まれます。
-
多数のSQL文を含む定期的なバッチ・ジョブが一定の時間内に完了する必要があるが、予想より時間がかかった。
-
データベースのアップグレード後に、重要なバッチ・ジョブの実行時間が増大した。この問題を解決するには、アップグレードの前後にバッチ・ジョブから十分な関連統計データを収集し、2つのデータ・セットを比較した上で、変更を特定する必要があります。
-
SQLチューニング・セット(STS)のパックに予想よりはるかに長く時間がかかった場合。この問題を診断するには、時間の経過ごとに何が実行されていたのかを把握する必要があります。この問題は簡単に再現できないため、プロセスを実行中に監視する必要があります。
関連項目:
SQLモニターの使用例を説明するビデオは、「SQLモニターを使用する理由」を参照
21.1.3 データベース監視の仕組み
リアルタイムSQL監視は、処理時間が長いSQL文やパラレルSQL文のパフォーマンスの問題を識別するのに役立つ、組込みデータベース・インフラストラクチャです。
次の図は、リアルタイムSQL監視のアーキテクチャの概要を示しています。
前の図に示されているように、DBMS_SQL_MONITORパッケージは、データベース操作を定義します。監視が開始されると、データベースは、データベース操作に関するメタデータをAWRに格納し、そのデータをAWRとASHの両方に格納します。データベースによる監視統計のリフレッシュは、監視対象の文の実行とほぼリアルタイムで実行されます(通常は1秒に一度)。データベースは、操作データ(文とメタデータ)をSGAに格納します。操作が完了すると、データベースはSQL監視レポートをディスクに書き込みます。このレポートは、DBA_HIST_REPORTSビューを使用して問い合せることができます。
V$SQL_MONITORビューには、監視対象のすべてのデータベース操作のエントリがあります。このエントリでは、実行のために収集される主要なパフォーマンス・メトリック(経過時間、CPU時間、読取り/書込み数、I/O待機時間、およびその他の様々な待機時間など)が追跡されます。V$SQL_PLAN_MONITORビューには、監視対象のSQL文の実行計画における各操作の監視統計が含まれています。DBMS_SQL_MONITOR.REPORT_SQL_MONITORを使用してレポートにアクセスできます。これには、Oracle Enterprise Manager Cloud Control (Cloud Control)またはEM Expressインタフェースが含まれます。
関連項目:
-
V$SQL_MONITOR、V$SQL_PLAN_MONITORおよびCONTROL_MANAGEMENT_PACK_ACCESSについて学習するには、『Oracle Databaseリファレンス』を参照してください -
DBMS_SQL_MONITORパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
21.1.4 データベース操作監視のユーザー・インタフェース
リアルタイムSQL監視は、Oracle Database Tuning Packの機能の1つです。CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータがDIAGNOSTIC+TUNING(デフォルト)に設定されている場合、データベース操作を使用できます。
21.1.4.1 Cloud Controlの「監視されたSQL実行」ページ
Cloud Controlの「モニターされたSQL実行」ページで、(「SQLモニター」とも呼ばれる)に、SQL実行の詳細が表示されます。SQL監視は、データベース操作に関するレポートを作成するための推奨インタフェースです。
実行計画の各ステップの統計は、主要なパフォーマンス・メトリック(経過時間、CPU時間、読取り/書込み数、I/O待機時間、その他の様々な待機時間など)によって追跡されます。これらのメトリックにより、DBAがSQL実行を徹底的に分析して監視対象のSQL文の最適なチューニング戦略を決定することが可能になります。
SQLモニターのアクティブ・レポートは、HTMLファイルにデータを保存できるフラッシュ・ベースの対話モード・レポートを提供します。このファイルを保存してオフラインで表示できます。
21.1.4.1.1 「監視されたSQL実行」ページへのアクセス
「監視されたSQL実行」には、SQL ID、データベース時間およびI/Oリクエストなどの情報が示されます。
「監視されたSQL実行」ページにアクセスするには:
-
適切な資格証明を使用してCloud Controlにログインします。
-
「ターゲット」メニューの下で、「データベース」を選択します。
-
データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。
-
データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。
-
「パフォーマンス」メニューから、「SQL監視」を選択します。
「監視されたSQL実行」ページが表示されます。
21.1.4.2 DBMS_SQL_MONITORパッケージ
DBMS_SQL_MONITORパッケージでは、複合データベース操作の開始と終了を定義して、データベース操作のレポートを生成します。
表21-1 DBMS_SQL_MONITOR
| サブプログラム | 説明 |
|---|---|
|
|
このファンクションは、現在のセッションでデータベース操作を開始します。 このファンクションでは、セッションがデータベース操作と関連付けられます。Oracle Database 12cリリース2 (12.2)からは、 |
|
|
このファンクションは、現在のセッションでデータベース操作を終了します。指定のデータベース操作が存在しない場合、このファンクションの効果はありません。 |
|
|
このファンクションは、SQL文、PL/SQLブロックまたはデータベース操作の監視情報によって詳細なレポートを作成します。 各操作には、キー情報および関連付けられているグローバルな統計情報が指定されます。このファンクションを使用して、データベース操作に関する詳細な監視情報を取得します。 このレポートで対象となるデータベース操作は次になります。
|
|
|
このファンクションは |
|
|
このファンクションは、Oracle Databaseによって監視されたすべてのデータベース操作またはデータベース操作のサブセットに関するレポートを作成します。 |
|
|
このファンクションは |
関連項目:
DBMS_SQL_MONITORパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
21.1.4.3 複合データベース操作の属性
DBMS_SQL_MONITOR.BEGIN_OPERATIONファンクションは、データベース操作を定義します。
複合データベース操作は次の情報によって一意に識別されます。
-
データベース操作名
これは、
daily_sales_reportなど、ユーザーが作成する名前です。1つのジョブが異なるセッションや異なるデータベースで同時に実行される場合でも、操作名は同じです。データベース操作名は異なる名前空間には存在しません。 -
データベース操作実行ID
複数の同じデータベース操作は、名前が同じでも実行IDが異なっていれば、同時に実行可能です。この数値IDによって同じデータベース操作の異なる実行が一意に識別されます。
データベース操作を開始すると、データベースによって、自動的に実行IDが作成されます。また、ユーザーが作成した実行IDも指定できます。
オプションで、データベース操作を開始するセッションIDおよびセッション・シリアル番号を指定できます。これにより、あるデータベース・セッションで、異なるデータベース・セッションで定義されたデータベース操作を開始できます。
データベースでは、V$SQL_MONITORビューで監視対象のSQL文およびPL/SQL文をそれぞれ識別するために次の3つの値が使用されます。文がデータベース操作に含まれているかどうかは関係ありません。
-
SQL文を識別するためのSQL識別子(
SQL_ID) -
実行開始タイムスタンプ(
SQL_EXEC_START) -
この主キーが一意であることを保証する内部生成識別子(
SQL_EXEC_ID)
ゼロ(0)または複数の追加属性を使用することで、複合データベース操作の特性を説明および特定できます。どの属性にも名前と値があります。たとえば、データベース操作daily_sales_reportの場合は、db_name属性を定義して、その属性に値prodを割り当てることができます。
21.1.4.4 MONITORヒントとNO_MONITORヒント
MONITORヒントとNO_MONITORヒントを使用すると、文の追跡を個別に制御できます。
MONITORヒントは、文の実行時間が長くない場合でも、問合せのリアルタイムSQL監視を強制します。このヒントは、パラメータCONTROL_MANAGEMENT_PACK_ACCESSがDIAGNOSTIC+TUNINGに設定されている場合にのみ有効です。次の問合せでは、SQL監視の追跡を強制的に有効にします。
SELECT /*+ MONITOR */ prod_id, AVG(amount_sold), AVG(quantity_sold)
FROM sales
GROUP BY prod_id
ORDER BY prod_id;
NO_MONITORヒントは、問合せの実行時間が長い場合でも、問合せのSQLのリアルタイム監視を無効にします。次の問合せでは、SQL監視の追跡を強制的に無効にします。
SELECT /*+ NO_MONITOR */ prod_id, AVG(amount_sold), AVG(quantity_sold)
FROM sales
GROUP BY prod_id
ORDER BY prod_id;
ノート:
MONITORヒントとNO_MONITORヒントについて学習するには、『Oracle Database SQL言語リファレンス』を参照
21.1.4.5 データベース操作の監視およびレポート作成用のビュー
V$およびデータ・ディクショナリ・ビューを使用して、データベース操作の統計を取得できます。
次の表は、それらのビューをまとめたものです。
表21-2 データベース操作監視のビュー
| ビュー | 説明 |
|---|---|
|
このビューは、自動ワークロード・リポジトリ(AWR)に取得されたXMLレポートに関するメタデータを表示します。各XMLレポートには、コンポーネントのアクティビティの詳細が含まれます。たとえば、SQLモニターのレポートには、特定のデータベース操作の詳細なレポートが含まれます。 重要な列には次のものがあります。
AWRは、SQL監視レポートの保存期間を制御します。 |
|
|
このビューは、AWR内に取得された各レポートの詳細を示します。各レポートのメタデータは |
|
|
このビューには、単純データベース操作および複合データベース操作に関するグローバルな上位レベルの情報が含まれます。 単純データベース操作の場合、監視の統計は複数の実行にわたる累積的なものではありません。この場合、 単純データベース操作の場合、 複合データベース操作の場合、操作の一部として同一セッションで実行される複数のSQL文およびPL/SQLサブプログラムにわたって統計が蓄積される操作が、各行に含まれます。主キーは、 |
|
|
このビューには、データベース操作に関連するすべてのセッションの統計が含まれています。 統計のほとんどは累積されます。データベースでは、その統計がXML形式で格納され、各統計に各列が使用されることはありません。このビューは、主にレポートの生成を目的としています。ここでは、 |
|
|
このビューには、監視対象のSQL文の実行計画における各ステップの監視統計が含まれています。 SQL文が実行されている間、データベースでは |
前述のV$ビューと次のビューを一緒に使用して、監視対象の実行に関する追加情報を取得できます。
-
V$ACTIVE_SESSION_HISTORY -
V$SESSION -
V$SESSION_LONGOPS -
V$SQL -
V$SQL_PLAN
関連項目:
データベース操作監視のV$ビューについて学習するには、Oracle Databaseリファレンスを参照してください
21.2 データベース操作監視の有効化および無効化
監視の有効化または無効化には、初期化パラメータを使用します。
21.2.1 システム・レベルでのデータベース操作監視の有効化
SQL監視機能は、STATISTICS_LEVEL初期化パラメータがTYPICAL(デフォルト値)またはALLのいずれかに設定されている場合に、デフォルトで有効になります。SQL監視は、長時間実行されるすべての問合せに対して自動的に開始されます。
前提条件
SQL監視はOracle Database Tuning Packの機能であるため、CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータをDIAGNOSTIC+TUNING(デフォルト値)に設定する必要があります。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
STATISTICS_LEVEL初期化パラメータがBASICに設定されています。 -
データベース操作の自動監視を有効にします。
データベース操作の監視を有効化するには:
-
適切な権限でSQL*Plusをデータベースに接続し、現行のデータベース操作の設定を問い合せます。
たとえば、次のSQL*Plusコマンドを実行します。
SQL> SHOW PARAMETER statistics_level NAME TYPE VALUE ----------------------------------- ----------- ----- statistics_level string BASIC -
統計レベルを
TYPICALに設定します。たとえば、次のSQL文を実行します。
SQL> ALTER SYSTEM SET STATISTICS_LEVEL='TYPICAL';
関連項目:
STATISTICS_LEVELおよびCONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください
21.2.2 文レベルでのデータベース操作監視の有効化および無効化
CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータをDIAGNOSTIC+TUNINGに設定すると、ヒントを使用して特定のSQL文の監視を有効化または無効化できます。
1回の実行でCPU時間またはI/O時間を最低5秒消費したとき、データベースはSQL文またはPL/SQLサブプログラムを自動的に監視します。MONITORヒントは、時間基準を満たさない文またはサブプログラムの監視を強制するのに役立ちます。
データベースによるSQL文の監視を強制または回避するには、2つの文レベルのヒントが使用できます。SQL監視を強制するには、次のようにMONITORヒントを使用します。
SELECT /*+ MONITOR */ SYSDATE FROM DUAL;
このヒントは、CONTROL_MANAGEMENT_PACK_ACCESSパラメータがDIAGNOSTIC+TUNINGに設定されている場合にのみ有効です。監視対象のヒント付きSQL文を監視から除外するには、NO_MONITOR逆ヒントを使用します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
現在、データベース監視がシステム・レベルで有効化されています。
-
SELECT * FROM sales ORDER BY time_id文の自動監視を無効にします。
SQL文のデータベース操作の監視を無効化するには:
-
NO_MONITORヒントを使用して問合せを実行します。たとえば、次の文を実行します。
SQL> SELECT * /*+NO_MONITOR*/ FROM sales ORDER BY time_id;
関連項目:
MONITORおよびNO_MONITORヒントの使用方法の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
21.3 複合データベース操作の定義
データベース操作を定義する際には、その操作に名前をつけて開始時間と終了時間を指定します。
DBMS_SQL_MONITOR.BEGIN_OPERATIONファンクションを使用してデータベース操作を開始し、DBMS_SQL_MONITOR.END_OPERATIONプロシージャを使用してデータベース操作を終了します。
異なるセッションの操作を開始するには、session_idおよびserial_numの組合せを指定します。BEGIN_OPERATIONファンクションは、データベース操作の実行IDを戻します。dbop_exec_idがnullの場合、データベースは一意の値を生成します。
データベース操作に対して存在するネームスペースは1つだけです。これは、名前が競合する可能性があることを意味します。次のネーミング規則をお薦めします: component_name.subcomponent_name.operation name。データベース内の操作の場合、コンポーネント名にはORAの使用をお薦めします。たとえば、マテリアライズド・ビューのリフレッシュはORA.MV.refreshという名前になる可能性があります。E-Business Suiteの給与関数はEBIZ.payrollという名前になる可能性があります。
現在のセッションでデータベース操作を作成するには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
DBMS_SQL_MONITOR.BEGIN_OPERATIONを使用して操作を開始します。このファンクションは、データベース操作の実行IDを戻します。次の例では、
ORA.sales.aggという名前の操作を作成し、実行IDをSQL*Plus変数に格納しています。VARIABLE exec_id NUMBER; BEGIN :exec_id := DBMS_SQL_MONITOR.BEGIN_OPERATION ( dbop_name => 'ORA.sales.agg' ); END; / -
監視対象のSQL文またはPL/SQLプログラムを実行します。
-
DBMS_SQL_MONITOR.END_OPERATIONを使用して操作を終了します。次の例では、操作
ORA.sales.aggを終了しています。BEGIN DBMS_SQL_MONITOR.END_OPERATION ( dbop_name => 'ORA.sales.agg', dbop_eid => :exec_id ); END; /
例21-1 データベース操作の作成
次の例では、DBMS_SQL_MONITORパッケージを使用して、データベース操作を異なるセッションで開始および終了する方法を示します。この例では、次のことを想定しています。
-
管理者であり、ユーザー
shが開始するセッションの文を監視します。 -
sh.sales表およびsh.customers表の問合せを監視します。 -
これら2つの問合せを
sh_countという名前のデータベース操作として監視します。
表21-3 データベース操作の作成
| SYSTEMセッション | SHセッション | 説明 |
|---|---|---|
|
該当なし |
SQL*Plusを起動し、管理者権限を持つユーザーとして接続します。 |
|
該当なし |
|
別の端末で、SQL*Plusを起動し、ユーザー |
|
該当なし |
|
|
該当なし |
|
|
該当なし |
|
|
|
該当なし |
操作名および実行IDを指定して、データベース操作を終了します。 |
|
該当なし |
|
|
該当なし |
|
変更されたセッション情報を収集するには、データベースへのラウンド・トリップを実行する問合せを実行します。 |
|
該当なし |
操作のステータスは |
21.4 SQL監視レポートの生成とアクセス
デフォルトでは、AWRによってSQLの監視レポートがXML形式で自動的に取得されます。
このレポートでは、実行されていないかキューに入っておらず、最終の取得サイクル以降に実行が完了しているSQL文のみが取得されます。AWRでは、経過した実行時間に基づいて最もコストの高い文に対してのみレポートを取得します。SQL監視の保存ポリシーは、AWRポリシーによって制御されます。この保存ポリシーは、DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGSプロシージャを使用して変更できます。
Enterprise Manager Cloud Control(Cloud Control)の「監視されたSQL実行」ページには、監視対象の文のアクティビティが概要が示されます。このページを使用して、特定の文に関する追加の詳細をドリルダウンして取得することができます。「監視されたSQL実行の詳細」ページでは、次のような様々なビューのデータが使用されます。
-
GV$SQL_MONITOR -
GV$SQL_PLAN_MONITOR -
GV$SQL_MONITOR_SESSTAT -
GV$SQL -
GV$SQL_PLAN -
GV$ACTIVE_SESSION_HISTORY -
GV$SESSION_LONGOPS -
DBA_HIST_REPORTS -
DBA_HIST_REPORTS_DETAILS
ノート:
Oracle Database 19c以降、Oracle Databaseには、SELECT_CATALOG_ROLEのないデータベース・ユーザーが単純データベース操作(個別のSQL文とPL/SQL文)の計画と統計を確認できるV$ビューが含まれています(このビューについては、ドキュメントに記載されていません)。SELECT_CATALOG_ROLEのないユーザーは、別のユーザーのSQL実行統計と詳細を確認できません。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
ユーザー
shは、長時間実行される次のパラレル問合せ(各顧客の売上の問合せ)を実行します。SELECT c.cust_id, c.cust_last_name, c.cust_first_name, s.prod_id, p.prod_name, s.time_id FROM sales s, customers c, products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id ORDER BY c.cust_id, s.time_id; -
この問合せで過剰なリソースが消費されないようにします。文が実行されている間に、並列性のレベル、合計データベース時間、I/Oリクエスト数など、データベース操作に関する基本的な統計を判別します。
-
Cloud Controlを使用して文の実行を監視します。
ノート:
コマンドラインでSQL監視レポートを生成するには、次のサンプルSQL*Plusスクリプトのように、
DBMS_SQLTUNEパッケージのREPORT_SQL_MONITORファンクションを実行します。VARIABLE my_rept CLOB BEGIN :my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR(); END; / PRINT :my_rept
SQLの実行を監視するには:
-
「Cloud Controlの「監視されたSQL実行」ページ」の説明に従って、「監視されたSQL実行」ページにアクセスします。
次の図では、一番上の行にパラレル問合せが表示されています。
この例では、問合せが1.4分間実行されています。
-
SQL ID列内の値をクリックして文の詳細を確認します。
監視されたSQLの詳細ページが表示されます。
前述のレポートには、文の実行に関する実行計画と統計が示されています。たとえば、「時系列」列には、実行計画の各ステップがアクティブであった時間が示されます。時間は、文の実行の開始と終了との相対で表示されます。「実行」列には、操作が実行された回数が示されます。
-
「概要」セクションで、SQLテキストの横のリンクをクリックします。
メッセージでSQL文のすべてのテキストが示されます。
-
「データベース時間」の隣にある「時間と待機の統計」セクションで、棒グラフの最も大きな部分にカーソルを移動します。
データベース時間の半分以上がユーザーI/Oで消費されていることがメッセージに示されます。
データベース時間では、データベースがこのSQL文で動作するために消費した時間量が測定されます。この値には、I/O時間など、CPU時間と待機時間が含まれます。棒グラフは、CPUリソース、ユーザーI/Oリソースおよびその他のリソースをハイライト表示するために色分けされた複数の部分に分割されています。任意の部分にカーソルを移動すると、合計に対するそのパーセント値が表示されます。
-
「詳細」セクションの「IOリクエスト」列で、I/Oリクエスト・バーの部分にカーソルを移動して、合計に対するパーセント値を表示します。
メッセージが表示されます。
前の図では、「IOリクエスト」メッセージに監視対象のSQLによって発行された読取りリクエストの合計数が示されています。このメッセージには、読取りリクエストが合計I/Oリクエストの80%を占めることが示されています。
関連項目:
-
「監視されたSQL実行の詳細」ページの要素の説明およびレポート内のすべての統計の詳細は、Cloud Controlオンライン・ヘルプを参照してください。
-
データベース操作監視の
V$SQL_MONITORおよび関連するビューについて学習するには、Oracle Databaseリファレンスを参照してください -
監視対象SQLの詳細レポートの便利な機能についての説明ビデオは、「SQL監視を使用する理由」を参照
21.5 データベース操作の監視: シナリオ
これらのシナリオでは、単純および複合データベース操作に関するレポートを作成します。
21.5.1 単純データベース操作に関するレポート: シナリオ
このシナリオでは、数秒で完了すると予測される問合せが実行を継続しています。
この例は、データベースにユーザーshとしてログインして、次の問合せを実行することを前提としています。
SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_idこの問合せは完了しません。Oracle Database 19c以降、shなどの権限の低いユーザーが、そのユーザーのセッションの単純データベース操作(個別のSQL文とPL/SQL文)についてのSQL監視レポートを生成できます。問題の原因を特定するには、その診断に次のようにSQL監視を使用します。
-
問合せを取り消します。
-
DBMS_SQL_MONITOR.REPORT_SQL_MONITORを呼び出してテキスト・レポートを取得します。SET LONG 1000000 VARIABLE my_rept CLOB; BEGIN :my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR( report_level => 'ALL', TYPE => 'text'); END; / PRINT :my_rept出力例の一部を次に示します。
SQL Text ------------------------------ SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id Global Information ------------------------------ Status : DONE (ERROR) Instance ID : 1 Session : SH (42:3617) SQL ID : d9w9dw5v007xp SQL Execution ID : 16777217 Execution Started : 09/18/2018 14:08:13 First Refresh Time : 09/18/2018 14:08:13 Last Refresh Time : 09/18/2018 14:08:34 MY_REPT ------------------------------------------------------------------------ Duration : 21s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@slc16iva (TNS V1-V3) Fetch Calls : 1 Global Stats MY_REPT ------------------------------------------------------------------------ | Time(s)|Time(s)|Waits(s) |Calls | Gets |Reqs | Bytes | Reqs | Bytes | ======================================================================== | 21 | 11 | 10 | 1 | 204 | 233 | 3MB | 4568 | 909MB | ======================================================================== SQL Plan Monitoring Details (Plan Hash Value=2036849021) ======================================================================== MY_REPT ------------------------------------------------------------------------ ======================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Tem p | Activity | Activity Detail | | | | | (Estim) | | Ac tive(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Ma x) | (%) | (# samples) | ================================================================================ ================================================================================ ================================= MY_REPT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 19 | +2 | 1 | 0 | | | | | . | . | | | | 1 | SORT ORDER BY | | 51G |316M | 20 | +1 | 1 | 0 | | | 4496 | 908MB | 99MB | 909 MB | | | | 2 | MERGE JOIN CARTESIAN | | 51G | 2M | 19 | +2 | 1 | 50M | | | | | . | . | | | | 3 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 414 | 20 | +2 | 1 | 54 | 3 | 120KB | | | . | . | | | | 4 | BUFFER SORT | | 919K |316M | 19 | +2 | 54 | 50M | | | | | 28MB | . | | | | 5 | PARTITION RANGE ALL | | 919K | 29 | 1 | +2 | 1 | 919K | | | | | . | . | | | | 6 | BITMAP CONVERSION TO ROWIDS | | 919K | 29 | 1 | +2 | 28 | 919K | | | | | . | . | | | | 7 | BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX | | | 1 | +2 | 28 | 1074 | 32 | 512KB | | | . | . | | | ================================================================================書式設定のために、前述の出力は読みにくくなっています。グラフィカルなアクティブSQL監視レポートを作成します。
-
次のコマンドが含まれているSQLスクリプトを作成します。
SET FEEDBACK OFF SET TERMOUT OFF SET TRIMSPOOL ON SET TRIM ON SET PAGES 0 SET LINESIZE 1000 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SPOOL /tmp/long_sql.htm SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR( report_level => 'ALL', TYPE => 'active') FROM DUAL; SPOOL OFF -
SQL*Plusで、前のステップで作成したSQLスクリプトを実行します。
-
ブラウザで出力HTMLファイルを開いて、レポートを確認します。
パフォーマンスの問題の原因は、2行目のデカルト結合にあります。問合せの作成者の不注意で、
WHERE句が抜けています。salesとcustomersの内部結合の場合は約100万行を返しますが、この問合せは取り消されるまでに5000万行を返していました。2つの表から結合したデータのソートに、DB時間の大部分を費やしています(行1)。
21.5.2 複合データベース操作に関するレポート: シナリオ
このシナリオでは、DBMS_SQL_MONITORを使用してデータベース操作を定義し、アクティブ・レポートを生成します。
shスキーマの表の4つの問合せを1つの操作にグループ化してから、レポートを生成することです。
-
SQL*Plusで、管理ユーザー
SAMとしてログオンします。次のように、SHOPという操作を開始し(forced_trackingを指定して、SQLモニターがSQLを追跡するように指定する)、4つの問合せを実行してから、操作を終了します。VARIABLE exec_id NUMBER; BEGIN :exec_id := DBMS_SQL_MONITOR.BEGIN_OPERATION ( dbop_name => 'SHOP', forced_tracking => 'Y' ); END; / SELECT COUNT(*) FROM sh.sales; SELECT COUNT(*) FROM sh.customers; SELECT prod_id, cust_id FROM sh.sales WHERE prod_id < 26 ORDER BY prod_id; SELECT cust_id, cust_first_name, cust_last_name, cust_city FROM sh.customers WHERE cust_id < 30000 ORDER BY cust_id; BEGIN DBMS_SQL_MONITOR.END_OPERATION ( dbop_name => 'SHOP', dbop_eid => :exec_id ); END; / -
ステータスおよびメタデータも含めて、操作のメタデータを取得するには、
V$SQL_MONITORを問い合せます(出力例も示します)。COL STATUS FORMAT a10 COL DBOP_NAME FORMAT a10 COL CON_NAME FORMAT a5 SELECT STATUS, SQL_ID, DBOP_NAME, DBOP_EXEC_ID, TO_CHAR(ELAPSED_TIME/1000000,'000.00') AS ELA_SEC FROM V$SQL_MONITOR WHERE DBOP_NAME = 'SHOP'; STATUS SQL_ID DBOP_NAME DBOP_EXEC_ID ELA_SEC ---------- ------------- ---------- ------------ ------- DONE SHOP 3 001.34 -
SQLモニター・レポートに関するメタデータを取得するには、
DBMS_SQL_MONITOR.REPORT_SQL_MONITORをコールします(出力例も示します)。SET LONG 10000000 SET LONGCHUNKSIZE 10000000 SET PAGES 0 SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR( dbop_name => 'SHOP', type => 'TEXT', report_level => 'ALL') AS rpt FROM DUAL; SQL Monitoring Report Global Information ------------------------------ Status : DONE Instance ID : 1 Session : SAM (87:6406) DBOP Name : SHOP DBOP Execution ID : 3 First Refresh Time : 10/03/2017 07:33:32 Last Refresh Time : 10/03/2017 07:34:24 Duration : 52s Module/Action : sqlplus@myhost (TNS V1-V3)/- Service : MYSERVICE Program : sqlplus@myhost (TNS V1-V3) Global Stats ======================================================== | Elapsed | Cpu | IO | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | ======================================================== | 1.36 | 1.34 | 0.02 | 202 | 583 | 27MB | ======================================================== -
アクティブなHTMLレポートを生成するには、
DBMS_SQL_MONITOR.REPORT_SQL_MONITORに操作の名前を渡します。SET TRIMSPOOL ON SET TRIM ON SET PAGES 0 SET LINESIZE 1000 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SPOOL /tmp/shop.htm SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(dbop_name=>'SHOP',report_level=>'ALL',TYPE=>'active') FROM DUAL; SPOOL OFF次の図は、アクティブ・レポートを示しています。
関連項目:
DBMS_SQL_MONITORについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください