| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
データベース操作は、バッチ・ジョブまたはデータ抽出、変換およびロード(ETL)処理など、エンド・ユーザーまたはアプリケーション・コードで定義された一連のデータベース・タスクです。データベース操作の定義、監視およびレポート生成が可能です。
この章の内容は次のとおりです。
データベース操作は単純または複合のいずれかです。
単純なデータベース操作は、単一SQL文またはPL/SQLのプロシージャまたはファンクションです。複合データベース操作は、各セクションが独自の開始点および終了点を定義する、データベース・セッションの2つの時点間でのアクティビティです。1つのセッションは、一度に1つの複合データベース操作にのみ関与できます。
Oracle Database 11gで導入されたリアルタイムSQL監視では、1つのSQL文またはPL/SQLプロシージャを監視できます。Oracle Database 12cから、リアルタイム・データベース操作で複合操作を自動的に監視できるようになりました。データベースは、パラレルな問合せ、DML文およびDDL文を、実行の開始と同時に自動的に監視します。デフォルトでは、リアルタイムSQL監視は、SQL文がパラレルに実行されるか、1回の実行で最小で5秒のCPU時間またはI/O時間が消費された場合に自動的に開始されます。
この項の内容は次のとおりです。
関連項目:
データベース操作の概要は、『Oracle Database概要』を参照してください
リアルタイムでのSQLの監視により、単一のSQL文またはPL/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パフォーマンスの問題と言えます。
関連項目:
Oracle Database管理者ガイド
単純操作のリアルタイムSQL監視は、現在実行中のSQL文が実行計画のどの段階にあるのを特定したり、文がその時間をどこで消費しているのかを特定したりするのに役立ちます。また、完了したばかりの文の時間やリソース使用率の内訳を確認できます。このようにして、特定の操作でコストが高くなる理由をより適切に特定できます。
リアルタイムSQL監視の通常のユースケースには次が含まれます。
頻繁に実行されるSQL文の実行速度が通常より遅い場合。この問題の根本原因を特定する必要があります。
データベース・セッションのパフォーマンスが低下する場合。
パラレルSQL文に時間がかかる場合。サーバー・プロセスで作業がどのように分割されているのかを特定する必要があります。
OLTPおよびデータ・ウェアハウス環境では、ジョブによって関連するSQL文が論理的にグループ化されることが多くあります。このジョブには複数のセッションを含むことができます。
データベース操作監視は、最適ではない状態で実行されているジョブを見つけ出して、リソースが消費されている場所を特定する場合に役に立ちます。このように、データベース操作では、関連情報を追跡してパフォーマンス・チューニング時間を改善できます。
複合操作を監視する通常のユースケースには次が含まれます。
多数のSQL文を含む定期的なバッチ・ジョブが一定数の時間内に完了する必要があるものの、予定の2倍の時間がかかった場合。
データベースのアップグレード後に、重要なバッチ・ジョブの実行時間が2倍になった場合。この問題を解決するには、アップグレードの前後にバッチ・ジョブから十分な関連統計データを収集し、2つのデータ・セットを比較した上で、変更を特定する必要があります。
SQLチューニング・セット(STS)のパックに予想よりはるかに長く時間がかかった場合。この問題を診断するには、時間の経過ごとに何が実行されていたのかを把握する必要があります。この問題は簡単に再現できないため、プロセスを実行中に監視する必要があります。
関連項目:
このトピックでは、データベース操作のアーキテクチャおよび属性の概要について説明します。
この項の内容は次のとおりです。
CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータをDIAGNOSTIC+TUNING(デフォルト)に設定するとデータベース操作を監視できます。リアルタイムSQL監視は、Oracle Database Tuning Packの機能の1つです。
次の図には、データベース操作のアーキテクチャの概要が示されています。
図16-1に示すように、DBMS_SQL_MONITORパッケージを使用して、データベース操作を定義することができます。監視が初期化されると、データベース操作に関するメタデータがAWRに格納されます。データベースによる監視統計のリフレッシュは、監視対象の文の実行とほぼリアルタイムで実行されます(通常は1秒に一度)。データベースでは定期的にデータがディスクに保存されます。
V$SQL_MONITORビューには、監視対象のすべてのデータベース操作のエントリがあります。このエントリでは、実行のために収集される主要なパフォーマンス・メトリック(経過時間、CPU時間、読取り/書込み数、I/O待機時間、およびその他の様々な待機時間など)が追跡されます。V$SQL_PLAN_MONITORビューには、監視対象のSQL文の実行計画における各操作の監視統計が含まれています。DBMS_SQL_MONITOR.REPORT_SQL_MONITORを使用してレポートにアクセスできます。これには、Oracle Enterprise Manager Cloud Control(Cloud Control)インタフェースが含まれます。
関連項目:
CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータの詳細は、Oracle Databaseリファレンスを参照してください。
V$SQL_MONITORおよびV$SQL_PLAN_MONITORビューの詳細は、『Oracle Databaseリファレンス』を参照してくださいDBMS_SQLTUNEパッケージおよびDBMS_SQL_MONITORパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
複合データベース操作は、2つの時点間の1つのセッションのアクティビティから成ります。データベース操作間に厳密に1つのセッションが存在します。
このセッションで実行されるSQL文またはPL/SQLプロシージャは複合操作の一部です。複合データベース操作は、データベース・カーネルでも定義できます。通常の複合操作には、SQL*Plusスクリプト、バッチ・ジョブおよびETL処理が含まれます。
データベース操作は、名前および実行IDによって一意に識別されます。
これらの属性は次のように定義されます。
データベース操作名
これは、daily_sales_reportなど、ユーザーが作成する名前です。1つのジョブが異なるセッションや異なるデータベースで同時に実行される場合でも、この名前は同じです。データベース操作名は異なる名前空間には存在しません。
データベース操作実行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値を割り当てることができます。
関連項目:
V$SQL_MONITORビューの詳細は、『Oracle Databaseリファレンス』を参照してください
DBMS_SQL_MONITOR.BEGIN_OPERATIONファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
この項の内容は次のとおりです。
「監視されたSQL実行」には、SQL ID、データベース時間、I/Oリクエストなどの情報が表示されます。
「監視されたSQL実行」ページにアクセスする手順は次のとおりです。
適切な資格証明を使用してCloud Controlにログインします。
「ターゲット」メニューの下で、「データベース」を選択します。
データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。
データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。
「パフォーマンス」メニューから、「SQL監視」を選択します。
「監視されたSQL実行」ページが表示されます。
DBMS_SQL_MONITORパッケージでは、データベース操作の開始と終了を定義し、データベース操作のレポートを生成します。
表16-1 DBMS_SQL_MONITOR
| プログラム・ユニット | 説明 |
|---|---|
|
このファンクションは、実行、レポートの詳細レベルおよびレポート・タイプを指定するいくつかの入力パラメータを受け入れます。指定されたパラメータがない場合は、監視されていた最後の実行のテキスト・レポートがファンクションによって生成されます。 |
|
このファンクションでは、セッションがデータベース操作と関連付けられます。 |
|
このファンクションでは、指定されたデータベース操作の実行からセッションの関連をなくします。 |
関連項目:
DBMS_SQL_MONITORパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
SQL文の実行の統計は、V$SQL_MONITOR、V$SQL_PLAN_MONITORおよびV$SQL_MONITOR_SESSTATの各ビューを使用して監視できます。
表16-2にはこれらのビューの概要が示されています。
表16-2 データベース操作監視のビュー
| ビュー | 説明 |
|---|---|
|
このビューには、データベース操作内の上位SQL文に関する、グローバルで高水準な情報が含まれています。 監視された各SQL文にはこのビューにあるエントリが含まれています。各行にはSQL文が含まれており、このSQL文の統計は、複数のセッションと操作内でのSQL文の実行のすべてから累積されたものです。主キーは、
|
|
このビューには、データベース操作に関連するすべてのセッションの統計が含まれています。 統計のほとんどは累積されます。データベースでは、その統計がXML形式で格納され、各統計に各列が使用されることはありません。このビューは、主にレポートの生成を目的としています。ここでは、 |
|
このビューには、監視対象のSQL文の実行計画における各ステップの監視統計が含まれています。 SQL文が実行されている間、データベースでは |
前述のビューと次のビューを使用して、監視された実行に関する追加情報を取得できます。
V$ACTIVE_SESSION_HISTORY
V$SESSION
V$SESSION_LONGOPS
V$SQL
V$SQL_PLAN
関連項目:
データベース操作監視のV$ビューの詳細は、Oracle Databaseリファレンスを参照してください
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リファレンス』を参照してください
CONTROL_MANAGEMENT_PACK_ACCESS初期化パラメータをDIAGNOSTIC+TUNINGに設定すると、ヒントを使用して特定のSQL文の監視を有効化または無効化できます。
MONITORヒントでは監視を有効化する一方で、NO_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言語リファレンス』を参照してください
データベース操作の作成では、その開始ポイントと終了ポイントを明示的に定義します。
DBMS_SQL_MONITOR.BEGIN_OPERATIONファンクションを使用してデータベース操作を開始し、DBMS_SQL_MONITOR.END_OPERATIONプロシージャを使用してデータベース操作を終了できます。
前提条件
このチュートリアルでは、次のことが前提となっています。
管理者として、sh.sales表の項目数とsh.customers表の顧客数を問い合せます。
これら2つの問合せをsh_countという名前のデータベース操作として監視します。
データベース操作を作成する手順は次のとおりです。
SQL*Plusを起動し、適切な権限を持つユーザーとして接続します。
実行IDを保持する変数を定義します。
たとえば、次のSQL*Plusコマンドを実行します。
VAR eid NUMBER
データベース操作を開始します。
たとえば、BEGIN_OPERATIONファンクションを次のように実行します。
EXEC :eid := DBMS_SQL_MONITOR.BEGIN_OPERATION('sh_count');
操作内で問合せを実行します。
たとえば、次の文を実行します。
SQL> SELECT count(*) FROM sh.sales;
COUNT(*)
----------
918843
SQL> SELECT COUNT(*) FROM sh.customers;
COUNT(*)
----------
55500
データベース操作を終了します。
たとえば、END_OPERATIONプロシージャを次のように実行します。
EXEC DBMS_SQL_MONITOR.END_OPERATION('sh_count', :eid);
データベース操作が完了したことを確認します。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT SUBSTR(DBOP_NAME, 1, 10), DBOP_EXEC_ID,
SUBSTR(STATUS, 1, 10)
FROM V$SQL_MONITOR
WHERE DBOP_NAME IS NOT NULL
ORDER BY EXEC_ID;
DBOP_NAME EXEC_ID STATUS
---------- ---------- ----------
sh_count 1 DONE
関連項目:
DBMS_SQL_MONITORパッケージの詳細は、『Oracle Database PL/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の実行を監視する手順は次のとおりです。
「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リファレンスを参照してください
