日本語PDF
  1. PL/SQLパッケージおよびタイプ・リファレンス
  2. DBMS_SQLSET

170 DBMS_SQLSET

DBMS_SQLSETパッケージは、SQLチューニング・セットを管理するためのインタフェースを提供します。

このパッケージは、DBMS_SQLTUNEのSQLチューニング・セット・サブプログラムとは名前が多少異なる場合がありますが、同じサブプログラムを提供します。違いは、DBMS_SQLSETにはOracle Tuning Packが不要なことです。

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

170.1 DBMS_SQLSETの概要

このパッケージを使用してSQLチューニング・セットを管理します。

ノート:

すべてのDBMS_SQLSETサブプログラムは、DBMS_SQLTUNEパッケージに同等のものがあります。

SQLチューニング・セットは、SQL文とともに次の情報を格納します。

  • 解析スキーマ名、バインド値などの実行コンテキスト。

  • 平均経過時間、実行件数などの実行統計情報。

  • 実行計画(データベースでSQL文を実行するために行われる一連の操作)

  • 行ソース統計(計画で実行される各操作の処理行数など)。

SQLチューニング・セットを作成する場合は、次のソースを使用してSQL文にフィルタ処理やランキングを実行します。

この機能は、DBMS_SQLSETサブプログラムの要約に示されているすべてのサブプログラムを使用すると、簡単に実行できます。次に例を示します。

170.2 DBMS_SQLSETのセキュリティ・モデル

このパッケージは、PUBLICで使用可能で、独自のセキュリティ・チェックが実行されます。

SQLチューニング・セット・サブプログラムでは、ADMINISTER SQL TUNING SETまたはADMINISTER ANY SQL TUNING SET権限が必要です。ADMINISTER SQL TUNING SET権限を持つユーザーは、自身が所有するSQLチューニング・セットのみを作成および変更できます。ADMINISTER ANY SQL TUNING SET権限を持っている場合は、すべてのSQLチューニング・セット(他のユーザーが所有するものも含む)を操作できます。たとえば、CREATE_SQLSETプロシージャおよびファンクションを使用して、他のユーザーによって所有されるSQLチューニング・セットを作成できます。この場合、そのユーザーは、ADMINISTER SQL TUNING SET権限がなくてもSQLチューニング・セットを操作できます。

170.3 DBMS_SQLSETのデータ構造

DBMS_SQLTUNEパッケージのSELECT_*サブプログラムはSQLSET_ROWタイプのオブジェクトを戻します。

オブジェクト・タイプ

170.3.1 SQLSET_ROWオブジェクト・タイプ

SQLSET_ROWオブジェクトは、ユーザーのSQLチューニング・セットの内容をモデル化します。

論理的には、SQLチューニング・セットはSQLSET_ROWオブジェクトの集まりです。各SQLSET_ROWには、1つのSQL文とその実行コンテキスト、統計、バインドおよび計画が含まれます。各SELECT_*サブプログラムは、(sql_id、plan_hash_value)によって一意に識別されるSQLSET_ROWオブジェクトのコレクションとしてデータ・ソースをモデル化します。同様に、LOAD_SQLSETプロシージャは、行タイプがSQLSET_ROWのカーソルを入力時に使用し、ユーザーが要求するポリシーに従って各SQLSET_ROWを個別に処理します。

いくつかのサブプログラム・パッケージは、SQLチューニング・セットまたはデータ・ソースの内容に対して基本フィルタを使用できます。これらのフィルタは、SQLSET_ROW内に定義されている属性に基づいて表現されます。

構文

CREATE TYPE sqlset_row AS object (
  sql_id                   VARCHAR(13),
  force_matching_signature NUMBER,
  sql_text                 CLOB,
  object_list              sql_objects,
  bind_data                RAW(2000),
  parsing_schema_name      VARCHAR2(30),
  module                   VARCHAR2(48),
  action                   VARCHAR2(32),
  elapsed_time             NUMBER,
  cpu_time                 NUMBER,
  buffer_gets              NUMBER,
  disk_reads               NUMBER,
  direct_writes            NUMBER,
  rows_processed           NUMBER,
  fetches                  NUMBER,
  executions               NUMBER,
  end_of_fetch_count       NUMBER,
  optimizer_cost           NUMBER,
  optimizer_env            RAW(2000),
  priority                 NUMBER,
  command_type             NUMBER,
  first_load_time          VARCHAR2(19),
  stat_period              NUMBER,
  active_stat_period       NUMBER,
  other                    CLOB,
  plan_hash_value          NUMBER,
  sql_plan                 sql_plan_table_type,
  bind_list                sql_binds,
  con_dbid                 NUMBER,
  last_exec_start_time     VARCHAR2(19))

属性

表170-1 SQLSET_ROWの属性

属性 説明

sql_id

一意なSQL ID。

forcing_matching_signature

リテラル、ケースおよび空白を除くシグネチャ。

sql_text

SQL文の全テキスト。

object_list

現在、実装されていません。

bind_data

このSQLに対して取得されるバインド・データ。このパラメータの引数を指定し、bind_listの引数も指定することはできません(これらのパラメータは、同時には指定できません)。

parsing_schema_name

SQLが解析されるスキーマ。

module

SQLの最後のアプリケーション・モジュール。

action

SQLの最後のアプリケーション・アクション。

elapsed_time

このSQL文の経過時間の合計。

cpu_time

このSQL文のCPU時間の合計。

buffer_gets

バッファ取得回数の合計。

disk_reads

ディスク読取り回数の合計。

direct_writes

直接パス書込み回数の合計。

rows_processed

このSQL文による処理行数の合計。

fetches

フェッチ回数の合計。

executions

このSQL文の実行回数の合計。

end_of_fetch_count

SQL文のすべての行がフェッチされ、文が完全に実行された回数。

optimizer_cost

このSQLのオプティマイザ・コスト。

optimizer_env

このSQL文のオプティマイザ環境。

priority

ユーザー定義の優先順位(1、2、3)。

command_type

文のタイプ(INSERT、SELECTなど)。

first_load_time

親カーソルのロード時間。

stat_period

このSQL文の統計情報が収集された期間(秒)。

active_stat_period

SQL文がアクティブだった有効期間(秒)。

other

他の列(ユーザー定義の属性用)。

plan_hash_value

計画の計画ハッシュ値。

sql_plan

SQL文の実行計画。

bind_list

SQL文のユーザー指定バインドのリスト。ユーザー指定ワークロードに使用されます。このパラメータの引数を指定し、bind_dataの引数も指定することはできません(これらのパラメータは、同時には指定できません)。

con_dbid

PDBまたはCDBルートのDBID。

last_exec_start_time

最後に実行されたSQL文の開始時刻。

170.4 DBMS_SQLSETサブプログラムの要約

この表は、DBMS_SQLSETサブプログラムを示し、簡単に説明しています。

表170-2 DBMS_SQLSETパッケージのサブプログラム

サブプログラム 説明

ADD_REFERENCEファンクション

このプロシージャは、新しい参照を既存のSQLチューニング・セットに追加して、クライアントによるその使用状況を示します。

CAPTURE_CURSOR_CACHEプロシージャ

このプロシージャは、共有SQL領域からワークロードを取得して、SQLチューニング・セットに移入します。

CREATE_SQLSETプロシージャおよびファンクション

このプロシージャまたはファンクションは、データベース内にSQLチューニング・セット・オブジェクトを作成します。

CREATE_STGTABプロシージャ

このプロシージャは、SQLチューニング・セットのインポートおよびエクスポートに使用するステージング表を作成します。

DELETE_SQLSETプロシージャ

このプロシージャは、SQL文のセットをSQLチューニング・セットから削除します。

DROP_SQLSETプロシージャ

このプロシージャは、SQLチューニング・セットがアクティブでない場合はそれを削除します。

LOAD_SQLSETプロシージャ

このプロシージャは、選択したSQL文のセットをSQLチューニング・セットに入力します。このプロシージャを複数回コールすると、新しいSQL文の追加または既存の文の属性の置換を行うことができます。

PACK_STGTABプロシージャ

このプロシージャは、1つ以上のSQLチューニング・セットを、SYSスキーマ内のそれぞれの場所からCREATE_STGTABプロシージャによって作成されたステージング表にコピーします。

REMAP_STGTABプロシージャ

このプロシージャは、異なる値でアンパックできるように、ステージング表内のチューニング・セットの名前および所有者を変更します。

REMOVE_REFERENCEプロシージャ

このプロシージャは、SQLチューニング・セットを非アクティブ化し、このSQLチューニング・セットをクライアントが使用していないことを示します。

SELECT_CURSOR_CACHEファンクション

このファンクションは、ワークロード・リポジトリからSQL文を収集します。

SELECT_SQL_TRACEファンクション

この表ファンクションは、1つ以上のトレース・ファイルの内容を読み取り、検出したSQL文をsqlset_rowの形式で戻します。

SELECT_SQLPA_TASKファンクション

このファンクションは、SQLパフォーマンス・アナライザの比較タスクからSQL文を収集します。

SELECT_SQLSETファンクション

SQLチューニング・セットのコンテンツを読み取るテーブル・ファンクションです。

SELECT_WORKLOAD_REPOSITORYファンクション

このファンクションは、ワークロード・リポジトリからSQL文を収集します。

UNPACK_STGTABプロシージャ

このプロシージャは、1つ以上のSQLチューニング・セットを、ステージング表内のそれぞれの場所からSQLチューニング・セット・スキーマにコピーして、適切なSQLチューニング・セットを作成します。

UPDATE_SQLSETプロシージャ

このオーバーロードされたプロシージャは、SQLチューニング・セットのSQL文の選択されているフィールドを更新します。

170.4.1 ADD_REFERENCEファンクション

このプロシージャは、新しい参照を既存のSQLチューニング・セットに追加して、クライアントによるその使用状況を示します。

構文

DBMS_SQLSET.ADD_REFERENCE (
   sqlset_name  IN  VARCHAR2,
   description  IN  VARCHAR2 := NULL,
   sqlset_owner IN  VARCHAR2 :=NULL)
 RETURN NUMBER;

パラメータ

パラメータはDBMS_SQLTUNE.ADD_SQLSET_REFERENCEおよびDBMS_SQLSET.ADD_REFERENCEの場合と同じです。

表170-3 ADD_SQLSET_REFERENCEおよびADD_REFERENCEファンクションのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前を指定します。

description

SQLチューニング・セットの使用方法の説明(オプション)を入力します。

256文字を超える場合は切り捨てられます。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します。

戻り値

追加した参照の識別子。

使用上のノート

SQLチューニング・セットへの参照を追加すると、使用中にチューニング・セットが変更されなくなります。SQLチューニング・セットに対してSQLチューニング・アドバイザを起動すると自動的に参照が追加されるため、ADD_REFERENCEは、自動生成された参照が十分でない場合にのみ使用します。ADD_REFERENCEファンクションは、後でREMOVE_SQLSET_REFERENCEプロシージャに指定する参照IDを戻します。指定したSQLチューニング・セットへのすべての参照を確認するには、DBA_SQLSET_REFERENCESビューを問い合せます。

例

この例では、my_workloadという名前のSQLチューニング・セットへの参照を生成し、b_rid変数に格納します。 

VARIABLE b_rid NUMBER; 
EXEC :b_rid := DBMS_SQLSET.ADD_REFERENCE(sqlset_name => 'my_workload', description => 'my sts ref');

170.4.2 CAPTURE_CURSOR_CACHEプロシージャ

このプロシージャは、共有SQL領域からワークロードを取得して、SQLチューニング・セットに移入します。

このプロシージャは、一定期間にわたって複数回キャッシュをポーリングし、そこに格納されているワークロード・データを更新します。必要な期間に実行して、システム・ワークロード全体を取得できます。

構文

DBMS_SQLSET.CAPTURE_CURSOR_CACHE (
    sqlset_name         IN VARCHAR2, 
    time_limit          IN POSITIVE := 1800,
    repeat_interval     IN POSITIVE := 300,
    capture_option      IN VARCHAR2 := 'MERGE',
    capture_mode        IN NUMBER   := MODE_REPLACE_OLD_STATS,
    basic_filter        IN VARCHAR2 := NULL,
    sqlset_owner        IN VARCHAR2 := NULL,
    recursive_sql       IN VARCHAR2 := HAS_RECURSIVE_SQL);

パラメータ

パラメータはDBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSETおよびDBMS_SQLSET.CAPTURE_CURSOR_CACHEの場合と同じです。

表170-4 CAPTURE_CURSOR_CACHE_SQLSETおよびCAPTURE_CURSOR_CACHEプロシージャのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前を指定します

time_limit

実行する合計時間(秒)を定義します。

repeat_interval

サンプリングの実行間隔(秒)を定義します。

capture_option

新しい文の挿入、既存の文の更新またはその両方のいずれかを指定します。

値は、INSERT、UPDATEまたはMERGEです。値はload_sqlsetのload_optionの場合と同じです。

capture_mode

取得モード(UPDATEおよびMERGE取得オプション)を指定します。有効な値は、次のとおりです。

  • MODE_REPLACE_OLD_STATS — 実行回数がSQLチューニング・セット内に保存されている値より大きい場合は、統計が置換されます

  • MODE_ACCUMULATE_STATS — SQLに対して格納済の現在の値に、新しい値が加算されます。このモードでは、文が期限切れになっているかどうかが検出されるため、最終的な統計値は、その文が存在したすべてのカーソルの統計の合計になることに注意してください。

basic_filter

各サンプルの共有SQL領域に適用するフィルタを定義します。

basic_filterがコール元によって設定されていない場合、このサブプログラムはCREATE TABLE、INSERT、SELECT、UPDATE、DELETEおよびMERGEタイプの文のみを取得します。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します

recursive_sql

SQLチューニング・セットに再帰的SQLを含めるフィルタ(HAS_RECURSIVE_SQL)、または再帰的SQLを除外するフィルタ(NO_RECURSIVE_SQL)を定義します。

例

次の例では、30秒間にわたって取得が実行され、キャッシュが5秒に1回ポーリングされます。これによって、その時間内に実行されたすべての文が取得されます(その時間帯の前後は含まれません)。同じ文が重複して使用された場合は、格納されている文が新しい文に置換されます。

本番システムでの制限時間および繰返し間隔は、この例よりかなり大きく設定されることに注意してください。time_limitパラメータおよびrepeat_intervalパラメータは、使用するシステムでのワークロード時間および共有SQL領域の回転率プロパティに基づいて調整する必要があります。 

 
EXEC DBMS_SQLSET.CAPTURE_CURSOR_CACHE( -
                                        sqlset_name     => 'my_workload', -
                                        time_limit      =>  30, -
                                        repeat_interval =>  5);

次のコールでは、実行中に実行統計を累積します。このオプションでは、期限切れにまたがる場合でも、各カーソルの累積アクティビティの正確な状況が示されますが、前述の例よりもコストが大きくなります。

EXEC DBMS_SQLSET.CAPTURE_CURSOR_CACHE( -
                         sqlset_name     => 'my_workload', -
                         time_limit      => 30, -
                         repeat_interval => 5, -
                         capture_mode    => DBMS_SQLSET.MODE_ACCUMULATE_STATS);

次のコールでは、非常に低いコストで取得が実行されます。このコールでは、新しい文が挿入されるのみで、それらの文がSQLチューニング・セットに挿入された後、統計は更新されません。

EXEC DBMS_SQLSET.CAPTURE_CURSOR_CACHE( -
                                    sqlset_name     => 'my_workload', -
                                    time_limit      => 30, -
                                    repeat_interval => 5, -
                                    capture_option  => 'INSERT');

170.4.3 CREATE_SQLSETプロシージャおよびファンクション

このプロシージャまたはファンクションは、データベース内にSQLチューニング・セット・オブジェクトを作成します。

構文

DBMS_SQLSET.CREATE_SQLSET (
   sqlset_name  IN  VARCHAR2,
   description  IN  VARCHAR2 := NULL
   sqlset_owner IN  VARCHAR2 := NULL);

DBMS_SQLSET.CREATE_SQLSET (
   sqlset_name  IN  VARCHAR2 := NULL,
   description  IN  VARCHAR2 := NULL,
   sqlset_owner IN  VARCHAR2 := NULL)
 RETURN VARCHAR2;

パラメータ

表170-5 CREATE_SQLSETプロシージャのパラメータ

パラメータ 説明

sqlset_name

作成するSQLチューニング・セットの名前を指定します。この名前はファンクションに渡される名前です。ファンクションに名前が渡されない場合、ファンクションは自動名を生成します。

description

SQLチューニング・セットの説明(オプション)を入力します。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します。

戻り値

作成するSQLチューニング・セットの名前を指定します。この名前はファンクションに渡される名前です。ファンクションに名前が渡されない場合、ファンクションは自動名を生成します。

例

EXEC DBMS_SQLSET.CREATE_SQLSET(- 
  sqlset_name => 'my_workload', -
  description => 'complete application workload');

170.4.4 CREATE_STGTABプロシージャ

このプロシージャは、SQLチューニング・セットのインポートおよびエクスポートに使用するステージング表を作成します。

構文

DBMS_SQLSET.CREATE_STGTAB (
   table_name        IN VARCHAR2,
   schema_name       IN VARCHAR2 := NULL,
   tablespace_name   IN VARCHAR2 := NULL,
   db_version        IN NUMBER   := NULL);

パラメータ

表170-6 CREATE_STGTAB_SQLSETおよびCREATE_STGTABプロシージャのパラメータ

パラメータ 説明

table_name

作成する表の名前を指定します。この名前は大文字と小文字を区別します。

schema_name

作成する表を格納するスキーマを定義します。現行のスキーマの場合はNULLを定義します。この名前は大文字と小文字を区別します。

tablespace_name

ステージング表を格納する表領域を指定します。現行ユーザーのデフォルト表領域の場合はNULLを指定します。この名前は大文字と小文字を区別します。

db_version

ステージング表の形式を決定するデータベース・バージョンを指定します。

また、古いデータベース・バージョンのステージング表を作成して、STSを古いデータベース・バージョンにエクスポートすることもできます。以下の値のいずれかを使用します。

  • NULL (デフォルト) — 現行のデータベース・バージョンを指定します。

  • STS_STGTAB_10_2_VERSION — 10.2データベース・バージョンを指定します。

  • STS_STGTAB_11_1_VERSION — 11.1データベース・バージョンを指定します。

  • STS_STGTAB_11_2_VERSION — 11.2データベース・バージョンを指定します。

  • STS_STGTAB_12_1_VERSION — 12.1データベース・バージョンを指定します。

  • STS_STGTAB_12_2_VERSION — 12.2データベース・バージョンを指定します。

例

SQLチューニング・セットをパックし最終的にはエクスポートするためのステージング表を作成します。:

EXEC DBMS_SQLSET.CREATE_STGTAB(table_name => 'STGTAB_SQLSET');

Oracle Database 12cリリース1 (12.1.0.2)形式でSQLチューニング・セットをパックするステージング表を作成します。 

BEGIN 
  DBMS_SQLSET.CREATE_STGTAB(
      table_name => 'STGTAB_SQLSET_121'
  ,   db_version => DBMS_SQLSET.STS_STGTAB_12_1_VERSION );
END;

170.4.5 DELETE_SQLSETプロシージャ

このプロシージャは、SQL文のセットをSQLチューニング・セットから削除します。

構文

DBMS_SQLSET.DELETE_SQLSET (
   sqlset_name   IN  VARCHAR2,
   basic_filter  IN  VARCHAR2 := NULL,
   sqlset_owner  IN  VARCHAR2 := NULL);

パラメータ

表170-7 DELETE_SQLSETプロシージャのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前を指定します。

basic_filter

SQLチューニング・セットからSQLをフィルタするためのSQL述語を指定します。この基本フィルタはSQLチューニング・セットのコンテンツ上でwhere句として使用され、目的のSQLのサブセットをSQLチューニング・セットから選択します。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します。

例

-- Delete all statements in a sql tuning set.
EXEC DBMS_SQLSET.DELETE_SQLSET(sqlset_name   => 'my_workload');
 
-- Delete all statements in a sql tuning set which ran for less than a second
EXEC DBMS_SQLSET.DELETE_SQLSET(sqlset_name   => 'my_workload', -
                               basic_filter  => 'elapsed_time < 1000000');

170.4.6 DROP_SQLSETプロシージャ

このプロシージャは、SQLチューニング・セットがアクティブでない場合はそれを削除します。

構文

DBMS_SQLSET.DROP_SQLSET (
   sqlset_name   IN  VARCHAR2,
   sqlset_owner  IN  VARCHAR2 := NULL);

パラメータ

表170-8 DROP_SQLSETプロシージャのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前を指定します。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します。

使用上のノート

SQLチューニング・セットは、1つ以上のクライアントが参照している場合は削除できません。

例

-- Drop the sqlset.
EXEC DBMS_SQLSET.DROP_SQLSET ('my_workload');

170.4.7 LOAD_SQLSETプロシージャ

このプロシージャは、選択したSQL文のセットをSQLチューニング・セットに入力します。このプロシージャを複数回コールすると、新しいSQL文の追加または既存の文の属性の置換を行うことができます。

構文

DBMS_SQLSET.LOAD_SQLSET (
   sqlset_name       IN  VARCHAR2,
   populate_cursor   IN  sqlset_cursor,
   load_option       IN VARCHAR2 := 'INSERT', 
   update_option     IN VARCHAR2 := 'REPLACE', 
   update_condition  IN VARCHAR2 :=  NULL,
   update_attributes IN VARCHAR2 :=  NULL,
   ignore_null       IN BOOLEAN  :=  TRUE,
   commit_rows       IN POSITIVE :=  NULL,
   sqlset_owner      IN VARCHAR2 := NULL);

パラメータ

表170-9 LOAD_SQLSETプロシージャのパラメータ

パラメータ 説明

sqlset_name

ロードするSQLチューニング・セットの名前を指定します。

populate_cursor

ロードするSQLチューニング・セットへのカーソル参照を指定します。

load_option

SQLチューニング・セットにロードする文を指定します。可能な値は次のとおりです:

  • INSERT(デフォルト) — 新しい文のみを追加します。

  • UPDATE — 既存のSQL文を更新します。新しい文は無視されます。

  • MERGE — 新しい文の挿入および既存の文の情報の更新が行われます。

update_option

既存のSQL文の更新方法を指定します。

このパラメータは、load_optionがUPDATEまたはMERGEに指定されている場合のみ考慮されます。可能な値は次のとおりです:

  • REPLACE(デフォルト) — 新しい統計、バインド・リスト、オブジェクト・リストなどを使用して文を更新します。

  • ACCUMULATE — 可能な場合(elapsed_timeなどの統計などの場合)は、属性を組み合せ、可能でない場合(モジュール、アクションなどの場合)は、既存の値を指定した値に置き換えます。累積が可能なSQL文の属性には、elapsed_time、buffer_gets、direct_writes、disk_reads、row_processed、fetches、executions、end_of_fetch_count、stat_periodおよびactive_stat_periodがあります。

update_condition

更新を実行するタイミングを指定します。

このプロシージャは、指定した条件が満たされている場合にのみ更新を実行します。条件によって、データのソースまたは宛先のいずれかを参照できます。条件では、次の接頭辞を使用してソースまたは宛先の属性を参照する必要があります。

  • OLD — SQLチューニング・セット(宛先).の文の属性を参照します。

  • NEW — 入力文(ソース).の文属性を参照します。

update_attributes

マージまたは更新時に更新するSQL文の属性のリストを指定します。

可能な値は次のとおりです:

  • NULL(デフォルト) — 入力カーソルの内容(実行コンテキストは除く)を指定します。モジュール、アクションなどの実行コンテキストを除いた場合のALLと同じです。

  • BASIC — 統計およびバインドのみ。

  • TYPICAL — BASICとSQL計画(行ソース統計を除く)を指定します。オブジェクト参照リストは除かれます。

  • ALL — 実行コンテキスト(モジュール、アクションなど)も含むすべての属性を指定します。

  • 更新するカンマ区切りの属性名のリスト。

    • EXECUTION_CONTEXT

    • EXECUTION_STATISTICS

    • SQL_BINDS

    • SQL_PLAN

    • SQL_PLAN_STATISTICS (SQL_PLANと追加された行ソース統計に類似)

ignore_null

新しい値がNULLの場合に属性を更新するかどうかを指定します。

TRUEに設定すると、新しい値がNULLの場合、プロシージャは属性を更新しません。つまり、意図しないかぎり、NULL値で上書きされることはありません。

commit_rows

DML後に文をコミットするかどうかを指定します。

値が指定されている場合、指定された数の文が挿入されるたびにロードがコミットされます。NULLを指定すると、操作の最後に1回のみロードがコミットされます。

この引数に値を指定すると、SQLチューニング・セットのロード操作の進行状況をDBA_SQLSETビューで監視できます。STATEMENT_COUNTの値は、新しいSQL文がロードされるたびに増加します。

sqlset_owner

SQLチューニング・セットの所有者または現行のスキーマ所有者を定義します。現行の所有者の場合はNULLを定義します。

例外

  • sqlset_nameが無効の場合、対応するSQLチューニング・セットが存在しない場合、またはpopulate_cursorが正しくないために実行できない場合、このプロシージャはエラーを戻します。

  • 指定したフィルタが無効な場合も例外が発生します。フィルタは、解析できない場合(sqlset_rowに存在しない属性を参照している場合など)またはユーザーの権限に違反している場合に無効になる可能性があります。

使用上のノート

入力populate_cursorの行は、SQLSET_ROWタイプである必要があります。

例

この例では、SQLチューニング・セットを作成し、そのチューニング・セットに経過時間が5秒以上のすべての共有SQL領域文を入力します(SYSスキーマに属する文は除きます)。SQL文のすべての属性を選択し、デフォルト・モードを使用してチューニング・セットにロードします。SQLチューニング・セットが空であるため、デフォルト・モードでは新しい文のみがロードされます。 

-- create the tuning set
EXEC DBMS_SQLSET.CREATE_SQLSET('my_workload');

-- populate the tuning set from the shared SQL area
DECLARE
 cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
     FROM table(
       DBMS_SQLSET.SELECT_CURSOR_CACHE(
         'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000',
          NULL, NULL, NULL, NULL, 1, NULL,
         'ALL')) P;
 
DBMS_SQLSET.LOAD_SQLSET(sqlset_name     => 'my_workload',
                        populate_cursor => cur);
 
END;
/

ここで、ワークロード・リポジトリ(AWR)に格納されている内容でこの情報を増補します。現在キャッシュに入っているカーソルは、スナップショットが取られたことで期限切れしているとみなされるため、チューニング・セットにupdate_optionとして'ACCUMULATE'を入力します。

AWRで取得されるすべての文が重要とみなされるため、elapsed_timeフィルタは省略します。ただし、再帰的SQLを回避するために、SYS解析のカーソルは破棄します。 

DECLARE
  cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
        DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(1,2,
                                                'parsing_schema_name <> ''SYS''',
                                                NULL, NULL,NULL,NULL,
                                                1,
                                                NULL,
                                                'ALL')) P;

  DBMS_SQLSET.LOAD_SQLSET(sqlset_name     => 'my_workload',
                          populate_cursor => cur,
                          load_option     => 'MERGE',
                          update_option   => 'ACCUMULATE');
END;

次の例は、新しい文のみをワークロード・リポジトリから挿入し、SQLチューニング・セットにすでに存在する文はスキップする単純なロード操作です。LOAD_SQLSETプロシージャのload_option引数のデフォルト値は'INSERT'であることに注意してください。 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
  SELECT VALUE(P)
  FROM table(DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(1,2)) P;
  DBMS_SQLSET.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur);
END;
/

次の例は、UPDATEオプションを指定したロード操作を示します。これによって、SQLチューニング・セットにすでに存在する文は更新されますが、新しい文は追加されません。デフォルトでは、古い統計が新しい値に置き換えられます。 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLSET.SELECT_CURSOR_CACHE) P;
  
  DBMS_SQLSET.LOAD_SQLSET(sqlset_name     => 'my_workload',
                          populate_cursor => cur,
                          load_option     => 'UPDATE');
END;
/

170.4.8 PACK_STGTABプロシージャ

このプロシージャは、1つ以上のSQLチューニング・セットを、SYSスキーマ内のそれぞれの場所からCREATE_STGTABプロシージャによって作成されたステージング表にコピーします。

構文

DBMS_SQLSET.PACK_STGTAB (
   sqlset_name          IN VARCHAR2,
   sqlset_owner         IN VARCHAR2 := NULL,
   staging_table_name   IN VARCHAR2,
   staging_schema_owner IN VARCHAR2 := NULL,
   db_version           IN NUMBER   := NULL);

パラメータ

パラメータはDBMS_SQLTUNE.PACK_STGTAB_SQLSETおよびDBMS_SQLSET.PACK_STGTABプロシージャの場合と同じです。

表170-10 PACK_STGTAB_SQLSETおよびPACK_STGTABプロシージャのパラメータ

パラメータ 説明

sqlset_name

パックするSQLチューニング・セットの名前を指定します。この名前は大文字と小文字を区別します。ワイルドカード文字(%)を使用できます。

sqlset_owner

パックするSQLチューニング・セットのカテゴリを指定します。この名前は大文字と小文字を区別します。ワイルドカード文字(%)を使用できます。

staging_table_name

使用する表の名前を指定します。値では大文字と小文字が区別されます。

staging_schema_owner

表が存在するスキーマ、または現行のスキーマの場合はNULLを指定します。値では大文字と小文字が区別されます。

db_version

ステージング表の形式を決定するデータベース・バージョンを指定します。また、古いデータベース・バージョンのステージング表を作成して、STSを古いデータベース・バージョンにエクスポートすることもできます。次のいずれかの値を使用します。

  • NULL (デフォルト) — 現行のデータベース・バージョンを指定します。

  • STS_STGTAB_10_2_VERSION — 10.2データベース・バージョンを指定します。

  • STS_STGTAB_11_1_VERSION — 11.1データベース・バージョンを指定します。

  • STS_STGTAB_11_2_VERSION — 11.2データベース・バージョンを指定します。

使用上のノート

  • 複数のSQLチューニング・セットを移動する場合は、このプロシージャを複数回コールします。次に、任意の方法(データベース・リンク、Oracle Data Pumpなど)を使用して移入済のステージング表を宛先データベースに移動し、宛先データベースでSQLチューニング・セットをアンパックできます。

  • このファンクションは、各SQLチューニング・セットをパックした後でCOMMITを発行します。実行途中でエラーが発生した場合は、ステージング表の行を削除することによりステージング表をクリアしてください。

例

データベース上のすべてのSQLチューニング・セットをステージング表に入れます。

BEGIN 
  DBMS_SQLSET.PACK_STGTAB(
      sqlset_name        => '%'
  ,   sqlset_owner       => '%'
  ,   staging_table_name => 'STGTAB_SQLSET');
END;

現行のユーザーが所有するSQLチューニング・セットのみをステージング表に入れます。:

BEGIN 
  DBMS_SQLSET.PACK_STGTAB(
      sqlset_name        => '%'
  ,   staging_table_name => 'STGTAB_SQLSET');
END;

特定のSQLチューニング・セットをパックします。:

BEGIN 
  DBMS_SQLSET.PACK_STGTAB(
      sqlset_name         => 'my_workload'
  ,   staging_table_name  => 'STGTAB_SQLSET');
END;

2つ目のSQLチューニング・セットをパックします。:

BEGIN 
  DBMS_SQLSET.PACK_STGTAB(
      sqlset_name         => 'workload_subset'
  ,   staging_table_name  => 'STGTAB_SQLSET');
END;

Oracle Database 11gリリース2 (11.2)に作成したステージング表stgtab_sqlsetに、STS my_workload_subsetをパックします。 

BEGIN
  DBMS_SQLSET.PACK_STGTAB(
      sqlset_name          => 'workload_subset'
  ,   staging_table_name   => 'STGTAB_SQLSET'
  ,   db_version           => DBMS_SQLSET.STS_STGTAB_11_2_VERSION);
END;

170.4.9 REMAP_STGTABプロシージャ

このプロシージャは、異なる値でアンパックできるように、ステージング表内のチューニング・セットの名前および所有者を変更します。

構文

DBMS_SQLSET.REMAP_STGTAB (
   old_sqlset_name        IN VARCHAR2,
   old_sqlset_owner       IN VARCHAR2 := NULL,
   new_sqlset_name        IN VARCHAR2 := NULL,
   new_sqlset_owner       IN VARCHAR2 := NULL,
   staging_table_name     IN VARCHAR2,
   staging_schema_owner   IN VARCHAR2 := NULL
   old_con_dbid           IN NUMBER   := NULL,
   new_con_dbid           IN NUMBER   := NULL);
);

パラメータ

パラメータはDBMS_SQLTUNE.REMAP_STGTAB_SQLSETおよびDBMS_SQLSET.REMAP_SQLSETプロシージャの場合と同じです。

表170-11 REMAP_STGTAB_SQLSETおよびREMAP_SQLSETプロシージャのパラメータ

パラメータ 説明

old_sqlset_name

再マップ操作の対象となるチューニング・セットの名前を指定します。ワイルドカード文字(%)は使用できません。

old_sqlset_owner

再マップ操作の対象となるチューニング・セット所有者の名前を指定します。現行のスキーマ所有者の場合はNULL。

new_sqlset_name

チューニング・セットの新しい名前を指定します。チューニング・セット名を変更しない場合はNULLを指定します。

new_sqlset_owner

チューニング・セットの新しい所有者を指定します。所有者を変更しない場合はNULLを指定します。

staging_table_name

再マップ操作を行う表の名前を指定します。値では大文字と小文字が区別されます。

staging_schema_owner

ステージング表の所有者の名前を指定します。現行のスキーマ所有者の場合はNULLを指定します。値では大文字と小文字が区別されます。

old_con_dbid

新しいコンテナDBIDに再マップされる古いコンテナDBIDを指定します。

同じコンテナDBIDを使用するにはNULLを指定します。再マップを成功させるには、old_con_dbidとnew_con_dbidの両方を指定する必要があります。

new_con_dbid

古いコンテナDBIDと置き換える新しいコンテナDBIDを指定します。

同じコンテナDBIDを使用するにはNULLを指定します。再マップを成功させるには、old_con_dbidとnew_con_dbidの両方を指定する必要があります。

使用上のノート

複数のチューニング・セットの名前または所有者を再マップするには、このプロシージャを複数回コールします。このプロシージャで処理できるチューニング・セットは、1回のコールで1つのみです。

例

-- Change the name of an STS in the staging table before unpacking it.
BEGIN 
  DBMS_SQLSET.REMAP_STGTAB(
      old_sqlset_name    => 'my_workload'                                      
  ,   old_sqlset_owner   => 'SH'
  ,   new_sqlset_name    => 'imp_workload'                                      
  ,   staging_table_name => 'STGTAB_SQLSET');
 
-- Change the owner of an STS in the staging table before unpacking it.
  DBMS_SQLSET.REMAP_STGTAB(
      old_sqlset_name    => 'imp_workload'
  ,   old_sqlset_owner   => 'SH'
  ,   new_sqlset_owner   => 'SYS'
  ,   staging_table_name => 'STGTAB_SQLSET');
END;

170.4.10 REMOVE_REFERENCEプロシージャ

このプロシージャは、SQLチューニング・セットを非アクティブ化し、このSQLチューニング・セットをクライアントが使用していないことを示します。

構文

DBMS_SQLSET.REMOVE_REFERENCE (
   sqlset_name   IN  VARCHAR2,
   reference_id  IN  NUMBER,
   sqlset_owner  IN  VARCHAR2 := NULL,
   force_remove  IN  NUMBER   := 0);

パラメータ

パラメータはDBMS_SQLTUNE.REMOVE_SQLSET_REFERENCEおよびDBMS_SQLSET.REMOVE_REFERENCEプロシージャの場合と同じです。

表170-12 REMOVE_SQLSET_REFERENCEおよびREMOVE_REFERENCEプロシージャのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前を指定します。

reference_id

削除する参照の識別子を指定します。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します。

force_remove

他のユーザーの参照を削除できるか(1)またはできないか(0)を指定します。

このパラメータを1に設定した場合、ユーザーにADMINISTER ANY SQL TUNING SET権限がある場合にのみ設定が有効になります。それ以外の場合、ユーザーが所有する参照のみが削除されます。

例

特定のSQLチューニング・セットの使用を終了した後、そのSQLチューニング・セットに関する参照を削除して、再度書込み可能にすることができます。次の例では、my_workloadへの参照を削除します。 

EXEC DBMS_SQLSET.REMOVE_REFERENCE(sqlset_name   => 'my_workload', -
                                  reference_id  => :rid);

DBA_SQLSET_REFERENCESビューを使用して、特定のSQLチューニング・セットへのすべての参照を確認します。

170.4.11 SELECT_CURSOR_CACHEファンクション

このファンクションは、共有SQL領域からSQL文を収集します。

構文

DBMS_SQLSET.SELECT_CURSOR_CACHE (
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL,
  attribute_list      IN   VARCHAR2 := 'TYPICAL',
  recursive_sql       IN   VARCHAR2 := HAS_RECURSIVE_SQL)
 RETURN sys.sqlset PIPELINED;

パラメータ

表170-13 SELECT_CURSOR_CACHEファンクションのパラメータ

パラメータ 説明

basic_filter

SQLSET_ROWの属性で定義された共有SQL領域からSQLをフィルタするためのSQL述語を指定します。

basic_filterがコール元によって設定されていない場合、このサブプログラムはCREATE TABLE、INSERT、SELECT、UPDATE、DELETEおよびMERGEタイプの文のみを取得します。

object_filter

現在はサポートされていません。

ranking_measure(n)

選択したSQLにORDER BY句を定義します。

result_percentage

提供されたランキング・メジャーに応じて先頭のn%を選択するフィルタを指定します。

この値は、1つのランキング・メジャーが指定されている場合にのみ適用されます。

result_limit

ランキング・メジャーでランク付けしたフィルタ済のソースの先頭から数件取得されるSQL文を定義します。

attribute_list

結果として戻すSQL文の属性リストを指定します。

使用可能な値は次のとおりです。

  • TYPICAL — BASIC + SQL計画(行ソース統計を除く)を指定します。オブジェクト参照リストは除かれます(デフォルト)。

  • BASIC — 計画を除くすべての属性(実行統計、バインドなど)を指定します。実行コンテキストは必ず結果に含まれます。

  • ALL — すべての属性を指定します。

  • 属性名のカンマ区切りのリスト。

    この値は、SQL属性のサブセットのみを戻します。

    • EXECUTION_STATISTICS

    • BIND_LIST

    • OBJECT_LIST

    • SQL_PLAN

    • SQL_PLAN_STATISTICS — SQL_PLAN + 行ソース統計に類似

recursive_sql

SQLチューニング・セットに再帰的SQLを含めるフィルタ(HAS_RECURSIVE_SQL)、または再帰的SQLを除外するフィルタ(NO_RECURSIVE_SQL)を指定します。

戻り値

このファンクションは、各データ・ソースで検出されるSQL_IDまたはPLAN_HASH_VALUEの組合せごとに1つのSQLSET_ROWを戻します。

使用上のノート

  • このファンクションで提供されるフィルタは、現行のユーザーが実行するSQLの一部として評価されます。したがって、それらのフィルタは、ユーザーのセキュリティ権限で実行され、ユーザーがアクセスできるすべての構成メンバーおよび副問合せを含んでいますが、それ以外のものは含んでいません。

  • ユーザーには、共有SQL領域ビューの権限が必要です。

例170-1 500回以上のバッファ取得がある文

この問合せは、500回のバッファ取得がある文のSQL IDとSQLテキストを取得します。

SELECT SQL_ID, SQL_TEXT 
FROM   TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE('buffer_gets > 500')) 
ORDER BY sql_id;

例170-2 文に関するすべての情報

次の問合せは、SQL ID 4rm4183czbs7jのSQL文に関するすべての情報を取得します。 

SELECT * FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));

例170-3 SQL文の複数の計画

データ・ソースには、各SQL文に対する複数の計画が格納されることがあります。SELECT_CURSOR_CACHEファンクションの出力は、SQL IDと計画ハッシュ値によって一意に識別されるSQL行セット・オブジェクトです。この例では、SQL ID ay1m3ssvtrh24の文の計画ハッシュ値を問い合せます。 

SELECT sql_id, plan_hash_value
FROM table(DBMS_SQLSET.select_cursor_cache('sql_id = ''ay1m3ssvtrh24'''))
ORDER BY sql_id, plan_hash_value;

例170-4 共有SQL領域内のすべての文の処理

この例では、共有SQL領域内のすべての文を処理します。

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) 
    FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE) p;
 
  -- Process each statement in cursor (or pass cursor to load_sqlset).
 
  CLOSE cur;
END;
/

例170-5 SYSによって解析されない文の処理

この例では、SYSスキーマで解析されないすべての文を処理します。 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur for
    SELECT VALUE(p) 
    FROM TABLE(
     DBMS_SQLSET.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) p;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
end;
/

例170-6 アプリケーション・モジュールおよびアクションからのすべての文

この例では、指定したアプリケーション・モジュールおよびアクションからのすべての文を処理します。

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) 
    FROM TABLE(
      DBMS_SQLSET.SELECT_CURSOR_CACHE(
         'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) p;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;/

例170-7 経過時間が5秒以上のすべての文

この例では、実行時間が5秒以上のすべての文を処理します。

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(DBMS_SQLSET.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;
/

例170-8 APPSスキーマで解析された文

この例では、APPSスキーマで解析され、100回を超えるバッファ取得があったすべてのSQL文を処理します。 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) 
    FROM TABLE(
      DBMS_SQLSET.SELECT_CURSOR_CACHE(
        'buffer_gets > 100 and parsing_schema_name = ''APPS'''))p;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;
/

例170-9 計画およびSQL文

この例では、5秒を超えるすべてのSQL文を処理します。それらの文の計画も選択します。パフォーマンス上の理由から、この例では実行統計とSQLバインドを選択します。sqlset_rowのSQL_PLAN属性はNULLです。 

-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL) 
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) 
    FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE(
      basic_filter      => 'elapsed_time > 5000000', 
      object_filter     => NULL, 
      ranking_measure1  => NULL, 
      ranking_measure2  => NULL, 
      ranking_measure3  => NULL, 
      result_percentage => 1, 
      result_limit      => NULL,
      attribute_list    => 'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN',
      recursive_sql     => HAS_RECURSIVE_SQL)) p;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;/

例170-10 経過時間によって順序付けられた上位100個の文

この例では、経過時間によって順序付けられた共有SQL領域内の上位100個の文を選択します。

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) 
    FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE(
      basic_filter      => NULL,
      object_filter     => NULL,
      ranking_measure_1 => 'ELAPSED_TIME', 
      ranking_measure_2 => NULL, 
      ranking_measure_3 => NULL,
      result_percentage => 1,
      result_limit      => 100,
      attribute_list    => 'TYPICAL',
      recursive_sql     => HAS_RECURSIVE_SQL))) p;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;
/

例170-11 大部分のバッファ取得を行う文

この例では、共有SQL領域内のバッファ取得の90%を累積的に占める文を処理します。すべての文のバッファ取得を合計すると、共有SQL領域に現在含まれているすべての文の合計の約90%になります。

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(DBMS_SQLSET.SELECT_CURSOR_CACHE(
      basic_filter      => NULL,
      object_filter     => NULL,
      ranking_measure_1 => 'BUFFER_GETS', 
      ranking_measure_2 => NULL, 
      ranking_measure_3 => NULL,
      result_percentage => .9,
      result_limit      => NULL,
      attribute_list    => 'TYPICAL',
      recursive_sql     => HAS_RECURSIVE_SQL))) p;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
END;
/

170.4.12 SELECT_SQL_TRACEファンクション

この表ファンクションは、1つ以上のトレース・ファイルの内容を読み取り、検出したSQL文をsqlset_rowの形式で戻します。

構文

DBMS_SQLSET.SELECT_SQL_TRACE (
  directory              IN VARCHAR2,
  file_name              IN VARCHAR2 := NULL,
  mapping_table_name     IN VARCHAR2 := NULL,
  mapping_table_owner    IN VARCHAR2 := NULL,,
  select_mode            IN POSITIVE := SINGLE_EXECUTION,
  options                IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
  pattern_start          IN VARCHAR2 := NULL,
  pattern_end            IN VARCHAR2 := NULL,
  result_limit           IN POSITIVE := NULL)
 RETURN sys.sqlset PIPELINED;

パラメータ

表170-14 SELECT_SQL_TRACEファンクションのパラメータ

パラメータ 説明

directory

トレース・ファイルを含むディレクトリ・オブジェクトを定義します。このフィールドは必須です。

file_name

トレース・ファイルの名前の全体または一部を指定します。

NULLの場合は、ファンクションは指定した場所またはパスにある現行のファイルまたは最新のファイルを使用します。トレース・ファイル名の一致検索については、ワイルドカード'%'がサポートされています。

mapping_table_name

マッピング表名を指定します。

マッピング表名は大/小文字が区別されないことに注意してください。マッピング表名がNULLの場合は、ファンクションは現行のデータベースにあるマッピングを使用します。

mapping_table_owner

マッピング表の所有者を指定します。

NULLの場合、ファンクションは現行ユーザーを使用します。

select_mode

トレースからSQLを選択するためのモードを指定します。

使用可能な値は次のとおりです。

  • SINGLE_EXECUTION — 1つのSQL実行を戻します。これはデフォルトです。

  • ALL_EXECUTIONS — すべての実行を戻します。

options

戻されるSQL文のタイプを指定します。

  • LIMITED_COMMAND_TYPE — コマンド・タイプがCREATE、INSERT、SELECT、UPDATE、DELETE、MERGEのSQL文を戻します。この値がデフォルトです。

  • ALL_COMMAND_TYPE — すべてのコマンド・タイプのSQL文を戻します。

pattern_start

対象とするトレース・ファイル・セクションの区切りパターンを指定します。現在使用できません。

pattern_end

処理するトレース・ファイル・セクションの終了区切りパターンを指定します。現在使用できません。

result_limit

除外されたソースの上位SQLを指定します。NULLの場合は、デフォルトのMAXSB4が設定されます。

戻り値

このファンクションは、SQLSET_ROWオブジェクトを戻します。

使用上のノート

システム・ディレクトリのディレクトリ・オブジェクトを作成する機能により、セキュリティ上の問題が発生する可能性があります。たとえば、CDBですべてのコンテナがトレース・ファイルを同じディレクトリに書き込みます。このディレクトリに対するSELECT権限を持つローカル・ユーザーは、任意のコンテナに属するトレース・ファイルの内容を読み取ることができます。

このタイプの不正アクセスを防ぐには、ファイルをデフォルトのSQLトレース・ディレクトリから別のディレクトリにコピーしてから、ディレクトリ・オブジェクトを作成します。CREATE PLUGGABLE DATABASE文のPATH_PREFIX句を使用して、PDBと関連付けられているすべてのディレクトリ・オブジェクト・パスが、指定したディレクトリまたはそのサブディレクトリに制限されるようにします。

例

次のコードは、いくつかのSQL文のSQLトレースを使用可能にして、SQLチューニング・セットに結果をロードする方法を示しています。

-- turn on the SQL trace in the capture database
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'

-- run sql statements
SELECT 1 FROM DUAL;
SELECT COUNT(*) FROM dba_tables WHERE table_name = :mytab;
 
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
 
-- create mapping table from the capture database
CREATE TABLE mapping AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
   FROM dba_objects
   WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
                             'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
                             'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
                             'LOB', 'OPERATOR', 'PACKAGE',
                             'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
                             'RESOURCE PLAN', 'TRIGGER', 'TYPE',
                             'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
   FROM dba_users;
 
-- create the directory object where the SQL traces are stored
CREATE DIRECTORY SQL_TRACE_DIR as '/home/foo/trace';

-- create the STS
EXEC DBMS_SQLSET.CREATE_SQLSET('my_sts', 'test purpose');
 
-- load the SQL statements into STS from SQL TRACE
DECLARE
   cur sys_refcursor;
BEGIN
   OPEN cur FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLSET.SELECT_SQL_TRACE(
           directory=>'SQL_TRACE_DIR',
           file_name=>'%trc',
           mapping_table_name=>'mapping')) p;
   DBMS_SQLSET.LOAD_SQLSET('my_sts', cur);
   CLOSE cur;
END;
/

参照:

PATH_PREFIX句についてさらに学習するには、Oracle Database SQL言語リファレンスを参照してください。

170.4.13 SELECT_SQLPA_TASKファンクション

このファンクションは、SQLパフォーマンス・アナライザの比較タスクからSQL文を収集します。

参照:

SELECT_SQLPA_TASKの例については、『Oracle Database Testingガイド』を参照してください。

構文

DBMS_SQLSET.SELECT_SQLPA_TASK(
    task_name         IN VARCHAR2,
    task_owner        IN VARCHAR2 := NULL,
    execution_name    IN VARCHAR2 := NULL,
    level_filter      IN VARCHAR2 := 'REGRESSED',
    basic_filter      IN VARCHAR2 := NULL,
    object_filter     IN VARCHAR2 := NULL,
    attribute_list    IN VARCHAR2 := 'TYPICAL')
RETURN sys.sqlset PIPELINED;

パラメータ

表170-15 SELECT_SQLPA_TASKファンクションのパラメータ

パラメータ 説明

task_name

SQLパフォーマンス・アナライザ・タスクの名前を指定します。

task_owner

SQLパフォーマンス・アナライザ・タスクの所有者を指定します。NULLの場合、現行のユーザーとみなされます。

execution_name

指定したフィルタの適用元になるSQLパフォーマンス・アナライザ・タスクの実行の名前(タイプCOMPARE PERFORMANCE)を指定します。NULLの場合、最新のCOMPARE PERFORMANCEの実行とみなされます。

level_filter

SQL文のどのサブセットを含めるかを指定します。DBMS_SQLPA.REPORT_ANALYSIS_TASK.LEVELと同じ形式で、一部の文字列が削除されています。

  • IMPROVED: 改善されたSQLのみが含まれます。

  • REGRESSED: 低下したSQLのみが含まれます(デフォルト)。

  • CHANGED: パフォーマンスが変更されたSQLのみが含まれます。

  • UNCHANGED: パフォーマンスが変更されていないSQLのみが含まれます。

  • CHANGED_PLANS: 計画が変更されたSQLのみが含まれます。

  • UNCHANGED_PLANS: 計画が変更されていないSQLのみが含まれます。

  • ERRORS: エラーが発生したSQLのみが含まれます。

  • MISSING_SQL: (STS全体で)欠落しているSQL文のみが含まれます。

  • NEW_SQL: (STS全体で)新規のSQL文のみが含まれます。

basic_filter

レベル・フィルタに加えてSQLをフィルタするためのSQL述語を指定します。

object_filter

現在はサポートされていません。

attribute_list

結果として戻すSQL文の属性を定義します。

使用可能な値は次のとおりです。

  • TYPICAL — BASIC + SQL計画(行ソース統計を除く)を戻します。オブジェクト参照リストは除かれます。これはデフォルトです。

  • BASIC — 計画を除くすべての属性(実行統計、バインドなど)を戻します。実行コンテキストは必ず結果に含まれます。

  • ALL — すべての属性を戻します。

  • 属性名のカンマ区切りリスト。これによって、SQL属性のサブセットのみを戻すことができます。設定可能な値は、EXECUTION_STATISTICS、SQL_BINDS、SQL_PLAN_STATISTICS (SQL_PLAN + 行ソース統計に類似)です。

戻り値

このファンクションは、SQLチューニング・セット・オブジェクトを戻します。

使用上のノート

たとえば、このファンクションを使用して、SQLパフォーマンス・アナライザ(SPA)の試用中に低下したSQL文のサブセットを含むSQLチューニング・セットを作成できます。また、他の任意のフィルタを指定することもできます。

170.4.14 SELECT_SQLSETファンクション

SQLチューニング・セットのコンテンツを読み取るテーブル・ファンクションです。

構文

DBMS_SQLSET.SELECT_SQLSET (
  sqlset_name         IN   VARCHAR2,
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL)
  attribute_list      IN   VARCHAR2 := 'TYPICAL',
  plan_filter         IN   VARCHAR2 := NULL,
  sqlset_owner        IN   VARCHAR2 := NULL,
  recursive_sql       IN   VARCHAR2 := HAS_RECURSIVE_SQL)
 RETURN sys.sqlset PIPELINED;

パラメータ

表170-16 SELECT_SQLSETファンクションのパラメータ

パラメータ 説明

sqlset_name

問い合せるSQLチューニング・セットの名前を指定します。

basic_filter

SQLSET_ROWの属性に対して定義されたSQLチューニング・セットからSQLをフィルタするためのSQL述語を指定します。

object_filter

現在はサポートされていません。

ranking_measure(n)

選択したSQLにORDER BY句を指定します。

result_percentage

提供されたランキング・メジャーに応じて先頭のn%を選択するフィルタを指定します。

このパラメータは、1つのランキング・メジャーが指定されている場合にのみ適用されます。

result_limit

ランキング・メジャーでランク付けしたフィルタ済のソースの先頭から数件取得されるSQL文。

attribute_list

結果として戻すSQL文の属性を定義します。

可能な値は次のとおりです:

  • BASIC — 計画を除くすべての属性(実行統計、バインドなど)を戻します。実行コンテキストは結果に含まれます。

  • TYPICAL — BASIC + SQL計画(行ソース統計を除く)を戻します。オブジェクト参照リストは除かれます。これはデフォルトです。

  • ALL — すべての属性を戻します。

  • 属性名のカンマ区切りのリスト。この値により、ファンクションはSQL属性のサブセットのみを戻すことができます。

    • EXECUTION_STATISTICS

    • SQL_BINDS

    • SQL_PLAN_STATISTICS (SQL_PLAN + 行ソース統計に類似)

plan_filter

計画フィルタを指定します。

このパラメータにより、文に複数の計画がある場合に1つの計画を選択できます。使用可能な値は次のとおりです。

  • LAST_GENERATED — タイムスタンプが最も新しい計画を戻します。

  • FIRST_GENERATED — タイムスタンプが最も古い計画を戻します。

  • LAST_LOADED — FIRST_LOAD_TIME統計情報が最も新しい計画を戻します。

  • FIRST_LOADED — FIRST_LOAD_TIME統計情報が最も古い計画を戻します。

  • MAX_ELAPSED TIME — 経過時間が最も長い計画を戻します。

  • MAX_BUFFER_GETS — バッファ取得回数が最も多い計画を戻します。

  • MAX_DISK_READS — ディスク読取り回数が最も多い計画を戻します。

  • MAX_DIRECT_WRITES — 直接書込み回数が最も多い計画を戻します。

  • MAX_OPTIMIZER_COST — オプティマイザ・コスト値が最も大きい計画を戻します。

sqlset_owner

SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合はNULLを指定します。

recursive_sql

SQLチューニング・セットに再帰的SQLを含めるフィルタ(HAS_RECURSIVE_SQL)、または再帰的SQLを除外するフィルタ(NO_RECURSIVE_SQL)を指定します。

戻り値

このファンクションは、各データ・ソースで検出されるSQL_IDまたはPLAN_HASH_VALUEの組合せごとに1つのSQLSET_ROWを戻します。

使用上のノート

このファンクションで提供されるフィルタは、現行のユーザーが実行するSQLの一部として評価されます。したがって、それらのフィルタは、ユーザーのセキュリティ権限で実行され、ユーザーがアクセスできるすべての構成メンバーおよび副問合せを含んでいますが、それ以外のものは含んでいません。

例

-- select from a sql tuning set
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(DBMS_SQLSET.SELECT_SQLSET('my_workload')) P;
 
  -- Process each statement (or pass cursor to load_sqlset) 

  CLOSE cur;
END;
/

170.4.15 SELECT_WORKLOAD_REPOSITORYファンクション

このファンクションは、ワークロード・リポジトリからSQL文を収集します。

オーバーロードされたフォームにより、次のソースからSQL文を収集できます。

  • begin_snapとend_snapの間のすべてのスナップショット

  • ワークロード・リポジトリのベースライン

構文

DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY (
  begin_snap        IN NUMBER,
  end_snap          IN NUMBER,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL,
  attribute_list    IN VARCHAR2 := 'TYPICAL',
  recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL,
  dbid              IN NUMBER   := NULL)
 RETURN sys.sqlset PIPELINED;

DBMS_SQLSET.SELECT_WORKLOAD REPOSITORY (
  baseline_name     IN VARCHAR2,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL,
  attribute_list    IN VARCHAR2 := 'TYPICAL',
  recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL,
  dbid              IN NUMBER   := NULL)
 RETURN sys.sqlset PIPELINED;

パラメータ

表170-17 SELECT_WORKLOAD_REPOSITORYファンクションのパラメータ

パラメータ 説明

begin_snap

開始AWRスナップショット(この値を含めない)を定義します。

end_snap

終了AWRスナップショット(この値を含む)を定義します。

baseline_name

AWRベースライン時間の名前を指定します。

basic_filter

ワークロード・リポジトリからSQLをフィルタするためのSQL述語を指定します。フィルタはSQLSET_ROWの属性に対して定義されます。

basic_filterがコール元によって設定されていない場合、このサブプログラムはCREATE TABLE、INSERT、SELECT、UPDATE、DELETEおよびMERGEタイプの文のみを取得します。

object_filter

現在はサポートされていません。

ranking_measure(n)

選択したSQLにORDER BY句を定義します。

result_percentage

提供されたランキング・メジャーに応じて先頭のn%を選択するフィルタを指定します。このパーセンテージは、1つのランキング・メジャーが指定されている場合にのみ適用されます。

result_limit

提供されたランキング・メジャーに応じてソースの先頭から数件取得されるSQL文を指定します。

attribute_list

結果として戻すSQL文の属性を指定します。可能な値は次のとおりです:

  • TYPICAL — BASIC + SQL計画(行ソース統計を除く)を戻します。オブジェクト参照リストは除かれます。これはデフォルトです。

  • BASIC — 計画を除くすべての属性(実行統計、バインドなど)を戻します。実行コンテキストは必ず結果に含まれます。

  • ALL — すべての属性を戻します

  • 属性名のカンマ区切りリスト。これによって、SQL属性のサブセットのみを戻すことができます。設定可能な値は、EXECUTION_STATISTICS、SQL_BINDS、SQL_PLAN_STATISTICS(SQL_PLAN + 行ソース統計に類似)です。

recursive_sql

SQLチューニング・セットに再帰的SQLを含めるフィルタ(HAS_RECURSIVE_SQL)、または再帰的SQLを除外するフィルタ(NO_RECURSIVE_SQL)を指定します。

dbid

インポート済またはPDBレベルのAWRデータのDBIDを指定します。NULLの場合、ファンクションは現在のデータベースDBIDを使用します。

戻り値

このファンクションは、各データ・ソースで検出されるSQL_IDまたはPLAN_HASH_VALUEの組合せごとに1つのSQLSET_ROWを戻します。

使用上のノート

このファンクションで提供されるフィルタは、現行のユーザーが実行するSQLの一部として評価されます。したがって、それらのフィルタは、ユーザーのセキュリティ権限で実行され、ユーザーがアクセスできるすべての構成メンバーおよび副問合せを含んでいますが、それ以外のものは含んでいません。

例

-- select statements from snapshots 1-2
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(1,2)) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;
/

170.4.16 UNPACK_STGTABプロシージャ

このプロシージャは、1つ以上のSQLチューニング・セットを、ステージング表内のそれぞれの場所からSQLチューニング・セット・スキーマにコピーして、適切なSQLチューニング・セットを作成します。

構文

DBMS_SQLSET.UNPACK_STGTAB (
   sqlset_name          IN VARCHAR2 := '%',
   sqlset_owner         IN VARCHAR2 := NULL,
   replace              IN BOOLEAN,
   staging_table_name   IN VARCHAR2,
   staging_schema_owner IN VARCHAR2 := NULL);

パラメータ

パラメータはDBMS_SQLTUNE.UNPACK_STGTAB_SQLSETおよびDBMS_SQLSET.UNPACK_STGTABプロシージャの場合と同じです。

表170-18 UNPACK_STGTAB_SQLSETおよびUNPACK_STGTABプロシージャのパラメータ

パラメータ 説明

sqlset_name

アンパックするチューニング・セットの名前(NULL以外)を指定します。

ワイルドカード文字(%)がサポートされていて、1回のコールで複数のチューニング・セットをアンパックできます。たとえば、%と指定すると、すべてのチューニング・セットがステージング表からアンパックされます。

sqlset_owner

チューニング・セットの所有者の名前を指定します。現行のスキーマ所有者の場合はNULLを指定します。ワイルドカード文字(%)はサポートされています。

replace

既存のSQLチューニング・セットを置き換えるかどうかを指定します。FALSEの場合、すでに存在するチューニング・セットを作成しようとすると、このプロシージャによりエラーが生成されます。

staging_table_name

DBMS_SQLTUNE.PACK_STGTAB_SQLSETまたはDBMS_SQLSET.PACK_STGTABプロシージャへのコール後に移動されたステージング表の名前(大/小文字区別)を指定します。

staging_schema_owner

ステージング表の所有者の名前(大/小文字区別)を指定します。現行のスキーマ所有者の場合はNULLを指定します。

例

 -- unpack all STS in the staging table
EXEC DBMS_SQLSET.UNPACK_STGTAB(sqlset_name         => '%', -
                               sqlset_owner        => '%', -
                               replace             => FALSE, -
                               staging_table_name  => 'STGTAB_SQLSET');
 
-- errors can arise during STS unpack when a STS in the staging table has the
-- same name/owner as STS on the system.  In this case, users should call
-- remap_stgtab_sqlset to patch the staging table and with which to call unpack
-- Replace set to TRUE.
EXEC DBMS_SQLSET.UNPACK_STGTAB(sqlset_name         => '%', -
                               sqlset_owner        => '%', -
                               replace             => TRUE, -
                               staging_table_name  => 'STGTAB_SQLSET');

170.4.17 UPDATE_SQLSETプロシージャ

このオーバーロードされたプロシージャは、SQLチューニング・セットのSQL文の選択されているフィールドを更新します。

構文

DBMS_SQLSET.UPDATE_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sql_id           IN  VARCHAR2,
   plan_hash_value  IN  NUMBER := NULL,
   attribute_name   IN  VARCHAR2,
   attribute_value  IN  VARCHAR2 := NULL,
   sqlset_owner     IN  VARCHAR2 := NULL);

DBMS_SQLSET.UPDATE_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sql_id           IN  VARCHAR2,
   plan_hash_value  IN  NUMBER := NULL,
   attribute_name   IN  VARCHAR2,
   attribute_value  IN  NUMBER := NULL,
   sqlset_owner     IN  VARCHAR2 := NULL);

パラメータ

表170-19 UPDATE_SQLSETプロシージャのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前を指定します。

sql_id

更新するSQL文の識別子を指定します。

plan_hash value

SQL文の実行計画のハッシュ値を指定します。

このパラメータは、文のすべての計画ではなく、文の特定の計画の属性を更新する場合に使用します。

attribute_name

変更する属性の名前を指定します。

MODULE、ACTION、PARSING_SCHEMA_NAMEおよびOTHERのテキスト・フィールドを更新できます。更新できる唯一の数値フィールドはPRIORITYです。

文に複数の計画がある場合、プロシージャはすべての計画の属性値を変更します。

attribute_value

属性の新しい値を指定します。