プライマリ・コンテンツに移動
Oracle® Database SQLチューニング・ガイド
12c リリース1 (12.1)
B71277-07
目次へ移動
目次
索引へ移動
索引

前
次
次へ

23 SQL計画ベースラインの管理

SQL計画ベースラインは、DBMS_SPMパッケージを使用して管理できます。

この章の内容は次のとおりです。

SQL計画管理について

SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムです。

SQL計画管理では、SQL計画ベースラインと呼ばれるメカニズムを使用します。計画ベースラインは、SQL文に対してオプティマイザで使用できる一連の承認済の計画です。通常のユースケースの場合、データベースで計画の正常な実行が検証された後のみ、計画が計画ベースラインに承認されます。このコンテキストで、計画には、オプティマイザで実行計画を再現するために必要な計画に関連するすべての情報(たとえば、SQL計画識別子、一連のヒント、バインド値、オプティマイザ環境など)が含まれます。

SQL計画管理の主要コンポーネントは次のとおりです。

  • 計画の取得

    このコンポーネントは、一連のSQL文に対する計画に関連する情報を保存します。「計画の取得」を参照してください。

  • 計画の選択

    このコンポーネントは、保存された計画履歴に基づいたオプティマイザによる計画変更の検出、およびパフォーマンスが低下する可能性を回避するための適切な計画の選択でのSQL計画ベースラインの使用です。「計画の選択」を参照してください。

  • 計画の展開

    このコンポーネントは、手動または自動での、既存のSQL計画ベースラインへの新規計画の追加プロセスです。「計画の展開」を参照してください。

この項の内容は次のとおりです。

SQL計画管理の目的

SQL計画管理の主な目的は、計画の変更によって発生するパフォーマンスの低下を防ぐことです。2つ目の目的は、計画変更を検証し、パフォーマンスを改善させる計画変更のみを承認することにより、新しいオプティマイザ統計や索引などの変更にスムーズに適応することです。

注意:

SQL計画ベースラインでは、索引の削除などによる元に戻せない実行計画の変更を防止することはできません。

SQL計画管理の利点

SQL計画の管理により、データベース・アップグレードおよびシステムとデータの変更でSQLパフォーマンスを向上または維持できます。

特に、次の利点が得られます。

  • 新しいバージョンのオプティマイザをインストールするデータベースのアップグレードでは、通常、ほんのわずかなSQL文に対して計画の変更が発生します。

    ほとんどの計画変更において、パフォーマンスの向上が見られるか、または変化がありません。ただし、一部の計画の変更は、パフォーマンスの低下の原因となることがあります。SQL計画ベースラインにより、アップグレードがもたらす低下の可能性を最小限に抑えられます。

    アップグレード時には、データベースにより計画ベースラインからの計画のみが使用されます。データベースは現在のベースラインに含まれない新しい計画を保持領域に入れ、後でそれらを評価し、ベースラインに含まれる現在の計画よりも少ないリソースを使用するかどうかを判断します。それらの計画のパフォーマンスがより優れている場合は、それらの計画をベースラインに昇格させます。そうでない場合は昇格させません。

  • 継続的なシステムおよびデータの変更は、一部のSQL文の計画に影響を及ぼし、パフォーマンスの低下を招く可能性があります。

    SQL計画ベースラインにより、パフォーマンスの低下を最小限に抑えて安定したSQLパフォーマンスを維持できます。

  • 新しいアプリケーション・モジュールのデプロイメントは、新しいSQL文をデータベースに導入します。

    アプリケーション・ソフトウェアは、新しい文に対して標準のテスト構成で作成された適切なSQL実行計画を使用できます。システム構成がテスト構成と大幅に異なる場合、データベースはSQL計画ベースラインを時間の経過とともに進化させてパフォーマンスを改善します。

関連項目:

Oracleデータベースのアップグレード方法については、『Oracle Databaseアップグレード・ガイド』を参照してください。

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

SQLプロファイルとSQL計画ベースラインのどちらも、オプティマイザで最適な計画のみが使用されるようにすることによりSQL文のパフォーマンスを向上させます。

プロファイルとベースラインはどちらも、ヒントを使用して内部的に実装されます。ただし、これらのメカニズムは次のような点で大きく異なります。

  • 一般的に、SQL計画ベースラインは事前対応型であるのに対し、SQLプロファイルは事後対応型です。

    通常、SQL計画ベースラインは重大なパフォーマンスの問題が発生する前に作成します。SQL計画ベースラインは、オプティマイザが最適ではない計画を将来的に使用することを防ぎます。

    SQLプロファイルは、SQLチューニング・アドバイザを起動するとデータベースにより作成されます。通常、チューニング・アドバイザは、SQL文により高負荷の兆候が示された後にのみ起動します。SQLプロファイルは主に、最適ではない計画につながったオプティマイザのミスを継続的に解決するために使用できます。SQLプロファイル・メカニズムは事後対応であるため、データベースに重大な変更が生じた場合は安定したパフォーマンスを保証できません。

    図23-1 SQL計画ベースラインとSQLプロファイル

    図23-1の説明は次にあります。
    「図23-1 SQL計画ベースラインとSQLプロファイル」の説明
  • SQL計画ベースラインは特定の計画を再現するのに対し、SQLプロファイルはオプティマイザのコスト見積りを修正します。

    SQL計画ベースラインは一連の承認済計画から構成されます。各計画は、特定の計画を完全に指定するアウトライン・ヒントのセットを使用して実装されます。SQLプロファイルもヒントを使用して実装されますが、これらのヒントでは特定の計画は指定されません。これらのヒントは、最適ではない計画につながったオプティマイザの見積りの計算ミスを修正します。たとえば、ヒントにより表のカーディナリティ予測が修正されます。

    プロファイルによりオプティマイザは特定の1つの計画に制限されないため、SQLプロファイルはSQL計画ベースラインよりも柔軟です。たとえば、初期化パラメータやオプティマイザ統計を変更することで、オプティマイザはより最適な計画を選択できます。

SQLチューニング・アドバイザを使用することをお薦めします。これにより、SQLプロファイルと計画ベースラインのどちらが各SQL文に最適なメカニズムであるのかを自分で判断せずに、アドバイザの推奨事項に従うことができます。

計画の取得

SQL計画の取得とは、一連のSQL文に対して、計画に関連する情報を取得し、SQL管理ベースに保存するための方法を指します。計画の取得とは、SQL計画管理にこの計画を認識させることを意味します。

初期化パラメータを設定することにより初期計画の取得が自動的に発生するように構成するか、DBMS_SPMパッケージを使用して計画を手動で取得できます。

初期計画の自動取得

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをtrueに設定することで(デフォルトはfalse)、初期計画の自動取得を有効にします。有効時には、データベースで実行される繰返し可能なSQL文のSQL計画ベースラインが自動的に作成されます。

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESOPTIMIZER_USE_SQL_PLAN_BASELINESの設定は独立しています。たとえば、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStrueの場合、OPTIMIZER_USE_SQL_PLAN_BASELINESの設定がtruefalseのいずれでも、初期計画ベースラインが作成されます。

関連項目:

OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください

計画の手動取得

SQL計画管理では、計画の手動取得とは、ユーザーによるSQL計画ベースラインへの既存計画の一括ロードを指します。

SQL文に対する実行計画をSQLチューニング・セット(STS)共有SQL領域、ステージング表またはストアド・アウトラインからロードするには、Cloud ControlまたはPL/SQLを使用します。

次の図は、SQL計画ベースラインへの計画のロードを示しています。

図23-2 SQL計画ベースラインへの計画のロード

図23-2の説明は次にあります。
「図23-2 SQL計画ベースラインへの計画のロード」の説明

ロード動作は、一括ロードに含まれる各文に対してSQL計画ベースラインが存在するかどうかによって異なります。

  • 文に対するベースラインが存在しない場合、データベースは次の処理を行います。

    1. 文に対する計画履歴と計画ベースラインを作成します。

    2. 文に対する最初の計画を承認済とマークします。

    3. 計画を新しいベースラインに追加します。

  • 文に対するベースラインが存在する場合、データベースは次の処理を行います。

    1. ロードした計画を承認済とマークします。

    2. 計画のパフォーマンスを検証せずに、計画を文に対する計画ベースラインに追加します。

オプティマイザは管理者により手動でロードされた計画のパフォーマンスは許容範囲にあると見なすため、手動でロードされた計画は常に承認済とマークされます。

関連項目:

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

計画の選択

SQL 計画の選択とは、保存された計画履歴に基づいて計画変更を検出するオプティマイザの機能、およびパフォーマンスが低下する可能性を回避するために適切な計画を選択するSQL計画ベースラインの使用を指します。

データベースがSQL文のハード解析を実行すると、オプティマイザは最適なコスト計画を生成します。デフォルトでは、オプティマイザは次に、文に対するSQL計画ベースライン内で一致する計画を探します。計画ベースラインが存在しない場合は、データベースは最適なコスト計画を使用して文を実行します。

計画ベースラインが存在する場合は、オプティマイザの動作は、新たに生成された計画が計画ベースラインに含まれるかどうかによって決まります。

  • 新しい計画がベースラインに含まれる場合、データベースは検出した計画を使用して文を実行します。

  • 新しい計画がベースラインに含まれない場合、オプティマイザは新たに生成された計画を未承認とマークし、計画履歴に追加します。オプティマイザの動作は、計画ベースラインの内容によって決まります。

    • 計画ベースラインに固定計画が含まれる場合、オプティマイザはコストが最も低い固定計画を使用します。

    • 計画ベースラインに固定計画が含まれない場合、オプティマイザはコストが最も低いベースライン計画を使用します。

    • 計画ベースライン内に再現可能な計画が含まれない場合(これはベースライン内のすべての計画が削除された索引を参照している場合に発生します)、オプティマイザは新たに生成されたコストベースの計画を使用します。

図23-3 SQL計画選択のデシジョン・ツリー

図23-3の説明は次にあります。
「図23-3 SQL計画選択のデシジョン・ツリー」の説明

関連項目:

"固定計画"

計画の展開

一般的に、SQL計画の展開は、オプティマイザが新しい計画を検証して既存のSQL計画ベースラインに追加するためのプロセスです。

具体的には、計画の展開は次の個別のステップで構成されます。

  1. 承認されていない計画がSQL計画ベースラインに含まれる承認済の計画と少なくとも同じパフォーマンスであることを検証します(計画の検証と呼ばれます)。

  2. データベースにより承認済の計画と同じパフォーマンスであることが証明された場合、承認されていない計画を承認済の計画として計画ベースラインに追加します。

計画の展開の標準的なケースでは、オプティマイザは前述の手順を順番に実行します。したがって、オプティマイザがSQL計画ベースラインと比較して計画のパフォーマンスを検証するまで、SQL計画管理で新しい計画は使用できません。ただし、 一方の手順を実行せずにもう一方の手順を実行するようにSQL計画管理を構成できます。次の図は、計画の展開で使用可能なパスを示しています。

計画の展開の目的

通常、SQL文のSQL計画ベースラインは、単一の承認済の計画から開始します。ただし、様々な条件の異なる計画とともに実行されると、SQL文の実行が良くなる場合があります。

たとえば、値が異なる選択性になるバインド変数を使用したSQL文は、いくつかの最適な計画を持つ場合があります。マテリアライズド・ビューまたは索引の作成あるいは表の再パーティション化により、現在の計画が他の計画よりコストが高くなる場合があります。

新しい計画がSQL計画ベースラインに一度も追加されなかった場合、一部のSQL文のパフォーマンスが低下する可能性があります。したがって、新しい承認済の計画をSQL計画ベースラインに展開する必要がある場合があります。計画の展開は、SQL計画ベースラインに含める前に新しい計画のパフォーマンスを検証して、パフォーマンスの低下を防止します。

計画の展開用のPL/SQLサブプログラム

DBMS_SPMパッケージは、計画の展開で使用できるプロシージャとファンクションを提供します。

これらのサブプログラムはタスク・インフラストラクチャを使用します。たとえば、CREATE_EVOLVE_TASKは展開タスクを作成し、EXECUTE_EVOLVE_TASKはそれを実行します。すべてのタスク展開サブプログラムの名前には、文字列EVOLVE_TASKが含まれます。

展開プロシージャを必要に応じて使用するか、サブプログラムが自動的に実行されるように構成します。自動メンテナンス・タスクSYS_AUTO_SPM_EVOLVE_TASKは、スケジュールされているメンテナンス・ウィンドウで毎日実行されます。このタスクにより次の処理が自動的に実行されます。

  1. 承認されていない計画を選択し、検証用にランク付けします。

  2. パフォーマンスしきい値を満たしていれば、各計画を承認します。

関連項目:

SQL計画管理の記憶域アーキテクチャ

SQL計画管理インフラストラクチャでは、解析された文のシグネチャおよび承認済の計画と未承認の計画の両方が記録されます。

この項の内容は次のとおりです。

SQL管理ベース

SQL管理ベース(SMB)は、データ・ディクショナリ内の論理リポジトリです。

SMBには、次のものが含まれます。

  • SQL文のログ(SQL IDのみを含む)

  • SQL計画履歴(SQL計画ベースラインを含む)

  • SQLプロファイル

  • SQLパッチ

SMBには、SQLパフォーマンスの維持または向上のためにオプティマイザで使用できる情報が保存されます。

SMBはSYSAUX表領域内にあり、自動セグメント領域管理を使用します。SMB全体がSYSAUX表領域内に存在するため、この表領域が使用可能でない場合、データベースはSQL計画管理およびSQLチューニング機能を使用しません。

図23-5 SMBのアーキテクチャ

図23-5の説明は次にあります。
「図23-5 SMBのアーキテクチャ」の説明

注意:

SMBをプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください。

関連項目:

SYSAUX表領域の詳細は、『Oracle Database管理者ガイド』を参照してください。

SQL文のログ

SQL計画の自動取得が有効になっている場合、SQL文のログには、オプティマイザがこれまでに評価してきた文のシグネチャが含まれます。

SQLシグネチャは、大/小文字および空白が正規化されているSQL文のテキストを使用して計算されたハッシュ値です。オプティマイザは文を解析する際、シグネチャを作成します。

自動取得中、データベースはこのシグネチャをSQL文のログ(SQLLOG$)に照らして照合し、シグネチャが以前に検出されているかどうかを判別します。検出されていない場合、データベースはシグネチャをログに追加します。シグネチャがすでにログに存在する場合、データベースは文が繰返し可能なSQL文であることを確認します。

注意:

フィルタによって文が除外された場合は、そのシグネチャもログから除外されます。

例23-1 SQL文のロギング

この例は、データベースがどのように文のログ内で文を追跡し、繰返し可能な文に対して自動的にベースラインを作成するかを示しています。文のログの最初の問合せでは、追跡されたSQL文は1つもないことが示されます。AD_PRESに対するhr.jobsの問合せ後、ログには文が1つ追跡されていることが示されます。

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

SQL> SELECT * FROM SQLLOG$;
 
no rows selected
 
SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';
 
JOB_TITLE
-----------------------------------
President
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.8096E+19          1

次に、セッションは別のjobs問合せを実行します。ログには2つの文が追跡されていることが示されます。

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT * FROM SQLLOG$;
 
 SIGNATURE     BATCH#
---------- ----------
1.7971E+19          1
1.8096E+19          1

DBA_SQL_PLAN_BASELINESの問合せでは、どちらの文に対してもベースラインが存在しないことが示されます。これは、どちらの文も繰返し可能ではないためです。

SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
no rows selected

セッションがjob_id='PR_REP'に対する問合せを再度実行します。これでこの文は繰返し可能になり、またSQL計画の自動取得が有効になっているため、データベースによりこの文に対する計画ベースラインが作成されます。job_id='AD_PRES'に対する問合せは一度しか実行されていないため、これに対する計画ベースラインは存在しません。

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
 
JOB_TITLE
-----------------------------------
Public Relations Representative
 
SQL> SELECT SQL_HANDLE, SQL_TEXT 
  2  FROM DBA_SQL_PLAN_BASELINES 
  3  WHERE SQL_TEXT LIKE 'SELECT job_title%';
 
SQL_HANDLE           SQL_TEXT
-------------------- --------------------
SQL_f9676a330f972dd5 SELECT job_title FRO
                     M hr.jobs WHERE job_
                     id='PR_REP'

関連項目:

SQL計画履歴

SQL計画履歴は、取得された一連のSQL実行計画です。この履歴には、SQL計画ベースラインおよび未承認の計画が含まれます。

SQL計画管理では、データベースが既存のSQL計画ベースラインの新しいSQL実行計画を検出し、展開(検証)できるように新しい計画を履歴に記録します。展開はデータベースによって自動的に開始されるか、DBAが手動で開始します。

Oracle Database 12c以降、すべてのSQL文の実行計画はSMBによりSQL計画履歴に保存されます。DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションは、SMBから計画をフェッチして表示します。Oracle Database 12cより前に作成された計画については、SMBに保存されていないため、ファンクションはSQL文をコンパイルして計画を生成する必要があります。

関連項目:

有効化された計画

有効な計画は、オプティマイザの使用対象となります。

enabledパラメータをYES (デフォルト)に設定して計画をロードした場合、データベースは結果のSQL計画ベースラインを、承認されていない場合でも、自動的に有効とマークします。有効な計画を手動で無効な計画に変更できます。この場合、計画が承認済でもオプティマイザはその計画を使用できなくなります。

承認済の計画

承認済の計画は、SQL文のSQL計画ベースラインに存在し、オプティマイザで使用できる計画です。承認済の計画には、ヒント・セット、計画のハッシュ値、計画に関連するその他の情報が含まれます。

SQL文に対する計画履歴には、すべての計画(承認済と未承認の両方の計画)が含まれます。オプティマイザが計画ベースライン内に最初の承認済の計画を生成すると、それ以降のすべての承認されていない計画は計画履歴に追加され、検証を待ちますが、SQL計画ベースラインには追加されません。

固定計画

固定計画は、優先としてマークされた承認済の計画です。これにより、オプティマイザはベースライン内の固定計画のみを考慮するようになります。固定計画は、オプティマイザの計画選択プロセスに影響します。

ある文に対するSQL計画ベースラインに3つの計画が存在するとします。オプティマイザに、その内の2つの計画のみを優先させたいとします。次の図に示すように、この2つの計画を固定とマークすることにより、オプティマイザがこれらの計画からの最適な計画のみを使用し、残りの計画は無視するようにします。

1つ以上の有効な固定計画を含むベースラインに新しい計画を追加した場合、それらの計画を手動で固定と宣言しないかぎり、オプティマイザはそれらの計画を使用することはできません。

SQL計画管理のユーザー・インタフェース

DBMS_SPMパッケージには、Cloud Controlまたはコマンドラインを通してアクセスします。

Cloud Controlでの「SQL計画ベースライン」ページへのアクセス

Cloud Controlの「SQL計画管理」ページは、SQLプロファイル、SQLパッチおよびSQL計画ベースラインに関する情報を示すGUIです。

「SQL計画ベースライン」ページにアクセスする手順は次のとおりです。

  1. 「Cloud Controlのデータベース・ホーム・ページへのアクセス」の説明に従って、「データベース・ホーム」ページにアクセスします。

  2. 「パフォーマンス」メニューから、「SQL」「SQL計画管理」の順に選択します。

    「SQL計画管理」ページが表示されます。

  3. 「ファイル」をクリックして、図23-7に示す「SQL計画ベースライン」ページを表示します。

    図23-7 「SQL計画ベースライン」サブページ

    図23-7の説明は次にあります。
    「図23-7 「SQL計画ベースライン」サブページ」の説明

    ほとんどのSQL計画管理タスクは、このページ、またはこのページからアクセスするページで実行できます。

関連項目:

DBMS_SPMパッケージ

コマンドラインでは、DBMS_SPMおよびDBMS_XPLAN PL/SQLパッケージを使用して、ほとんどのSQL計画管理タスクを実行します。

次の表に、SQL計画ベースラインを作成、削除およびロードするための主なDBMS_SPMプロシージャとファンクションを示します。

表23-1 DBMS_SPMプロシージャとファンクション

パッケージ プロシージャまたはファンクション 説明

DBMS_SPM

CONFIGURE

このプロシージャは、名前/値の形式でSMBに対する構成オプションを変更します。

DBMS_SPM

CREATE_STGTAB_BASELINE

このプロシージャは、SQL計画ベースラインを1つのデータベースから別のデータベースへと移植することを可能にするステージング表を作成します。

DBMS_SPM

DROP_SQL_PLAN_BASELINE

このファンクションは、計画ベースライン内の一部またはすべての計画を削除します。

DBMS_SPM

LOAD_PLANS_FROM_CURSOR_CACHE

このファンクションは、共有SQL領域(カーソル・キャッシュとも呼ばれます)内の計画をSQL計画ベースラインにロードします。

DBMS_SPM

LOAD_PLANS_FROM_SQLSET

このファンクションは、STSに保存されている計画をSQL計画ベースラインにロードします。

DBMS_SPM

PACK_STGTAB_BASELINE

このファンクションは、SQL計画ベースラインをパックします。つまり、SMBからステージング表にコピーします。

DBMS_SPM

UNPACK_STGTAB_BASELINE

このファンクションは、SQL計画ベースラインをアンパックします。つまり、SQL計画ベースラインをステージング表からSMBにコピーします。

DBMS_XPLAN

DISPLAY_SQL_PLAN_BASELINE

このファンクションは、SQLハンドルによって識別されるSQL文の1つ以上の実行計画を表示します。

関連項目:

  • SQL計画の展開に関連するファンクションの詳細は、「DBMS_SPM展開ファンクションについて」を参照してください。

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

SQL計画管理の基本タスク

この項では、計画のパフォーマンスの低下を防ぎ、オプティマイザによる新しい計画の検討を可能にするためにSQL計画管理を使用する際の基本的なタスクについて説明します。

タスクは次のとおりです。

SQL計画管理の構成

この項の内容は次のとおりです。

SQL計画ベースラインの取得と使用の構成

SQL計画管理は初期化パラメータを使用して制御します。

デフォルト値は次のとおりです。

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false

    計画履歴に存在しない繰返し可能なSQL文の場合、その文に対する初期SQL計画ベースラインは自動的に作成されません

  • OPTIMIZER_USE_SQL_PLAN_BASELINES=true

    既存のSQL計画ベースラインがあるSQL文の場合、新しい計画が未承認の計画としてSQL計画ベースラインに自動的に追加されます。

注意:

前述のパラメータの設定は、互いに独立しています。たとえば、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStrueの場合、OPTIMIZER_USE_SQL_PLAN_BASELINESfalseでも、新しい文に対して初期計画ベースラインが作成されます。

デフォルト動作が希望の動作であれば、この項はスキップしてください。

次の各項では、コマンドラインからデフォルトのパラメータ設定を変更する方法について説明します。Cloud Controlを使用している場合は、これらのパラメータは「SQL計画ベースライン」サブページで設定します。

SQL計画管理での初期計画自動取得の有効化

計画履歴に存在しないSQL文に初期SQL計画ベースラインを自動的に作成するために必要な手順は、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをtrueに設定するのみです。このパラメータは、前に作成されたSQL計画ベースラインへの新しく検出された計画の自動的な追加を制御しません。

SQL計画管理に対して初期計画の自動取得を有効にする手順は次のとおりです。

注意:

自動ベースライン取得が有効になっていると、データベースはすべての再帰的SQLおよび監視SQLを含む、すべての繰返し可能な文に対してSQL計画ベースラインを作成します。したがって、自動取得により、非常に多くの計画ベースラインが作成される可能性があります。

  1. SQL*Plusを起動し、必要な権限でデータベースにログインします。

  2. SQL計画管理の現在の設定を表示します。

    たとえば、管理者権限でSQL*Plusをデータベースに接続し、次のコマンドを実行します(出力例も示します)。

    SQL> SHOW PARAMETER SQL_PLAN
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -----
    optimizer_capture_sql_plan_baselines boolean     FALSE
    optimizer_use_sql_plan_baselines     boolean     TRUE
    

    パラメータが希望どおりに設定されていれば、残りの手順はスキップしてください。

  3. 繰返し可能なSQL文の自動認識およびこれらの文に対するSQL計画ベースラインの生成を有効にするには、次の文を入力します。

    SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

すべてのSQL計画ベースラインの無効化

OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータをfalseに設定すると、データベースはデータベース内のいずれの計画ベースラインも使用しません。

通常は、1つまたは2つの計画ベースラインを無効化しますが、すべては無効化しません。可能なユースケースとしては、SQL計画管理の利点のテストがあげられます。

データベース内のすべてのSQL計画ベースラインを無効化する手順は次のとおりです。

  1. SQL*Plusを適切な権限でデータベースに接続し、SQL計画管理に対する現在の設定を表示します。

    たとえば、管理者権限でSQL*Plusをデータベースに接続し、次のコマンドを実行します(出力例も示します)。

    SQL> SHOW PARAMETER SQL_PLAN
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -----
    optimizer_capture_sql_plan_baselines boolean     FALSE
    optimizer_use_sql_plan_baselines     boolean     TRUE
    

    パラメータが希望どおりに設定されていれば、残りの手順はスキップしてください。

  2. すべての既存の計画ベースラインを無視するには、次の文を入力します。

    SQL> ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false

関連項目:

SQL計画ベースライン初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

SPM展開アドバイザのタスクの管理

SPM展開アドバイザは、SQL計画ベースラインに最近追加された計画を展開するSQLアドバイザです。アドバイザを使用することにより手動で実行する必要がなくなるため、計画の展開が容易になります。

SPM展開アドバイザのタスクの有効化と無効化

SPM展開アドバイザの自動タスクに対する個別のスケジューラ・クライアントは存在しません。

1つのクライアントが、自動SQLチューニング・アドバイザと自動SPM展開アドバイザの両方を制御します。したがって、同じタスクにより両方のアドバイザが有効化または無効化されます。

関連項目:

自動SPM展開アドバイザの有効化および無効化方法については、「自動SQLチューニング・タスクの有効化と無効化」を参照してください

SPM展開アドバイザの自動タスクの構成

DBMS_SPMパッケージでは、SET_EVOLVE_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することにより、計画の自動展開を構成できます。タスクはSYSが所有しているため、SYSのみがタスク・パラメータを設定できます。

ACCEPT_PLANSチューニング・タスク・パラメータは、推奨された計画を自動的に承認するかどうかを指定します。ACCEPT_PLANStrueに設定されている場合(デフォルト)、SQL計画管理はタスクにより推奨されたすべての計画を自動的に承認します。falseに設定されている場合は、タスクは計画を検証し、検証結果についてのレポートを生成しますが、計画の展開は行いません。

前提条件

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

  • データベースにより自動的に計画を展開しない。

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

自動展開タスクのパラメータを設定する手順は次のとおりです。

  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_SPM_EVOLVE_TASK') AND
             ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
               (PARAMETER_NAME = 'TIME_LIMIT') ) );
    

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

    PARAMETER_NAME            VALUE
    ------------------------- ----------
    ACCEPT_PLANS              TRUE
    TIME_LIMIT                3600
    
  2. 次の形式のPL/SQLコードを使用してパラメータを設定します。

    BEGIN
      DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
        task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
    ,   parameter => parameter_name
    ,   value     => value
    );
    END;
    /
    

    たとえば、次のPL/SQLブロックは時間制限を20分に設定し、計画を自動的に承認します。

    BEGIN
      DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
        task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
    ,   parameter => 'LOCAL_TIME_LIMIT'
    ,   value     => 1200
    );
      DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
        task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
    ,   parameter => 'ACCEPT_PLANS'
    ,   value     => 'true'
    );
    END;
    /

関連項目:

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

SQL計画ベースラインでの計画の表示

特定の文についてSQL計画ベースラインに保存された計画を表示するには、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションを使用します。このファンクションは、計画履歴に保存された計画の情報を使用して計画を表示します。

次の表は、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションの関連するパラメータを示しています。

表23-2 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEパラメータ

ファンクション・パラメータ 説明

sql_handle

文のSQLハンドル。PLAN_NAME列でV$SQL.SQL_PLAN_BASELINEDBA_SQL_PLAN_BASELINESビューを結合することにより、SQLハンドルを取得します。

plan_name

文に対する計画の名前。

この項では、コマンドラインからベースライン内の計画を表示する方法について説明します。Cloud Controlを使用する場合は、「SQL計画ベースライン」サブページ(図23-7を参照)から計画ベースラインを表示します。

SQL計画ベースラインに計画を表示するには、次の手順を実行します。

  1. SQL*Plusを適切な権限でデータベースに接続し、計画を表示する問合せのSQL IDを取得します。

    たとえば、SQL IDが31d96zzzpcys9SELECT文に対するSQL計画ベースラインが存在するとします。

  2. SQL IDを使用して計画を問い合せます。

    次の問合せは、SQL IDが31d96zzzpcys9の文に対する実行計画を表示します。

    SELECT PLAN_TABLE_OUTPUT
    FROM   V$SQL s, DBA_SQL_PLAN_BASELINES b, 
           TABLE(
           DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic') 
           ) t
    WHERE  s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
    AND    b.PLAN_NAME=s.SQL_PLAN_BASELINE
    AND    s.SQL_ID='31d96zzzpcys9';
    

    この問合せ例の結果は次のようになります。

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
     
    ---------------------------------------------------------------------------
    SQL handle: SQL_513f7f8a91177b1a
    SQL text: select * from hr.employees where employee_id=100
    ---------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------
    Plan name: SQL_PLAN_52gvzja8jfysuc0e983c6         Plan id: 3236529094
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    ---------------------------------------------------------------------------
     
    Plan hash value: 3236529094
     
    -----------------------------------------------------
    | Id  | Operation                   | Name          |
    -----------------------------------------------------
    |   0 | SELECT STATEMENT            |               |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
    |   2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |
    -----------------------------------------------------
    

    結果は、SQL ID 31d96zzzpcysに対する計画の名前はSQL_PLAN_52gvzja8jfysuc0e983c6であり、自動的に取得されたことを示しています。

関連項目:

  • 「SQL管理ベース」

  • DISPLAY_SQL_PLAN_BASELINEファンクションで使用する追加のパラメータの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

SQL計画ベースラインのロード

DBMS_SPMを使用すると、SQL計画ベースラインへの既存計画セットの一括ロードを開始できます。

この項の内容は次のとおりです。

SQLチューニング・セットからの計画のロード

SQLチューニング・セットは、1つ以上のSQL文、実行統計および実行コンテキストを含むデータベース・オブジェクトです。このトピックでは、STSから計画をロードする方法について説明します。

計画は、DBMS_SPM.LOAD_PLANS_FROM_SQLSETファンクションまたはCloud Controlを使用してロードできます。次の表では、ファンクション・パラメータの一部を説明します。

表23-3 LOAD_PLANS_FROM_SQLSETのパラメータ

ファンクション・パラメータ 説明

sqlset_name

そこからSQL計画ベースラインに計画をロードするSTSの名前。

basic_filter

条件を満たす計画のみをロード対象として選択するためにSTSに適用されるフィルタ。このフィルタは、DBA_SQLSET_STATEMENTSビューに対して指定可能なWHERE句条件の形式にすることができます。例は、basic_filter => 'sql_text like ''select /*LOAD_STS*/%'''です。

fixed

デフォルトのNOにすると、ロードされた計画が、固定されていない計画として使用されます。YESにすると、ロードされた計画は固定計画になります。「計画の選択」では、オプティマイザが計画ベースライン内で固定されていない計画よりも固定計画を優先させることについて説明しています。

この項では、コマンドラインから計画をロードする方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図23-7を参照)に移動し、「ロード」をクリックして、SQLチューニング・セットから計画ベースラインをロードします。

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

  • ロードした計画は固定にしない。

  • 次の問合せを実行済である。

    SELECT /*LOAD_STS*/ *
    FROM   sh.sales
    WHERE  quantity_sold > 40
    ORDER BY prod_id;
    
  • 共有SQL領域から、ユーザーSPMにより所有され、SPM_STSという名前のSQLチューニング・セットに計画をロード済である。

SQLチューニング・セットから計画をロードする手順は次のとおりです。

  1. SQL*Plusを適切な権限でデータベースに接続し、SQLチューニング・セットに含まれる計画を確認します。

    たとえば、DBA_SQLSET_STATEMENTSでSTS名を問い合せます(出力例も示します)。

    SELECT SQL_TEXT
    FROM   DBA_SQLSET_STATEMENTS
    WHERE  SQLSET_NAME = 'SPM_STS';
     
    SQL_TEXT
    --------------------
    SELECT /*LOAD_STS*/
    *
    FROM sh.sales
    WHERE quantity_sold
    > 40
    ORDER BY prod_id
    

    出力は、select /*LOAD_STS*/文に対する計画がSTS内にあることを示しています。

  2. STSからSQL計画ベースラインに計画をロードします。

    たとえば、SQL*Plusで次のようにファンクションを実行します。

    VARIABLE cnt NUMBER
    EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
                    sqlset_name  => 'SPM_STS', -
                    basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' );
    

    basic_filterパラメータは、関心のある問合せに対する計画のみをロードするWHERE句を指定します。変数cntには、STSからロードされた計画の数が含まれます。

  3. データ・ディクショナリを問い合せて、文に対するベースラインに計画がロードされたことを確認します。

    次の文では、DBA_SQL_PLAN_BASELINESビューを問い合せます(出力例も示します)。

    SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
      2         ORIGIN, ENABLED, ACCEPTED
      3  FROM   DBA_SQL_PLAN_BASELINES;
    
    SQL_HANDLE            SQL_TEXT        PLAN_NAME        ORIGIN      ENA ACC
    --------------------- --------------- ---------------- ----------- --- ---
    SQL_a8632bd857a4a25e  SELECT          SQL_PLAN_ahstb   MANUAL-LOAD YES YES
                          /*LOAD_STS*/*   v1bu98ky1694fc6b
                          FROM sh.sales
                          WHERE 
                          quantity_sold 
                          > 40 ORDER BY                    
                          prod_id

    出力は、計画が承認済であること、つまり計画ベースラインに含まれることを示しています。また、ロード元はMANUAL-LOADであり、これは計画は自動的に取得されたのではなく、エンド・ユーザーによりロードされたことを意味します。

  4. オプションで、STSを削除します。

    たとえば、DBMS_SQLTUNE.DROP_SQLSETを次のように実行して、SPM_STSチューニング・セットを削除します。

    EXEC SYS.DBMS_SQLTUNE.DROP_SQLSET( sqlset_name  => 'SPM_STS', -
                                       sqlset_owner => 'SPM' );

関連項目:

  • DBA_SQL_PLAN_BASELINESビューの詳細は、『Oracle Databaseリファレンス』を参照してください

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

共有SQL領域からの計画のロード

このトピックでは、PL/SQLを使用してカーソル・キャッシュとも呼ばれる共有SQL領域から計画をロードする方法について説明します。

DBMS_SPMパッケージのLOAD_PLANS_FROM_CURSOR_CACHEファンクションを使用して計画をロードします。次の表では、ファンクション・パラメータの一部を説明します。

表23-4 LOAD_PLANS_FROM_CURSOR_CACHEのパラメータ

ファンクション・パラメータ 説明

sql_id

SQL文の識別子。共有SQL領域内のSQL文を識別します。

fixed

デフォルトのNOにすると、ロードされた計画が、固定されていない計画として使用されます。YESにすると、ロードされた計画は固定計画になります(「固定計画」を参照)。「計画の選択」では、オプティマイザが計画ベースライン内で固定されていない計画よりも固定計画を優先させることについて説明しています。

この項では、コマンドラインを使用して計画をロードする方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図23-7を参照)に移動し、「ロード」をクリックして、共有SQL領域から計画ベースラインをロードします。

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

  • 次の問合せを実行済である。

    SELECT /*LOAD_CC*/ *
    FROM   sh.sales
    WHERE  quantity_sold > 40
    ORDER BY prod_id;
    
  • ロードした計画は固定にしない。

共有SQL領域から計画をロードする手順は次のとおりです。

  1. SQL*Plusを適切な権限でデータベースに接続し、共有SQL領域内の関連する文のSQL IDを確認します。

    たとえば、V$SQLsh.sales問合せのSQL IDを問い合せます(出力例も示します)。

    SELECT   SQL_ID, CHILD_NUMBER AS "Child Num",
             PLAN_HASH_VALUE AS "Plan Hash",
             OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash"
    FROM     V$SQL
    WHERE    SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%';
     
    SQL_ID         Child Num  Plan Hash Opt Env Hash
    ------------- ---------- ---------- ------------
    27m0sdw9snw59          0 1421641795   3160571937
    

    前述の出力は、文のSQL IDが27m0sdw9snw59であることを示しています。

  2. 指定した文に対する計画をSQL計画ベースラインにロードします。

    たとえば、SQL*PlusでLOAD_PLANS_FROM_CURSOR_CACHEファンクションを実行して、SQL IDが27m0sdw9snw59の文に対する計画をロードします。

    VARIABLE cnt NUMBER
    EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                        sql_id => '27m0sdw9snw59');
    

    前述の例では、変数cntにはロードされた計画の数が含まれます。

  3. データ・ディクショナリを問い合せて、文に対するベースラインに計画がロードされたことを確認します。

    次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
           ORIGIN, ENABLED, ACCEPTED
    FROM   DBA_SQL_PLAN_BASELINES;
     
    SQL_HANDLE            SQL_TEXT             PLAN_NAME             ORIGIN      ENA ACC
    --------------------- -------------------- --------------------- ----------- --- ---
    SQL_a8632bd857a4a25e  SELECT /*LOAD_CC*/   SQL_PLAN_gdkvzfhrgkda MANUAL-LOAD YES YES
                          *                    71694fc6b
                          FROM sh.sales
                          WHERE quantity_sold
                          > 40                           
                          ORDER BY prod_id

    出力は、計画が承認済であること、つまり文に対する計画ベースラインに含まれることを示しています。また、ロード元はMANUAL-LOADであり、これは文は自動的に取得されたのではなく、手動でロードされたことを意味します。

関連項目:

  • DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEファンクションの使用方法の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

  • DBA_SQL_PLAN_BASELINESビューの詳細は、『Oracle Databaseリファレンス』を参照してください

ステージング表からの計画のロード

最適な計画をソース・データベースから別の宛先データベースに転送することが必要になる場合があります。

たとえば、テスト・データベース上で一連の計画を調査し、高いパフォーマンスを発揮することを確認したとします。その後、これらの計画を本番データベースにロードする場合が考えられます。

ステージング表とは、計画がアンパック中に表から消えないように、その表が存在する間、計画を保存する表です。ステージング表を作成するには、DBMS.CREATE_STGTAB_BASELINEプロシージャを使用します。ステージング表のパック(行の挿入)およびアンパック(行の抽出)には、DBMS_SPMパッケージのPACK_STGTAB_BASELINEUNPACK_STGTAB_BASELINEファンクションを使用します。Oracle Data Pump ImportとExportを使用すると、ステージング表を別のデータベースにコピーできます。

次の図は、基本的な手順を示しています。

図23-8 ステージング表からの計画のロード

図23-8の説明は次にあります。
「図23-8 ステージング表からの計画のロード」の説明

前提条件

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

  • ソース・データベース内にstage1という名前のステージング表を作成する。

  • ユーザーspmが所有するすべての計画をステージング表にロードする。

  • ステージング表を宛先データベースに転送する。

  • stage1内の計画を固定計画としてロードする。

あるデータベースから別のデータベースへとSQL計画ベースラインのセットを転送する手順は次のとおりです。

  1. SQL*Plusを適切な権限でソース・データベースに接続し、CREATE_STGTAB_BASELINEプロシージャを使用してステージング表を作成します。

    次の例では、stage1というステージング表が作成されます。

    BEGIN
      DBMS_SPM.CREATE_STGTAB_BASELINE (
        table_name => 'stage1');
    END;
    /
    
  2. ソース・データベースで、SQL管理ベースからステージング表にエクスポートするSQL計画ベースラインをパックします。

    次の例では、ユーザーspmが作成した有効な計画ベースラインをステージング表stage1にパックします。計画名(plan_name)、SQLハンドル(sql_handle)またはその他の計画基準を使用して、SQL計画ベースラインを選択します。table_nameパラメータは必須です。

    DECLARE
      my_plans number;
    BEGIN
      my_plans := DBMS_SPM.PACK_STGTAB_BASELINE (
        table_name => 'stage1'
    ,   enabled    => 'yes'
    ,   creator    => 'spm'
    );
    END;
    /
    
  3. Oracle Data Pump Exportを使用して、ステージング表stage1をダンプ・ファイルにエクスポートします。

  4. ダンプ・ファイルを宛先データベースのホストに転送します。

  5. 宛先データベースで、Oracle Data Pump Importユーティリティを使用して、ステージング表stage1をダンプ・ファイルからインポートします。

  6. 宛先データベースで、SQL計画ベースラインをステージング表からSQL管理ベースにアンパックします。

    次の例では、ステージング表stage1に格納された固定計画ベースラインがすべて解凍されます。

    DECLARE
      my_plans NUMBER;
    BEGIN
      my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (
        table_name => 'stage1'
    ,   fixed      => 'yes'
    );
    END;
    /

関連項目:

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

  • データ・ポンプ・エクスポートおよびインポート・ユーティリティの使用方法の詳細は、『Oracle Databaseユーティリティ』を参照してください。

SQL計画ベースラインの手動での展開

SQL計画管理の展開タスクが自動的に実行されるように構成することをお薦めします。またPL/SQLまたはCloud Controlを使用して、承認されていない計画を手動で展開することにより、計画ベースライン内の現在のいずれの計画よりもパフォーマンスが優れているかどうかを判断することもできます。

この項の内容は次のとおりです。

DBMS_SPM展開ファンクションについて

このトピックでは、計画の展開を管理するための主なDBMS_SPMファンクションについて説明します。展開タスクは手動で実行するか、自動的に実行されるようにスケジュールします。

表23-5 計画の展開タスクを管理するためのDBMS_SPMファンクションとプロシージャ

プロシージャまたはファンクション 説明

ACCEPT_SQL_PLAN_BASELINE

このファンクションは、1つの計画をSQL計画ベースラインに展開するという1つの推奨事項を承認します。

CREATE_EVOLVE_TASK

このファンクションは、指定されたSQL文に対する1つ以上の計画の展開を準備するためのアドバイザ・タスクを作成します。入力パラメータは、SQLハンドル、計画名または計画名のリスト、時間制限、タスク名および説明になります。

EXECUTE_EVOLVE_TASK

このファンクションは展開タスクを実行します。入力パラメータは、タスク名、実行名および実行の説明になります。指定しない場合は、アドバイザによって名前が生成され、ファンクションによって戻されます。

IMPLEMENT_EVOLVE_TASK

このファンクションは展開タスクに対するすべての推奨事項を実装します。基本的にはこのファンクションは、推奨されるすべての計画に対してACCEPT_SQL_PLAN_BASELINEを使用することと同じです。入力パラメータは、タスク名、計画名、所有者名および実行名などです。

REPORT_EVOLVE_TASK

このファンクションは、展開タスクの結果をCLOBとして表示します。入力パラメータは、タスク名およびレポートに含めるセクションです。

SET_EVOLVE_TASK_PARAMETER

このファンクションは展開タスク・パラメータの値を更新します。このリリースでは、有効なパラメータはTIME_LIMITのみです。

SPM展開アドバイザが自動的に実行されるように構成することをお薦めします(「SPM展開アドバイザの自動タスクの構成」を参照)。SQL計画ベースラインを手動で展開することもできます。図23-9は、SQL計画管理タスクを管理するための基本的なワークフローを示しています。

図23-9 SQL計画ベースラインの展開

図23-9の説明は次にあります。
「図23-9 SQL計画ベースラインの展開」の説明

通常、SQL計画の展開タスクを管理する順序は次のとおりです。

  1. 展開タスクを作成します。

  2. オプションで、展開タスクのパラメータを設定します。

  3. 展開タスクを実行します。

  4. タスク内の推奨事項を実装します。

  5. タスクの結果をレポートします。

関連項目:

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

展開タスクの管理

このトピックでは、タスクを作成して実行し、その後その推奨事項を実装する典型的なユースケースについて説明します。

次の表では、CREATE_EVOLVE_TASKファンクションのパラメータの一部を説明します。

表23-6 DBMS_SPM.CREATE_EVOLVE_TASKのパラメータ

ファンクション・パラメータ 説明

sql_handle

文のSQLハンドル。デフォルトのNULLでは、承認されていない計画のあるすべてのSQL文を考慮します。

plan_name

計画の識別子。デフォルトのNULLは、指定されたSQLハンドルのすべての承認されていない計画、またはSQLハンドルがNULLの場合はすべてのSQL文のすべての承認されていない計画を考慮することを意味します。

time_limit

時間制限(分)。最初の承認されていない計画に対する時間制限は、入力値と等しくなります。2つ目の承認されていない計画に対する時間制限は、入力値から最初の計画の検証にかかった時間を引いた時間になります。以下同様です。デフォルトのDBMS_SPM.AUTO_LIMITは、計画に対して実行する必要がある検証の数に基づいて適切な時間制限が選択されることを意味します。

task_name

展開タスクのユーザー指定名。

この項では、コマンドラインから計画ベースラインを展開する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページで、計画を選択してから「展開」をクリックします。

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

  • 自動展開タスクを有効にしていない(「SPM展開アドバイザのタスクの管理」を参照)。

  • 次の問合せに対するSQL計画ベースラインを作成する。

    SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
    FROM   products p, sales s
    WHERE  p.prod_id = s.prod_id
    AND    p.prod_category_id =203
    GROUP BY prod_name;
    
  • 問合せのパフォーマンスを向上させるための索引を2つ作成し、これらの索引を使用する計画のパフォーマンスが計画ベースライン内に現在含まれる計画よりも優れている場合は、その計画を展開する。

指定した計画を展開する手順は次のとおりです。

  1. 次の手順で初期設定を実行します。

    1. SQL*Plusを管理者権限でデータベースに接続し、共有プールとバッファ・キャッシュをフラッシュすることによりチュートリアルの準備を行います。

      ALTER SYSTEM FLUSH SHARED_POOL;
      ALTER SYSTEM FLUSH BUFFER_CACHE;
      
    2. SQL計画ベースラインの自動取得を有効にします。

      たとえば、次の文を入力します。

      ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
      
    3. ユーザーshとしてデータベースに接続し、SQL*Plusの表示パラメータを設定します。

      CONNECT sh
      -- enter password
      SET PAGES 10000 LINES 140
      SET SERVEROUTPUT ON
      COL SQL_TEXT FORMAT A20
      COL SQL_HANDLE FORMAT A20
      COL PLAN_NAME FORMAT A30
      COL ORIGIN FORMAT A12
      SET LONGC 60535
      SET LONG 60535
      SET ECHO ON
      
  2. SQL計画管理により取得されるように、SELECT文を実行します。

    1. SELECT /* q1_group_by */文を実行します(1回目)。

      データベースは繰返し可能な文に対する計画のみを取得するため、この文に対する計画ベースラインは空になります。

    2. データ・ディクショナリを問い合せて、計画ベースライン内に計画がないことを確認します。

      たとえば、次の問合せを実行します(出力例も示します)。

      SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, 
             ACCEPTED, FIXED, AUTOPURGE
      FROM   DBA_SQL_PLAN_BASELINES
      WHERE  SQL_TEXT LIKE '%q1_group%';
      
      no rows selected
      

      SQL計画管理は繰返し可能な文に対する計画のみを取得するため、この結果は予想どおりです。

    3. SELECT /* q1_group_by */文を実行します(2回目)。

  3. データ・ディクショナリを問い合せて、文に対する計画ベースラインに計画がロードされたことを確認します。

    次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
           ORIGIN, ENABLED, ACCEPTED, FIXED 
    FROM   DBA_SQL_PLAN_BASELINES
    WHERE  SQL_TEXT LIKE '%q1_group%';
     
    SQL_HANDLE           SQL_TEXT         PLAN_NAME                      ORIGIN       ENA ACC FIX
    -------------------- ---------------- ------------------------------ ------------ --- --- ---
    SQL_07f16c76ff893342 SELECT /* q1_gro SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO
                         up_by */ prod_na
                         me, sum(quantity
                         _sold) FROM 
                         products p, 
                         sales s WHERE 
                         p.prod_id = 
                         s.prod_id AND
                         p.prod_category
                         _id =203 GROUP
                         BY prod_name
    

    出力は、計画が承認済であること、つまり文に対する計画ベースラインに含まれることを示しています。また、ロード元はAUTO-CAPTUREであり、これは文は手動でロードされたのではなく、自動的に取得されたことを意味します。

  4. 文に対する計画をEXPLAINし、オプティマイザによりこの計画が使用されていることを確認します。

    たとえば、次のように計画をEXPLAINしてから表示します。

    EXPLAIN PLAN FOR  
      SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
      FROM   products p, sales s
      WHERE  p.prod_id = s.prod_id
      AND    p.prod_category_id =203
      GROUP BY prod_name;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));
    

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

    Plan hash value: 1117033222
     
    ------------------------------------------
    | Id  | Operation             | Name     |
    ------------------------------------------
    |   0 | SELECT STATEMENT      |          |
    |   1 |  HASH GROUP BY        |          |
    |   2 |   HASH JOIN           |          |
    |   3 |    TABLE ACCESS FULL  | PRODUCTS |
    |   4 |    PARTITION RANGE ALL|          |
    |   5 |     TABLE ACCESS FULL | SALES    |
    ------------------------------------------
     
    Note
    -----
       - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement
    

    注意事項は、オプティマイザが前の手順でリストされていた計画名で示されている計画を使用していることを示しています。

  5. SELECT /* q1_group_by */文のパフォーマンスを向上させるために2つの索引を作成します。

    たとえば、次の文を使用します。

    CREATE INDEX ind_prod_cat_name 
      ON products(prod_category_id, prod_name, prod_id);
    CREATE INDEX ind_sales_prod_qty_sold 
      ON sales(prod_id, quantity_sold);
    
  6. SELECT /* q1_group_by */文を再度実行します。

    自動取得が有効になっているため、この文に対する新しい計画が計画ベースラインに移入されます。

  7. データ・ディクショナリを問い合せて、文に対するSQL計画ベースラインに計画がロードされたことを確認します。

    次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
    FROM   DBA_SQL_PLAN_BASELINES
    WHERE  SQL_HANDLE IN ('SQL_07f16c76ff893342')
    ORDER BY SQL_HANDLE, ACCEPTED;
    
    SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN       ENA ACC
    -------------------- -------------------- ------------------------------ ------------ --- ---
    SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO
                         y */ prod_name, sum(
                         quantity_sold)
                         FROM   products p, s
                         ales s
                         WHERE  p.prod_id = s
                         .prod_id
                         AND    p.prod_catego
                         ry_id =203
                         GROUP BY prod_name
     
    SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
                         y */ prod_name, sum(
                         quantity_sold)
                         FROM   products p, s
                         ales s
                         WHERE  p.prod_id = s
                         .prod_id
                         AND    p.prod_catego
                         ry_id =203
                         GROUP BY prod_name
    

    出力は、新しい計画が承認されていないこと、つまり文の履歴には含まれているが、SQL計画ベースラインに含まれていないことを示しています。

  8. 文に対する計画をEXPLAINし、オプティマイザにより元の索引なしの計画が使用されていることを確認します。

    たとえば、次のように計画をEXPLAINしてから表示します。

    EXPLAIN PLAN FOR
      SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
      FROM   products p, sales s
      WHERE  p.prod_id = s.prod_id
      AND    p.prod_category_id =203
      GROUP BY prod_name;
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));
    

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

    Plan hash value: 1117033222
     
    ------------------------------------------
    | Id  | Operation             | Name     |
    ------------------------------------------
    |   0 | SELECT STATEMENT      |          |
    |   1 |  HASH GROUP BY        |          |
    |   2 |   HASH JOIN           |          |
    |   3 |    TABLE ACCESS FULL  | PRODUCTS |
    |   4 |    PARTITION RANGE ALL|          |
    |   5 |     TABLE ACCESS FULL | SALES    |
    ------------------------------------------
     
    Note
    -----
       - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement
    

    注意事項は、オプティマイザが手順3でリストされていた計画名で示されている計画を使用していることを示しています。

  9. 管理者として接続し、承認されていない計画を持つすべてのSQL文を考慮する展開タスクを作成します。

    たとえば、DBMS_SPM.CREATE_EVOLVE_TASKファンクションを実行し、その後タスクの名前を取得します。

    CONNECT / AS SYSDBA
    VARIABLE cnt NUMBER
    VARIABLE tk_name VARCHAR2(50)
    VARIABLE exe_name VARCHAR2(50)
    VARIABLE evol_out CLOB
     
    EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK( 
      sql_handle => 'SQL_07f16c76ff893342', 
      plan_name  => 'SQL_PLAN_0gwbcfvzskcu20135fd6c');
     
    SELECT :tk_name FROM DUAL;
    

    次の出力例は、タスクの名前を示しています。

    :EVOL_OUT
    --------------------------------------------------------------------------
    TASK_11
    

    これでタスクが作成され、一意の名前が付けられたので、タスクを実行できます。

  10. タスクを実行します。

    たとえば、DBMS_SPM.EXECUTE_EVOLVE_TASKファンクションを実行します(出力例も示します)。

    EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); 
    SELECT :exe_name FROM DUAL;
    
    :EXE_NAME
    ---------------------------------------------------------------------------
    EXEC_1
    
  11. レポートを表示します。

    たとえば、DBMS_SPM.REPORT_EVOLVE_TASKファンクションを実行します(出力例も示します)。

    EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
    SELECT :evol_out FROM DUAL;
    
    GENERAL INFORMATION SECTION
    --------------------------------------------------------------------------
     
     Task Information:
     ---------------------------------------------
     Task Name            : TASK_11
     Task Owner           : SYS
     Execution Name       : EXEC_1
     Execution Type       : SPM EVOLVE
     Scope                : COMPREHENSIVE
     Status               : COMPLETED
     Started              : 01/09/2012 12:21:27
     Finished             : 01/09/2012 12:21:29
     Last Updated         : 01/09/2012 12:21:29
     Global Time Limit    : 2147483646
     Per-Plan Time Limit  : UNUSED
     Number of Errors     : 0
    ---------------------------------------------------------------------------
     
    SUMMARY SECTION
    ---------------------------------------------------------------------------
      Number of plans processed  : 1
      Number of findings         : 1
      Number of recommendations  : 1
      Number of errors           : 0
    ---------------------------------------------------------------------------
     
    DETAILS SECTION
    ---------------------------------------------------------------------------
     Object ID         : 2
     Test Plan Name    : SQL_PLAN_0gwbcfvzskcu20135fd6c
     Base Plan Name    : SQL_PLAN_0gwbcfvzskcu242949306
     SQL Handle        : SQL_07f16c76ff893342
     Parsing Schema    : SH
     Test Plan Creator : SH
     SQL Text          : SELECT /*q1_group_by*/ prod_name, 
                         sum(quantity_sold) 
                         FROM products p, sales s 
                         WHERE p.prod_id=s.prod_id AND p.prod_category_id=203 
                         GROUP BY prod_name
     
    Execution Statistics:
    -----------------------------
                        Base Plan                     Test Plan
                        ----------------------------  ------------------------
     Elapsed Time (s):  .044336                       .012649
     CPU Time (s):      .044003                       .012445
     Buffer Gets:       360                           99
     Optimizer Cost:    924                           891
     Disk Reads:        341                           82
     Direct Writes:     0                             0
     Rows Processed:    4                             2
     Executions:        5                             9
     
     
    FINDINGS SECTION
    ---------------------------------------------------------------------------
     
    Findings (1):
    -----------------------------
     1. The plan was verified in 2.18 seconds. It passed the benefit criterion
        because its verified performance was 2.01 times better than that of the
        baseline plan.
     
    Recommendation:
    -----------------------------
     Consider accepting the plan. Execute
     dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', object_id => 2,
     task_owner => 'SYS');
     
    EXPLAIN PLANS SECTION
    ---------------------------------------------------------------------------
     
    Baseline Plan
    -----------------------------
     Plan Id          : 1
     Plan Hash Value  : 1117033222
     
    ---------------------------------------------------------------------------
    | Id| Operation               | Name     | Rows | Bytes   |Cost | Time    |
    ---------------------------------------------------------------------------
    | 0 | SELECT STATEMENT        |          |   21 |     861 | 924 | 00:00:12|
    | 1 |   HASH GROUP BY         |          |   21 |     861 | 924 | 00:00:12|
    | *2|    HASH JOIN            |          |267996|10987836 | 742 | 00:00:09|
    | *3|     TABLE ACCESS FULL   | PRODUCTS |   21 |     714 |   2 | 00:00:01|
    | 4 |     PARTITION RANGE ALL |          |918843| 6431901 | 662 | 00:00:08|
    | 5 |      TABLE ACCESS FULL  | SALES    |918843| 6431901 | 662 | 00:00:08|
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ------------------------------------------
    * 2 - access("P"."PROD_ID"="S"."PROD_ID")
    * 3 - filter("P"."PROD_CATEGORY_ID"=203)
      
    Test Plan
    -----------------------------
     Plan Id          : 2
     Plan Hash Value  : 20315500
     
    ---------------------------------------------------------------------------
    |Id| Operation            | Name             | Rows | Bytes  | Cost| Time |
    ---------------------------------------------------------------------------
    | 0|SELECT STATEMENT      |                  |    21|     861|891|00:00:11|
    | 1|  SORT GROUP BY NOSORT|                  |    21|     861|891|00:00:11|
    | 2|   NESTED LOOPS       |                  |267996|10987836|891|00:00:11|
    |*3|    INDEX RANGE SCAN  |IND_PROD_CAT_NAME |    21|     714|  1|00:00:01|
    |*4|    INDEX RANGE SCAN  |IND_SALES_PROD_QTY| 12762|   89334| 42|00:00:01|
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ------------------------------------------
    * 3 - access("P"."PROD_CATEGORY_ID"=203)
    * 4 - access("P"."PROD_ID"="S"."PROD_ID")
    

    このレポートは、2つの新しい索引を使用する新しい実行計画のパフォーマンスが元の計画よりも優れていることを示しています。

  12. 展開タスクの推奨事項を実装します。

    たとえば、IMPLEMENT_EVOLVE_TASKファンクションを実行します。

    EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
    
  13. データ・ディクショナリを問い合せて、新しい計画が承認されたことを確認します。

    問合せにより、次のサンプル出力が提供されます。

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
    FROM   DBA_SQL_PLAN_BASELINES
    WHERE  SQL_HANDLE IN ('SQL_07f16c76ff893342')
    ORDER BY SQL_HANDLE, ACCEPTED;
    
    SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN       ENA ACC
    -------------------- -------------------- ------------------------------ ------------ --- ---
    SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES YES
                         y */ prod_name, sum(
                         quantity_sold)
                         FROM   products p, s
                         ales s
                         WHERE  p.prod_id = s
                         .prod_id
                         AND    p.prod_catego
                         ry_id =203
                         GROUP BY prod_name
     
    SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
                         y */ prod_name, sum(
                         quantity_sold)
                         FROM   products p, s
                         ales s
                         WHERE  p.prod_id = s
                         .prod_id
                         AND    p.prod_catego
                         ry_id =203
                         GROUP BY prod_name
    

    出力は、新しい計画が承認されたことを示しています。

  14. 例の後のクリーンアップを実行します。

    たとえば、次の文を入力します。

    EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');
    EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_9049245213a986b3');
    EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_bb77077f5f90a36b');
    EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_02a86218930bbb20');
    DELETE FROM SQLLOG$;
    CONNECT sh
    -- enter password
    DROP INDEX IND_SALES_PROD_QTY_SOLD;
    DROP INDEX IND_PROD_CAT_NAME;

関連項目:

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

SQL計画ベースラインの削除

SQL計画ベースラインから一部またはすべての計画を削除できます。この方法は、SQL計画管理のテスト時に役立つことがあります。

計画を削除するには、DBMS_SPM.DROP_SQL_PLAN_BASELINEファンクションを使用します。このファンクションは、削除された計画の数を戻します。次の表では、入力パラメータを説明します。

表23-7 DROP_SQL_PLAN_BASELINEのパラメータ

ファンクション・パラメータ 説明

sql_handle

SQL文の識別子。

plan_name

特定の計画の名前。デフォルトのNULLにすると、sql_handleで識別されるSQL文に関連付けられているすべての計画が削除されます。

この項では、コマンドラインからベースラインを削除する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページで、計画を選択してから「削除」をクリックします。

このチュートリアルでは、次のSQL文に対するすべての計画を削除することにより、実質的にSQL計画ベースラインを削除するものとします。

SELECT /* repeatable_sql */ COUNT(*) FROM hr.jobs;

SQL計画ベースラインを削除する手順は次のとおりです。

  1. SQL*Plusを適切な権限でデータベースに接続し、データ・ディクショナリで計画ベースラインを問い合せます。

    次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。

    SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
      2         ENABLED, ACCEPTED
      3  FROM   DBA_SQL_PLAN_BASELINES
      4  WHERE  SQL_TEXT LIKE 'SELECT /* repeatable_sql%';
     
    SQL_HANDLE           SQL_TEXT             PLAN_NAME                      ORIGIN         ENA ACC
    -------------------- -------------------- ------------------------------ -------------- --- ---
    SQL_b6b0d1c71cd1807b SELECT /* repeatable SQL_PLAN_bdc6jswfd303v2f1e9c20 AUTO-CAPTURE   YES YES
                         _sql */ count(*) fro
                         m hr.jobs
    
  2. 文に対するSQL計画ベースラインを削除します。

    次の例は、SQLハンドルがSQL_b6b0d1c71cd1807bの計画ベースラインを削除し、削除された計画の数を戻します。計画名(plan_name)、SQLハンドル(sql_handle)またはその他の計画基準を使用して、計画ベースラインを指定します。table_nameパラメータは必須です。

    DECLARE
      v_dropped_plans number;
    BEGIN
      v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
         sql_handle => 'SQL_b6b0d1c71cd1807b'
    );
      DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
    END;
    /
    
  3. 計画が削除されたことを確認します。

    たとえば、次の問合せを実行します。

    SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
           ENABLED, ACCEPTED
    FROM   DBA_SQL_PLAN_BASELINES
    WHERE  SQL_TEXT LIKE 'SELECT /* repeatable_sql%';
     
    no rows selected

関連項目:

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

SQL管理ベースの管理

SQL管理ベース(SMB)は、SYSAUX表領域にあるデータ・ディクショナリの一部です。SMBには、文のログ、計画履歴、SQL計画ベースラインおよびSQLプロファイルが格納されます。

この項では、SMB用のディスク領域使用量パラメータの変更方法およびSMBでの計画の保存時間の変更方法について説明します。

DBA_SQL_MANAGEMENT_CONFIGビューには、SMBの現在の構成設定が表示されます。表23-8に、PARAMETER_NAME列のパラメータを示します。

表23-8 DBA_SQL_MANAGEMENT_CONFIG.PARAMETER_NAMEのパラメータ

パラメータ 説明

SPACE_BUDGET_PERCENT

SQL管理ベースが使用可能なSYSAUX領域の最大の割合。デフォルトは10です。この制限の許容範囲は1から50%です。

PLAN_RETENTION_WEEKS

消去するまで未使用の計画を保持する週の数。デフォルトは53です。

SMBのディスク領域制限の変更

週に1回のバックグラウンド・プロセスによって、SMBが使用する総領域が測定されます。定義した制限を超過すると、アラート・ログに警告が書き込まれます。SMB領域の制限が引き上げられるか、SYSAUX表領域のサイズが引き上げられるか、またはSQL管理オブジェクト(SQL計画ベースラインまたはSQLプロファイル)の消去によりSMBによって使用されている領域が減るまで、データベースは週に1回アラートを生成し続けます。このタスクでは、DBMS_SPM.CONFIGUREプロシージャを使用して制限を変更する方法について説明します。

前提条件

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

  • 現在のSMB領域制限がデフォルトの10%である。

  • 制限の比率を30%に変更する。

SMBの制限の比率を変更する手順は次のとおりです。

  1. SQL*Plusを適切な権限でデータベースに接続し、データ・ディクショナリを問い合せて、現在の領域配分の比率を確認します。

    たとえば、次の問合せを実行します(出力例も示します)。

    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 
           ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
             WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
           PARAMETER_VALUE/100 * 
           ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
             WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
    FROM DBA_SQL_MANAGEMENT_CONFIG
    WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';
    
    PARAMETER_NAME          %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
    -------------------- ---------- ----------------- -------------------
    SPACE_BUDGET_PERCENT         10          211.4375            21.14375
    
  2. 比率設定を変更します。

    たとえば、設定を30%に変更するには次のコマンドを実行します。

    EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);
    
  3. データ・ディクショナリを問い合せて、変更を確認します。

    たとえば、次の結合を実行します(出力例も示します)。

    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 
           ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
             WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
           PARAMETER_VALUE/100 * 
           ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
             WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
    FROM   DBA_SQL_MANAGEMENT_CONFIG
    WHERE  PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';
    
    PARAMETER_NAME          %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
    -------------------- ---------- ----------------- -------------------
    SPACE_BUDGET_PERCENT         30          211.4375            63.43125

関連項目:

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

SMBでの計画保存ポリシーの変更

週次にスケジュールされた消去タスクにより、SQL計画の管理で使用されるディスク領域が管理されます。このタスクは、メンテナンス期間に自動化タスクとして実行されます。データベースは、計画保存期間を超える間使用されていない計画を消去します。消去対象は、SMBに保存されたその計画のLAST_EXECUTEDタイムスタンプで識別されます。デフォルトの保存期間は53週です。指定できる期間の範囲は5から523週です。

このタスクでは、DBMS_SPM.CONFIGUREプロシージャを使用して計画保存期間を変更する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図23-7を参照)で計画保存ポリシーを設定します。

SMBの計画保存期間を変更する手順は次のとおりです。

  1. SQL*Plusを適切な権限でデータベースに接続し、データ・ディクショナリを問い合せて、現在の計画保存期間を確認します。

    たとえば、次の問合せを実行します(出力例も示します)。

    SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
      2  FROM   DBA_SQL_MANAGEMENT_CONFIG
      3  WHERE  PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';
     
    PARAMETER_NAME                 PARAMETER_VALUE
    ------------------------------ ---------------
    PLAN_RETENTION_WEEKS                        53
    
  2. 保存期間を変更します。

    たとえば、CONFIGUREプロシージャを実行して期間を105週に変更します。

    EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);
    
  3. データ・ディクショナリを問い合せて、変更を確認します。

    たとえば、次の問合せを実行します。

    SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
      2  FROM   DBA_SQL_MANAGEMENT_CONFIG
      3  WHERE  PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';
     
    PARAMETER_NAME                 PARAMETER_VALUE
    ------------------------------ ---------------
    PLAN_RETENTION_WEEKS                       105

関連項目:

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