147 DBMS_SPD

DBMS_SPDパッケージは、SQLプラン・ディレクティブ(SPD)を管理するためのサブプログラムを提供します。

関連項目: 第155章「DBMS_STATS」 SQLプラン・ディレクティブについては、『Oracle Database SQLチューニング・ガイド』を参照してください。

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

• DBMS_SPDの使用

  • 概要

  • セキュリティ・モデル

  • ビュー

• DBMS_SPDサブプログラムの要約

DBMS_SPDの使用

• 概要

• セキュリティ・モデル

• ビュー

概要

このパッケージは、SQLプラン・ディレクティブ(SPD)を管理するためのサブプログラムを提供します。SPDは、Oracleによって自動的に生成されるオブジェクトです。たとえば、オプティマイザによって推測された単一表のカーディナリティが、表へのアクセス時に戻された実際の行数と異なることがOracleによって検出された場合、その表の動的統計を実行するディレクティブが自動的に作成されます。表を参照するSQL文がコンパイルされたときに、オプティマイザはその表の動的統計を実行し、より正確な推測値を取得します。

セキュリティ・モデル

DBMS_SPDは、実行者権限パッケージです。実行者は、このパッケージに含まれるほとんどのサブプログラムを実行するときに、ADMINISTER SQL MANAGEMENT OBJECT権限を必要とします。また、これらのサブプログラムは、現行のトランザクション(ある場合)をコミットし、操作を実行してから再度コミットします。

ビュー

DBAビューDBA_SQL_PLAN_DIRECTIVESは、システムで作成されたすべてのディレクティブを表示し、ビューDBA_SQL_PLAN_DIR_OBJECTSは、それらのディレクティブに含まれるオブジェクトを表示します。

DBMS_SPDサブプログラムの要約

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

サブプログラム	説明
ALTER_SQL_PLAN_DIRECTIVEプロシージャ	SQLプラン・ディレクティブの様々な属性を変更します。
CREATE_STGTAB_DIRECTIVEプロシージャ	SQLプラン・ディレクティブをパック(エクスポート)するステージング表を作成します。
DROP_SQL_PLAN_DIRECTIVEプロシージャ	SQLプラン・ディレクティブを削除します。
FLUSH_SQL_PLAN_DIRECTIVEプロシージャ	SQL文の実行中にSGAメモリーに自動的に記録されるSQLプラン・ディレクティブの手動フラッシュを許可します。
GET_PREFSファンクション	SQLプラン・ディレクティブのプリファレンスの値を取得します。
PACK_STGTAB_DIRECTIVEファンクション	SQLプラン・ディレクティブをステージング表にパック(エクスポート)します。
SET_PREFSプロシージャ	SQLプラン・ディレクティブの様々なプリファレンスの設定を許可します。
UNPACK_STGTAB_DIRECTIVEファンクション	SQLプラン・ディレクティブをステージング表からアンパック(インポート)します。

ALTER_SQL_PLAN_DIRECTIVEプロシージャ

このプロシージャは、SQLプラン・ディレクティブの様々な属性を変更します。

構文

DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE (
   directive_id        IN     NUMBER,
   attribute_name      IN     VARCHAR2,
   attribute_value     IN     VARCHAR2);

パラメータ

表147-2 ALTER_SQL_PLAN_DIRECTIVEプロシージャのパラメータ

パラメータ	説明
directive_id	SQLプラン・ディレクティブのID。
attribute_name	ENABLED AUTO_DROP
attribute_value	有効な値は、次のとおりです。 ENABLED: - YESディレクティブが有効で使用可能な場合 - NOディレクティブが有効ではなく、使用不可の場合 AUTO_DROP: - SPD_RETENTION_WEEKSの期間使用されなかった場合、YESディレクティブを自動的に削除します。この動作はデフォルトです。 - NOディレクティブを自動的に削除しません。

例外

• 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;

CREATE_STGTAB_DIRECTIVEプロシージャ

このプロシージャは、SQLプラン・ディレクティブをパック(エクスポート)するステージング表を作成します。

構文

DBMS_SPD.CREATE_STGTAB_DIRECTIVE (
   table_name         IN VARCHAR2,
   table_owner        IN VARCHAR2  := USER,
   tablespace_name    IN VARCHAR2  := NULL);

パラメータ

表147-3 CREATE_STGTAB_DIRECTIVEプロシージャのパラメータ

パラメータ	説明
table_name	ステージング表の名前。
table_owner	ステージング表のスキーマ所有者の名前。デフォルトは現在のスキーマです。
tablespace_name	表領域の名前。デフォルトのNULLにすると、デフォルトの表領域にステージング表が作成されます。

例外

• 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権限が必要です。

DROP_SQL_PLAN_DIRECTIVEプロシージャ

このプロシージャは、SQLプラン・ディレクティブを削除します。

構文

DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (
   directive_id        IN     NUMBER);

パラメータ

表147-4 DROP_SQL_PLAN_DIRECTIVEプロシージャのパラメータ

パラメータ	説明
directive_id	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;

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;

GET_PREFSファンクション

この機能では、SQLプラン・ディレクティブに指定されたプリファレンスの値が戻されます。

構文

DBMS_SPD.GET_PREFS (
   pname     IN   VARCHAR2)
 RETURN VARCHAR2;

パラメータ

表147-5 GET_PREFSファンクションのパラメータ

パラメータ	説明
pname	プリファレンスの名前。このプロシージャでは、プリファレンスSPD_RETENTION_WEEKSがサポートされます。

戻り値

プリファレンスの値。

例外

• 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;

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

パラメータ

表147-6 PACK_STGTAB_DIRECTIVEファンクションのパラメータ

パラメータ	説明
table_name	ステージング表の名前。
table_owner	ステージング表のスキーマ所有者の名前。デフォルトは現在のスキーマです。
directive_id	SQLプラン・ディレクティブのID。デフォルトのNULLは、システム内のすべてのディレクティブを意味します。
obj_list	ディレクティブで使用されるオブジェクトに基づいて、パックするディレクティブをフィルタするために使用されます。obj_listがNULLでない場合、ディレクティブがパックされるのは、そのディレクティブ内のすべてのオブジェクトがobj_list内に存在するときのみです。

戻り値

パックする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;

SET_PREFSプロシージャ

このプロシージャは、SQLプラン・ディレクティブの様々なプリファレンスの設定を許可します。

構文

DBMS_SPD.SET_PREFS (
   pname     IN   VARCHAR2,
   pvalue    IN   VARCHAR2);

パラメータ

表147-7 SET_PREFSプロシージャのパラメータ

パラメータ	説明
pname	プリファレンスの名前。このプロシージャでは、プリファレンスSPD_RETENTION_WEEKSがサポートされます。
pvalue	プリファレンスの値。 SPD_RETENTION_WEEKS: SQLプラン・ディレクティブは、このプリファレンスの設定値よりも長い期間使用されなかった場合、パージされます。デフォルトの53(SPD_RETENTION_WEEKS_DEFAULT)週は、期間ディレクティブが1年余り経っても使用されなかった場合、そのディレクティブがパージされることを意味します。0以上の値を設定できます。また、値NULLを渡すと、プリファレンスをデフォルトに設定できます。

例外

• 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;

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

パラメータ

表147-8 UNPACK_STGTAB_DIRECTIVEファンクションのパラメータ

パラメータ	説明
table_name	ステージング表の名前。
table_owner	ステージング表のスキーマ所有者の名前。デフォルトは現在のスキーマです。
directive_id	SQLプラン・ディレクティブのID。デフォルトのNULLは、システム内のすべてのディレクティブを意味します。
obj_list	ディレクティブで使用されるオブジェクトに基づいて、アンパックするディレクティブをフィルタするために使用されます。obj_listがNULLでない場合、ディレクティブがアンパックされるのは、そのディレクティブ内のすべてのオブジェクトがobj_list内に存在するときのみです。

戻り値

アンパックする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;