121 DBMS_SPM

DBMS_SPMパッケージは、様々なSQL文に対して保持される、計画履歴およびSQL計画のベースラインに対する制御された操作を実行するためのインタフェースを、DBAおよびその他のユーザーに提供することによって、SQL計画管理機能をサポートします。

DBMS_SPMの使用方法

セキュリティ・モデル

セキュリティ・モデル

このパッケージの所有者はSYSです。パッケージのプロシージャを実行するには、EXECUTEパッケージ権限が必要です。 ADMINISTER SQL MANAGEMENT OBJECT権限を付与されているすべてのユーザーがDBMS_SPMパッケージを実行できます。

DBMS_SPMサブプログラムの要約

次の表に、このパッケージのサブプログラムをアルファベット順に示します。

表121-1 DBMS_SPMパッケージのサブプログラム

サブプログラム	説明
ALTER_SQL_PLAN_BASELINEファンクション	属性名/値の形式でSQL文と関連付けられている1つまたはすべての計画の属性を変更します。
CONFIGUREプロシージャ	パラメータ/値の形式でSQL管理ベースの構成オプションを設定します。
CREATE_STGTAB_BASELINEプロシージャ	システム間でSQL計画ベースラインを転送する目的で使用されるステージング表を作成します。
DROP_SQL_PLAN_BASELINEファンクション	SQL文に関連付けられている1つまたはすべての計画を削除します。
EVOLVE_SQL_PLAN_BASELINEファンクション	1つ以上のSQL文に関連付けられているSQL計画ベースラインを進化させます。
LOAD_PLANS_FROM_CURSOR_CACHEファンクション	SQL文のカーソル・キャッシュに存在する1つ以上の計画をロードします。
LOAD_PLANS_FROM_SQLSETファンクション	SQLチューニング・セット（STS）に保存されている計画をSQL計画ベースラインにロードします。
PACK_STGTAB_BASELINEファンクション	SQL管理ベースからステージング表にSQL計画ベースラインをパック（エクスポート）します。
UNPACK_STGTAB_BASELINEファンクション	ステージング表からSQL管理ベースにSQL計画ベースラインをアンパック（インポート）します。

ALTER_SQL_PLAN_BASELINEファンクション

このファンクションは、属性名/値の形式でSQL文と関連付けられている1つまたはすべての計画の属性を変更します。

構文

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
   sql_handle        IN VARCHAR2 := NULL,
   plan_name         IN VARCHAR2 := NULL,
   attribute_name    IN VARCHAR2,
   attribute_value   IN VARCHAR2)
 RETURN PLS_INTEGER;

パラメータ

表121-2 ALTER_SQL_PLAN_BASELINEファンクションのパラメータ

パラメータ	説明
`sql_handle`	SQL文のハンドル。属性変更のためにSQL文に関連付けられている計画を識別します。`NULL`の場合は、`plan_name`を指定する必要があります。
`plan_name`	計画名。特定の計画を識別します。デフォルトの`NULL`にすると、`sql_handle`で識別されるSQL文に関連付けられているすべての計画の属性が設定されます。 `NULL`の場合は、`sql_handle`を指定する必要があります。
`attribute_name`	設定する計画属性の名前（次の表を参照）。
`attribute_value`	設定する計画属性の値（次の表を参照）。

表121-3 ALTER_SQL_PLAN_BASELINEファンクションのパラメータの名前および値

名前	説明	設定可能な値
`enabled`	'`YES`'にすると、計画をオプティマイザで使用できます。計画が使用されるかどうかは、`accepted`ステータスによって決まります。	'`YES`'または'`NO`'
`fixed`	'`YES`にすると、時間が経過してもSQL計画ベースラインは進化しません。固定された計画は、固定されていない計画より優先されます。	'`YES`'または'`NO`'
`autopurge`	'`YES`'にすると、計画は特定の期間使用されない場合にパージされます。'`NO`'にすると、計画はパージされません。	'`YES`'または'`NO`'
`plan_name`	計画の名前。	最大30文字の文字列。
`description`	計画の説明。	最大500文字の文字列。

戻り値

変更した計画の数。

使用上の注意

1つの計画を指定した場合は、様々なステータスの1つ、計画名または説明を変更できます。SQL文のすべての計画を指定した場合は、様々なステータスの1つまたは説明を変更できます。このファンクションは、同じ計画または他の計画で別の計画属性を設定するたびに何回もコールできます。

CONFIGUREプロシージャ

このプロシージャは、パラメータ/値の形式でSQL管理ベースの構成オプションを設定します。このファンクションは、構成オプションを設定するたびに何回もコールできます。

構文

DBMS_SPM.CONFIGURE (
   parameter_name    IN VARCHAR2,
   parameter_value   IN NUMBER);

パラメータ

表121-4 CONFIGUREプロシージャのパラメータ

パラメータ	説明
`parameter_name`	設定するパラメータの名前（次の表を参照）。
`parameter_value`	設定するパラメータの値（次の表を参照）。

表121-5 CONFIGUREプロシージャのパラメータの名前および値

名前	説明	設定可能な値	デフォルト値
`space_budget_percent`	SQL管理ベースに使用可能な`SYSAUX`領域の最大の割合。	1,2, …, 50	10
`plan_retention_weeks`	パージする前に未使用の計画を保持する週の数。	5,6, …, 523	53

使用上の注意

SQL管理ベースのデフォルトの領域配分は、SYSAUX表領域の10パーセントのみです。領域配分は、最大50%に設定できます。未使用の計画のデフォルトの保存期間は1年と1週間です。つまり、計画は、1年を超えて未使用の場合、自動的にパージされます。保存期間は、最大523週間（10年強）に設定できます。
SQL管理ベースによって占有される領域が、定義されている領域配分の制限を超えると、週次データベース・アラートが生成されます。

CREATE_STGTAB_BASELINEプロシージャ

このプロシージャは、システム間でSQL計画ベースラインを転送する目的で使用されるステージング表を作成します。

構文

DBMS_SPM.CREATE_STGTAB_BASELINE (
   table_name        IN VARCHAR2,
   table_owner       IN VARCHAR2 := NULL,
   tablespace_name   IN VARCHAR2 := NULL);

パラメータ

表121-6 CREATE_STGTAB_BASELINEプロシージャのパラメータ

パラメータ	説明
`table_name`	SQL計画ベースラインをパックおよびアンパックする目的で作成するステージング表の名前。
`table_owner`	ステージング表の所有者の名前。デフォルトの`NULL`にすると、現行のスキーマが表の所有者になります。
`tablespace_name`	表領域の名前。デフォルトのNULLにすると、デフォルトの表領域にステージング表が作成されます。

使用上の注意

ステージング表の作成が最初の手順です。システム間でSQL計画ベースラインを移行するには、次の一連の手順を実行する必要があります。

ソース・システムにステージング表を作成します。
ソース・システムでSQL計画ベースラインを選択して、ステージング表にパックします。
Oracle EXPユーティリティまたはOracle Data Pumpを使用して、フラット・ファイルにステージング表をエクスポートします。
ターゲット・システムにフラット・ファイルを転送します。
Oracle IMPユーティリティまたはOracle Data Pumpを使用して、フラット・ファイルからステージング表をインポートします。
ステージング表からSQL計画ベースラインを選択して、ターゲット・システムにアンパックします。

DROP_SQL_PLAN_BASELINEファンクション

このファンクションは、SQL文に関連付けられている1つまたはすべての計画を削除します。

構文

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
   sql_handle     IN VARCHAR2 := NULL,
   plan_name      IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;

パラメータ

表121-7 DROP_SQL_PLAN_BASELINEファンクションのパラメータ

パラメータ	説明
`sql_handle`	SQL文のハンドル。削除するSQL文に関連付けられている計画を識別します。`NULL`の場合は、`plan_name`を指定する必要があります。
`plan_name`	計画名。特定の計画を識別します。デフォルトの`NULL`にすると、`sql_handle`で識別されるSQL文に関連付けられているすべての計画が削除されます。

戻り値

削除した計画の数。

EVOLVE_SQL_PLAN_BASELINEファンクション

このファンクションは、1つ以上のSQL文に関連付けられているSQL計画ベースラインを進化させます。SQL計画ベースラインは、1つ以上の未承認の計画が承認済の計画に変更される場合に進化します。ユーザーによる問合せ（パラメータverify = 'YES'）が行われた場合、関連付けられているSQL計画ベースラインから選択された計画のパフォーマンスと各未承認の計画の実行パフォーマンスが比較されます。未承認の計画のパフォーマンスがSQL計画ベースラインのパフォーマンスより優れている場合、未承認の計画は承認済の計画に変更されます。ただし、ユーザーがこのようなアクションを許可している場合（commit = 'YES'）にかぎられます。

このファンクションの2番目の形式では、計画リスト形式を使用します。

構文

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
   sql_handle   IN VARCHAR2 := NULL,
   plan_name    IN VARCHAR2 := NULL,
   time_limit   IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
   verify       IN VARCHAR2 := 'YES',
   commit       IN VARCHAR2 := 'YES')
  RETURN CLOB;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
   plan_list    IN DBMS_SPM.NAME_LIST,
   time_limit   IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
   verify       IN VARCHAR2 := 'YES',
   commit       IN VARCHAR2 := 'YES')
  RETURN CLOB;

パラメータ

表121-8 EVOLVE_SQL_PLAN_BASELINEファンクションのパラメータ

パラメータ	説明
`sql_handle`	SQL文の識別子。 `plan_name`を指定しないかぎり、`NULL`にすると、SQL計画ベースライン内の未承認の計画を含むすべての文が考慮されます。
`plan_name`	計画の識別子。デフォルトの`NULL`にすると、識別されたSQL文、または`sql_handle`が`NULL`の場合はすべてのSQL文のSQL計画ベースラインで未承認の計画が考慮されます。
`plan_list`	計画名のリスト。リストないの各計画は、同じSQL文に属するものでも、別のSQL文に属するものでもかまいません。
`time_limit`	時間制限（分）。 `verify` = '`YES`'の場合にのみ適用されます。時間制限はグローバルです。最初の未承認の計画を検証する場合の時間制限は入力値と同じ値に設定されます。2番目の未承認の計画を検証する場合の制限時間は（入力値 - 最初の計画の検証に要した時間）に設定されます。以降同様に設定されます。 `DBMS_SPM`.`AUTO_LIMIT`（デフォルト）にすると、計画に対して実行する必要がある検証の数に基づいて適切な時間制限が選択されます。 `DBMS_SPM`.`NO_LIMIT`にすると、時間制限は設定されません。正の整数値は、ユーザーが指定した時間制限を表します。
`verify`	未承認の計画を承認済の計画に変更する前に、計画を実行し、パフォーマンスを比較するかどうかを指定します。パフォーマンスの検証では、未承認の計画および対応するSQL計画ベースラインから選択された計画が実行され、これらの計画のパフォーマンス統計が比較されます。未承認の計画でパフォーマンスが向上した場合、未承認の計画は承認済の計画に変更されます。 '`YES`'（デフォルト）にすると、未承認の計画を承認済の計画に変更する前に、未承認の計画でパフォーマンスが向上することが検証されます。 '`NO`'にすると、計画は実行されず、未承認の計画が承認済の計画に変更されるのみです。
`commit`	未承認の計画の`ACCEPTED`ステータスを'`NO`'から'`YES`'に更新するかどうかを指定します。 '`YES`'（デフォルト）: 条件を満たす未承認の計画を更新し、verify = '`YES`'の場合はその更新およびパフォーマンスの検証結果を示すレポートを生成します。 '`NO`': 更新せずにレポートを生成します。 `verify` = '`NO`'とともに`commit` = '`NO`'を指定すると、操作できなくなります。

戻り値

書式設定されたテキスト・レポートが含まれているCLOB。レポートには、ACCEPTEDステータスへの変更が可能な未承認の計画が順に示されます。また、verify = 'YES'の場合は、それらの計画のパフォーマンスの検証結果も示されます。

使用上の注意

このサブプログラムを起動するには、ADMINISTER SQL MANAGEMENT OBJECT権限が必要です。

LOAD_PLANS_FROM_CURSOR_CACHEファンクション

このファンクションは、1つまたは一組のSQL文のカーソル・キャッシュに存在する1つ以上の計画をロードします。また、4つのオーバーロード（SQL文テキストを使用したオーバーロード、SQLハンドルを使用したオーバーロード、SQL IDを使用したオーバーロードまたはattribute_nameとattribute_valueのペアを使用したオーバーロード）を含んでいます。

構文

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_handle        IN  VARCHAR2,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   attribute_name   IN VARCHAR2,
   attribute_value  IN VARCHAR2,
   fixed            IN VARCHAR2 := 'NO',
   enabled          IN VARCHAR2 := 'YES')
  RETURN PLS_INTEGER;

パラメータ

表121-9 LOAD_PLANS_FROM_CURSOR_CACHEファンクションのパラメータ

パラメータ	説明
`sql_id`	SQL文の識別子。カーソル・キャッシュ内のSQL文を識別します。注意: 3番目のオーバーロードでは、識別されたSQL文のテキストがカーソル・キャッシュから抽出され、計画のロード先となるSQL計画ベースラインの特定にそのテキストが使用されます。SQL計画ベースラインが存在しない場合は作成されます。
`plan_hash_value`	計画の識別子。デフォルトの`NULL`にすると、`SQL_ID`で識別されるSQL文のカーソル・キャッシュに存在するすべての計画が取得されます。
`sql_text`	計画のロード先となるSQL計画ベースラインを特定する場合に使用されるSQLテキスト。SQL計画ベースラインが存在しない場合は作成されます。ユーザーがSQL文のテキストにヒントを追加してSQL文をチューニングし、その結果生成された計画を元のSQL文のSQL計画ベースラインにロードする場合は、テキストの使用が重要となります。
`sql_handle`	計画のロード先となるSQL計画ベースラインを特定する場合に使用されるSQLハンドル。 `sql_handle`は、既存のSQL計画ベースラインである必要があります。ユーザーがSQL文のテキストにヒントを追加してSQL文をチューニングし、その結果生成された計画を元のSQL文のSQL計画ベースラインにロードする場合は、ハンドルの使用が重要となります。
`fixed`	デフォルトの'`NO`'にすると、ロードされた計画が、固定されていない計画として使用されます。値を'`YES`'にすると、ロードされた計画が、固定された計画として使用され、時間が経過してもSQL計画のベースラインは進化しません。
`attribute_name`	次の属性名のいずれかになります。 '`SQL_TEXT`' '`PARSING_SCHEMA_NAME`' '`MODULE`' '`ACTION'`
`attribute_value`	属性値は、属性名が'`SQL_TEXT`'の場合、`LIKE`条件の検索パターンとして使用されます。それ以外の場合は、等価検索値として使用されます（たとえば、`attribute_name` => '`SQL_TEXT`'および`attribute_value` => '`% HR-123` %'を指定すると、選択フィルタとして`SQL_TEXT` `LIKE` '`%` `HR`-`123` `%`'が適用されます。同様に、`attribute_name` => '`MODULE`'および`attribute_value` => '`HR`'を指定すると、計画選択フィルタとして`MODULE` = '`HR`'が適用されます）。属性値は、二重引用符で囲んだ場合または属性名が'`SQL_TEXT`'の場合を除き、大文字になります。
`enabled`	デフォルトの'`YES`'にすると、ロードされたプランが、オプティマイザで使用できるように有効になります。

戻り値

ロードした計画の数。

使用上の注意

このサブプログラムを起動するには、ADMINISTER SQL MANAGEMENT OBJECT権限が必要です。

LOAD_PLANS_FROM_SQLSETファンクション

このファンクションは、SQLチューニング・セット（STS）に保存されている計画をSQL計画ベースラインにロードします。STSからロードした計画は、パフォーマンスに関しては検証されませんが、承認済の計画として既存または新規のSQL計画ベースラインに追加されます。このファンクションを使用すると、新しいSQL計画ベースラインでSQL管理ベースをシードできます。

構文

DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sqlset_owner     IN  VARCHAR2 := NULL,
   basic_filter     IN  VARCHAR2 := NULL,
   fixed            IN  VARCHAR2 := 'NO',
   enabled          IN  VARCHAR2 := 'YES'
   commit_rows      IN  NUMBER   := 1000)
RETURN PLS_INTEGER;

パラメータ

表121-10 LOAD_PLANS_FROM_SQLSETファンクションのパラメータ

パラメータ	説明
`sqlset_name`	SQL計画ベースラインに計画をロードするSTSの名前。
`sqlset_owner`	STSの所有者。`NULL`にすると、現行のスキーマが所有者になります。
`basic_filter`	条件を満たす計画のみをロード対象として選択するためにSTSに適用されるフィルタ。このフィルタは、`DBA_SQLSET_STATEMENTS`ビューに対して指定可能な`WHERE`句条件の形式にすることができます。たとえば、`basic_filter` => '`sql_text like ''select /LOAD_STS/%'''`や`basic_filter => 'sql_id="b62q7nc33gzwx"'`などです。
`fixed`	デフォルトの'`NO`'にすると、ロードされた計画が、固定されていない計画として使用されます。値を'`YES`'にすると、ロードされた計画が、固定された計画として使用され、時間が経過してもSQL計画のベースラインは進化しません。
`enabled`	デフォルトの'`YES`'にすると、ロードされたプランが、オプティマイザで使用できるように有効になります。
`commit_rows`	定期的なコミットを実行する前にロードするSQL計画の数。UNDOログのサイズ縮小に役立ちます。

戻り値

ロードした計画の数。

使用上の注意

リモート・システムから計画をロードするには、まずリモート・システムのSTSに計画をロードし、リモート・システムからローカル・システムにSTSをエクスポート/インポートしてから、このファンクションを使用します。
自動ワークロード・リポジトリ（AWR）から計画をロードするには、まずAWRスナップショットに保存されている計画をSTSにロードしてから、このファンクションを使用します。
また、1つ以上のSQL文のカーソル・キャッシュに存在する計画をSTSに取得してから、このファンクションを使用することもできます。

PACK_STGTAB_BASELINEファンクション

このファンクションは、SQL管理ベースからステージング表にSQL計画ベースラインをパック（エクスポート）します。

構文

DBMS_SPM.PACK_STGTAB_BASELINE (
   table_name       IN VARCHAR2,
   table_owner      IN VARCHAR2 := NULL,
   sql_handle       IN VARCHAR2 := NULL,
   plan_name        IN VARCHAR2 := NULL,
   sql_text         IN CLOB     := NULL,
   creator          IN VARCHAR2 := NULL,   origin           IN VARCHAR2 := NULL,
   enabled          IN VARCHAR2 := NULL,
   accepted         IN VARCHAR2 := NULL,
   fixed            IN VARCHAR2 := NULL,
   module           IN VARCHAR2 := NULL,
   action           IN VARCHAR2 := NULL)
RETURN NUMBER;

パラメータ

表121-11 PACK_STGTAB_BASELINEファンクションのパラメータ

パラメータ	説明
`table_name`	SQL計画ベースラインのパック先のステージング表の名前（二重引用符で囲まれていないかぎり、大/小文字区別なし）。
`table_owner`	ステージング表の所有者の名前。デフォルトの`NULL`にすると、現行のスキーマが表の所有者になります。
`sql_handle`	SQLハンドル（大/小文字区別）
`plan_name`	計画名（大/小文字区別、%ワイルドカードを使用可）
`sql_text`	SQLテキスト文字列（大/小文字区別、%ワイルドカードを使用可）
`creator`	SQL計画ベースラインの作成者（二重引用符で囲まれていないかぎり、大/小文字区別なし）
`origin`	SQL計画ベースラインの起点。`'MANUAL-LOAD'`、`'AUTO-CAPTURE'`、`'MANUAL_SQLTUNE'`または`'AUTO-SQLTUNE'`（大/小文字区別なし）である必要があります。
`enabled`	'`YES`'または'`NO`'（大/小文字区別なし）である必要があります。
`accepted`	'`YES`'または'`NO`'（大/小文字区別なし）である必要があります。
`fixed`	'`YES`'または'`NO`'（大/小文字区別なし）である必要があります。
`module`	モジュール（大/小文字区別）
`action`	アクション（大/小文字区別）

戻り値

パックしたSQL計画ベースラインの数。

UNPACK_STGTAB_BASELINEファンクション

このファンクションは、ステージング表からSQL管理ベースにSQL計画ベースラインをアンパック（インポート）します。

構文

DBMS_SPM.UNPACK_STGTAB_BASELINE (
   table_name       IN VARCHAR2,
   table_owner      IN VARCHAR2 := NULL,
   sql_handle       IN VARCHAR2 := NULL,
   plan_name        IN VARCHAR2 := NULL,
   sql_text         IN CLOB     := NULL,
   creator          IN VARCHAR2 := NULL,   origin           IN VARCHAR2 := NULL,
   enabled          IN VARCHAR2 := NULL,
   accepted         IN VARCHAR2 := NULL,
   fixed            IN VARCHAR2 := NULL,
   module           IN VARCHAR2 := NULL,
   action           IN VARCHAR2 := NULL)
RETURN NUMBER;

パラメータ

表121-12 UNPACK_STGTAB_BASELINEファンクションのパラメータ

パラメータ	説明
`table_name`	SQL計画ベースラインのアンパック元のステージング表の名前（二重引用符で囲まれていないかぎり、大/小文字区別なし）。
`table_owner`	ステージング表の所有者の名前。デフォルトのNULLにすると、現行のスキーマが表の所有者になります。
`sql_handle`	SQLハンドル（大/小文字区別）
`plan_name`	計画名（大/小文字区別、%ワイルドカードを使用可）
`sql_text`	SQLテキスト文字列（大/小文字区別、%ワイルドカードを使用可）
`creator`	SQL計画ベースラインの作成者（二重引用符で囲まれていないかぎり、大/小文字区別なし）
`origin`	SQL計画ベースラインの起点。`'MANUAL-LOAD'`、`'AUTO-CAPTURE'`、`'MANUAL_SQLTUNE'`または`'AUTO-SQLTUNE'`（大/小文字区別なし）である必要があります。
`enabled`	'`YES`'または'`NO`'（大/小文字区別なし）である必要があります。
`accepted`	'`YES`'または'`NO`'（大/小文字区別なし）である必要があります。
`fixed`	'`YES`'または'`NO`'（大/小文字区別なし）である必要があります。
`module`	モジュール（大/小文字区別）
`action`	アクション（大/小文字区別）

戻り値

アンパックした計画の数。