1. 2日でパフォーマンス・チューニング・ガイド
  2. SQLチューニング
  3. 高負荷のSQL文の識別

11 高負荷のSQL文の識別

高負荷SQL文は不均衡な量のシステム・リソースを消費します。これらのSQL文はデータベースのパフォーマンスに多大な影響を与える頻度が高く、パフォーマンスおよびリソース使用量を最適化するためにチューニングする必要があります。データベースが正常にチューニングされた場合でも、非効率的なSQLがパフォーマンスを著しく低下させることがあります。

高負荷SQL文の識別は定期的に実行する必要がある重要なSQLチューニング・アクティビティです。自動データベース診断モニター(ADDM)により、事前に潜在的な高負荷SQL文を識別することで、このタスクが自動化されます。また、Oracle Enterprise Manager Cloud Control (Cloud Control)を使用して、詳細な調査を必要とする高負荷SQL文を識別できます。高負荷SQL文を識別した後、SQLチューニング・アドバイザおよびSQLアクセス・アドバイザを使用してこれらをチューニングできます。

この章では高負荷SQL文の識別方法を説明しています。次の項で構成されています。

11.1 ADDMの検出結果に基づく高負荷SQL文の識別

デフォルトで、ADDMが1時間に1回事前に実行されます。これにより自動ワークロード・リポジトリ(AWR)によって最近1時間に収集された主要統計が分析され、高負荷SQL文を含むあらゆるパフォーマンスの問題を識別します。パフォーマンスの問題が見つかった場合は、それらの問題がADDMの結果として自動データベース診断モニター(ADDM)ページに表示されます

ADDMにより、各ADDM結果と推奨事項が提供されます。高負荷SQL文が識別されると、SQL文でのSQLチューニング・アドバイザの実行などの推奨事項がADDMにより提供されます。「SQL文のチューニング」 の説明に従って、SQL文のチューニングを開始できます。

参照:

11.2 上位SQLに基づく高負荷SQL文の識別

ADDMはシステム規模のパフォーマンス低下を引き起こす可能性のある高負荷SQL文を自動的に識別します。通常の状況では、高負荷SQL文の手動による識別は不要です。ただし、場合によってはより大まかなレベルでSQL文を監視することがあります。Cloud Controlのトップ・アクティビティ・ページの「上位SQL」セクションにより高負荷SQL文を5分間隔で識別できます。

図11-1は、「トップ・アクティビティ」ページの例を示しています。このページには、データベースで実行される上位アクティビティの1時間の時間軸が表示されます。データベース・アクティビティの高い割合を使用するSQL文は、「上位SQL」セクションの下にリストされ、5分間隔で表示されます。

図11-1 「トップ・アクティビティ」ページ

図11-1の説明が続きます
「図11-1 「トップ・アクティビティ」ページ」の説明

「トップ・アクティビティ」ページへのアクセス手順:

  1. データベース・ホームページにアクセスします。

    詳細は、「データベースのホームページのアクセス」を参照してください。

  2. 「パフォーマンス」メニューから、「トップ・アクティビティ」を選択します。

    「データベース・ログイン」ページが表示されたら、管理者権限のあるユーザーとしてログインします。「トップ・アクティビティ」ページが表示されます。

  3. 5分間隔を移動するには、影付きボックスを目的の時間にドラッグします。

    「上位SQL」セクションに含まれる情報は自動的に更新され、選択した時間間隔を反映します。

  4. 1時間より長い期間でSQL文を監視するには、「データの表示」リストから「履歴」を選択します。

    「履歴」ビューで、AWRの保存期間で定義された期間での上位SQL文を表示できます。

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

11.2.1 SQL文の待機クラスごとの表示

「トップ・アクティビティ」ページの「上位SQL」セクションに表示されるSQL文は、「トップ・アクティビティ」グラフの凡例に従って対応するクラスに基づいて様々な待機クラスに分類されます。

特定の待機クラスのSQL文を表示するには、次のステップを実行します。

  1. データベース・ホームページにアクセスします。

    詳細は、「データベースのホームページのアクセス」を参照してください。

  2. 「パフォーマンス」メニューから、「トップ・アクティビティ」を選択します。

    「データベース・ログイン」ページが表示されたら、管理者権限のあるユーザーとしてログインします。「トップ・アクティビティ」ページが表示されます。

  3. 「トップ・アクティビティ」グラフで、待機クラスのグラフのカラー・ブロックまたは凡例で対応する待機クラスをクリックします。

    選択されている待機クラスの実行中のアクティブ・セッション・ページが表示されます。「上位SQL」セクションは、指定されている待機クラスのSQL文のみを表示するように自動的に更新されます。

参照:

11.2.2 SQL文の詳細の表示

「トップ・アクティビティ」ページの「上位SQL」セクションには、選択した5分間隔内で実行されたSQL文がリソース使用量に基づいて降順で表示されます。この表の上部にあるSQL文は、この期間に最もリソースが集中したSQL文を表し、次に2番目に集中したSQL文が続きます。

SQL文の詳細の表示手順:

  1. データベース・ホームページにアクセスします。

    詳細は、「データベースのホームページのアクセス」を参照してください。

  2. 「パフォーマンス」メニューから、「トップ・アクティビティ」を選択します。

    「データベース・ログイン」ページが表示されたら、管理者権限のあるユーザーとしてログインします。「トップ・アクティビティ」ページが表示されます。

  3. 「上位SQL」セクションで、SQL文の「SQL ID」リンクをクリックします。

    選択したSQL文に対して「SQLの詳細」ページが表示されます。

  4. より長い期間のSQLの詳細を表示するには、「データの表示」リストから「履歴」を選択します。

    sql_details_historical.gifの説明が続きます。
    sql_details_historical.gifの説明

    AWRの保存期間で定義された期間に応じて過去のSQLの詳細を表示できます。

  5. 「テキスト」セクションで、SQL文のSQLテキストを確認します。

    「テキスト」セクションには選択したSQL文に対するSQLテキストが含まれます。SQL文の一部が表示されると、プラス記号(+)のアイコンが「テキスト」ヘッダーの横に表示されることに注意してください。SQL文全体のテキストを表示するには、プラス記号(+)のアイコンをクリックします。

    図11-1では、SQL文ddthrb7j9a63fのテキストは次のとおりです。 

    SELECT E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM   HR.EMPLOYEES E, HR.DEPARTMENTS D, HR.JOBS J
WHERE  E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND    E.JOB_ID = J.JOB_ID 
AND    E.LAST_NAME LIKE 'A%'

  6. 「詳細」セクションの「計画ハッシュ値」リストで、次のいずれかの操作を行います。

    • SQL文に複数の計画がある場合は、「すべて」を選択して、すべての計画のSQLの詳細を表示します。

    • 特定の計画を選択して、その計画のみのSQLの詳細を表示します。

  7. 次の項の説明に従って、「SQLの詳細」ページの使用可能なサブページにアクセスし、SQL文に関するより多くの情報を表示します。

  8. SQL文が高負荷SQL文である場合は、「SQL文のチューニング」 の説明に従って、チューニングします。

11.2.2.1 SQL統計の表示

「SQLの詳細」ページの「統計」サブページには、SQL文の統計情報が表示されます。

SQL文の統計の表示手順:

  1. 「SQLの詳細」ページにアクセスします(「SQL文の詳細の表示」を参照)。

  2. 「詳細」で、「統計」をクリックします。

    統計サブページが表示されます。

    sql_details_statistics.gifの説明が続きます。
    sql_details_statistics.gifの説明

  3. 次の項の説明に従って、SQL文の統計を表示します。

11.2.2.1.1 SQL統計のサマリー

「サマリー」セクションにはグラフのSQL統計およびSQLアクティビティが表示されます。

「実行時間」ビューで、「アクティブ・セッション」グラフに過去1時間にSQL文を実行したアクティブ・セッションの平均数が表示されます。SQL文に複数の計画があり、「計画ハッシュ値」リストで「すべて」が選択されている場合、グラフには異なる色で各計画が表示されます。これにより、計画が変更されたかどうか、またこれがパフォーマンスの低下の原因かどうかを簡単に見分けられます。また、特定の計画を選択してその計画のみを表示することもできます。

「履歴」ビューでは、グラフに異なるディメンションで実行統計が表示されます。実行統計を表示するには、「表示」リストから該当するディメンションを選択します。

  • 1実行当たりの経過時間

  • 1時間当たりの実行数

  • 1実行当たりのディスク読取り

  • 1実行当たりのバッファ読取り

この操作により、異なるディメンションを使用したSQL文のレスポンス時間の追跡が可能になります。SQL文のパフォーマンスの低下が、選択されたディメンションに起因しているかどうかを決定できます。

特定の期間に対するSQL文の統計を表示するには、グラフの下のスナップショット・アイコンをクリックします。矢印を使用してスクロールすると、目的のスナップショットの位置に移動できます。

11.2.2.1.2 一般的なSQL統計

「一般」セクションでは次の情報がリストされ、SQL文の起点を識別できます。

  • モジュール(DBMS_APPLICATION_INFOパッケージの使用を指定した場合)

  • アクション(DBMS_APPLICATION_INFOパッケージの使用を指定した場合)

  • SQL文の実行に使用された解析スキーマ、またはデータベース・アカウント

  • PL/SQLソース(SQL文がPL/SQLプログラムの一部だった場合はそのコード行)

11.2.2.1.3 待機ごとのアクティビティの統計および時間ごとのアクティビティの統計

「待機ごとのアクティビティ」セクションおよび「時間ごとのアクティビティ」セクションでは、SQL文が時間の大部分をどのように費やしたかを識別できます。「待機ごとのアクティビティ」セクションでは、CPUおよび残っている待機によって消費される経過時間の量がグラフィカルに表示されます。「時間ごとのアクティビティ」セクションでは、経過時間の合計がCPU時間および待機時間に毎秒分割されます。

11.2.2.1.4 経過時間ブレークダウンの統計

「経過時間ブレークダウン」セクションでは、SQL文自体が時間を大量に消費しているかどうか、または発信元プログラムやアプリケーションがPL/SQLまたはJavaエンジンを使用した時間のために経過時間の合計が長くなったのかどうかを識別できます。PL/SQL時間またはJava時間が経過時間の大部分を占める場合、SQLのチューニングでは大きな改善はできない可能性があります。かわりに、アプリケーションを調査して、PL/SQL時間またはJava時間を削減できるかどうかを判別する必要があります。

11.2.2.1.5 共有カーソル統計および実行統計

「共有カーソル統計」セクションおよび「実行統計」セクションではSQL実行プロセスの様々なステージの効率性に関連する情報を提供します。

11.2.2.1.6 その他のSQL統計

「その他統計」セクションでは、平均の永続メモリーおよびランタイム・メモリーなど、SQL文の詳細が表示されます。

11.2.2.2 セッション・アクティビティの表示

「アクティビティ」サブページにはセッション・アクティビティがグラフィカルに表示されています。

SQL文のセッション・アクティビティの表示手順:

  1. 「SQLの詳細」ページにアクセスします(「SQL文の詳細の表示」を参照)。

  2. 「詳細」で、「アクティビティ」をクリックします。

    「SQLの詳細」ページが表示され、「アクティビティ」サブページが表示されます。

    sql_details_activity.gifの説明が続きます。
    sql_details_activity.gifの説明

    「アクティビティ」サブページにはSQL文を実行する各種セッションの詳細が表示されます。「アクティブ・セッション」グラフには、一定期間のアクティブ・セッション数の平均が表示されます。

  3. 影付きのボックスをドラッグして5分間隔を選択することもできます。

    「選択した5分間隔の詳細」セクションには、選択した5分間隔の間にSQL文が実行されたセッションがリストされています。「アクティビティ%」列にある複数色の棒は、SQL文の実行中に各セッションに分割されたデータベース時間の割当てを表しています。

  4. オプションで、表示するセッションの「SID」列のリンクをクリックして「セッションの詳細」ページを表示します。

参照:

  • セッション・アクティビティおよび詳細の監視方法については、「上位セッションの監視」を参照してください。

11.2.2.3 SQL実行計画の表示

SQL文の実行計画は、文を実行するためにOracle Databaseで行われる操作の順序です。「プラン」サブページに、SQL文に対する実行計画がグラフ・ビューおよび表ビューで表示されます。

SQL文の実行計画の表示手順:

  1. 「SQLの詳細」ページにアクセスします(「SQL文の詳細の表示」を参照)。

  2. 「詳細」で、「プラン」をクリックします。

    「SQLの詳細」ページが表示され、「プラン」サブページが表示されます。

  3. グラフ・ビューでSQL実行を表示するには、「グラフィカル」をクリックします。

    sql_details_plan_graph.gifの説明が続きます。
    sql_details_plan_graph.gifの説明

  4. オプションで、グラフにある操作を選択して、実行計画に表示される操作の詳細を表示します。

    「選択の詳細」セクションをリフレッシュすると、選択した操作の詳細が表示されます。

  5. 選択した操作が表などの特定のデータベース・オブジェクト上にある場合、「オブジェクト」リンクをクリックすると、データベース・オブジェクトのより詳細な情報を表示できます。

  6. 表ビューでSQL実行を表示するには、「表」をクリックします。

    「プラン」サブページがリフレッシュされ、実行計画が表で表示されます。

    sql_details_plan_table.gifの説明が続きます。
    sql_details_plan_table.gifの説明

    クエリー・リライトは、マスター表について記述されたユーザー要求を、マテリアライズド・ビューを含む同等の要求に変換する最適化手法です。データベースではリライト付きとリライトなしの問合せのコストを比較し、費用の少ない方を選択します。リライトが必要な場合、クエリー・リライトおよびそのコスト・ベネフィットが「リライトの説明」セクションに表示されます。

参照:

11.2.2.4 計画管理の表示

「計画管理」サブページには、次の項目に関する情報が表示されます。

  • SQLプロファイル

    SQLプロファイルにはSQL文の追加統計が含まれます。オプティマイザはこれらの統計を使用して、文に対するより適切な実行計画を生成します。

  • SQLパッチ

    SQLパッチは、単一のSQL文のエラーまたはパフォーマンスの問題を修正するために、自動的に生成されます。

  • SQL計画ベースライン

    SQL計画ベースラインは、指定されたSQL文について許容可能なパフォーマンスを持つことが実証された実行計画です。

計画管理に関する情報の表示手順:

  1. 「SQLの詳細」ページにアクセスします(「SQL文の詳細の表示」を参照)。

  2. 「詳細」で、「計画管理」をクリックします。

    「SQLの詳細」ページが表示され、「計画管理」サブページが表示されます。

  3. 計画に関連する情報を確認します。

    次の例では、オプティマイザで、SQL文についてSTMT01というSQL計画ベースラインが使用されています。

    sql_details_plan_control.gifの説明が続きます。
    sql_details_plan_control.gifの説明

参照:

11.2.2.5 チューニング履歴の表示

「SQLチューニング履歴」セクションにSQLチューニング・アドバイザまたはSQLアクセス・アドバイザのタスクの履歴が表示されます。

SQLチューニング履歴の表示手順:

  1. 「SQLの詳細」ページにアクセスします(「SQL文の詳細の表示」を参照)。

  2. 「詳細」で、「チューニング履歴」をクリックします。

    「SQLの詳細」ページが表示され、「チューニング履歴」サブページが表示されます。

  3. チューニング履歴に関する情報を確認します。

    「SQLチューニング履歴」セクションにSQLチューニング・アドバイザまたはSQLアクセス・アドバイザのタスクの履歴が表示されます。

    「このSQLの履歴期間中のADDM結果」セクションに、SQL文に関連付けられたADDM結果の発生数が表示されます。

    次の例は、SQLチューニング・タスクがユーザーDBA1によって2012年2月9日に実行されたことを示しています。

    sql_details_tuning.gifの説明が続きます。
    sql_details_tuning.gifの説明

参照: