173 DBMS_SPD
DBMS_SPD
パッケージは、SQLプラン・ディレクティブ(SPD)を管理するためのサブプログラムを提供します。
この章のトピックは、次のとおりです:
参照:
-
SQLプラン・ディレクティブについては、『Oracle Database SQLチューニング・ガイド』を参照してください。
173.1 DBMS_SPDの概要
このパッケージは、SQLプラン・ディレクティブ(SPD)を管理するためのサブプログラムを提供します。
SPDは、Oracleによって自動的に生成されるオブジェクトです。たとえば、オプティマイザによって推測された単一表のカーディナリティが、表へのアクセス時に戻された実際の行数と異なることがOracleによって検出された場合、その表の動的統計を実行するディレクティブが自動的に作成されます。表を参照するSQL文がコンパイルされたときに、オプティマイザはその表の動的統計を実行し、より正確な推測値を取得します。
173.2 DBMS_SPDのセキュリティ・モデル
DBMS_SPD
は、実行者権限パッケージです。実行者は、このパッケージに含まれるほとんどのサブプログラムを実行するときに、ADMINISTER SQL MANAGEMENT OBJECT
権限を必要とします。また、これらのサブプログラムは、現行のトランザクション(ある場合)をコミットし、操作を実行してから再度コミットします。
173.3 DBMS_SPDのビュー
DBAビューDBA_SQL_PLAN_DIRECTIVES
は、システムで作成されたすべてのディレクティブを表示し、ビューDBA_SQL_PLAN_DIR_OBJECTS
は、それらのディレクティブに含まれるオブジェクトを表示します。
173.4 DBMS_SPDサブプログラムの要約
この表では、DBMS_SPD
パッケージのサブプログラムをリストし、簡単に説明します。
表173-1 DBMS_SPDパッケージのサブプログラム
サブプログラム | 説明 |
---|---|
SQLプラン・ディレクティブの様々な属性を変更します。 |
|
SQLプラン・ディレクティブをパック(エクスポート)するステージング表を作成します。 |
|
SQLプラン・ディレクティブを削除します。 |
|
SQL文の実行中にSGAメモリーに自動的に記録されるSQLプラン・ディレクティブの手動フラッシュを許可します。 |
|
SQLプラン・ディレクティブのプリファレンスの値を取得します。 |
|
SQLプラン・ディレクティブをステージング表にパック(エクスポート)します。 |
|
SQLプラン・ディレクティブの様々なプリファレンスの設定を許可します。 |
|
SQLプラン・ディレクティブをステージング表からアンパック(インポート)します。 |
173.4.1 ALTER_SQL_PLAN_DIRECTIVEプロシージャ
このプロシージャは、SQLプラン・ディレクティブの様々な属性を変更します。
構文
DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( directive_id IN NUMBER, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2);
パラメータ
表173-2 ALTER_SQL_PLAN_DIRECTIVEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLプラン・ディレクティブのID。 |
|
|
|
有効な値は、次のとおりです。
|
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。 -
ORA-13158 OBJECT_DOES_NOT_EXIST
: 指定されたオブジェクトは存在しません。
使用上のノート
このプロシージャを実行するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
例
BEGIN DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE (12345, 'STATE', 'PERMANENT'); END;
173.4.2 CREATE_STGTAB_DIRECTIVEプロシージャ
このプロシージャは、SQLプラン・ディレクティブをパック(エクスポート)するステージング表を作成します。
構文
DBMS_SPD.CREATE_STGTAB_DIRECTIVE ( table_name IN VARCHAR2, table_owner IN VARCHAR2 := USER, tablespace_name IN VARCHAR2 := NULL);
パラメータ
表173-3 CREATE_STGTAB_DIRECTIVEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
ステージング表の名前。 |
|
ステージング表のスキーマ所有者の名前。デフォルトは現在のスキーマです。 |
|
表領域の名前。デフォルトの |
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。 -
ORA-44001 INVALID_SCHEMA
: 入力されたスキーマは存在しません。 -
ORA-13159 TABLE_ALREADY_EXISTS
: 指定された表はすでに存在します。 -
ORA-29304 TABLESPACE_MISSING
: 指定された表領域は存在しません。
使用上のノート
このプロシージャを実行するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
173.4.3 DROP_SQL_PLAN_DIRECTIVEプロシージャ
このプロシージャは、SQLプラン・ディレクティブを削除します。
構文
DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE ( directive_id IN NUMBER);
パラメータ
表173-4 DROP_SQL_PLAN_DIRECTIVEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLプラン・ディレクティブのID。 |
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。 -
ORA-13158 OBJECT_DOES_NOT_EXIST
: 指定されたオブジェクトは存在しません。
使用上のノート
このプロシージャを実行するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
例
BEGIN DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (12345); END;
173.4.4 FLUSH_SQL_PLAN_DIRECTIVEプロシージャ
このプロシージャは、SQL文の実行中にSGAメモリーに自動的に記録されるSQLプラン・ディレクティブの手動フラッシュを許可します。
SGAで記録された情報は、Oracleバックグラウンド処理で定期的にフラッシュされます。このプロシージャは、手動で情報をフラッシュする方法を提供します。
構文
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
例外
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。
使用上のノート
このプロシージャを実行するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
例
BEGIN DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE; END;
173.4.5 GET_PREFSファンクション
この機能では、SQLプラン・ディレクティブに指定されたプリファレンスの値が戻されます。
構文
DBMS_SPD.GET_PREFS ( pname IN VARCHAR2) RETURN VARCHAR2;
パラメータ
表173-5 GET_PREFSファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
プリファレンスの名前。このプロシージャでは、プリファレンス |
戻り値
プリファレンスの値
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。
使用上のノート
-
このプロシージャを実行するには、
ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。 -
SPD_RETENTION_WEEKS
- SQLプラン・ディレクティブは、このプリファレンスの設定値よりも長い期間使用されなかった場合、パージされます。
例
SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM DUAL;
173.4.6 PACK_STGTAB_DIRECTIVEファンクション
このファンクションは、SQLプラン・ディレクティブをステージング表にパック(エクスポート)します。
構文
DBMS_SPD.PACK_STGTAB_DIRECTIVE ( table_name IN VARCHAR2, table_owner IN VARCHAR2 := USER directive_id IN NUMBER := NULL, obj_list IN OBJECTTAB := NULL) RETURN NUMBER
パラメータ
表173-6 PACK_STGTAB_DIRECTIVEファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
ステージング表の名前。 |
|
ステージング表のスキーマ所有者の名前。デフォルトは現在のスキーマです。 |
|
SQLプラン・ディレクティブのID。デフォルトの |
|
ディレクティブで使用されるオブジェクトに基づいて、パックするディレクティブをフィルタするために使用されます。 |
戻り値
パックするSQLプラン・ディレクティブの数。
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。 -
ORA-44001 INVALID_SCHEMA
: 入力されたスキーマは存在しません。 -
ORA-29304 INVALID_STGTAB
: 指定されたステージング表が無効であるか、存在しません。 -
ORA-13158 OBJECT_DOES_NOT_EXIST
: 指定されたオブジェクトは存在しません。
使用上のノート
このプロシージャを実行するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
例
-- Pack all directives in the system SELECT DBMS_SPD.PACK_STGTAB_DIRECTIVE('mydirtab') FROM DUAL; SET SERVEROUTPUT ON; -- Pack directives relevant to objects in SH schema DECLARE my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab(); dir_cnt NUMBER; BEGIN my_list.extend(1); my_list(1).owner := 'SH'; -- schema name my_list(1).object_name := NULL; -- all tables in SH my_list(1).object_type := 'TABLE'; -- type of object dir_cnt := DBMS_SPD.PACK_STGTAB_DIRECTIVE('mydirtab', obj_list => my_list); DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt); END; -- Pack directives relevant to tables SALES and CUSTOMERS in SH schema DECLARE my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab(); dir_cnt NUMBER; BEGIN my_list.extend(2); -- SALES table my_list(1).owner := 'SH'; my_list(1).object_name := 'SALES'; my_list(1).object_type := 'TABLE'; -- CUSTOMERS table my_list(2).owner := 'SH'; my_list(2).object_name := 'CUSTOMERS'; my_list(2).object_type := 'TABLE'; dir_cnt := DBMS_SPD.PACK_STGTAB_DIRECTIVE('mydirtab', obj_list => my_list); DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt); END;
173.4.7 SET_PREFSプロシージャ
このプロシージャは、SQLプラン・ディレクティブの様々なプリファレンスの設定を許可します。
構文
DBMS_SPD.SET_PREFS ( pname IN VARCHAR2, pvalue IN VARCHAR2);
パラメータ
表173-7 SET_PREFSプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
プリファレンスの名前。このプロシージャでは、プリファレンス |
|
プリファレンスの値。
|
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。
使用上のノート
-
このプロシージャを実行するには、
ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。 -
SPD_RETENTION_WEEKS
- SQLプラン・ディレクティブは、このプリファレンスの設定値よりも長い期間使用されなかった場合、パージされます。
例
BEGIN DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS', '4'); END;
173.4.8 UNPACK_STGTAB_DIRECTIVEファンクション
このプロシージャは、SQLプラン・ディレクティブをステージング表からアンパック(インポート)します。
構文
DBMS_SPD.UNPACK_STGTAB_DIRECTIVE ( table_name IN VARCHAR2, table_owner IN VARCHAR2 := USER, directive_id IN NUMBER := NULL, obj_list IN OBJECTTAB := NULL) RETURN NUMBER
パラメータ
表173-8 UNPACK_STGTAB_DIRECTIVEファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
ステージング表の名前。 |
|
ステージング表のスキーマ所有者の名前。デフォルトは現在のスキーマです。 |
|
SQLプラン・ディレクティブのID。デフォルトの |
|
ディレクティブで使用されるオブジェクトに基づいて、アンパックするディレクティブをフィルタするために使用されます。 |
戻り値
アンパックするSQLプラン・ディレクティブの数。
例外
-
ORA-38171 INSUFFICIENT_PRIVILEGE
: ユーザーには、操作を実行するための適切な権限がありません。 -
ORA-28104 INVALID_INPUT
: 入力値が無効です。 -
ORA-44001 INVALID_SCHEMA
: 入力されたスキーマは存在しません。 -
ORA-29304 INVALID_STGTAB
: 指定されたステージング表が無効であるか、存在しません。 -
ORA-13158 OBJECT_DOES_NOT_EXIST
: 指定されたオブジェクトは存在しません。
使用上のノート
このプロシージャを実行するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
例
-- Unack all directives in the staging table SELECT DBMS_SPD.UNPACK_STGTAB_DIRECTIVE('mydirtab') FROM DUAL; SET SERVEROUTPUT ON; -- Unpack directives relevant to objects in SH schema DECLARE my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab(); dir_cnt number; BEGIN my_list.extend(1); my_list(1).owner := 'SH'; -- schema name my_list(1).object_name := null; -- all tables in SH my_list(1).object_type := 'TABLE'; -- type of object dir_cnt := DBMS_SPD.UNPACK_STGTAB_DIRECTIVE('mydirtab', obj_list => my_list); DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt); END; -- Unpack directives relevant to tables SALES and CUSTOMERS in SH schema DECLARE my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab(); dir_cnt NUMBER; begin my_list.extend(2); -- SALES table my_list(1).owner := 'SH'; my_list(1).object_name := 'SALES'; my_list(1).object_type := 'TABLE'; -- CUSTOMERS table my_list(2).owner := 'SH'; my_list(2).object_name := 'CUSTOMERS'; my_list(2).object_type := 'TABLE'; dir_cnt := DBMS_SPD.UNPACK_STGTAB_DIRECTIVE('mydirtab', obj_list => my_list); DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt); END;