1. SQLチューニング・ガイド
  2. SQL管理オブジェクト
  3. SQL計画ベースラインの管理

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

この章では、DBMS_SPMパッケージを使用したSQL計画管理の概要およびタスクについて説明します。

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

関連項目:

29.1 SQL計画ベースラインの管理について

このトピックでは、SQL計画管理の使用可能なインタフェースおよび基本タスクについて説明します。

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

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

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

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

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

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

「SQL計画ベースライン」ページにアクセスするには:

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

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

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

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

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

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

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

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

    図29-1の説明が続きます
    「図29-1 「SQL計画ベースライン」サブページ」の説明

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

関連項目:

29.1.1.2 DBMS_SPMパッケージ

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

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

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

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

CONFIGURE

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

CREATE_STGTAB_BASELINE

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

DROP_SQL_PLAN_BASELINE

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

LOAD_PLANS_FROM_CURSOR_CACHE

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

LOAD_PLANS_FROM_SQLSET

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

LOAD_PLANS_FROM_AWR

このファンクションは、AWRから計画をSQL計画ベースラインにロードします。

PACK_STGTAB_BASELINE

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

UNPACK_STGTAB_BASELINE

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

また、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEを使用して、SQLハンドルによって識別されるSQL文の1つ以上の実行計画を表示できます。

関連項目:

29.1.2 SQL計画管理の基本タスク

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

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

29.2 SQL計画管理の構成

SQL計画ベースラインの取得および使用と、SPM展開アドバイザのタスクを構成できます。

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

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

初期化パラメータOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESおよびOPTIMIZER_USE_SQL_PLAN_BASELINESを使用して、SQL計画管理を制御します。

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

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false

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

    OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=trueの場合、DBMS_SPM.CONFIGUREプロシージャを使用して、計画を取得できる文を決定するフィルタを構成できます。デフォルトでは、フィルタが構成されません。これは、すべての繰返し可能な文で計画を取得できることを意味します。

  • OPTIMIZER_USE_SQL_PLAN_BASELINES=true

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

ノート:

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

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

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

関連項目:

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

計画履歴に存在しない適格なSQL文に初期SQL計画ベースラインを自動的に作成するには、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをtrueに設定する手順のみが必要です。

デフォルトでは、データベースは、すべての繰返し可能なSQL文を取得の対象とみなします。ただし、次の例外があります。

  • AS SELECT句が指定されていない場合のCREATE TABLE

  • DROP TABLE

  • INSERT INTO ... VALUES

注意:

デフォルトでは、自動ベースライン取得が有効になっていると、データベースはすべての再帰的SQLおよび監視SQLを含む、すべての適格な繰返し性のある文に対してSQL計画ベースラインを作成します。したがって、自動取得により、非常に多くの計画ベースラインが作成される可能性があります。計画ベースラインの対象である文を制限するには、DBMS_SPM.CONFIGUREプロシージャを使用してフィルタを構成します。

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESパラメータは、前に作成されたSQL計画ベースラインへの新しく検出された計画の自動的な追加を制御しません。

SQL計画管理に対して初期計画の自動取得を有効にするには:

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

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

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

    SHOW PARAMETER SQL_PLAN

    次のサンプル出力は、初期計画の自動取得が無効化されていることを示しています。

    NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

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

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

    ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

関連項目:

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

29.2.1.2 計画の自動取得のフィルタの構成

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=trueの場合、DBMS_SPM.CONFIGUREプロシージャを使用して、繰返し可能な文の自動取得フィルタを作成できます。

自動フィルタにより、必要な文のみを取得して重要でない文を除外できます。この手法により、SYSAUX表領域の領域が削減されます。

次の表は、DBMS_SPM.CONFIGUREプロシージャの関連するパラメータを示しています。

表29-2 DBMS_SPM.CONFIGUREパラメータ

パラメータ 説明

parameter_name

自動取得のフィルタのタイプ。

可能な値は、AUTO_CAPTURE_SQL_TEXT、AUTO_CAPTURE_PARSING_SCHEMA_NAME、AUTO_CAPTURE_MODULEおよびAUTO_CAPTURE_ACTIONです。

parameter_value

自動取得フィルタの検索基準。

parameter_nameがAUTO_CAPTURE_SQL_TEXTに設定されている場合、検索パターンはallow設定に依存します。

  • LIKE

    パラメータは、allow=>trueのときにこのパターンを使用します。

  • NOT LIKE

    パラメータは、allow=>falseのときにこのパターンを使用します。

その他、null以外のすべてのparameter_nameの値の場合、検索パターンはallowの設定に依存します。

  • =

    パラメータは、allow=>trueのときにこのパターンを使用します。

  • <>

    パラメータは、allow=>falseのときにこのパターンを使用します。

null値は、parameter_nameのフィルタ全体を削除します。

allow 一致するSQL文および計画を含めるか(true)除外するか(false)を示します。nullの場合、プロシージャは指定されたパラメータを無視します。

異なるタイプの複数のパラメータを構成できます。また、データベースが組み合せる別々の文の同じパラメータに複数の値を指定できます。設定は付加型で、あるパラメータ設定が前の設定をオーバーライドしません。たとえば、次のフィルタは解析スキーマSYSまたはSYSTEMのSQLを取得します。 

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYS',true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYSTEM',true);

ただし、同じプロシージャの同じパラメータに複数の値を構成することはできません。たとえば、AUTO_CAPTURE_SQL_TEXTに複数のSQLテキスト文字列を指定できません。

DBA_SQL_MANAGEMENT_CONFIGビューは、現在のパラメータ値を示します。

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

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータはtrueに設定されます。

  • ベースラインの対象となるshスキーマで解析された文のみを含めます。

  • テキストTEST_ONLYを含む文を除外します。

shスキーマで解析された文以外のすべての文をフィルタ処理で除外するには:

  1. 適切な権限でSQL*Plusをデータベースに接続します。

  2. スキーマおよびSQLテキストを解析する既存のフィルタを削除するには、次のPL/SQLプログラムを実行します。

    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME',null,true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT',null,true);

  3. 自動取得を考慮して、shスキーマで解析された文のみを含めます。 

    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','sh',true);

  4. 自動取得を考慮して、テキストTEST_ONLYを含む文を除外します。 

    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%TEST_ONLY%',false);

  5. オプションで、フィルタを確認するには、DBA_SQL_MANAGEMENT_CONFIGを問い合せます。

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

    COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32

SELECT PARAMETER_NAME, PARAMETER_VALUE 
FROM   DBA_SQL_MANAGEMENT_CONFIG 
WHERE  PARAMETER_NAME LIKE '%AUTO%';

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH)
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT            (sql_text NOT LIKE %TEST_ONLY%)

関連項目:

29.2.1.3 すべての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リファレンス』を参照

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

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

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

29.2.2.1 SPM展開アドバイザのタスクについて

デフォルトでは、SYS_AUTO_SPM_EVOLVE_TASKはスケジュールされているメンテナンス・ウィンドウで毎日実行されます。

SPM展開アドバイザのタスクでは、次の操作を実行します。

  1. 承認されていない計画を特定します

  2. すべての承認されていない計画をランク付けします

  3. メンテナンス・ウィンドウ中にできるだけ多数の計画をテスト実行します

  4. 最もコストの低い計画を選択し、承認されていない各計画と比較します

  5. コストベースのアルゴリズムを使用して、既存の承認済の計画よりもパフォーマンスが十分に優れた承認されていない計画を自動的に受け入れます

ノート:

SPM展開アドバイザのタスクは、単一の文に対して1つ以上の計画を受け入れることができます。

SPM展開アドバイザは、オプティマイザが以前のハード解析中にSMBに追加した承認されていない計画を特定します。場合によっては、より最適な計画が他の場所に存在する可能性があります。DBMS_SPM.SET_EVOLVE_TASK_PARAMETERプロシージャを使用することで、自動タスクを構成して、SMB計画履歴にまだ存在しない計画の共有SQL領域、AWRリポジトリまたはSQLチューニング・セットを検索できます。アドバイザが代替の場所の対象となる計画を検索する場合、他の承認されていない計画とともにそれらが含まれます。デフォルトでは、自動タスクは代替の場所を検索しません。

関連項目:

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

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

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

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

SPM展開アドバイザの自動タスクを無効化するには:

  1. 適切な権限でデータベースにログインします。

  2. ALTERNATE_PLAN_BASELINEパラメータをnullに設定します。 

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

  3. ALTERNATE_PLAN_SOURCEパラメータを空の文字列に設定します。 

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

関連項目:

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

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

DBMS_SPM.SET_EVOLVE_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することにより、計画の自動展開を構成します。

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

次の表に、プロシージャ・パラメータの一部を示します。

表29-3 DBMS_SPM.SET_EVOLVE_TASK_PARAMETERパラメータ

パラメータ 説明

alternate_plan_source

追加の計画を検索するソース(CURSOR_CACHE、AUTOMATIC_WORKLOAD_REPOSITORYまたはSQL_TUNING_SETS)を決定します。プラス記号(+)を使用して複数の値を結合します。デフォルトはCURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORYです。

alternate_plan_baseline

ロードする代替計画を決定します。デフォルトのEXISTINGは、既存のベースラインを使用して文の計画をロードします。NEWは、ベースラインを使用しない文の計画をロードし、新しいベースラインを作成します。プラス記号(+)を使用して複数の値を結合します。

alternate_plan_limit

ロードする計画の合計最大数を指定します(SQL文ごとではありません)。デフォルトは10です。

accept_plans

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

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

  • データベースにより自動的に計画を受け入れます。

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

  • 展開タスクで共有SQL領域およびAWRリポジトリの最大500個の計画までを検索します

自動展開タスクのパラメータを設定するには:

  1. SYSとしてSQL*Plusをデータベースに接続します。

  2. SYS_AUTO_SPM_EVOLVE_TASKの現在のパラメータ設定を問い合せます。

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

    COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a42
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 LIKE '%ALT%') OR
           (PARAMETER_NAME = 'TIME_LIMIT') ) );

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

    PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT      0
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true
TIME_LIMIT                3600

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

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

    たとえば、次のPL/SQLブロックでは、SYS_AUTO_SPM_EVOLVE_TASKタスクを構成して計画を自動的に受け入れ、共有SQL領域およびAWRリポジトリの最大500個の計画を検索し、20分後にタイムアウトします。 

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

  4. オプションで、SYS_AUTO_SPM_EVOLVE_TASKの現在のパラメータ設定を問い合せて、変更を確認します。

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

    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 LIKE '%ALT%') OR
           (PARAMETER_NAME = 'TIME_LIMIT') ) );

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

    PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT      500
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true
TIME_LIMIT                1200

関連項目:

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

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

このファンクションは、計画履歴に保存された計画の情報を使用して計画を表示します。次の表は、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEの関連パラメータを示しています。

表29-4 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEパラメータ

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

sql_handle

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

plan_name

文に対する計画の名前。

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

SQL計画ベースラインに計画を表示するには:

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

    たとえば、SQL IDが31d96zzzpcys9のSELECT文に対する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であり、自動的に取得されたことを示しています。

関連項目:

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

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

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

29.4.1 SQL計画ベースラインのロードについて

DBMS_SPMパッケージにより、複数のソースから計画をロードできます。

このタスクの目的は、次のソースから計画をロードすることです。

  • AWR

    自動ワークロード・リポジトリ(AWR)のスナップショットから計画をロードします。スナップショット範囲の最初および最後を指定する必要があります。オプションで、フィルタを適用して指定された基準を満たす計画のみをロードできます。デフォルトでは、オプティマイザはデータベースが次にSQL文を実行する際にロードされた計画を使用します。

  • 共有SQL領域

    文に対する計画を、SGAの共有プール内にある共有SQL領域から直接ロードします。モジュール名、スキーマまたはSQL IDにフィルタを適用することにより、取得するSQL文またはSQL文のセットを特定します。オプティマイザはデータベースが次にSQL文を実行する際に計画を使用します。

    共有SQL領域からの計画の直接ロードは、アプリケーションSQLがヒントを使用して手動でチューニングされている場合に有用です。ヒントを含むようにSQLを変更できない可能性が高いため、SQL計画ベースラインに挿入することにより、アプリケーションSQLで最適な計画が使用されるようにすることができます。

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

    SQLワークロードに対する計画を取得してSTSに保存してから、計画をSQL計画ベースラインにロードします。オプティマイザはデータベースが次にSQL文を実行する際に計画を使用します。STSからの実行計画の一括ロードは、データベース・アップグレード後に計画のパフォーマンスの低下を防ぐ効果的な方法です。

  • ステージング表

    DBMS_SPMパッケージを使用して、ベースラインをステージング表にコピーするためのステージング表DBMS_SPM.PACK_STGTAB_BASELINEを定義し、Oracle Data Pumpを使用して、表を別のデータベースに転送します。宛先データベースで、DBMS_SPM.UNPACK_STGTAB_BASELINEを使用してステージング表から計画をアンパックし、ベースラインをSMBに挿入します。

    ユースケースとして、新しいアプリケーション・モジュールからの新しいSQL文のデータベースへの導入があげられます。ベンダーは、新しいSQLに対するSQL計画ベースラインとともにアプリケーション・ソフトウェアを出荷できます。このようにして、標準のテスト構成で最適なパフォーマンスを発揮することがわかっている計画を新しいSQLで使用するようにすることができます。または、社内でアプリケーションを開発またはテストした場合に、テスト・データベースから適切な計画をエクスポートし、本番データベースにインポートできます。

  • ストアド・アウトライン

    ストアド・アウトラインをSQL計画ベースラインに移行する。移行した後、ストアド・アウトラインを使用した場合と同じ計画の安定性を維持し、同時にSQL計画管理で提供される計画の展開などの拡張機能を使用できます。次を参照してください。

関連項目:

29.4.2 AWRからの計画のロード

このトピックでは、PL/SQLを使用してAWRから計画をロードする方法について説明します。

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

表29-5 LOAD_PLANS_FROM_AWRパラメータ

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

begin_snap

範囲の最初のスナップショットの数。必須です。

end_snap

範囲の最後のスナップショットの数。必須です。

basic_filter

条件を満たす計画のみをロード対象として選択するためにAWRに適用されるフィルタ。デフォルトのnullは、AWRのすべての計画が選択されていることを意味します。このフィルタは、DBA_HIST_SQLTEXT.SQL_TEXT列に対して指定可能なWHERE句条件の形式にすることができます。例は、basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%'''です。

fixed

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

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

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

  • 次の問合せの計画をSMBにロードします。

    SELECT /*LOAD_AWR*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

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

  • ユーザーshは、DBA_HIST_SNAPSHOTおよびDBA_SQL_PLAN_BASELINESを問い合せる権限、DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOTを実行する権限およびDBMS_SPM.LOAD_PLANS_FROM_AWRを実行する権限を持ちます。

共有SQL領域から計画をロードするには:

  1. 適切な権限を使用してデータベースにログインし、最新の3つのAWRスナップショットを問い合せます。

    たとえば、次のようにDBA_HIST_SNAPSHOTを問い合せます。 

    SELECT *
FROM   (SELECT SNAP_ID, SNAP_LEVEL, 
               TO_CHAR(BEGIN_INTERVAL_TIME, 'DD/MM/YY HH24:MI:SS') BEGIN
        FROM   DBA_HIST_SNAPSHOT
        ORDER BY SNAP_ID DESC)
WHERE   ROWNUM <= 3;

   SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
       212	    1 10/12/15 06:00:02
       211	    1 10/12/15 05:00:11
       210	    1 10/12/15 04:00:59

  2. SQL文を識別するためにLOAD_AWRタグを使用して、sh.salesを問い合せます。

    たとえば、次の問合せを使用します。

    SELECT /*LOAD_AWR*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

  3. 新しいAWRスナップショットを取得します。

    たとえば、次のプログラムを使用します。

    EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

  4. 最新の3つのAWRスナップショットを問い合せて、新しいスナップショットが取得されたことを確認します。

    たとえば、次のようにDBA_HIST_SNAPSHOTを問い合せます。 

    SELECT *
FROM   (SELECT SNAP_ID, SNAP_LEVEL, 
               TO_CHAR(BEGIN_INTERVAL_TIME, 'DD/MM/YY HH24:MI:SS') BEGIN
        FROM   DBA_HIST_SNAPSHOT
        ORDER BY SNAP_ID DESC)
WHERE   ROWNUM <= 3;

   SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
       213	    1 10/12/15 06:24:53
       212	    1 10/12/15 06:00:02
       211	    1 10/12/15 05:00:11

  5. AWRから最新の2つのスナップショットの計画をロードします。

    たとえば、SQL*PlusのLOAD_PLANS_FROM_AWRファンクションを実行して、スナップショット212から213に計画をロードします。 

    VARIABLE v_plan_cnt NUMBER
EXEC :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 212, end_snap => 213);

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

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

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

    COL SQL_HANDLE FORMAT a20
COL SQL_TEXT FORMAT a20
COL PLAN_NAME FORMAT a30
COL ORIGIN FORMAT a20

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
       ORIGIN, ENABLED, ACCEPTED
FROM   DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%LOAD_AWR%';

SQL_HANDLE           SQL_TEXT          PLAN_NAME                  ORIGIN       ENA ACC
-------------------- ----------------- -------------------------- -----------  --- ---
SQL_495d29c5f4612cda SELECT /*LOAD_AWR SQL_PLAN_4kr99sru62b6u54bc MANUAL-LOAD- YES YES
                     */ * FROM         8843                       FROM-AWR 
                     sh.sales WHERE  
                     quantity_sold 
                     > 40
                     ORDER BY prod_id

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

関連項目:

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

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

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

表29-6 LOAD_PLANS_FROM_CURSOR_CACHEパラメータ

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

sql_id

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

fixed

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

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

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

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

    SELECT /*LOAD_CC*/ *
FROM   sh.sales
WHERE  quantity_sold > 40
ORDER BY prod_id;

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

共有SQL領域から計画をロードするには:

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

    たとえば、V$SQLでsh.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 v_plan_cnt NUMBER
BEGIN
  :v_plan_cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( 
    sql_id => '27m0sdw9snw59');
END;

    前述の例では、変数v_plan_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_gdkvz MANUAL-LOAD-FROM-CC YES YES
                      *                    fhrgkda71694fc
                      FROM sh.sales        6b
                      WHERE quantity_sold
                      > 40                           
                      ORDER BY prod_id

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

関連項目:

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

SQLチューニング・セット(STS)は、1つ以上のSQL文、実行統計および実行コンテキストを含むデータベース・オブジェクトです。

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

表29-7 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計画ベースライン」サブページ(図29-1を参照)に移動し、「ロード」をクリックして、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 v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
           sqlset_name  => 'SPM_STS', -
           basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' );

    basic_filterパラメータは、関心のある問合せに対する計画のみをロードするWHERE句を指定します。変数v_plan_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-FROM-STS YES YES
                      /*LOAD_STS*/*   v1bu98ky1694fc6b
                      FROM sh.sales
                      WHERE 
                      quantity_sold 
                      > 40 ORDER BY                    
                      prod_id

    出力は、計画が承認済であること、つまり計画ベースラインに含まれることを示しています。また、ロード元はMANUAL-LOAD-FROM-STSであり、これは計画は自動的に取得されたのではなく、SQLチューニング・セットから手動でロードされたことを意味します。

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

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

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

関連項目:

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

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

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

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

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

図29-2の説明が続きます
「図29-2 ステージング表からの計画のロード」の説明

DBMS_SPMパッケージのPACK_STGTAB_BASELINEファンクションを使用して計画をエクスポートし、UNPACK_STGTAB_BASELINEを使用してインポートします。次の表では、ファンクション・パラメータの一部を説明します。

表29-8 PACK_STGTAB_BASELINEおよびUNPACK_STGTAB_BASELINEパラメータ

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

table_name

インポートまたはエクスポートする表を指定します。

origin

SQL計画ベースラインの元。これらのプロシージャは、origin引数としてDBA_SQL_PLAN_BASELINES.ORIGINのすべての可能な値を受け入れます。たとえば、originパラメータは、MANUAL-LOAD-FROM-STS、MANUAL-LOAD-FROM-AWRおよびMANUAL-LOAD-FROM-CCを許可します。

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

  • ソース・データベース内に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
  v_plan_cnt NUMBER;
BEGIN
  v_plan_cnt := 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
  v_plan_cnt NUMBER;
BEGIN
  v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE (
    table_name => 'stage1'
,   fixed      => 'yes'
);
END;
/

関連項目:

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

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

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

関連項目:

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

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

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

表29-9 計画の展開タスクを管理するための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

このファンクションは展開タスク・パラメータの値を更新します。

SPM展開アドバイザを構成して自動的に実行することをお薦めします。SQL計画ベースラインを手動で展開することもできます。次の図は、SQL計画管理タスクを管理する基本ワークフローを示しています。

図29-3 SQL計画ベースラインの展開

図29-3の説明が続きます
「図29-3 SQL計画ベースラインの展開」の説明

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

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

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

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

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

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

関連項目:

29.5.2 展開タスクの管理

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

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

表29-10 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計画ベースライン」サブページで、計画を選択してから「展開」をクリックします。

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

  • 自動展開タスクを有効にしていません。

  • 次の問合せに対する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_0gwbcfvzskcu2 AUTO-CAPTURE YES YES NO
                     up_by */ prod_na 42949306
                     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_0gwbcfvzskcu2 AUTO-CAPTURE YES NO
                     y */ prod_name, sum( 0135fd6c
                     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_0gwbcfvzskcu2 AUTO-CAPTURE YES YES
                     y */ prod_name, sum( 42949306
                     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       |                  |267K |10987K|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|  9334| 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ファンクションを実行します。 

    BEGIN 
  :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( 
    task_name=>:tk_name, execution_name=>:exe_name );
END;

  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_0gwbcfvzskcu2 AUTO-CAPTURE YES YES
                     y */ prod_name, sum( 0135fd6c
                     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_0gwbcfvzskcu2 AUTO-CAPTURE YES YES
                     y */ prod_name, sum( 42949306
                     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;

関連項目:

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

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

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

表29-11 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_bdc6jswfd303v AUTO-CAPTURE YES YES
                     _sql */ count(*)     2f1e9c20
                     from 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パッケージおよびタイプ・リファレンス』を参照してください。

29.7 SQL管理ベースの管理

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

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

29.7.1 SMBの管理について

DBMS_SPM.CONFIGUREプロシージャを使用して、SMBおよびSQL計画ベースラインのメンテナンスの構成オプションを設定します。

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

表29-12 DBA_SQL_MANAGEMENT_CONFIG.PARAMETER_NAMEのパラメータ

パラメータ 説明

SPACE_BUDGET_PERCENT

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

PLAN_RETENTION_WEEKS

消去するまで未使用の計画を保持する週の数。デフォルトは53です。
AUTO_CAPTURE_PARSING_SCHEMA_NAME スキーマ名の解析フィルタの内部表現である形式(% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...)のリスト。解析スキーマ・フィルタが存在しない場合、外部連結の一方の側が存在しません。
AUTO_CAPTURE_MODULE モジュール・フィルタの内部表現である形式(% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...)のリスト。モジュール・フィルタが存在しない場合、外部連結の一方の側が存在しません。
AUTO_CAPTURE_ACTION アクション・フィルタの内部表現である形式(% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...)のリスト。アクション・フィルタが存在しない場合、外部連結の一方の側が存在しません。
AUTO_CAPTURE_SQL_TEXT SQLテキスト・フィルタの内部表現である形式(% LIKE a OR % LIKE b ...) AND (% NOT LIKE c AND % NOT LIKE d ...)のリスト。SQLテキスト・フィルタが存在しない場合、外部連結の一方の側が存在しません。

関連項目:

29.7.2 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

関連項目:

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

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

週次にスケジュールされた消去タスクにより、SQL計画の管理で使用されるディスク領域が管理されます。

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

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

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

関連項目:

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