日本語PDF

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 ID 5hc07qvt8v7379ht3ba3arrzt3に対してインスタンス・レベルの監視を強制しています。
    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_OPERATIONFORCED_TRACKINGYに設定することで操作のトラッキングが強制されている。

ノート:

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監視のアーキテクチャの概要を示しています。

図21-1 データベース操作監視のアーキテクチャ

図21-1の説明が続きます
「図21-1 データベース操作監視のアーキテクチャ」の説明

前の図に示されているように、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インタフェースが含まれます。

関連項目:

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実行」ページにアクセスするには:

  1. 適切な資格証明を使用してCloud Controlにログインします。

  2. 「ターゲット」メニューの下で、「データベース」を選択します。

  3. データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。

  4. データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。

  5. 「パフォーマンス」メニューから、「SQL監視」を選択します。

    「監視されたSQL実行」ページが表示されます。

    図21-2 監視されたSQL実行ページ

    図21-2の説明が続きます
    「図21-2 監視されたSQL実行」の説明
21.1.4.2 DBMS_SQL_MONITORパッケージ

DBMS_SQL_MONITORパッケージでは、複合データベース操作の開始と終了を定義して、データベース操作のレポートを生成します。

表21-1 DBMS_SQL_MONITOR

サブプログラム 説明

BEGIN_OPERATION

このファンクションは、現在のセッションでデータベース操作を開始します。

このファンクションでは、セッションがデータベース操作と関連付けられます。Oracle Database 12cリリース2 (12.2)からは、session_idおよびsession_numを使用して、監視を開始するセッションを指定できます。

END_OPERATION

このファンクションは、現在のセッションでデータベース操作を終了します。指定のデータベース操作が存在しない場合、このファンクションの効果はありません。

REPORT_SQL_MONITOR

このファンクションは、SQL文、PL/SQLブロックまたはデータベース操作の監視情報によって詳細なレポートを作成します。

各操作には、キー情報および関連付けられているグローバルな統計情報が指定されます。このファンクションを使用して、データベース操作に関する詳細な監視情報を取得します。

このレポートで対象となるデータベース操作は次になります。

  • Oracle Databaseによって監視された最後のデータベース操作(デフォルトはパラメータなし)。

  • 指定されたセッションで実行され、Oracle Databaseによって監視された最後のデータベース操作。このセッションは、セッションIDか、オプションのシリアル番号で識別されます(-1が現在のセッションです)。

  • sql_idで識別された特定のデータベース操作の最後の実行。

  • sql_idsql_exec_startおよびsql_exec_idを組み合せて識別されたデータベース操作の特定の実行。

  • dbop_nameで識別される特定のデータベース操作の最後の実行。

  • dbop_namedbop_exec_idの組合せで識別されるデータベース操作の特定の実行。

typeパラメータを使用して次の出力タイプを指定します: TEXT(デフォルト)、HTMLACTIVEまたはXML

REPORT_SQL_MONITOR_XML

このファンクションはREPORT_SQL_MONITORファンクションと同じですが、戻り値の型はXMLTypeです。

REPORT_SQL_MONITOR_LIST

このファンクションは、Oracle Databaseによって監視されたすべてのデータベース操作またはデータベース操作のサブセットに関するレポートを作成します。

REPORT_SQL_MONITOR_LIST_XML

このファンクションはREPORT_SQL_MONITOR_LISTファンクションと同じですが、XMLTypeを戻します。

関連項目:

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_ACCESSDIAGNOSTIC+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 データベース操作監視のビュー

ビュー 説明

DBA_HIST_REPORTS

このビューは、自動ワークロード・リポジトリ(AWR)に取得されたXMLレポートに関するメタデータを表示します。各XMLレポートには、コンポーネントのアクティビティの詳細が含まれます。たとえば、SQLモニターのレポートには、特定のデータベース操作の詳細なレポートが含まれます。

重要な列には次のものがあります。

  • REPORT_SUMMARY列にはレポートの要約が含まれます。

  • COMPONENT_NAME列は値sqlmonitorを受け取ります。

  • REPORT_ID列には、レポートの生成時にDBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAILRIDパラメータで指定できるレポートのIDが示されます。

  • KEY1列は、文のSQL IDです。

  • KEY2列は、文のSQL実行IDです

AWRは、SQL監視レポートの保存期間を制御します。DBA_HIST_REPORTSのすべてのSQL監視レポートは、AWR SNAP_IDに関連付けられています。SQL監視レポートは、対応するAWRデータをエクスポートまたはインポートしても、エクスポートまたはインポートされないことに注意してください。

DBA_HIST_REPORTS_DETAILS

このビューは、AWR内に取得された各レポートの詳細を示します。各レポートのメタデータはDBA_HIST_REPORTSビューに表示されますが、実際のレポートはDBA_HIST_REPORTS_DETAILSビューで使用できます。

V$SQL_MONITOR

このビューには、単純データベース操作および複合データベース操作に関するグローバルな上位レベルの情報が含まれます。

単純データベース操作の場合、監視の統計は複数の実行にわたる累積的なものではありません。この場合、V$SQL_MONITORの1つのエントリは、SQL文の1回の実行のみに対応します。同じSQL文の2つの実行を監視する場合、V$SQL_MONITORに各実行のエントリが個別に作成されます。

単純データベース操作の場合、V$SQL_MONITORには、パラレル実行のコーディネータ・プロセスに対して1つのエントリと、パラレル実行のサーバー・プロセスごとに1つのエントリが割り当てられます。各エントリにはV$SQL_PLAN_MONITORの対応するエントリがあります。1つのSQL文のパラレル実行に割り当てられたプロセス群は同じ実行について連携しているため、これらのエントリは同じ実行キー(SQL_IDSQL_EXEC_STARTおよびSQL_EXEC_IDの組合せ)を共有します。

複合データベース操作の場合、操作の一部として同一セッションで実行される複数のSQL文およびPL/SQLサブプログラムにわたって統計が蓄積される操作が、各行に含まれます。主キーは、DBOP_NAME列とDBOP_EXEC_ID列の組合せです。

V$SQL_MONITOR_SESSTAT

このビューには、データベース操作に関連するすべてのセッションの統計が含まれています。

統計のほとんどは累積されます。データベースでは、その統計がXML形式で格納され、各統計に各列が使用されることはありません。このビューは、主にレポートの生成を目的としています。ここでは、V$SQL_MONITOR_SESSTATではなくV$SESSTATを使用することをお薦めします。

V$SQL_PLAN_MONITOR

このビューには、監視対象のSQL文の実行計画における各ステップの監視統計が含まれています。

SQL文が実行されている間、データベースではV$SQL_PLAN_MONITOR内の統計が1秒ごとに更新されます。監視対象のすべてのSQL文のV$SQL_PLAN_MONITORに複数のエントリが存在します。各エントリは、文の実行計画のステップに対応しています。

前述のV$ビューと次のビューを一緒に使用して、監視対象の実行に関する追加情報を取得できます。

  • V$ACTIVE_SESSION_HISTORY

  • V$SESSION

  • V$SESSION_LONGOPS

  • V$SQL

  • V$SQL_PLAN

関連項目:

データベース操作監視のV$ビューについて学習するには、Oracle Databaseリファレンスを参照してください

21.1.5 データベース操作監視の基本的なタスク

この項では、データベース操作監視の基本的なタスクを説明します。

基本的なタスクには次があります。

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に設定されています。

  • データベース操作の自動監視を有効にします。

データベース操作の監視を有効化するには:

  1. 適切な権限でSQL*Plusをデータベースに接続し、現行のデータベース操作の設定を問い合せます。

    たとえば、次のSQL*Plusコマンドを実行します。

    SQL> SHOW PARAMETER statistics_level
     
    NAME                                TYPE        VALUE
    ----------------------------------- ----------- -----
    statistics_level                    string      BASIC
    
  2. 統計レベルを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文のデータベース操作の監視を無効化するには:

  1. 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という名前になる可能性があります。

現在のセッションでデータベース操作を作成するには:

  1. SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

  2. 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; 
    /
  3. 監視対象のSQL文またはPL/SQLプログラムを実行します。

  4. 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> CONNECT SYSTEM
Enter password: *********
Connected.

該当なし

SQL*Plusを起動し、管理者権限を持つユーザーとして接続します。

該当なし

SQL> CONNECT sh
Enter password: ******
Connected.

別の端末で、SQL*Plusを起動し、ユーザーshとして接続します。

SELECT SID, SERIAL# 
FROM   V$SESSION 
WHERE  USERNAME = 'SH';

       SID    SERIAL#
---------- ----------
       121      13397

該当なし

SYSTEMセッションで、shセッションのセッションIDおよびシリアル番号を問い合せます。

VARIABLE eid NUMBER

BEGIN
:eid:=DBMS_SQL_MONITOR.BEGIN_OPERATION
      ('sh_count', null, null,
       null, '121', '13397');
END;
/

PRINT eid

       EID
----------
         2

該当なし

SYSTEMセッションで、shセッションのセッションIDおよびシリアル番号を指定して、データベース操作を開始します。

該当なし

SELECT count(*) 
FROM   sh.sales;
 
  COUNT(*)
----------
    918843

SELECT COUNT(*) 
FROM   sh.customers;
 
  COUNT(*)
----------
     55500

shセッションで、salesおよびcustomers表を問い合せます。これらのSQL問合せは、sh_count操作の一部です。

BEGIN 
  DBMS_SQL_MONITOR.END_OPERATION
        ('sh_count',:eid);
END;
/

該当なし

操作名および実行IDを指定して、データベース操作を終了します。

COL DBOP_NAME FORMAT a10
COL STATUS FORMAT a10
COL ID FORMAT 999

SELECT DBOP_NAME, DBOP_EXEC_ID AS ID,
       STATUS, CPU_TIME, BUFFER_GETS
FROM   V$SQL_MONITOR 
WHERE DBOP_NAME IS NOT NULL
      ORDER BY DBOP_EXEC_ID;

DBOP_NAME  ID     STATUS CPU_TIME GETS
---------- -- ---------- -------- ----
sh_count    1  EXECUTING    24997   65

該当なし

sh_countデータベース操作のメタデータを問い合せます。セッションが新しいセッション・ステータスをまだ取得していないため、操作のステータスはEXECUTINGです。

該当なし

SELECT SYSDATE FROM DUAL;

変更されたセッション情報を収集するには、データベースへのラウンド・トリップを実行する問合せを実行します。

COL DBOP_NAME FORMAT a10
COL STATUS FORMAT a10
COL ID FORMAT 999

SELECT DBOP_NAME, DBOP_EXEC_ID AS ID,
       STATUS, CPU_TIME, BUFFER_GETS
FROM   V$SQL_MONITOR 
WHERE DBOP_NAME IS NOT NULL
      ORDER BY DBOP_EXEC_ID;

DBOP_NAME  ID     STATUS CPU_TIME GETS
---------- -- ---------- -------- ----
sh_count    1       DONE    24997   65

該当なし

操作のステータスはDONEに更新されています。

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の実行を監視するには:

  1. 「Cloud Controlの「監視されたSQL実行」ページ」の説明に従って、「監視されたSQL実行」ページにアクセスします。

    次の図では、一番上の行にパラレル問合せが表示されています。

    この例では、問合せが1.4分間実行されています。

  2. SQL ID列内の値をクリックして文の詳細を確認します。

    監視されたSQLの詳細ページが表示されます。

    前述のレポートには、文の実行に関する実行計画と統計が示されています。たとえば、「時系列」列には、実行計画の各ステップがアクティブであった時間が示されます。時間は、文の実行の開始と終了との相対で表示されます。「実行」列には、操作が実行された回数が示されます。

  3. 「概要」セクションで、SQLテキストの横のリンクをクリックします。

    メッセージでSQL文のすべてのテキストが示されます。

  4. 「データベース時間」の隣にある「時間と待機の統計」セクションで、棒グラフの最も大きな部分にカーソルを移動します。

    データベース時間の半分以上がユーザーI/Oで消費されていることがメッセージに示されます。

    データベース時間では、データベースがこのSQL文で動作するために消費した時間量が測定されます。この値には、I/O時間など、CPU時間と待機時間が含まれます。棒グラフは、CPUリソース、ユーザーI/Oリソースおよびその他のリソースをハイライト表示するために色分けされた複数の部分に分割されています。任意の部分にカーソルを移動すると、合計に対するそのパーセント値が表示されます。

  5. 「詳細」セクションの「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監視を使用します。

  1. 問合せを取り消します。

  2. 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監視レポートを作成します。

  3. 次のコマンドが含まれている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
    
  4. SQL*Plusで、前のステップで作成したSQLスクリプトを実行します。

  5. ブラウザで出力HTMLファイルを開いて、レポートを確認します。

    パフォーマンスの問題の原因は、2行目のデカルト結合にあります。問合せの作成者の不注意で、WHERE句が抜けています。salescustomersの内部結合の場合は約100万行を返しますが、この問合せは取り消されるまでに5000万行を返していました。2つの表から結合したデータのソートに、DB時間の大部分を費やしています(行1)。

21.5.2 複合データベース操作に関するレポート: シナリオ

このシナリオでは、DBMS_SQL_MONITORを使用してデータベース操作を定義し、アクティブ・レポートを生成します。

目標は、shスキーマの表の4つの問合せを1つの操作にグループ化してから、レポートを生成することです。
  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;
    /
  2. ステータスおよびメタデータも含めて、操作のメタデータを取得するには、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
  3. 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 |
    ========================================================
    
  4. アクティブな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

    次の図は、アクティブ・レポートを示しています。

    図21-3 SQL監視レポート

    図21-3の説明が続きます
    「図21-3 SQL監視レポート」の説明

関連項目:

DBMS_SQL_MONITORについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください