21 データベース操作の監視

この章では、SQLおよびPL/SQLを監視する方法について説明します。

この章のトピックは、次のとおりです:

21.1 データベース操作の監視について

データベース操作は、2つの時点の間のセッション・アクティビティを含むユーザー定義の論理オブジェクトです。

データベース操作には、一連のデータベース・タスクが含まれます。一般的なタスクとして、バッチ・ジョブや、抽出、変換およびロード(ETL)処理を行うジョブなどがあります。

データベース操作は、名前および実行IDによって一意に識別されます。各操作を複数回実行できます。操作の実行はそれぞれ一意に識別できます。

リアルタイムなデータベース操作では、複合操作を自動的に監視できます。データベースは、パラレルな問合せ、DML文およびDDL文を、実行の開始と同時に自動的に監視します。デフォルトでは、リアルタイムSQL監視は、SQL文がパラレルに実行されるか、1回の実行で最小で5秒のCPU時間またはI/O時間が消費された場合に自動的に開始されます。

SQL監視機能は、STATISTICS_LEVEL初期化パラメータがTYPICAL(デフォルト値)またはALLのいずれかに設定されている場合に、デフォルトで有効になります。DBMS_SQL_MONITORパッケージを使用すると、データベース操作の開始と停止、定義、監視およびレポート生成が可能です。

この項では、次の項目について説明します。

関連項目:

データベース操作の概要は、『Oracle Database概要』を参照してください

21.1.1 データベース操作の監視の目的

リアルタイムでのSQLの監視により、単一のSQL文またはPL/SQLプログラム・ユニットを監視できます。

データベース操作は、一連の文またはプロシージャを名前付きの一意に識別される再実行可能な単位として処理できるようにすることにより、リアルタイムSQL監視を拡張します。データベース操作の監視は通常、次のユーザーにとって役に立ちます。

• コストの高い(応答時間が短い)SQL文とPL/SQLファンクションの識別を行うDBA

• データ・ウェアハウスまたはOLTPシステムでバッチ・ジョブを管理するDBA

• Oracle Data Pump操作など、特定の操作に関連するアクティビティの監視が必要なアプリケーションまたはデータベースの開発者

データベース操作の監視は次のタスクの実行に役に立ちます。

• 追跡およびレポート作成

  追跡にはまずDBMS_SQL_MONITOR、OCI、JDBC APIなどのデータベース操作の定義が必要です。異なるセッションからの操作を、現在使用している操作から定義できます。データベース・インフラストラクチャで、定義された操作のための追跡対象が決定されます。

  操作のレポートを生成できます。たとえば、チューニング・タスクとして、バッチ・ジョブのかわりに実行するSQL文やその実行統計の以前の状態、その操作の実行中にデータベースで発生した事象などの特定が必要な場合もあります。

• 実行の進捗状況の監視

  このタスクでは、現在実行中のデータベース操作の監視が行われます。この情報は、操作の完了に時間がかかる理由を調査している場合に特に役立ちます。

• リソース使用率の監視

  SQLの実行により過剰なCPUが使用されたり、過剰なI/Oが発行されたりするタイミング、およびSQLの実行に時間がかかるタイミングを検出することが必要な場合があります。Oracle Database Resource Manager(Resource Manager)を使用して、コンシューマ・グループごとに、そのグループにおけるすべてのSQL実行の最大リソース使用率を指定するしきい値を構成できます。SQL操作が指定されたしきい値に達したとき、Resource Managerでは、その操作をより優先順位の低いコンシューマ・グループに切り替えたり、セッションまたはコールを終了したり、イベントのログを記録できます。これらのSQL操作を監視できます。

• レスポンス時間のチューニング

  データベース操作をチューニングする際、通常はレスポンス時間の改善を念頭に置きます。多くの場合に、データベース操作のパフォーマンスの問題は主にSQLパフォーマンスの問題と言えます。

この項では、次の項目について説明します。

関連項目:

• 「Cloud Controlを使用したSQL実行の監視」

• Oracle Database管理者ガイド

21.1.2 データベース操作監視の概念

DBMS_SQL_MONITORパッケージは、データベース操作のアーキテクチャの主要コンポーネントです。

この項では、次の項目について説明します。

21.1.2.1 リアルタイムSQL監視のアーキテクチャについて

リアルタイムSQL監視は、処理時間が長いSQL文やパラレルSQL文のパフォーマンスの問題を識別するのに役立つ、組込みデータベース・インフラストラクチャです。

リアルタイムSQL監視は、Oracle Database Tuning Packの機能の1つです。CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータがDIAGNOSTIC+TUNING(デフォルト)に設定されている場合、データベース操作を使用できます。

次の図は、リアルタイムSQL監視のアーキテクチャの概要を示しています。

図21-1 リアルタイムSQL監視のアーキテクチャ

「図21-1 リアルタイムSQL監視のアーキテクチャ」の説明

前の図に示したように、PL/SQLパッケージDBMS_SQL_MONITORによってデータベース操作が定義されます。監視が開始すると、データベースでは、AWRでのデータベース操作に関するメタデータが格納され、そのデータ自体がAWRとASHの両方に格納されます。データベースによる監視統計のリフレッシュは、監視対象の文の実行とほぼリアルタイムで実行されます(通常は1秒に一度)。データベースでは、操作データ(文および文に関するメタデータ)をSGAに格納し、定期的にディスクに書き込みます。

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インタフェースが含まれます。

関連項目:

• 「Cloud Controlを使用したSQL実行の監視」

• V$SQL_MONITOR、V$SQL_PLAN_MONITORおよびCONTROL_MANAGEMENT_PACK_ACCESSについて学習するには、『Oracle Databaseリファレンス』を参照してください

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

21.1.2.2 データベースはいつ操作を監視するか

操作の監視は、データベース操作が単純か複合かによって異なります。

この項では、次の項目について説明します。

21.1.2.2.1 単純データベース操作

単一のSQL文、あるいは単一のPL/SQLサブプログラムはシンプル・データベースの操作です。

Oracle Databaseは、次の条件のいずれかがtrueの場合に、単純データベース操作を監視します。

• SQL文がパラレルで実行されます。

• SQL文のトラッキングは、/*+ MONITOR */ヒントによって強制されます。

• SQL文やPL/SQLのサブプログラムが、CPUまたはI/O時間を1回の実行で5秒以上消費しました。

単純操作のリアルタイムSQL監視は、現在実行中のSQL文が実行計画のどの段階にあるのを特定したり、文がその時間をどこで消費しているのかを特定したりするのに役立ちます。また、完了したばかりの文の時間やリソース使用率の内訳を確認できます。このようにして、特定の操作でコストが高くなる理由をより適切に特定できます。

リアルタイムSQL監視の通常のユースケースには次が含まれます。

• 頻繁に実行されるSQL文の実行速度が通常より遅い場合。この問題の根本原因を特定する必要があります。

• データベース・セッションのパフォーマンスが低下する場合。

• パラレルSQL文に時間がかかる場合。サーバー・プロセスで作業がどのように分割されているのかを特定する必要があります。

21.1.2.2.2 複合データベース操作

複合データベース操作は、各セクションが独自の開始点および終了点を定義する、データベース・セッションの2つの時点間でのアクティビティです。

2つの時点間で実行されるSQL文またはPL/SQLサブプログラムは、複合操作の一部です。1つのセッションは、一度に1つの複合データベース操作にのみ関与できます。

Oracle Databaseは、次の条件のいずれかがtrueの場合に、複合データベース操作を監視します。

• DBMS_SQL_MONITOR.BEGIN_OPERATIONで操作を開始し、その操作でCPUまたはI/O時間を5秒以上消費している。

• BEGIN_OPERATIONでFORCE_TRACKINGをYに設定することにより、操作の追跡が強制されている。

OLTPおよびデータ・ウェアハウス環境では、ジョブによって関連するSQL文が論理的にグループ化されることが多くあります。ジョブは、複数同時セッションに及ぶことが可能です。複合操作を監視する通常のユースケースには次が含まれます。

• 多数のSQL文を含む定期的なバッチ・ジョブが一定の時間内に完了する必要があるが、予想より時間がかかった。

• データベースのアップグレード後に、重要なバッチ・ジョブの実行時間が増大した。この問題を解決するには、アップグレードの前後にバッチ・ジョブから十分な関連統計データを収集し、2つのデータ・セットを比較した上で、変更を特定する必要があります。

• SQLチューニング・セット(STS)のパックに予想よりはるかに長く時間がかかった場合。この問題を診断するには、時間の経過ごとに何が実行されていたのかを把握する必要があります。この問題は簡単に再現できないため、プロセスを実行中に監視する必要があります。

関連項目:

• 「SQLチューニング・セットについて」

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

21.1.2.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.3 データベース操作監視のユーザー・インタフェース

コマンド・ラインから、またはCloud Controlを使用してデータベース操作を監視できます。

この項では、次の項目について説明します。

21.1.3.1 Cloud Controlの「監視されたSQL実行」ページ

Cloud Controlの「モニターされたSQL実行」ページで、(「SQLモニター」とも呼ばれる)に、SQL実行の詳細が表示されます。SQL監視は、データベース操作に関するレポートを作成するための推奨インタフェースです。

実行計画の各ステップの統計は、主要なパフォーマンス・メトリック(経過時間、CPU時間、読取り/書込み数、I/O待機時間、その他の様々な待機時間など)によって追跡されます。これらのメトリックにより、DBAがSQL実行を徹底的に分析して監視対象のSQL文の最適なチューニング戦略を決定することが可能になります。

SQLモニターのアクティブ・レポートは、HTMLファイルにデータを保存できるフラッシュ・ベースの対話モード・レポートを提供します。このファイルを保存してオフラインで表示できます。

この項では、次の項目について説明します。

21.1.3.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 監視されたSQL実行」の説明

21.1.3.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_id、sql_exec_startおよびsql_exec_idを組み合せて識別されたデータベース操作の特定の実行。 dbop_nameで識別される特定のデータベース操作の最後の実行。 dbop_nameとdbop_exec_idの組合せで識別されるデータベース操作の特定の実行。 typeパラメータを使用して次の出力タイプを指定します: TEXT(デフォルト)、HTML、ACTIVEまたは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.3.3 データベース操作の監視およびレポート作成用のビュー

いくつかのV$およびデータ・ディクショナリ・ビューを使用して、データベース操作の統計を取得できます。

次の表は、それらのビューをまとめたものです。

表21-2 データベース操作監視のビュー

ビュー	説明
DBA_HIST_REPORTS	このビューは、自動ワークロード・リポジトリ(AWR)内に取得されたXMLレポートに関するメタデータを表示します。各XMLレポートには、コンポーネントのアクティビティの詳細が含まれます。たとえば、SQLモニターのレポートには、特定のデータベース操作の詳細なレポートが含まれます。 重要な列には次のものがあります。 REPORT_SUMMARY列にはレポートの要約が含まれます。 COMPONENT_NAME列は値sqlmonitorを受け取ります。 REPORT_ID列は、DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAILのRIDパラメータで指定できる、レポートのIDを提供します。
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_ID、SQL_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.4 データベース操作監視の基本的なタスク

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

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

• 「データベース操作監視の有効化および無効化」

  このタスクでは、データベース操作の自動監視がシステム・レベルおよび文レベルでどのように有効化できるのかを説明します。

• 「複合データベース操作の定義」

  この項では、PL/SQLを使用してデータベース操作の開始と終了をどのように定義できるのかが説明されています。

• 「Cloud Controlを使用したSQL実行の監視」

  この項では、データベース操作のレポートをどのように生成し解釈できるのかが説明されています。

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 Cloud Controlを使用したSQL実行の監視

デフォルトでは、AWRによってSQLの監視レポートがXML形式で自動的に取得されます。

このレポートでは、実行されていないかキューに入っておらず、最終の取得サイクル以降に実行が完了しているSQL文のみが取得されます。AWRでは、経過した実行時間に基づいて最もコストの高い文に対してのみレポートを取得します。

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

前提条件

このチュートリアルでは、次のことが前提となっています。

• ユーザー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実行」ページにアクセスします。

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

   
   図mon_sql_exec_main.jpgの説明

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

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

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

   
   図mon_sql_exec_details.gifの説明

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

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

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

   
   図sql_id_tooltip.gifの説明

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

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

   
   図time_stats.gifの説明

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

5. 「詳細」セクションの「IOリクエスト」列で、I/Oリクエスト・バーの部分にカーソルを移動して、合計に対するパーセント値を表示します。

   メッセージが表示されます。

   
   図io_requests.gifの説明

   前の図では、「IOリクエスト」メッセージに監視対象のSQLによって発行された読取りリクエストの合計数が示されています。このメッセージには、読取りリクエストが合計I/Oリクエストの80%を占めることが示されています。

関連項目:

• 「監視されたSQL実行の詳細」ページの要素の説明およびレポート内のすべての統計の詳細は、Cloud Controlオンライン・ヘルプを参照してください。

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

21.5 データベース操作の監視: シナリオ

これらのシナリオでは、単純および複合データベース操作に関するレポートを作成します。

この項では、次の項目について説明します。

21.5.1 単純操作に関するレポート: シナリオ

このシナリオでは、1つの問合せは秒単位で完了しますが、数分後にも実行されています。

ユーザーshは問合せを実行中で、完了していません。V$SQLを問い合せると、問合せのSQL IDはfbjymtacazga2であることが判明しま。SQLモニターを使用して診断することにします。

1. SQL*Plusを起動し、管理者としてデータベースにログインします。

2. DBMS_SQL_MONITOR.REPORT_SQL_MONITORを呼び出してテキスト・レポートを取得します。

   SET LONG 1000000
   VARIABLE my_rept CLOB;
   BEGIN
     :my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
                   sql_id       => 'fbjymtacazga2', 
                   report_level => 'ALL', 
                   TYPE         => 'text');
   END;
   /
   PRINT :my_rept
   

   出力例の一部を次に示します。

   SQL Text
   ------------------------------
   SELECT s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id
   
   Global Information
   ------------------------------
    Status              :  EXECUTING
    Instance ID         :  1
    Session             :  SH (31:25365)
   
   MY_REPT
   --------------------------------------------------------------------------------
    SQL ID              :  fbjymtacazga2
    SQL Execution ID    :  16777216
    Execution Started   :  10/03/2017 13:07:39
    First Refresh Time  :  10/03/2017 13:07:43
    Last Refresh Time   :  10/03/2017 13:10:03
    Duration            :  145s
    Module/Action       :  SQL*Plus/-
    Service             :  SYS$USERS
    Program             :  sqlplus@myhost (TNS V1-V3)
   
   Global Stats
   
   MY_REPT
   --------------------------------------------------------------------------------
   =========================================================================
   | Elapsed |   Cpu   |    IO    | Buffer | Read  | Read  | Write | Write |
   | Time(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes | Reqs  | Bytes |
   =========================================================================
   |     157 |     129 |       28 |  10328 | 28974 |   5GB | 45028 |  10GB |
   =========================================================================
   
   SQL Plan Monitoring Details (Plan Hash Value=2036849021)
   ================================================================================
   ================================================================================
   ===================================
   
   MY_REPT
   --------------------------------------------------------------------------------
   | Id   |             Operation              |      Name      |  Rows   | Cost |
     Time    | Start  | Execs |   Rows   | Read  | Read  | Write | Write |  Mem  |
   Temp | Activity | Activity Detail |
   |      |                                    |                | (Estim) |      |
   Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes |       |
        |   (%)    |   (# samples)   |
   
   ...

3. 計画の読みやすい表示を望んでいることから、アクティブ・レポートを生成します。

   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(
            sql_id       => 'fbjymtacazga2', 
            report_level => 'ALL', 
            TYPE         => 'active') 
   FROM   DUAL;
   SPOOL OFF
   

   ブラウザでHTMLファイルを開き、レポートを確認してください。

   
   illustration cartesian.pngの説明

   問題の原因は、2行目のデカルト結合にあります。問合せに、WHERE句がありません。salesおよびcustomersの内部結合の場合と同様に、およそ100万行を返すかわりに、問合せで12億行が返されています。2つの表からマージしたデータのソートに、DB時間の大部分を消費しています(行1)。

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

このシナリオでは、複合操作を作成し、アクティブなレポートを生成します。

目標は、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

   
   「図21-3」の説明

関連項目:

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