29 SQL計画ベースラインの管理
この章では、DBMS_SPM
パッケージを使用したSQL計画管理の概要およびタスクについて説明します。
関連項目:
-
DBMS_SPM
についてさらに学習するには、『Oracle Database PL/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計画ベースライン」ページにアクセスするには:
-
適切な資格証明を使用してCloud Controlにログインします。
-
「ターゲット」メニューの下で、「データベース」を選択します。
-
データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。
-
データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。
-
「パフォーマンス」メニューから、「SQL」、「SQL計画管理」の順に選択します。
「SQL計画管理」ページが表示されます。
-
「ファイル」をクリックして、図29-1に示す「SQL計画ベースライン」ページを表示します。
ほとんどのSQL計画管理タスクは、このページ、またはこのページからアクセスするページで実行できます。
関連項目:
-
「SQL計画ベースライン」サブページのオプションについて学習するには、Cloud Control状況依存オンライン・ヘルプを参照してください。
29.1.1.2 DBMS_SPMパッケージ
コマンドラインでは、DBMS_SPM
およびDBMS_XPLAN
PL/SQLパッケージを使用して、ほとんどのSQL計画管理タスクを実行します。
次の表に、SQL計画ベースラインを作成、削除およびロードするための主なDBMS_SPM
プロシージャとファンクションを示します。
表29-1 DBMS_SPMプロシージャとファンクション
プロシージャまたはファンクション | 説明 |
---|---|
|
このプロシージャは、名前/値の形式でSMBに対する構成オプションを変更します。 |
|
このプロシージャは、SQL計画ベースラインを1つのデータベースから別のデータベースへと移植することを可能にするステージング表を作成します。 |
|
このファンクションは、計画ベースライン内の一部またはすべての計画を削除します。 |
|
このファンクションは、共有SQL領域(カーソル・キャッシュとも呼ばれます)内の計画をSQL計画ベースラインにロードします。 |
|
このファンクションは、STSに保存されている計画をSQL計画ベースラインにロードします。 |
|
このファンクションは、AWRから計画をSQL計画ベースラインにロードします。 |
|
このファンクションは、SQL計画ベースラインをパックします。つまり、SMBからステージング表にコピーします。 |
|
このファンクションは、SQL計画ベースラインをアンパックします。つまり、SQL計画ベースラインをステージング表からSMBにコピーします。 |
また、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
を使用して、SQLハンドルによって識別されるSQL文の1つ以上の実行計画を表示できます。
関連項目:
-
SQL計画の展開に関連するファンクションの詳細は、「DBMS_SPM展開ファンクションについて」を参照してください。
-
DBMS_SPM
およびDBMS_XPLAN
パッケージについて学習するには、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照してください
29.1.2 SQL計画管理の基本タスク
このトピックでは、パフォーマンスの低下を防ぎ、オプティマイザによる新しい実行計画の検討を可能にするためにSQL計画管理を使用する際の基本的なタスクについて説明します。
タスクは次のとおりです。
-
データベースがSQL計画ベースラインを取得して使用するかどうか、また新しい計画を展開するかどうかを制御する初期化パラメータを設定する。
「SQL計画管理の構成」を参照してください。
-
SQL計画ベースラインに計画を表示する。
「SQL計画ベースラインでの計画の表示」を参照してください。
-
計画をSQL計画ベースラインに手動でロードする。
AWR、SQLチューニング・セット、共有SQL領域、ステージング表またはストアド・アウトラインから計画をロードします。
「SQL計画ベースラインのロード」を参照してください。
-
計画をSQL計画ベースラインに手動で展開する。
PL/SQLを使用して、指定した計画のパフォーマンスを検証し、計画ベースラインに追加します。
「SQL計画ベースラインの手動での展開」を参照してください。
-
SQL計画ベースライン内の一部またはすべての計画を削除する。
「SQL計画ベースラインの削除」を参照してください。
-
SMBを管理する。
ディスク領域制限および計画保存ポリシーの期間を変更します。
「SQL管理ベースの管理」を参照してください。
-
ストアド・アウトラインを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計画管理に対して初期計画の自動取得を有効にするには:
-
SQL*Plusで、必要な権限でデータベースにログインします。
-
SQL計画管理の現在の設定を表示します。
たとえば、管理者権限でSQL*Plusをデータベースに接続し、次のコマンドを実行します(出力例も示します)。
SHOW PARAMETER SQL_PLAN
次のサンプル出力は、初期計画の自動取得が無効化されていることを示しています。
NAME TYPE VALUE ------------------------------------ ----------- ----- optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
パラメータが希望どおりに設定されていれば、残りのステップはスキップしてください。
-
繰返し可能な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パラメータ
パラメータ | 説明 |
---|---|
|
自動取得のフィルタのタイプ。 可能な値は、 |
|
自動取得フィルタの検索基準。
その他、null以外のすべての
null値は、 |
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スキーマで解析された文以外のすべての文をフィルタ処理で除外するには:
-
適切な権限でSQL*Plusをデータベースに接続します。
-
スキーマおよびSQLテキストを解析する既存のフィルタを削除するには、次のPL/SQLプログラムを実行します。
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME',null,true); EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT',null,true);
-
自動取得を考慮して、
sh
スキーマで解析された文のみを含めます。EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','sh',true);
-
自動取得を考慮して、テキスト
TEST_ONLY
を含む文を除外します。EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%TEST_ONLY%',false);
-
オプションで、フィルタを確認するには、
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%)
関連項目:
-
DBMS_SPM.CONFIGURE
プロシージャについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
DBA_SQL_MANAGEMENT_CONFIG
ビューについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
29.2.1.3 すべてのSQL計画ベースラインの無効化
OPTIMIZER_USE_SQL_PLAN_BASELINES
初期化パラメータをfalse
に設定すると、データベースはデータベース内のいずれの計画ベースラインも使用しません。
通常は、1つまたは2つの計画ベースラインを無効化しますが、すべては無効化しません。可能なユースケースとしては、SQL計画管理の利点のテストがあげられます。
データベース内のすべてのSQL計画ベースラインを無効化するには:
-
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
パラメータが希望どおりに設定されていれば、残りのステップはスキップしてください。
-
すべての既存の計画ベースラインを無視するには、次の文を入力します。
SQL> ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false
関連項目:
SQL計画ベースライン初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照
29.2.2 SPM展開アドバイザのタスクの管理
SPM展開アドバイザは、SQL計画ベースラインに最近追加された計画を展開するSQLアドバイザです。アドバイザを使用することにより手動で実行する必要がなくなるため、計画の展開が容易になります。
29.2.2.1 SQL計画の自動管理
SQL計画ベースラインは、最適ではない計画によるパフォーマンスの低下を防止します。
高頻度SPM展開アドバイザ・タスクを構成すれば、実行計画の変更を識別し、新しい計画と自動SQLチューニング・セットで以前に取得した計画のパフォーマンスを比較できます。
SQL計画ベースラインがSQL文にない場合は、高頻度SPM展開アドバイザ・タスクによって、このようなパフォーマンス低下が自動的に解決されることがあります。このアドバイザは、使用可能なすべての計画を比較して、パフォーマンスが最適になる計画をベースラインとして選択します。
SPM構成パラメータAUTO_SPM_EVOLVE_TASK
で自動SQL計画管理を管理します。ON
を設定すると、このタスクがバックグラウンドで有効になります。OFF
に設定することもできます。
次の図は、高頻度SPM展開アドバイザ・タスクのワークフローを示しています:
高頻度SPM展開アドバイザ・タスク実行するたびに、次のタスクが実行されます:
-
リソース集中型のSQL文についてAWRおよびASTSを検索します。
-
自動SQLチューニング・セットで代替計画を探します。
-
複数の計画が識別されると、それらがSQL計画履歴に追加されます。
-
代替計画がテストで実行され、パフォーマンスが測定されます。
データベース・テストによって文が実行され、パフォーマンス統計が記録されます。
-
代替計画が現在の計画よりも優れているかどうかに応じて、次のいずれかの処理を実行します。
-
パフォーマンスが高いと、高頻度SPM展開アドバイザがその計画を承認します。代替計画は、この時点でベースラインにあります。
-
パフォーマンスが低くなる場合、その計画はベースラインではなく文履歴に残ります。
-
関連項目:
- 自動SQLチューニング・セットは、SQL実行計画およびデータベースに表示されるSQL文のパフォーマンス・メトリックに関して、システムで保守されるレコードです。高頻度SPM展開アドバイザ・タスクでは、新しい計画と自動SQLチューニング・セットに以前に取得された計画のパフォーマンスが比較されます。
-
各種エディションおよびサービスでサポートされる機能の詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアル』を参照
-
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展開アドバイザの自動タスクを無効化するには:
-
適切な権限でデータベースにログインします。
-
ALTERNATE_PLAN_BASELINE
パラメータをnullに設定します。BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_BASELINE', value => ''); END; /
-
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
パッケージを使用して、計画の自動展開を構成します。
SPM展開アドバイザの自動タスクの概要
SET_EVOLVE_TASK_PARAMETER
プロシージャを使用して、自動タスクのパラメータを指定します。次の表では、一部のプロシージャ・パラメータを説明します。
表29-3 DBMS_SPM.SET_EVOLVE_TASK_PARAMETERパラメータ
パラメータ | 説明 | デフォルト |
---|---|---|
|
追加の計画を検索するソースを決定します。
複数の値をプラス記号( |
デフォルトは、SPM展開アドバイザのタスクが自動か手動かによって決まります。
|
|
ロードする代替計画を決定します。
|
|
|
ロードする計画の合計最大数を指定します(SQL文ごとの制限ではありません)。 |
デフォルトは、SPM展開アドバイザのタスクが自動か手動かによって決まります。
|
|
推奨された計画を自動的に受け入れるかどうかを指定します。
|
|
|
グローバル時間制限(秒)。これは、タスクに許可される合計時間です。 |
デフォルトは、SPM展開アドバイザのタスクが自動か手動かによって決まります。
|
前提条件
この項のチュートリアルでは、次のことが前提となっています。
-
SYS
としてデータベースにログインできます。SYS_AUTO_SPM_EVOLVE_TASK
タスクはSYS
が所有しているため、SYS
のみがタスク・パラメータを設定できます。 -
データベースにより自動的に計画を受け入れます。
-
実行ごとに1200秒経過したら、タスクをタイムアウトする。
-
展開タスクで共有SQL領域およびAWRリポジトリの最大500個の計画までを検索します
自動展開タスクのパラメータを設定するには:
-
SQL*Plusを起動して、データベースに
SYS
としてログインします。 -
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
-
次の形式の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; /
-
オプションで、
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
関連項目:
-
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER
の詳細な参照情報については、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
DBA_ADVISOR_PARAMETERS
ビューについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
29.2.2.4 SPM展開アドバイザの高頻度自動タスクの構成
計画の自動展開を頻繁に実行するように構成できます。
29.2.2.4.1 SPM展開アドバイザの高頻度自動タスクについて
SPM展開アドバイザの高頻度タスクは、標準のSPM展開アドバイザの自動タスクを補完します。
デフォルトでは、SYS_AUTO_SPM_EVOLVE_TASK
はスケジュールされているAutoTaskメンテナンス・ウィンドウで毎日実行されます。連続する2つのタスク実行間でデータが頻繁に変更される場合、オプティマイザは、最適ではない計画を選択することがあります。たとえば、製品リストの価格がSYS_AUTO_SPM_EVOLVE_TASK
の実行より頻繁に変更される場合、範囲外の問合せが発生する可能性があり、最適ではない計画が生成されることがあります。
SPM展開アドバイザの高頻度自動タスクを有効にすると、SYS_AUTO_SPM_EVOLVE_TASK
の実行頻度が高くなり、実行ごとに同じ操作が実行されます。高頻度タスクは1時間ごとに実行され、30分以内で実行されます。これらの設定は構成可能ではありません。頻繁に実行されるということは、オプティマイザがよりよい実行計画を見つけて展開する機会が増えることを意味します。
SPM展開アドバイザの標準自動タスクと高頻度タスクの両方の名前は、同じSYS_AUTO_SPM_EVOLVE_TASK
です。DBA_ADVISOR_EXECUTIONS
では、2つのタスクは実行名によって識別されます。標準タスク実行の名前がEXEC_number
という形式であるのに対し、高頻度実行の名前はSYS_SPM_timestamp
という形式です。
DBMS_SPM.CONFIGURE
は高頻度タスクを有効にしますが、SPM展開アドバイザには依存しません。標準タスクと高頻度タスクは独立しており、2つの異なるフレームワークを通してスケジュールされます。
関連項目:
-
各種エディションおよびサービスでサポートされる機能の詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアル』を参照
-
DBMS_SPM.CONFIGURE
プロシージャについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
29.2.2.4.2 SPM展開アドバイザの高頻度自動タスクの有効化: チュートリアル
SPM展開アドバイザの高頻度自動タスクを有効化および無効化するには、DBMS_SPM.CONFIGURE
プロシージャを使用します。
auto_spm_evolve_task
は次のいずれかの値に設定できます。
-
ON
— SPM展開アドバイザの高頻度タスクを有効にします。 -
OFF
— SPM展開アドバイザの高頻度タスクを無効にします。これはデフォルトです。 -
AUTO
— SPM展開アドバイザの高頻度タスクを実行するタイミングをデータベースで決定できます。このリリースでは、AUTO
はOFF
と同じです。
タスクの間隔と実行時は固定されており、ユーザーによる調整はできないことに注意してください。
SPM展開アドバイザの高頻度タスクを有効にするには:
-
SYS
としてデータベースにログインします。 -
DBMS_SPM.CONFIGURE
の現在の設定を問い合せます(出力例も示します)。COL PARAMETER_NAME FORMAT a32 COL PARAMETER_VALUE FORMAT a32 SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME LIKE '%SPM%'; PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
-
タスクを有効にします。
次のPL/SQLコードを実行します。
EXEC DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');
-
タスクが有効であることを確認するには、
AUTO_SPM_EVOLVE_TASK
の現在の設定を問い合せます(出力例も示します)。COL PARAMETER_NAME FORMAT a32 COL PARAMETER_VALUE FORMAT a32 SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME = 'AUTO_SPM_EVOLVE_TASK'; PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_SPM_EVOLVE_TASK ON
-
オプションで、数時間待機してからタスク実行のステータスを問い合せます。
SET LINESIZE 150 COL TASK_NAME FORMAT a30 COL EXECUTION_NAME FORMAT a30 SELECT TASK_NAME, EXECUTION_NAME, STATUS FROM DBA_ADVISOR_EXECUTIONS WHERE TASK_NAME LIKE '%SPM%' AND (EXECUTION_NAME LIKE 'SYS_SPM%' OR EXECUTION_NAME LIKE 'EXEC_%') ORDER BY EXECUTION_END; TASK_NAME EXECUTION_NAME STATUS ------------------------------ ------------------------------ --------- SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/13:15:26 COMPLETED SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/14:16:04 COMPLETED SYS_AUTO_SPM_EVOLVE_TASK EXEC_6 COMPLETED SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/15:16:32 COMPLETED SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/16:17:00 COMPLETED ...
この出力では、
EXEC_6
がSPM展開アドバイザの標準自動タスクの実行名です。その他は高頻度タスクの実行です。
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ハンドル。 |
|
文に対する計画の名前。 |
この項では、コマンドラインからベースライン内の計画を表示する方法について説明します。Cloud Controlを使用する場合は、「SQL計画ベースライン」サブページ(図29-1を参照)から計画ベースラインを表示します。
SQL計画ベースラインに計画を表示するには:
-
SQL*Plusを適切な権限でデータベースに接続し、計画を表示する問合せのSQL IDを取得します。
たとえば、SQL IDが
31d96zzzpcys9
のSELECT
文に対するSQL計画ベースラインが存在するとします。 -
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
であり、自動的に取得されたことを示しています。
関連項目:
-
DISPLAY_SQL_PLAN_BASELINE
ファンクションで使用する追加のパラメータについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
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計画管理で提供される計画の展開などの拡張機能を使用できます。次を参照してください。
関連項目:
-
DBMS_SPM.PACK_STGTAB_BASELINE
ファンクションについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
29.4.2 AWRからの計画のロード
このトピックでは、PL/SQLを使用してAWRから計画をロードする方法について説明します。
DBMS_SPM
パッケージのLOAD_PLANS_FROM_AWR
ファンクションを使用して計画をロードします。次の表では、ファンクション・パラメータの一部を説明します。
表29-5 LOAD_PLANS_FROM_AWRパラメータ
ファンクション・パラメータ | 説明 |
---|---|
|
範囲の最初のスナップショットの数。必須です。 |
|
範囲の最後のスナップショットの数。必須です。 |
|
条件を満たす計画のみをロード対象として選択するためにAWRに適用されるフィルタ。デフォルトのnullは、AWRのすべての計画が選択されていることを意味します。このフィルタは、 |
|
デフォルトの |
この項では、コマンドラインを使用して計画をロードする方法について説明します。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領域から計画をロードするには:
-
適切な権限を使用してデータベースにログインし、最新の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
-
SQL文を識別するために
LOAD_AWR
タグを使用して、sh.sales
を問い合せます。たとえば、次の問合せを使用します。
SELECT /*LOAD_AWR*/ * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id;
-
新しいAWRスナップショットを取得します。
たとえば、次のプログラムを使用します。
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
-
最新の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
-
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
にはロードされた計画の数が含まれます。 -
データ・ディクショナリを問い合せて、
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から手動でロードされたことを意味します。
関連項目:
-
DBMS_SPM.LOAD_PLANS_FROM_AWR
ファンクションの使用方法を学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
DBA_SQL_PLAN_BASELINES
ビューについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
29.4.3 共有SQL領域からの計画のロード
このトピックでは、PL/SQLを使用してカーソル・キャッシュとも呼ばれる共有SQL領域から計画をロードする方法について説明します。
DBMS_SPM
パッケージのLOAD_PLANS_FROM_CURSOR_CACHE
ファンクションを使用して計画をロードします。次の表では、ファンクション・パラメータの一部を説明します。
表29-6 LOAD_PLANS_FROM_CURSOR_CACHEパラメータ
ファンクション・パラメータ | 説明 |
---|---|
|
SQL文の識別子。共有SQL領域内のSQL文を識別します。 |
|
デフォルトの |
この項では、コマンドラインを使用して計画をロードする方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図29-1を参照)に移動し、「ロード」をクリックして、共有SQL領域から計画ベースラインをロードします。
このチュートリアルでは、次のことが前提となっています。
-
次の問合せを実行済である。
SELECT /*LOAD_CC*/ * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id;
-
ロードした計画は固定にしない。
共有SQL領域から計画をロードするには:
-
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
であることを示しています。 -
指定した文に対する計画を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
にはロードされた計画の数が含まれます。 -
データ・ディクショナリを問い合せて、文に対するベースラインに計画がロードされたことを確認します。
次の文では、
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領域から手動でロードされたことを意味します。
関連項目:
-
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
ファンクションの使用方法を学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
DBA_SQL_PLAN_BASELINES
ビューについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
29.4.4 SQLチューニング・セットからの計画のロード
SQLチューニング・セット(STS)は、1つ以上のSQL文、実行統計および実行コンテキストを含むデータベース・オブジェクトです。このトピックでは、STSから計画をロードする方法について説明します。
計画は、DBMS_SPM.LOAD_PLANS_FROM_SQLSET
ファンクションまたはCloud Controlを使用してロードできます。次の表では、ファンクション・パラメータの一部を説明します。
表29-7 LOAD_PLANS_FROM_SQLSETパラメータ
ファンクション・パラメータ | 説明 |
---|---|
|
そこからSQL計画ベースラインに計画をロードするSTSの名前。 |
|
条件を満たす計画のみをロード対象として選択するためにSTSに適用されるフィルタ。このフィルタは、 |
|
デフォルトの |
この項では、コマンドラインから計画をロードする方法について説明します。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チューニング・セットに計画をロード済である。 -
操作が終わったら、同等の
DBMS_SQLSET.DROP_SQLSET
ではなく、DBMS_SQLTUNE.DROP_SQLSET
を使用してSTSを削除する必要があります。
SQLチューニング・セットから計画をロードするには:
-
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内にあることを示しています。 -
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からロードされた計画の数が含まれます。 -
データ・ディクショナリを問い合せて、文に対するベースラインに計画がロードされたことを確認します。
次の文では、
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チューニング・セットから手動でロードされたことを意味します。 -
オプションで、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パッケージおよびタイプ・リファレンス』を参照してください
29.4.5 ステージング表からの計画のロード
最適な計画をソース・データベースから別の宛先データベースに転送することが必要になる場合があります。
たとえば、テスト・データベース上で一連の計画を調査し、高いパフォーマンスを発揮することを確認したとします。その後、これらの計画を本番データベースにロードする場合が考えられます。
ステージング表とは、計画がアンパック中に表から消えないように、その表が存在する間、計画を保存する表です。ステージング表を作成するには、DBMS_SPM.CREATE_STGTAB_BASELINE
プロシージャを使用します。ステージング表のパック(行の挿入)およびアンパック(行の抽出)には、DBMS_SPM
パッケージのPACK_STGTAB_BASELINE
とUNPACK_STGTAB_BASELINE
ファンクションを使用します。Oracle Data Pump ImportとExportを使用すると、ステージング表を別のデータベースにコピーできます。
DBMS_SPM
パッケージのPACK_STGTAB_BASELINE
ファンクションを使用して計画をエクスポートし、UNPACK_STGTAB_BASELINE
を使用してインポートします。次の表では、ファンクション・パラメータの一部を説明します。
表29-8 PACK_STGTAB_BASELINEおよびUNPACK_STGTAB_BASELINEパラメータ
ファンクション・パラメータ | 説明 |
---|---|
|
インポートまたはエクスポートする表を指定します。 |
|
SQL計画ベースラインの元。これらのプロシージャは、 |
このチュートリアルでは、次のことが前提となっています。
-
ソース・データベース内に
stage1
という名前のステージング表を作成する。 -
ユーザー
spm
が所有するすべての計画をステージング表にロードする。 -
ステージング表を宛先データベースに転送する。
-
stage1
内の計画を固定計画としてロードする。
あるデータベースから別のデータベースへとSQL計画ベースラインのセットを転送するには:
-
SQL*Plusを適切な権限でソース・データベースに接続し、
CREATE_STGTAB_BASELINE
プロシージャを使用してステージング表を作成します。次の例では、
stage1
というステージング表が作成されます。BEGIN DBMS_SPM.CREATE_STGTAB_BASELINE ( table_name => 'stage1'); END; /
-
ソース・データベースで、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; /
-
Oracle Data Pump Exportを使用して、ステージング表
stage1
をダンプ・ファイルにエクスポートします。 -
ダンプ・ファイルを宛先データベースのホストに転送します。
-
宛先データベースで、Oracle Data Pump Importユーティリティを使用して、ステージング表
stage1
をダンプ・ファイルからインポートします。 -
宛先データベースで、SQL計画ベースラインをステージング表からSQL管理ベースにアンパックします。
次の例では、ステージング表
stage1
に格納された固定計画ベースラインがすべて解凍されます。DECLARE v_plan_cnt NUMBER; BEGIN v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE ( table_name => 'stage1' , fixed => 'yes' ); END; /
関連項目:
-
DBMS_SPM
パッケージの使用方法の詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
DBA_SQL_PLAN_BASELINES
ビューについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください -
データ・ポンプ・エクスポートおよびインポート・ユーティリティの使用方法の詳細は、『Oracle Databaseユーティリティ』を参照してください。
29.5 SQL計画ベースラインの手動での展開
PL/SQLまたはCloud Controlを使用して、承認されていない計画を手動で展開することにより、計画ベースライン内の現在のいずれの計画よりもパフォーマンスが優れているかどうかを判断することができます。
関連項目:
29.5.1 DBMS_SPM展開ファンクションについて
このトピックでは、計画の展開を管理するための最も関連性の高いDBMS_SPM
ファンクションについて説明しています。展開タスクは手動で実行するか、自動的に実行されるようにスケジュールします。
表29-9 計画の展開タスクを管理するためのDBMS_SPMファンクションとプロシージャ
プロシージャまたはファンクション | 説明 |
---|---|
|
このファンクションは、1つの計画をSQL計画ベースラインに展開するという1つの推奨事項を承認します。 |
|
このファンクションは、指定されたSQL文に対する1つ以上の計画の展開を準備するためのアドバイザ・タスクを作成します。入力パラメータは、SQLハンドル、計画名または計画名のリスト、時間制限、タスク名および説明になります。 |
|
このファンクションは展開タスクを実行します。入力パラメータは、タスク名、実行名および実行の説明になります。指定しない場合は、アドバイザによって名前が生成され、ファンクションによって戻されます。 |
|
このファンクションは展開タスクに対するすべての推奨事項を実装します。基本的にはこのファンクションは、推奨されるすべての計画に対して |
|
このファンクションは、展開タスクの結果を |
|
このファンクションは展開タスク・パラメータの値を更新します。 |
SPM展開アドバイザを構成して自動的に実行することをお薦めします。SQL計画ベースラインを手動で展開することもできます。次の図は、SQL計画管理タスクを管理する基本ワークフローを示しています。
通常、SQL計画の展開タスクを管理する順序は次のとおりです。
-
展開タスクを作成します。
-
オプションで、展開タスクのパラメータを設定します。
-
展開タスクを実行します。
-
タスク内の推奨事項を実装します。
-
タスクの結果をレポートします。
関連項目:
-
SET_EVOLVE_TASK_PARAMETER
について学習するには、「SPM展開アドバイザの自動タスクの構成」を参照 -
DBMS_SPM
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
29.5.2 展開タスクの管理
このトピックでは、タスクを作成して実行し、その後その推奨事項を実装する典型的なユースケースについて説明します。
次の表では、CREATE_EVOLVE_TASK
ファンクションのパラメータの一部を説明します。
表29-10 DBMS_SPM.CREATE_EVOLVE_TASKパラメータ
ファンクション・パラメータ | 説明 |
---|---|
|
文のSQLハンドル。デフォルトの |
|
計画の識別子。デフォルトの |
|
時間制限(分)。最初の承認されていない計画に対する時間制限は、入力値と等しくなります。2つ目の承認されていない計画に対する時間制限は、入力値から最初の計画の検証にかかった時間を引いた時間になります。以下同様です。デフォルトの |
|
展開タスクのユーザー指定名。 |
この項では、コマンドラインから計画ベースラインを展開する方法について説明します。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つ作成し、これらの索引を使用する計画のパフォーマンスが計画ベースライン内に現在含まれる計画よりも優れている場合は、その計画を展開する。
指定した計画を展開するには:
-
次の手順で初期設定を実行します。
-
SQL*Plusを管理者権限でデータベースに接続し、共有プールとバッファ・キャッシュをフラッシュすることによりチュートリアルの準備を行います。
ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE;
-
SQL計画ベースラインの自動取得を有効にします。
たとえば、次の文を入力します。
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
-
ユーザー
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
-
-
SQL計画管理により取得されるように、
SELECT
文を実行します。-
SELECT /* q1_group_by */
文を実行します(1回目)。データベースは繰返し可能な文に対する計画のみを取得するため、この文に対する計画ベースラインは空になります。
-
データ・ディクショナリを問い合せて、計画ベースライン内に計画がないことを確認します。
たとえば、次の問合せを実行します(出力例も示します)。
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計画管理は繰返し可能な文に対する計画のみを取得するため、この結果は予想どおりです。
-
SELECT /* q1_group_by */
文を実行します(2回目)。
-
-
データ・ディクショナリを問い合せて、文に対する計画ベースラインに計画がロードされたことを確認します。
次の文では、
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
であり、これは文は手動でロードされたのではなく、自動的に取得されたことを意味します。 -
文に対する計画を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
ノートは、オプティマイザが前のステップでリストされていた計画名で示されている計画を使用していることを示しています。
-
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);
-
SELECT /* q1_group_by */
文を再度実行します。自動取得が有効になっているため、この文に対する新しい計画が計画ベースラインに移入されます。
-
データ・ディクショナリを問い合せて、文に対する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計画ベースラインに含まれていないことを示しています。
-
文に対する計画を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でリストされていた計画名で示されている計画を使用していることを示しています。
-
管理者として接続し、承認されていない計画を持つすべての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
これでタスクが作成され、一意の名前が付けられたので、タスクを実行できます。
-
タスクを実行します。
たとえば、
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
-
レポートを表示します。
たとえば、
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つの新しい索引を使用する新しい実行計画のパフォーマンスが元の計画よりも優れていることを示しています。
-
展開タスクの推奨事項を実装します。
たとえば、
IMPLEMENT_EVOLVE_TASK
ファンクションを実行します。BEGIN :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name ); END;
-
データ・ディクショナリを問い合せて、新しい計画が承認されたことを確認します。
問合せにより、次のサンプル出力が提供されます。
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
出力は、新しい計画が承認されたことを示しています。
-
例の後のクリーンアップを実行します。
たとえば、次の文を入力します。
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パッケージおよびタイプ・リファレンス』を参照してください
29.6 SQL計画ベースラインの削除
SQL計画ベースラインから一部またはすべての計画を削除できます。この方法は、SQL計画管理のテスト時に役立つことがあります。
計画を削除するには、DBMS_SPM.DROP_SQL_PLAN_BASELINE
ファンクションを使用します。このファンクションは、削除された計画の数を戻します。次の表では、入力パラメータを説明します。
表29-11 DROP_SQL_PLAN_BASELINEパラメータ
ファンクション・パラメータ | 説明 |
---|---|
|
SQL文の識別子。 |
|
特定の計画の名前。デフォルトの |
この項では、コマンドラインからベースラインを削除する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページで、計画を選択してから「削除」をクリックします。
このチュートリアルでは、次のSQL文に対するすべての計画を削除することにより、実質的にSQL計画ベースラインを削除するものとします。
SELECT /* repeatable_sql */ COUNT(*) FROM hr.jobs;
SQL計画ベースラインを削除するには:
-
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
-
文に対する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; /
-
計画が削除されたことを確認します。
たとえば、次の問合せを実行します。
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のパラメータ
パラメータ | 説明 |
---|---|
|
SQL管理ベースが使用可能な |
|
消去するまで未使用の計画を保持する週の数。デフォルトは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テキスト・フィルタが存在しない場合、外部連結の一方の側が存在しません。
|
関連項目:
-
DBA_SQL_MANAGEMENT_CONFIG
についてさらに学習するには、『Oracle Databaseリファレンス』を参照してください -
DBMS_SPM.CONFIGURE
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
29.7.2 SMBのディスク領域制限の変更
週に1回のバックグラウンド・プロセスによって、SMBが使用する総領域が測定されます。
定義した制限を超過すると、アラート・ログに警告が書き込まれます。SMB領域の制限が引き上げられるか、SYSAUX
表領域のサイズが引き上げられるか、またはSQL管理オブジェクト(SQL計画ベースラインまたはSQLプロファイル)の消去によりSMBによって使用されている領域が減るまで、データベースは週に1回アラートを生成し続けます。このタスクでは、DBMS_SPM.CONFIGURE
プロシージャを使用して制限を変更する方法について説明します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
現在のSMB領域制限がデフォルトの10%である。
-
制限の比率を30%に変更する。
SMBの制限の比率を変更するには:
-
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
-
比率設定を変更します。
たとえば、設定を30%に変更するには次のコマンドを実行します。
EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);
-
データ・ディクショナリを問い合せて、変更を確認します。
たとえば、次の結合を実行します(出力例も示します)。
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の計画保存期間を変更するには:
-
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
-
保存期間を変更します。
たとえば、
CONFIGURE
プロシージャを実行して期間を105週に変更します。EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);
-
データ・ディクショナリを問い合せて、変更を確認します。
たとえば、次の問合せを実行します。
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パッケージおよびタイプ・リファレンスを参照してください