1. SQLチューニング・ガイド
  2. 自動SQLチューニング
  3. SQLチューニング・アドバイザを使用したSQLの分析

25 SQLチューニング・アドバイザを使用したSQLの分析

SQLチューニング・アドバイザを使用すると、高負荷SQL文のパフォーマンスを向上するための推奨事項を取得し、最適な計画を実行することによってパフォーマンスの低下を防ぐことができます。

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

25.1 SQLチューニング・アドバイザについて

SQLチューニング・アドバイザは、Oracle Database Tuning Packに含まれるSQL診断ソフトウェアです。

アドバイザの入力として1つ以上のSQL文を発行し、文のチューニング方法に関するアドバイスや推奨事項、および理論的根拠や予測されるメリットを受け取ることができます。

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

25.1.1 SQLチューニング・アドバイザの目的

SQLチューニング・アドバイザは、パフォーマンスが最適でないSQL文に関する問題を解決するためのメカニズムです。

SQLチューニング・アドバイザを使用すると、高負荷SQL文のパフォーマンスを向上するための推奨事項を取得し、最適な計画を実行することによってパフォーマンスの低下を防ぐことができます。

次のようなチューニングの推奨事項があります。

  • オブジェクト統計の収集

  • 索引の作成

  • SQL文のリライト

  • SQLプロファイルの作成

  • SQL計画ベースラインの作成

SQLチューニング・アドバイザによって生成される推奨事項は、次に示す特定の目標の達成に役立ちます。

  • 手間のかかる手動チューニングを回避します。

    高付加SQL文の識別とチューニングは、専門家にとっても困難な作業です。SQLチューニング・アドバイザは、オプティマイザを使用して自動的にSQLをチューニングします。

  • 自動的に推奨事項を生成し、SQLプロファイルを実装します。

    夜間のメンテナンス・ウィンドウで実行されるように、自動SQLチューニング・タスクを構成できます。このように起動すると、アドバイザは自動的に推奨事項を生成し、SQLプロファイルを実装できます。

  • データベースによって生成された統計を分析して最適な計画を作成します。

    データベースには、それ自体の操作に関する大量の統計が含まれます。SQLチューニング・アドバイザは、内部情報の詳細なマイニングと分析を実行して、実行計画を最適化できます。

  • 開発者がSQLを本番システムではなくテスト・システム上でチューニングできるようにします。

    本番データベースにパフォーマンスが最適でないSQL文がある場合、開発者が直接本番システム上で調査およびチューニングを実行することは望ましくありません。DBAにより、開発者が安全に分析およびチューニングできるテスト用データベースに、問題のあるSQL文を転送できます。

複数の文をチューニングする場合、SQLチューニング・アドバイザは文間の相互依存を認識しません。SQLチューニング・アドバイザを多数の文に対して実行すると、チューニングに関する推奨事項を取得できます。

ノート:

SQLチューニング・アドバイザをプラガブル・データベースとともに使用すると、データの可視性と権限の要件一致しない場合があります。アドバイザは、現在のプラガブル・データベース(PDB)およびこの問合せが実行されている別のPDBで、問合せを調整できます。PDB管理者が1つのPDBしかチューニングできないのに対して、この方法では、コンテナ・データベース(CDB)管理者が、同じ問合せを多数のPDBで同時に調整できます。

関連項目:

25.1.2 SQLチューニング・アドバイザのアーキテクチャ

自動チューニング・オプティマイザは、SQLチューニング・アドバイザによって中心的に使用されるツールです。アドバイザは、複数のソースから入力としてSQL文を受け入れ、オプティマイザを使用してこれらの文を分析して推奨できます。

ハード解析のたびに自動チューニング・オプティマイザを起動すると、時間とリソースが大幅に消費されます。チューニング・モードの対象となるのは、データベースのパフォーマンスに大きく影響を与える複雑な高負荷SQL文です。

SQLチューニング・アドバイザなどの管理性アドバイザは、アドバイザ・フレームワークと呼ばれる共通インフラストラクチャを使用します。このフレームワークは、タスク・オブジェクトを格納するための共通スキーマおよびインタフェースを提供します。アドバイザ・スキーマは、アドバイザからデータを格納する一連の表です。SQLチューニング・アドバイザは、チューニング入力を受け取り、アドバイザ・フレームワークによってアドバイザ・スキーマに書き込みます。SQLチューニング・アドバイザは、レポートの作成時にアドバイザ・スキーマからデータを読み取ります。

次の図は、SQLチューニング・アドバイザの基本的なアーキテクチャを示しています。

図25-1 SQLチューニング・アドバイザのアーキテクチャ

図25-1の説明が続きます
「図25-1 SQLチューニング・アドバイザのアーキテクチャ」の説明

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

関連項目:

「SQLの解析」

25.1.2.1 SQLチューニング・アドバイザへの入力

SQLチューニング・アドバイザの入力は、ADDM、AWR、共有SQL領域およびSQLチューニング・セットを含む複数のソースから取り込むことができます。

SQLチューニング・アドバイザは、次のような入力ソースを使用します。

  • 自動データベース診断モニター(ADDM)

    SQLチューニング・アドバイザの主な入力ソースは、ADDM(アダムと読みます)です。デフォルトで、ADDMが1時間に1回事前に実行されます。高負荷のSQL文を含むパフォーマンスの問題を識別するため、ADDMにより、過去1時間に自動ワークロード・リポジトリ(AWR)により収集された主要統計が分析されます。高負荷のSQL文が識別されると、そのSQLに対してSQLチューニング・アドバイザを実行するようにADDMにより推奨されます。

  • AWR

    AWRは、CPU使用率や待機時間など、関連統計でランク付けされた高負荷のSQL文を含むシステム・アクティビティについて、通常のスナップショットを作成します。

    AWRを参照して負荷の高いSQL文を手動で識別できます。これらの文に対してSQLチューニング・アドバイザを実行できますが、この操作は自動SQLチューニングの一環としてOracle Databaseによって自動的に実行されます。デフォルトで、AWRには過去8日間のデータが保持されます。この方法を使用してAWRの保存期間内に実行された負荷の高いSQLを見つけてチューニングできます。

  • 共有SQL領域

    共有SQL領域を使用して、AWRにまだ取得されていない最近のSQL文をチューニングします。共有SQL領域とAWRは、現在の時刻からAWRの許容保存期間(デフォルトは8日以上)までの高負荷のSQL文を識別してチューニングする機能を提供します。

  • SQLチューニング・セット

    SQLチューニング・セット(STS)は、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。STSには、パフォーマンスを個別に測定することや、パフォーマンスが予測より低下しているSQL文を識別することを目的として、まだデプロイされていないSQL文を含めることができます。SQL文のセットを入力として使用する場合、最初にSTSを作成し、使用する必要があります。

関連項目:

25.1.2.2 SQLチューニング・アドバイザの出力

SQL文を分析した後、SQLチューニング・アドバイザは推奨事項を公開します。

具体的には、SQLチューニング・アドバイザは、次のタイプの出力を生成します。

  • 実行計画の最適化に関するアドバイス

  • 提案した最適化の理論的根拠

  • 推定パフォーマンス向上率

  • アドバイスを実装するためのSQL文

各推奨事項の向上率は、次の式を使用して計算されます。

abnf% = (time_old - time_new)/(time_old)

たとえば、チューニング前の実行時間が100秒であり、推奨事項を実装した後の新しい実行時間が推定で33秒になるものとします。このパフォーマンス向上率の計算式は、次のようになります。

67% = (100 - 33)/(100)

SQL文を最適化するには、推奨事項を受け入れるかどうかを選択します。自動SQLチューニング・アドバイザは、ユーザーの操作を必要とせずに、SQLプロファイルの推奨事項を実装して文をチューニングするように構成できます。オンデマンドで起動された場合、SQLチューニング・アドバイザはユーザーがSQLプロファイルを実装することを推奨できますが、それを自動的に実装できません。

25.1.2.3 自動チューニング・オプティマイザの分析

チューニング・モードでは、オプティマイザがオプションを考慮し、統計を収集するための時間がより長く与えられます。たとえば、自動チューニング・オプティマイザは動的統計と文の部分的な実行を使用できます。

次の図は、自動チューニング・オプティマイザが実行する様々なタイプの分析を示しています。

図25-2 自動チューニング・オプティマイザ

図25-2の説明が続きます
「図25-2 自動チューニング・オプティマイザ」の説明

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

関連項目:

「問合せオプティマイザの概念」

25.1.2.3.1 統計分析

オプティマイザは、実行計画を生成する際にオブジェクトの統計に依存します。

このような統計が失効しているか存在しない場合、最適でない計画がオプティマイザから生成される可能性があります。自動チューニング・オプティマイザは、存在しない統計または失効している統計があるかどうか、各問合せオブジェクトをチェックし、必要に応じて新しい統計の収集を推奨します。

次の図は、統計分析のプロセスを示しています。

図25-3 自動チューニング・オプティマイザによる統計分析

図25-3の説明が続きます
「図25-3 自動チューニング・オプティマイザによる統計分析」の説明
25.1.2.3.2 SQLプロファイリング

SQLプロファイリングは、自動チューニング・オプティマイザが自身の見積りを検証することです。

実行履歴を確認し、SQLをテストすることによって、オプティマイザは自身が実行計画を生成するための最も正確な情報を保持しているかどうかを確認できます。SQLプロファイリングは、SQLチューニング・アドバイザから推奨事項を生成し、それらを実装するステップに関連していますが、そのステップとは異なります。

次の図は、SQLチューニング・アドバイザがSQLプロファイルを推奨し、それを自動的に実装する様子を示しています。プロファイルの作成後、オプティマイザは、実行計画を生成する際の追加入力としてそれを使用できます。

図25-4 SQLプロファイル

図25-4の説明が続きます
「図25-4 SQLプロファイル」の説明

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

関連項目:

「SQLプロファイルについて」

25.1.2.3.2.1 SQLプロファイリングの動作

データベースでは、一部のDMLおよびDDL文をプロファイルできます。

具体的には、SQLチューニング・アドバイザは、次のタイプの文をプロファイルできます。

  • DML文(SELECT、INSERTとSELECT句、UPDATE、DELETEおよびMERGEの更新または挿入操作)

  • CREATE TABLE文(AS SELECT句の場合のみ)

分析を実行した後、SQLチューニング・アドバイザはSQLプロファイルの実装を推奨または推奨しません。

次の図は、SQLプロファイリングのプロセスを示しています。

図25-5 SQLプロファイリング

図25-5の説明が続きます
「図25-5 SQLプロファイリング」の説明

SQLプロファイリング中、オプティマイザは文のコスト、選択性およびカーディナリティを検証します。オプティマイザは、次のいずれかの方法を使用します。

  • データのサンプルを取得し、適切な述語をサンプルに適用します。

    オプティマイザは、新しい見積りを通常の見積りと比較し、その違いが大きい場合は修正ファクタを適用します。

  • SQL文の一部を実行します。

    述語によって効率的なアクセス・パスが提供される場合、この方法はサンプルを取得する方法よりも効率的です。

オプティマイザは、過去の文の実行履歴を使用して、正しい設定を特定します。たとえば、通常SQL文が部分的にのみ実行されることが履歴からわかった場合、オプティマイザはALL_ROWS最適化ではなくFIRST_ROWSを使用します。

関連項目:

オプティマイザの目標の選択

25.1.2.3.2.2 SQLプロファイルの実装

統計分析またはSQLプロファイリング中にオプティマイザから補足情報が生成された場合、オプティマイザはSQLプロファイルの実装を推奨します。

図25-6に示すとおり、次のオプションが可能です。

  • SQLチューニング・アドバイザをオンデマンドで実行する場合、ユーザーはSQLプロファイルを実装するかどうかを選択する必要があります。

  • 自動SQLチューニング・タスクがSQLプロファイルを自動的に実装するように構成されている場合、アドバイザの動作は、ACCEPT_SQL_PROFILEチューニング・タスク・パラメータの設定によって異なります。

    • trueに設定した場合、アドバイザはSQLプロファイルを自動的に実装します。

    • falseに設定した場合、ユーザーの操作が必要になります。

    • AUTO (デフォルト)に設定した場合、この設定は、1つ以上のSQL文がSQLプロファイルとともに存在する場合はtrue、この条件が満たされない場合はfalseになります。

    ノート:

    自動SQLチューニング・タスクは、自動的にSQL計画ベースラインを作成したり、それらに計画を追加したりできません。

図25-6 SQLプロファイルの実装

図25-6の説明が続きます
「図25-6 SQLプロファイルの実装」の説明

自動SQLチューニングの実行中または実行後の任意のタイミングで、レポートを表示できます。このレポートには、分析されたSQL文、生成された推奨事項、および自動的に実装されたSQLプロファイルの詳細が含まれます。

関連項目:

25.1.2.3.3 アクセス・パス分析

アクセス・パスは、データベースによってデータが取り出される手段です。

たとえば、索引を利用した問合せと全表スキャンを利用した問合せでは、使用されるアクセス・パスが異なります。索引を使用すると全表スキャンを回避できるため、場合によってはSQL文のパフォーマンスを大幅に改善できます。次の図は、アクセス・パス分析を示しています。

図25-7 アクセス・パス分析

図25-7の説明が続きます
「図25-7 アクセス・パス分析」の説明

自動チューニング・オプティマイザは、新しい索引によって問合せのパフォーマンスが向上するかどうかを調べ、次のいずれかを推奨します。

  • 索引の作成

    索引の推奨事項は、SQLチューニング・アドバイザによって処理されるSQL文に固有の推奨事項です。新しい索引によって、単一のSQL文に関連するパフォーマンスの問題が簡単に解決される場合があります。

  • SQLアクセス・アドバイザの実行

    自動チューニング・オプティマイザでは、索引に関する推奨事項がSQL全体のワークロードにどのように影響するかは分析されないため、典型的なSQLワークロードを持つSQL文に対してSQLアクセス・アドバイザを実行することも推奨されます。SQLアクセス・アドバイザは、索引作成がSQL全体のワークロードに与える影響を調べてから、推奨事項を作成します。

25.1.2.3.4 SQL構造分析

構造分析中、自動チューニング・オプティマイザは、パフォーマンスの低下につながる可能性がある構文、意味または設計上の問題の識別を試みます。この目的は、記述が適切でないSQL文を識別し、それらの再構成方法に関するアドバイスを提供することです。

次の図は、構造分析を示しています。

図25-8 構造分析

図25-8の説明が続きます
「図25-8 構造分析」の説明

構文の一部のバリエーションは、パフォーマンスが低下する原因となります。構造分析において、自動チューニング・オプティマイザは一連のルールに対して文を評価し、効率的でないコーディング手法を識別した後、可能な場合は代替文を推奨します。

図25-8に示すとおり、自動チューニング・オプティマイザは、次のカテゴリの構造的な問題を識別します。

  • SQLコンストラクタの使用方法が効率的でない

    パフォーマンスが低い文では、NOT EXISTSではなくNOT IN、またはUNION ALLではなくUNIONが使用されている場合があります。UNION演算子は、UNION ALL演算子とは対照的に、結果セットに重複行が含まれないように特別なソートを使用します。2つの問合せから重複行が戻されないことがわかっている場合は、UNION ALLを使用します。

  • データ型が一致しない

    索引付けされた列と、比較する値のデータ型が一致しない場合、暗黙的なデータ型の変換が発生するため、データベースは索引を使用しません。また、データベースはデータ型の変換に追加のリソースを消費する必要があり、データ値が正しく変換されないため、一部のSQL文が失敗することがあります。よくある誤りとして、数値データが含まれているが算術演算に使用されない列(電話番号、クレジット・カード番号、小切手番号など)があります。カーディナリティが低い見積り、最適ではない計画、およびORA-01722エラーを回避するには、開発者は、バインド変数が数値型ではなくVARCHAR2型であることを確認する必要があります。

  • 設計に誤りがある

    設計の誤りの典型的な例は、結合条件の欠落です。問合せブロック内の表の数がnの場合、デカルト積を回避するためにn-1個の結合条件が存在する必要があります。

いずれの場合も、自動チューニング・オプティマイザは、文の再構成に関連する提案を行います。提案される代替文は、元の文と類似していますが同じではありません。たとえば、提案される文では、UNIONではなくUNION ALLが使用される場合があります。このとき、提案された事項が適切であるかどうかを判断します。

25.1.2.3.5 代替計画分析

SQLチューニング・アドバイザは、SQL文をチューニングする際に、リアルタイムおよび履歴パフォーマンス・データに文の代替実行計画があるかどうかを検索します。

最初の計画以外の計画が存在する場合、SQLチューニング・アドバイザは、代替計画が見つかったことを報告します。次の図は、SQLチューニング・アドバイザが2つの代替計画を見つけて、代替計画の検出を生成する様子を示しています。

図25-9 代替計画分析

図25-9の説明が続きます
「図25-9 代替計画分析」の説明

SQLチューニング・アドバイザは、代替実行計画を検証し、再生可能でない計画がある場合は通知します。再生可能な代替計画が見つかった場合、SQL計画ベースラインを作成して、将来これらの計画を選択するようオプティマイザに指示できます。

例25-1 代替計画の検出

次の例に、SELECT文の代替計画の検出を示します。 

2- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.
 
  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.
 
  id plan hash  last seen            elapsed (s)  origin          note
  -- ---------- -------------------- ------------ --------------- ----------------
   1 1378942017  2009-02-05/23:12:08        0.000 Cursor Cache    original plan
   2 2842999589  2009-02-05/23:12:08        0.002 STS
 
  Information
  -----------
  - The Original Plan appears to have the best performance, based on the
    elapsed time per execution.  However, if you know that one alternative
    plan is better than the Original Plan, you can create a SQL plan baseline
    for it. This will instruct the Oracle optimizer to pick it over any other
    choices in the future.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_XXXXX',
            object_id => 2, task_owner => 'SYS', plan_hash => xxxxxxxx);

前述の例では、SQLチューニング・アドバイザにより、2つの計画が見つかりました(共有SQL領域とSQLチューニング・セットに各1個)。共有SQL領域の計画は、最初の計画と同じです。

SQLチューニング・アドバイザは、最初の計画の経過時間が代替計画よりも劣る場合のみ代替計画を推奨します。この場合、SQLチューニング・アドバイザは、パフォーマンスの最も優れている計画で、SQL計画ベースラインを作成するよう推奨します。例25-1では、代替計画よりも最初の計画のほうがパフォーマンスが優れているため、SQLチューニング・アドバイザは代替計画の使用を推奨していません。

例25-2 代替計画セクション

この例では、SQLチューニング・アドバイザ出力の代替計画セクションに、最初の計画と代替計画が含まれ、それらのパフォーマンスが要約されています。最も重要な統計は、経過時間です。最初の計画では索引が使用されていますが、代替計画では全表スキャンが使用され、経過時間が.002秒だけ増加しています。

Plan 1
------
 
  Plan Origin                 :Cursor Cache
  Plan Hash Value             :1378942017
  Executions                  :50
  Elapsed Time                :0.000 sec
  CPU Time                    :0.000 sec
  Buffer Gets                 :0
  Disk Reads                  :0
  Disk Writes                 :0
 
Notes:
  1. Statistics shown are averaged over multiple executions.
  2. The plan matches the original plan.
 
--------------------------------------------
| Id  | Operation            | Name        |
--------------------------------------------
|   0 | SELECT STATEMENT     |             |
|   1 |  SORT AGGREGATE      |             |
|   2 |   MERGE JOIN         |             |
|   3 |    INDEX FULL SCAN   | TEST1_INDEX |
|   4 |    SORT JOIN         |             |
|   5 |     TABLE ACCESS FULL| TEST        |
--------------------------------------------
 
Plan 2
------
 
  Plan Origin                 :STS
  Plan Hash Value             :2842999589
  Executions                  :10
  Elapsed Time                :0.002 sec
  CPU Time                    :0.002 sec
  Buffer Gets                 :3
  Disk Reads                  :0
  Disk Writes                 :0
 
Notes:
  1. Statistics shown are averaged over multiple executions.
 
-------------------------------------
| Id  | Operation           | Name  | 
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  SORT AGGREGATE     |       |
|   2 |   HASH JOIN         |       |
|   3 |    TABLE ACCESS FULL| TEST  |
|   4 |    TABLE ACCESS FULL| TEST1 |
-------------------------------------

SQLチューニング・アドバイザの推奨にかかわらず、代替計画を適用するには、DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINEをコールします。このプロシージャを使用して、任意の既存の再生可能な計画でSQL計画ベースラインを作成できます。

関連項目:

「SQL計画ベースラインとSQLプロファイルの違い」

25.1.3 SQLチューニング・アドバイザ操作

SQLチューニング・アドバイザを自動またはオンデマンドで実行できます。また、ローカルまたはリモート・データベースでアドバイザを実行できます。

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

25.1.3.1 自動およびオンデマンドSQLチューニング

DBMS_AUTO_SQLTUNEを使用して自動的に実行するか、DBMS_SQLTUNEを使用してオンデマンドで実行するために、SQLチューニング・アドバイザを構成します。

起動方法が次のように異なります。

  • 自動

    システムの夜間のメンテナンス・ウィンドウで実行されるように、SQLチューニング・アドバイザを構成できます。AUTOTASKによって実行される場合、自動SQLチューニング・アドバイザとして知られるこのアドバイザは、自動SQLチューニングを実行します。

  • オンデマンド

    オンデマンドSQLチューニングでは、手動でSQLチューニング・アドバイザを起動し、SQLのパフォーマンスに関する問題を検出し、その後それらの問題を診断して解決します。オンデマンドでSQLをチューニングする場合、インタフェースとしてOracle Enterprise Manager Cloud Control (Cloud Control)を使用することをお薦めしますが、DBMS_SQLTUNE PL/SQLパッケージも使用できます。

SQLチューニング・アドバイザは、自動チューニング・オプティマイザを使用して自身の分析を実行します。オプティマイザはユーザーではなくSQLを分析するため、この最適化は自動的に行われます。自動チューニング・オプティマイザを自動SQLチューニングと混同しないようにしてください。このドキュメントで言及しているのは、自動SQLチューニング・タスクによって実行される作業のみです。

関連項目:

25.1.3.2 ローカルおよびリモートSQLチューニング

SQLチューニング・アドバイザでは、ローカル・データベースまたはリモート・データベースのワークロードを分析できます。

最も単純な場合、SQLチューニング・アドバイザは入力を受け入れて実行し、単一のデータベース内に結果を格納します。ローカル・モードは、SQLチューニング・アドバイザ実行のパフォーマンス上のオーバーヘッドを許容できるデータベースに適しています。

リモート・チューニングで、チューニング・タスクを起動するデータベースは、チューニング・プロセスを実行するデータベースまたは結果が格納されるデータベースと異なります。たとえば、スタンバイ・データベースでは、問合せの固有のワークロードを含めることができ、一部でチューニングが必要になる場合があります。SQLチューニング・アドバイザ文はスタンバイ・データベースで発行できます。スタンバイとプライマリのデータベース・リンクにより、DBMS_SQLTUNEでプライマリ・データベースへのデータの書込みおよび読取りが可能になります。このリンクが必要なのは、スタンバイ・データベースは読取り専用でSQLチューニング・データを書き込めないためです。

次の図に、プライマリ・データベースのスタンバイ・ワークロードをチューニングするための一般的な設定を示します。この手法には、スタンバイとプライマリのデータベース・リンクが必要です。

図25-10 プライマリ・データベースのスタンバイ・ワークロードのチューニング

図25-10の説明が続きます
「図25-10 プライマリ・データベースのスタンバイ・ワークロードのチューニング」の説明

プライマリ・データベースのスタンバイ・ワークロードをチューニングするには、database_link_toパラメータをDBMS_SQLTUNEプロシージャに指定します。デフォルトでは、database_link_toパラメータはnullです。これはチューニングがローカルであることを意味します。

database_link_toパラメータはプライベート・データベース・リンクを指定する必要があります。このリンクはSYSによって所有され、デフォルト権限を持つユーザーSYS$UMFによってアクセスされる必要があります。次のサンプル文では、lnk_to_priという名前のリンクが作成されます。 

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

次の表に、一般的なリモート・チューニング・セッションを示します。すべての文をスタンバイ・データベースで発行します。DBMS_SQLTUNEは、プライマリ・データベースからデータをフェッチするためと、プライマリ・データベースにデータを格納するための両方の目的でデータベース・リンクを使用します。

表25-1 プライマリ・データベースへのデータベース・リンクを使用したスタンバイ・データベース・ワークロードのチューニング

ステップ スタンバイ・データベースで発行される文 結果
1 CREATE_TUNING_TASK DBMS_SQLTUNEは、スタンバイとプライマリのデータベース・リンクを使用して、プライマリ・データベースにタスク・データを作成します。
2 EXECUTE_TUNING_TASK DBMS_SQLTUNEは、データベース・リンクを使用して、プライマリ・データベースに格納されたSQLチューニング・アドバイザ・タスクを読み取ります。チューニング分析はスタンバイ・データベースで発生しますが、DBMS_SQLTUNEは結果をリモートでプライマリ・データベースに書き込みます。
3 REPORT_TUNING_TASK DBMS_SQLTUNEは、データベース・リンクを使用して、SQLチューニング・アドバイザ・レポート・データをプライマリ・データベースから読み取り、その後そのレポートをスタンバイ・データベースでローカルに構成します。
4 ACCEPT_SQL_PROFILE DBMS_SQLTUNEは、データベース・リンクを使用して、SQLプロファイル・データをリモートにプライマリ・データベースに書き込みます。

関連項目:

25.2 自動SQLチューニング・タスクの管理

SQLのパフォーマンスに関する問題をプロアクティブに識別することが目的である場合、SQLチューニング・アドバイザを自動タスクとして構成することが単純な解決策となります。このタスクは、AWRから選択された、チューニングの候補として条件を満たす高負荷SQL文を処理します。

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

関連項目:

自動メンテナンス・タスクについてさらに学習するには、『Oracle Database管理者ガイド』を参照してください

25.2.1 自動SQLチューニング・タスクについて

デフォルトでは、自動SQLチューニング・タスクは夜間のメンテナンス・ウィンドウで実行されます。

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

関連項目:

マルチテナント環境のSQLチューニング・アドバイザについて学習するには、『Oracle Database管理者ガイド』を参照してください。

25.2.1.1 自動SQLチューニングの目的

チューニングを手動で実行せずに、自動SQLチューニングを構成すると、コストが減少し、管理性が向上します

多くのDBAには、SQLチューニングに必要な集中分析を行う時間がありません。時間がある場合でも、SQLチューニングには手動のステップがいくつか含まれます。様々なSQL文が特定の日に高負荷になる場合があるため、DBAがかなりの時間を費やしてそれらを監視し、チューニングすることが必要になる可能性があります。

自動SQLチューニング・タスクでは、次のタイプのSQLは処理されません。

  • 非定型SQL文または1週間以内に繰り返し実行されないSQL文

  • パラレル問合せ

  • SQLプロファイリング後の実行に時間がかかりすぎるため、SQLチューニング・アドバイザによる実行のテストが現実的ではない問合せ

  • 再帰的SQL

SQLチューニング・アドバイザをオンデマンドで起動し、前述のタイプのSQL文をチューニングできます。

25.2.1.2 自動SQLチューニングの概要

Oracle Schedulerは、自動化メンテナンス・タスクのインフラストラクチャ(AutoTaskと呼ばれる)を使用して、タスクを自動的に実行するようにスケジュールします。

デフォルトでは、自動SQLチューニング・タスクは夜間のメンテナンス・ウィンドウで最大1時間実行されます。メンテナンス・ウィンドウの属性(開始時刻と終了時刻、頻度、曜日など)は、カスタマイズできます。

関連項目:

25.2.1.3 SQLチューニング・アドバイザへのコマンドライン・ユーザー・インタフェース

コマンドラインでは、PL/SQLパッケージを使用してSQLチューニング・タスクを実行できます。

次の表では、最も関連性の高いパッケージについて説明します。

表25-2 SQLチューニング・アドバイザのパッケージ

パッケージ 説明

DBMS_AUTO_SQLTUNE

SQLチューニング・アドバイザの実行、SQLプロファイルの管理、SQLチューニング・セットの管理およびリアルタイムでのSQLのパフォーマンスの監視を行うことができます。このAPIを使用するには、ADVISOR権限が必要です。

DBMS_AUTO_TASK_ADMIN

AUTOTASKへのインタフェースが提供されます。このインタフェースを使用して、自動SQLチューニング・タスクを有効および無効にできます。

関連項目:

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

25.2.1.4 自動SQLチューニングの基本タスク

この項では、SQLチューニング・アドバイザを自動タスクとして実行する際の基本タスクについて説明します。

次の図は、基本ワークフローを示しています。

図25-11 自動SQLチューニングのAPI

図25-11の説明が続きます
「図25-11 自動SQLチューニングのAPI」の説明

図25-12に示した基本手順は、次のとおりです。

  1. 自動SQLチューニング・タスクを有効にします。

    「自動SQLチューニング・タスクの有効化と無効化」を参照してください。

  2. オプションで、自動SQLチューニング・タスクを構成します。

    「自動SQLチューニング・タスクの構成」を参照してください。

  3. 自動SQLチューニング・タスクの結果を表示します。

    「自動SQLチューニング・レポートの表示」を参照してください。

  4. 自動SQLチューニング・タスクを無効にします。

    「自動SQLチューニング・タスクの有効化と無効化」を参照してください。

25.2.2 自動SQLチューニング・タスクの有効化と無効化

自動SQLチューニング・タスクは、Cloud Control(優先)またはコマンドライン・インタフェースを使用して有効化または無効化できます。

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

25.2.2.1 Cloud Controlを使用した自動SQLチューニング・タスクの有効化と無効化

Cloud Controlを使用して、自動SQLチューニング・タスクを含む、すべての自動メンテナンス・タスクを有効および無効にできます。

Cloud Controlを使用して自動SQLチューニング・タスクを有効および無効にするには:

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

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

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

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

  5. 「管理」メニューから、「Oracle Scheduler」を選択し、「自動化メンテナンス・タスク」を選択します。

    自動化メンテナンス・タスク・ページが表示されます。

    このページには、事前定義されたタスクが表示されます。対応するリンクをクリックして各タスクにアクセスすると、そのタスクの詳細を確認できます。

  6. 「自動SQLチューニング」をクリックします。

    「自動SQLチューニング結果のサマリー」ページが表示されます。

    タスク・ステータスセクションには、自動SQLチューニング・タスクが有効になっているか無効になっているかが表示されます。次の図では、タスクが無効になっています。

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

  7. 自動SQLチューニングで、「構成」をクリックします。

    「自動化メンテナンス・タスク構成」ページが表示されます。

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

    デフォルトでは、自動SQLチューニングはMAINTENANCE_WINDOW_GROUPで事前定義されたすべてのメンテナンス・ウィンドウで実行されます。

  8. 次のステップを実行します。

    1. 自動SQLチューニングのタスク設定で、「有効」または「無効」を選択して自動タスクを有効または無効にします。

    2. 週の特定の曜日に自動SQLチューニングを無効にするには、ウィンドウ名の横にある該当するチェック・ボックスを選択します。

    3. ウィンドウの特性を変更するには、ウィンドウ・グループの編集をクリックします。

    4. 「適用」をクリックします。

25.2.2.2 コマンドラインを使用した自動SQLチューニング・タスクの有効化と無効化

自動SQLチューニング・タスクを有効および無効にするためにCloud Controlを使用しない場合は、コマンドラインを使用する必要があります。

ここでは次のオプションを指定できます。

  • DBMS_AUTO_TASK_ADMIN PL/SQLパッケージに含まれるENABLEまたはDISABLEプロシージャを実行します。

    コマンドラインで使用する手法としては、このパッケージをお薦めします。ENABLEおよびDISABLEプロシージャのどちらに対しても、window_nameパラメータを使用して特定のメンテナンス・ウィンドウを指定できます。

  • STATISTICS_LEVEL初期化パラメータをBASICに設定して、自動SQLチューニング・アドバイザを含むすべてのアドバイザと統計の収集を無効にします。

    監視機能と多くの自動機能が無効になるため、STATISTICS_LEVELはBASICに設定しないことを強くお薦めします。

DBMS_AUTO_TASK_ADMINを使用して自動SQLチューニングを有効および無効にするには:

  1. 管理者権限でデータベースにSQL*Plusを接続し、次のいずれかを実行します。

    • 自動タスクを有効にするには、次のPL/SQLブロックを実行します。

      BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (
    client_name => 'sql tuning advisor'
,   operation   => NULL
,   window_name => NULL
);
END;
/

    • 自動タスクを無効にするには、次のPL/SQLブロックを実行します。

      BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (
    client_name => 'sql tuning advisor'
,   operation   => NULL
,   window_name => NULL
);
END;
/

  2. データ・ディクショナリを問い合せて、変更を確認します。

    たとえば、次のようにDBA_AUTOTASK_CLIENTを問い合せます(出力例も示します)。 

    COL CLIENT_NAME FORMAT a20

SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'sql tuning advisor';
 
CLIENT_NAME          STATUS
-------------------- --------
sql tuning advisor   ENABLED

すべてのアドバイザと統計の収集を無効にするには:

  1. 管理者権限でデータベースにSQL*Plusを接続し、現在の統計レベルの設定を問い合せます。

    次のSQL*Plusコマンドを実行すると、STATISTICS_LEVELがALLに設定されていることが示されます。 

    sys@PROD> SHOW PARAMETER statistics_level
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
statistics_level                     string      ALL

  2. 次のようにSTATISTICS_LEVELをBASICに設定します。 

    sys@PROD> ALTER SYSTEM SET STATISTICS_LEVEL ='BASIC';
 
System altered.

関連項目:

詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

25.2.3 自動SQLチューニング・タスクの構成

Cloud Controlまたはコマンドラインを使用して、自動SQLチューニング・タスクを構成できます。

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

25.2.3.1 Cloud Controlを使用した自動SQLチューニング・タスクの構成

Cloud Controlを使用して、自動SQLチューニング・タスクを含む、すべての自動メンテナンス・タスクを有効および無効にできます。操作をSYSとして実行するか、PL/SQLパッケージDBMS_AUTO_SQLTUNEに対するEXECUTE権限を持っている必要があります。

Cloud Controlを使用して自動SQLチューニング・タスクを構成するには:

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

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

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

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

  5. 「管理」メニューの「Oracle Scheduler」をクリックし、「自動化メンテナンス・タスク」をクリックします。

    自動化メンテナンス・タスク・ページが表示されます。

    このページには、事前定義されたタスクが表示されます。対応するリンクをクリックして各タスクにアクセスすると、そのタスク自体の詳細を確認できます。

  6. 「自動SQLチューニング」をクリックします。

    「自動SQLチューニング結果のサマリー」ページが表示されます。

  7. タスク設定で、「自動SQLチューニング」(SYS_AUTO_SQL_TUNING_TASK)の横にある「構成」をクリックします。

    「自動化メンテナンス・タスク構成」ページが表示されます。

  8. タスク設定で、「自動SQLチューニング」の横にある「構成」をクリックします。

    自動SQLチューニング設定ページが表示されます。

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

  9. 必要な変更を行い、「適用」をクリックします。

25.2.3.2 コマンドラインを使用した自動SQLチューニング・タスクの構成

DBMS_AUTO_SQLTUNEパッケージを使用すると、SET_AUTO_TUNING_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することによって、自動SQLチューニングを構成できます。

タスクはSYSが所有しているため、SYSのみがタスク・パラメータを設定できます。

ACCEPT_SQL_PROFILEチューニング・タスク・パラメータは、SQLプロファイルを自動的に実装する(true)か、ユーザーの操作を必要とする(false)かを指定します。デフォルトのAUTOに設定した場合、1つ以上のSQL文がSQLプロファイルとともに存在する場合はtrue、この条件が満たされない場合はfalseになります。

ノート:

自動実装が有効になっている場合、アドバイザはSQLプロファイルを作成するための推奨事項のみを実装します。新しい索引の作成、オプティマイザ統計の収集、SQL計画ベースラインの作成などの推奨事項は、自動的には実装されません。

前提条件

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

  • データベースでSQLプロファイルを自動的に実装しますが、実装するのは実行ごとに50以下のSQLプロファイル、およびデータベースで合計50以下のプロファイルとします。

  • 実行ごとに1200秒経過したら、タスクをタイムアウトする。

自動SQLチューニング・タスク・パラメータを設定するには:

  1. SQL*Plusを適切な権限でデータベースに接続し、オプションで現在のタスク設定を問い合せます。

    たとえば、管理者権限でSQL*Plusをデータベースに接続し、次の問合せを実行します。

    COL PARAMETER_NAME FORMAT a25 
COL VALUE FORMAT a10   

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  ( (TASK_NAME = 'SYS_AUTO_SQL_TUNING_TASK') AND
         ( (PARAMETER_NAME LIKE '%PROFILE%') OR 
           (PARAMETER_NAME = 'LOCAL_TIME_LIMIT') OR
           (PARAMETER_NAME = 'EXECUTION_DAYS_TO_EXPIRE') ) );

    出力例は次のように表示されます。

    PARAMETER_NAME            VALUE
------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE  30
LOCAL_TIME_LIMIT          1000
ACCEPT_SQL_PROFILES       FALSE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES     10000

  2. 次の形式のPL/SQLコードを使用してパラメータを設定します。

    BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
    task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,   parameter => parameter_name
,   value     => value
);
END;
/

例25-3 SQLチューニング・タスク・パラメータの設定

次のPL/SQLブロックでは、時間制限を20分に設定し、自動的にSQLプロファイルを実装して、それらのプロファイルの制限を設定します。

BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'LOCAL_TIME_LIMIT', 1200);
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'ACCEPT_SQL_PROFILES', 'true');
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'MAX_SQL_PROFILES_PER_EXEC', 50);
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
    'MAX_AUTO_SQL_PROFILES', 10002);
END;
/

関連項目:

DBMS_AUTO_SQLTUNEの詳細な参照情報については、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

25.2.4 自動SQLチューニング・レポートの表示

自動SQLチューニング・タスクの実行中または実行後の任意のタイミングで、チューニング・レポートを表示できます。

チューニング・レポートには、自動SQLチューニング・タスクのすべての実行に関する情報が含まれます。レポートに含まれるセクションに応じて、次のセクションで情報を参照できます。

  • 一般情報

    このセクションには、自動SQLチューニング・タスクの概要(レポートに指定された入力に関する情報、メンテナンス中にチューニングされたSQL文の数、作成されたSQLプロファイルの数など)が含まれます。

  • サマリー

    このセクションには、メンテナンス・ウィンドウ内にチューニングされたSQL文(SQL識別子別)と各SQLプロファイルのメリットの見積り、またはそのSQLプロファイルを使用したSQL文のテスト実行後の実行統計がリストされます。

  • チューニングの結果

    このセクションには、SQLチューニング・アドバイザによって分析された各SQL文に関する次の情報が含まれます。

    • 各SQL文に関連付けられたすべての検出結果

    • プロファイルがデータベースに実装されたかどうか、およびその理由

    • データベースでSQLプロファイルが現在有効であるかどうか

    • SQLプロファイルのテスト時に取得された実行統計の詳細

  • EXPLAIN PLAN

    このセクションには、SQLチューニング・アドバイザにより分析された各SQL文で使用された新旧のEXPLAIN PLANが表示されます。

  • エラー

    このセクションには、自動SQLチューニング・タスクで発生したすべてのエラーがリストされます。

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

25.2.4.1 コマンドラインを使用した自動SQLチューニング・レポートの表示

SQLチューニング・レポートをCLOBとして生成するには、DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASKファンクションを実行します。

CLOBを変数に格納した後、その変数を出力してレポートを表示できます。

前提条件

この項では、実装されなかった推奨事項を含め、最新の実行において分析されたすべてのSQL文を表示するものとします。

自動SQLチューニング・アドバイザ・レポートを作成し、それにアクセスするには:

  1. 管理者権限でデータベースにSQL*Plusを接続し、DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASKファンクションを実行します。

    次の例では、実装されなかった推奨事項を含め、最新の実行において分析されたすべてのSQL文を表示するテキスト・レポートが生成されます。

    VARIABLE my_rept CLOB;
BEGIN
  :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK (
    begin_exec   => NULL
,   end_exec     => NULL
,   type         => 'TEXT'
,   level        => 'TYPICAL'
,   section      => 'ALL'
,   object_id    => NULL
,   result_limit => NULL
);
END;
/

PRINT :my_rept

  2. 一般情報セクションを参照し、チューニングの実行の概要を確認します。

    次の例は、わずか7分間で17のSQL文を分析した自動SQLチューニング・タスクを示しています。

    MY_REPT
------------------------------------------------------------------------
GENERAL INFORMATION SECTION
------------------------------------------------------------------------
Tuning Task Name                     : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                    : SYS
Workload Type                        : Automatic High-Load SQL Workload
Execution Count                      : 6
Current Execution                    : EXEC_170
Execution Type                       : TUNE SQL
Scope                                : COMPREHENSIVE
Global Time Limit(seconds)           : 3600
Per-SQL Time Limit(seconds)          : 1200
Completion Status                    : COMPLETED
Started at                           : 04/16/2012 10:00:00
Completed at                         : 04/16/2012 10:07:11
Number of Candidate SQLs             : 17
Cumulative Elapsed Time of SQL (s)   : 8

  3. 結果と推奨事項を見つけます。

    SQLチューニング・アドバイザから推奨事項が作成された場合、それを受け入れることのメリットとデメリットを検討します。

    次の例は、SQLチューニング・アドバイザが、既存の計画よりも優れている可能性がある文の計画を見つけたことを示しています。アドバイザはSQLプロファイルの実装を推奨します。 

    ------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
------------------------------------------------------------------------
ob ID  SQL ID        stats profile(benefit) index(benefit) restructure
------ ------------- ----- ---------------- -------------- -----------
    82 dqjcc345dd4ak                 58.03%
    72 51bbkcd9zwsjw                                                 2
    81 03rxjf8gb18jg

------------------------------------------------------------------------
DETAILS SECTION
------------------------------------------------------------------------
 Statements with Results Ordered by Max (Profile/Index) Benefit, Obj ID
------------------------------------------------------------------------
Object ID  : 82
Schema Name: DBA1
SQL ID     : dqjcc345dd4ak
SQL Text   : SELECT status FROM dba_autotask_client WHERE client_name=:1
 
------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
  The SQL profile was not automatically created because the verified
  benefit was too low.
 
  Recommendation (estimated benefit: 58.03%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
         'SYS_AUTO_SQL_TUNING_TASK', object_id => 82, replace => TRUE);
 
  Validation results
  ------------------
  The SQL profile was tested by executing its plan and the original 
  plan and measuring their respective execution statistics. A plan 
  may have been only partially executed if the other could be run 
  to completion in less time.
 
                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time(us):               26963              8829      67.25 %
  CPU Time(us):                   27000              9000      66.66 %
  User I/O Time(us):                 25                14         44 %
  Buffer Gets:                      905               380      58.01 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:            7372              7372          0 %
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1
 
  Notes
  -----
  1. The original plan was first executed to warm the buffer cache.
  2. Statistics for original plan were averaged over next 9 executions.
  3. The SQL profile plan was first executed to warm the buffer cache.
  4. Statistics for the SQL profile plan were averaged over 
     next 9 executions.

関連項目:

詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

25.3 オンデマンドでのSQLチューニング・アドバイザの実行

SQLチューニング・アドバイザをオンデマンドで実行できます。

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

25.3.1 オンデマンドSQLチューニングについて

オンデマンドSQLチューニングは、自動SQLチューニング・タスクの結果として行われるのではないSQLチューニング・アドバイザの起動として定義されます。

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

25.3.1.1 オンデマンドSQLチューニングの目的

通常は、ADDMを事前に実行するか、パフォーマンスが最適でないというユーザーの苦情に応じてSQL文を事後チューニングするために、SQLチューニング・アドバイザを起動します。

事前と事後のどちらのシナリオでも、通常はSQLチューニング・アドバイザを実行することが、SQLのパフォーマンスに関する予期しない問題を最もすばやく解決する方法になります。

25.3.1.2 オンデマンドSQLチューニングのユーザー・インタフェース

手動でSQLチューニング・アドバイザを実行するための推奨インタフェースは、Cloud Controlです。

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

25.3.1.2.1 Cloud Controlを使用したSQLチューニング・アドバイザへのアクセス

自動データベース診断モニター(ADDM)は、自動的に高負荷SQL文を識別します。ADDMがこのような文を識別した場合は、「推奨事項の詳細」ページの「SQLチューニング・アドバイザのスケジュール」または「SQLチューニング・アドバイザの実行」をクリックして、SQLチューニング・アドバイザを実行します。

SQLチューニング・アドバイザを使用したSQL文のチューニングするには:

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

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

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

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

  5. 「パフォーマンス」メニューの「SQL」をクリックし、「SQLチューニング・アドバイザ」をクリックします。

    「SQLチューニング・アドバイザのスケジュール」ページが表示されます。

    sql_tuning_advisor_empty.gifの説明が続きます。
    図sql_tuning_advisor_empty.gifの説明

関連項目:

Cloud Controlを使用したSQLチューニング・アドバイザの構成および実行方法を学習するには、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。

25.3.1.2.2 オンデマンドSQLチューニングへのコマンドライン・インタフェース

Cloud Controlが使用可能でない場合、DBMS_SQLTUNEパッケージのプロシージャを使用してSQLチューニング・アドバイザを実行できます。

このAPIを使用するには、ユーザーがADVISOR権限を持っている必要があります。

関連項目:

詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

25.3.1.3 オンデマンドSQLチューニングの基本タスク

この項では、DBMS_SQLTUNEパッケージを使用してSQLチューニング・アドバイザを実行する際の基本タスクについて説明します。

次の図は、PL/SQL APIを使用する際の基本ワークフローを示しています。

図25-12 SQLチューニング・アドバイザAPI

図25-12の説明が続きます
「図25-12 SQLチューニング・アドバイザAPI」の説明

図25-12に示した基本手順は、次のとおりです。

  1. SQLチューニング・アドバイザへの入力を準備または作成します。入力には次のいずれかを使用できます。

    • 単一のSQL文のテキスト

    • 1つ以上の文を含むSQLチューニング・セット

  2. SQLチューニング・タスクを作成します。

    「SQLチューニング・タスクの作成」を参照してください。

  3. オプションで、作成したSQLチューニング・タスクを構成します。

    「SQLチューニング・タスクの構成」を参照してください。

  4. SQLチューニング・タスクを実行します。

    「SQLチューニング・タスクの実行」を参照してください。

  5. オプションで、SQLチューニング・タスクのステータスまたは進行状況をチェックします。

    「SQLチューニング・タスクの監視」を参照してください。

  6. SQLチューニング・タスクの結果を表示します。

    「SQLチューニング・タスクの結果の表示」を参照してください。

  7. 必要に応じて推奨事項を実装します。

関連項目:

Cloud Controlを使用したSQLのチューニング方法を学習するには、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。

25.3.2 SQLチューニング・タスクの作成

SQLチューニング・タスクを作成するには、DBMS_SQLTUNE.CREATE_TUNING_TASKファンクションを実行します。

チューニング・タスクは、次のいずれかから作成できます。

  • 単一のSQL文のテキスト

  • 複数の文を含むSQLチューニング・セット

  • 共有SQL領域からSQL識別子によって選択されたSQL文

  • AWRからSQL識別子によって選択されたSQL文

scopeパラメータは、このファンクションにとって最も重要なパラメータの1つです。このパラメータは、次の値に設定できます。

  • LIMITED

    SQLチューニング・アドバイザでは、統計チェック、アクセス・パス分析およびSQL構造分析に基づいて推奨事項が生成されます。SQLプロファイルの推奨事項は生成されません。

  • COMPREHENSIVE

    SQLチューニング・アドバイザでは、制限付きの有効範囲で実行されるすべての分析と、SQLプロファイリングが実行されます。

前提条件

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

  • ADVISOR権限を持つユーザーhrとしてチューニングを実行します。

  • 次の問合せをチューニングします。

    SELECT /*+ ORDERED */ * 
FROM   employees e, locations l, departments d
WHERE  e.department_id = d.department_id 
AND    l.location_id = d.location_id 
AND    e.employee_id < :bnd;

  • バインド変数100を前述の問合せに渡します。

  • SQLチューニング・アドバイザでSQLプロファイリングを実行します。

  • タスクの実行時間は60秒以内です。

SQLチューニング・タスクを作成するには:

  1. 適切な権限でデータベースにSQL*Plusを接続し、DBMS_SQLTUNE.CREATE_TUNING_TASKファンクションを実行します。

    たとえば、次のPL/SQLプログラムを実行します。

    DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext := 'SELECT /*+ ORDERED */ * '                      ||
                'FROM employees e, locations l, departments d ' ||
                'WHERE e.department_id = d.department_id AND '  ||
                      'l.location_id = d.location_id AND '      ||
                      'e.employee_id < :bnd';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
          sql_text    => my_sqltext
,         bind_list   => sql_binds(anydata.ConvertNumber(100))
,         user_name   => 'HR'
,         scope       => 'COMPREHENSIVE'
,         time_limit  => 60
,         task_name   => 'STA_SPECIFIC_EMP_TASK'
,         description => 'Task to tune a query on a specified employee'
);
END;
/

  2. オプションで、タスクのステータスを問い合せます。

    次の例では、現行のユーザー(この例ではhr)によって所有されているすべてのタスクのステータスを問い合せます。 

    COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM   USER_ADVISOR_LOG;

    出力例は次のように表示されます。

    TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
------- ------------------------- ----------- --------------
    884 STA_SPECIFIC_EMP_TASK     INITIAL

    前述の出力において、INITIALステータスはタスクの実行が開始されていないことを示しています。

関連項目:

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

25.3.3 SQLチューニング・タスクの構成

チューニング・タスクの作成後に、そのパラメータを変更するには、DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERファンクションを実行します。

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

  • ADVISOR権限を付与されているユーザー・アカウントhrを使用してチューニングします。

  • 「SQLチューニング・タスクの作成」で作成したSTA_SPECIFIC_EMP_TASKをチューニングします。

  • SQLチューニング・タスクの最大実行時間を300秒に変更します。

SQLチューニング・タスクを構成するには:

  1. 適切な権限でデータベースにSQL*Plusを接続し、DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERファンクションを実行します。

    たとえば、次のPL/SQLプログラムを実行すると、チューニング・タスクの時間制限を300秒に変更できます。

    BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
    task_name => 'STA_SPECIFIC_EMP_TASK'
,   parameter => 'TIME_LIMIT'
,   value     => 300
);
END;
/

  2. オプションで、タスク・パラメータが変更されたことを確認します。

    次の例では、タスクSTA_SPECIFIC_EMP_TASKで使用されているすべてのパラメータの値を問い合せます。 

    COL PARAMETER_NAME FORMAT a25 
COL VALUE FORMAT a15   

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   USER_ADVISOR_PARAMETERS
WHERE  TASK_NAME = 'STA_SPECIFIC_EMP_TASK'
AND    PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;

    出力例は次のように表示されます。

    PARAMETER_NAME            VALUE
------------------------- ---------------
DAYS_TO_EXPIRE            30
DEFAULT_EXECUTION_TYPE    TUNE SQL
EXECUTION_DAYS_TO_EXPIRE  UNLIMITED
JOURNALING                INFORMATION
MODE                      COMPREHENSIVE
SQL_LIMIT                 -1
SQL_PERCENTAGE            1
TARGET_OBJECTS            1
TEST_EXECUTE              AUTO
TIME_LIMIT                300

例25-4 データベース・リンクを使用したスタンバイ・データベース・ワークロードのチューニング

Oracle Database 12cリリース2 (12.2)から、database_link_toパラメータにデータベース・リンクを指定することでスタンバイ・データベース・ワークロードをチューニングできます。セキュリティ上の理由で、プライベート・データベース・リンクの使用をお薦めします。リンクはSYSによって所有され、権限を持つユーザーによってアクセスされる必要があります。Oracle Databaseには、SYS$UMFという名前のデフォルト権限を持つユーザーが含まれています。

次のプログラムは、スタンバイ・データベースで発行され、table1の問合せのSQLチューニング・セッションの例を示しています。database_link_toパラメータは、スタンバイとプライマリのデータベース・リンクの名前を指定します。 

VARIABLE tname VARCHAR2(30);
VARIABLE query VARCHAR2(500);

EXEC :tname := 'my_task';
EXEC :query := 'SELECT /*+ FULL(t)*/ col1 FROM table1 t WHERE col1=9000';

BEGIN 
:tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(
            sql_text         => :query
          , task_name        => :tname
          , database_link_to => 'lnk_to_pri' );
END;
/

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:tname);

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:tname) FROM DUAL;

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name        => :tname
,   name             => 'prof'
,   task_owner       => 'SYS'
,   replace          => TRUE
,   database_link_to => 'lnk_to_pri' );
END;
/

CREATE_TUNING_TASKのbind_listパラメータがスタンバイ・データベースでサポートされていないことに注意してください。

関連項目:

25.3.4 SQLチューニング・タスクの実行

SQLチューニング・タスクを実行するには、DBMS_SQLTUNE.EXECUTE_TUNING_TASKファンクションを使用します。最も重要なパラメータは、task_nameです。

ノート:

また、EXECUTE_TUNING_TASK APIを使用して自動チューニング・タスクSYS_AUTO_SQL_TUNING_TASKを実行することもできます。SQLチューニング・アドバイザは、自動的に実行する場合と同じ分析およびアクションを実行します。

前提条件

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

SQLチューニング・タスクを実行するには:

  1. 適切な権限でデータベースにSQL*Plusを接続し、DBMS_SQLTUNE.EXECUTE_TUNING_TASKファンクションを実行します。

    たとえば、次のPL/SQLプログラムを実行します。

    BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_SPECIFIC_EMP_TASK');
END;
/

  2. オプションで、タスクのステータスを問い合せます。

    次の例では、現行のユーザー(この例ではhr)によって所有されているすべてのタスクのステータスを問い合せます。 

    COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM   USER_ADVISOR_LOG;

    出力例は次のように表示されます。

    TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
------- ------------------------- ----------- --------------
    884 STA_SPECIFIC_EMP_TASK     COMPLETED

関連項目:

DBMS_SQLTUNE.EXECUTE_TUNING_TASKファンクションの詳細な参照情報は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

25.3.5 SQLチューニング・タスクの監視

Cloud ControlでSQLチューニング・タスクを作成する場合、別途の監視ステップは必要ありません。Cloud Controlによって自動的にステータス・ページが表示されます。

Cloud Controlを使用しない場合、データ・ディクショナリおよび動的パフォーマンス・ビューを問い合せることによって、現在実行中のSQLチューニング・タスクを監視できます。次の表では、関連するビューについて説明します。

表25-3 DBMS_SQLTUNE.EXECUTE_TUNING_TASKパラメータ

ビュー 説明

USER_ADVISOR_TASKS

現行のユーザーが所有するタスクに関する情報を示します。このビューは、タスクごとに1行の情報を示します。各タスクには、所有者固有の名前が含まれています。タスク名は、情報を提供するのみで、他のネームスペース内部で一意性が施行されることはありません。

V$ADVISOR_PROGRESS

アドバイザの実行の進行状況に関する情報を示します。

前提条件

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

SQLチューニング・タスクを監視するには:

  1. 適切な権限でデータベースにSQL*Plusを接続し、タスクが実行中であるか完了しているかを確認します。

    たとえば、次のようにSTA_SPECIFIC_EMP_TASKのステータスを問い合せます。 

    SELECT STATUS 
FROM   USER_ADVISOR_TASKS
WHERE  TASK_NAME = 'STA_SPECIFIC_EMP_TASK';

    次に、タスクが完了していることを示す出力例を示します。

    STATUS
-----------
EXECUTING

  2. 実行中のタスクの進行状況を確認します。

    次の例では、タスクID 884のタスクのステータスを問い合せます。 

    VARIABLE my_tid NUMBER;  
EXEC :my_tid := 884
COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999

SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK, 
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM   V$ADVISOR_PROGRESS
WHERE  TASK_ID = :my_tid;

    出力例は次のように表示されます。

       TASK_ID ADVISOR_NAME         SOFAR TOTALWORK %_COMPLETE
---------- -------------------- ----- --------- ----------
       884 SQL Tuning Advisor       1         2         50

関連項目:

V$ADVISOR_PROGRESSビューについて学習するには、Oracle Databaseリファレンスを参照してください

25.3.6 SQLチューニング・タスクの結果の表示

チューニング・タスクの結果をレポートするには、DBMS_SQLTUNE.REPORT_TUNING_TASKファンクションを使用します。

このレポートには、SQLチューニング・アドバイザのすべての検出結果および推奨事項が含まれます。提案される推奨事項ごとに、その実装に必要なSQL文、理論的根拠およびメリットが提供されます。

前提条件

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

SQLチューニング・タスクのレポートを表示するには:

  1. 適切な権限でデータベースにSQL*Plusを接続し、DBMS_SQLTUNE.REPORT_TUNING_TASKファンクションを実行します。

    たとえば、次の文を実行します。

    SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' )
FROM   DUAL;

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

    DBMS_SQLTUNE.REPORT_TUNING_TASK('STA_SPECIFIC_EMP_TASK')
------------------------------------------------------------------------
GENERAL INFORMATION SECTION
------------------------------------------------------------------------
Tuning Task Name   : STA_SPECIFIC_EMP_TASK
Tuning Task Owner  : HR
Workload Type      : Single SQL Statement
Execution Count    : 11
Current Execution  : EXEC_1057
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 04/22/2012 07:35:49
Completed at       : 04/22/2012 07:35:50
 
------------------------------------------------------------------------
Schema Name: HR
SQL ID     : dg7nfaj0bdcvk
SQL Text   : SELECT /*+ ORDERED */ * FROM employees e, locations l,
             departments d WHERE e.department_id = d.department_id AND
             l.location_id = d.location_id AND e.employee_id < :bnd
Bind Variables :
 1 -  (NUMBER):100
 
------------------------------------------------------------------------
FINDINGS SECTION (4 findings)
-----------------------------------------------

  2. 結果を解釈します。

関連項目: