1. Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス
  2. DBMS_SPD

150 DBMS_SPD

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

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

参照:

150.1 DBMS_SPDの概要

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

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

150.2 DBMS_SPDのセキュリティ・モデル

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

150.3 DBMS_SPDのビュー

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

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

この表では、DBMS_SPDパッケージのサブプログラムをリストし、簡単に説明します。

表150-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プラン・ディレクティブをステージング表からアンパック(インポート)します。

150.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);

パラメータ

表150-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;

150.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);

パラメータ

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

150.4.3 DROP_SQL_PLAN_DIRECTIVEプロシージャ

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

構文 

DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (
   directive_id        IN     NUMBER);

パラメータ

表150-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;

150.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;

150.4.5 GET_PREFSファンクション

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

構文 

DBMS_SPD.GET_PREFS (
   pname     IN   VARCHAR2)
 RETURN VARCHAR2;

パラメータ

表150-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;

150.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

パラメータ

表150-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;

150.4.7 SET_PREFSプロシージャ

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

構文 

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

パラメータ

表150-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;

150.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

パラメータ

表150-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;