182 DBMS_SQLSET
DBMS_SQLSET
パッケージは、SQLチューニング・セットを管理するためのインタフェースを提供します。
このパッケージは、DBMS_SQLTUNE
のSQLチューニング・セット・サブプログラムとは名前が多少異なる場合がありますが、同じサブプログラムを提供します。違いは、DBMS_SQLSET
にはOracle Tuning Packが不要なことです。
この章のトピックは、次のとおりです:
182.1 DBMS_SQLSETの概要
このパッケージを使用してSQLチューニング・セットを管理します。
ノート:
すべてのDBMS_SQLSET
サブプログラムは、DBMS_SQLTUNE
パッケージに同等のものがあります。
SQLチューニング・セットは、SQL文とともに次の情報を格納します。
-
解析スキーマ名、バインド値などの実行コンテキスト。
-
平均経過時間、実行件数などの実行統計情報。
-
実行計画(データベースでSQL文を実行するために行われる一連の操作)
-
行ソース統計(計画で実行される各操作の処理行数など)。
SQLチューニング・セットを作成する場合は、次のソースを使用してSQL文にフィルタ処理やランキングを実行します。
-
SELECT_CURSOR_CACHEファンクションを使用した共有SQL領域。
-
自動ワークロード・リポジトリの先頭にあるSQL文(SELECT_WORKLOAD_REPOSITORYファンクションを使用)。
-
その他のSQLチューニング・セット(SELECT_SQLSETファンクションを使用)。
-
SQLパフォーマンス・アナライザ・タスクの比較結果(SELECT_SQLPA_TASKファンクションを使用)。
-
SQLトレース・ファイル(SELECT_SQL_TRACEファンクションを使用)。
-
ユーザー定義ワークロード
この機能は、DBMS_SQLSETサブプログラムの要約に示されているすべてのサブプログラムを使用すると、簡単に実行できます。次に例を示します。
-
CREATE_SQLSETプロシージャおよびファンクションでは、データベース内にSQLチューニング・セット・オブジェクトを作成します。
-
選択した一連のSQLをSQLチューニング・セットに入力する場合は、LOAD_SQLSETプロシージャを使用します。
-
CAPTURE_CURSOR_CACHEプロシージャでは、指定した期間にわたり共有SQL領域からSQL文を収集し、データベース・ワークロードにおける実際のピクチャの作成を試みます。
182.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チューニング・セットを操作できます。
182.3 DBMS_SQLSETのデータ構造
DBMS_SQLTUNE
パッケージのSELECT_*
サブプログラムはSQLSET_ROW
タイプのオブジェクトを戻します。
オブジェクト・タイプ
182.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))
属性
表182-1 SQLSET_ROWの属性
属性 | 説明 |
---|---|
|
一意なSQL ID。 |
|
リテラル、ケースおよび空白を除くシグネチャ。 |
|
SQL文の全テキスト。 |
|
現在、実装されていません。 |
|
このSQLに対して取得されるバインド・データ。このパラメータの引数を指定し、 |
|
SQLが解析されるスキーマ。 |
|
SQLの最後のアプリケーション・モジュール。 |
|
SQLの最後のアプリケーション・アクション。 |
|
このSQL文の経過時間の合計。 |
|
このSQL文のCPU時間の合計。 |
|
バッファ取得回数の合計。 |
|
ディスク読取り回数の合計。 |
|
直接パス書込み回数の合計。 |
|
このSQL文による処理行数の合計。 |
|
フェッチ回数の合計。 |
|
このSQL文の実行回数の合計。 |
|
SQL文のすべての行がフェッチされ、文が完全に実行された回数。 |
|
このSQLのオプティマイザ・コスト。 |
|
このSQL文のオプティマイザ環境。 |
|
ユーザー定義の優先順位(1、2、3)。 |
|
文のタイプ( |
|
親カーソルのロード時間。 |
|
このSQL文の統計情報が収集された期間(秒)。 |
|
SQL文がアクティブだった有効期間(秒)。 |
|
他の列(ユーザー定義の属性用)。 |
|
計画の計画ハッシュ値。 |
|
SQL文の実行計画。 |
|
SQL文のユーザー指定バインドのリスト。ユーザー指定ワークロードに使用されます。このパラメータの引数を指定し、 |
|
PDBまたはCDBルートのDBID。 |
|
最後に実行されたSQL文の開始時刻。 |
182.4 DBMS_SQLSETサブプログラムの要約
この表は、DBMS_SQLSET
サブプログラムを示し、簡単に説明しています。
表182-2 DBMS_SQLSETパッケージのサブプログラム
サブプログラム | 説明 |
---|---|
このプロシージャは、新しい参照を既存のSQLチューニング・セットに追加して、クライアントによるその使用状況を示します。 |
|
このプロシージャは、共有SQL領域からワークロードを取得して、SQLチューニング・セットに移入します。 |
|
このプロシージャまたはファンクションは、データベース内にSQLチューニング・セット・オブジェクトを作成します。 |
|
このプロシージャは、SQLチューニング・セットのインポートおよびエクスポートに使用するステージング表を作成します。 |
|
このプロシージャは、SQL文のセットをSQLチューニング・セットから削除します。 |
|
このプロシージャは、SQLチューニング・セットがアクティブでない場合はそれを削除します。 |
|
このプロシージャは、選択したSQL文のセットをSQLチューニング・セットに入力します。このプロシージャを複数回コールすると、新しいSQL文の追加または既存の文の属性の置換を行うことができます。 |
|
このプロシージャは、1つ以上のSQLチューニング・セットを、 |
|
このプロシージャは、異なる値でアンパックできるように、ステージング表内のチューニング・セットの名前および所有者を変更します。 |
|
このプロシージャは、SQLチューニング・セットを非アクティブ化し、このSQLチューニング・セットをクライアントが使用していないことを示します。 |
|
このファンクションは、ワークロード・リポジトリからSQL文を収集します。 |
|
この表ファンクションは、1つ以上のトレース・ファイルの内容を読み取り、検出したSQL文を |
|
このファンクションは、SQLパフォーマンス・アナライザの比較タスクからSQL文を収集します。 |
|
SQLチューニング・セットのコンテンツを読み取るテーブル・ファンクションです。 |
|
このファンクションは、ワークロード・リポジトリからSQL文を収集します。 |
|
このプロシージャは、1つ以上のSQLチューニング・セットを、ステージング表内のそれぞれの場所からSQLチューニング・セット・スキーマにコピーして、適切なSQLチューニング・セットを作成します。 |
|
このオーバーロードされたプロシージャは、SQLチューニング・セットのSQL文の選択されているフィールドを更新します。 |
182.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
の場合と同じです。
表182-3 ADD_SQLSET_REFERENCEおよびADD_REFERENCEファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
SQLチューニング・セットの名前を指定します。 |
|
SQLチューニング・セットの使用方法の説明(オプション)を入力します。 256文字を超える場合は切り捨てられます。 |
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
戻り値
追加した参照の識別子。
使用上のノート
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');
182.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
の場合と同じです。
表182-4 CAPTURE_CURSOR_CACHE_SQLSETおよびCAPTURE_CURSOR_CACHEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLチューニング・セットの名前を指定します |
|
実行する合計時間(秒)を定義します。 |
|
サンプリングの実行間隔(秒)を定義します。 |
|
新しい文の挿入、既存の文の更新またはその両方のいずれかを指定します。 値は、 |
|
取得モード(
|
|
各サンプルの共有SQL領域に適用するフィルタを定義します。
|
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
|
SQLチューニング・セットに再帰的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');
182.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;
パラメータ
表182-5 CREATE_SQLSETプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
作成するSQLチューニング・セットの名前を指定します。この名前はファンクションに渡される名前です。ファンクションに名前が渡されない場合、ファンクションは自動名を生成します。 |
|
SQLチューニング・セットの説明(オプション)を入力します。 |
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
戻り値
作成するSQLチューニング・セットの名前を指定します。この名前はファンクションに渡される名前です。ファンクションに名前が渡されない場合、ファンクションは自動名を生成します。
例
EXEC DBMS_SQLSET.CREATE_SQLSET(-
sqlset_name => 'my_workload', -
description => 'complete application workload');
182.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);
パラメータ
表182-6 CREATE_STGTAB_SQLSETおよびCREATE_STGTABプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
作成する表の名前を指定します。この名前は大文字と小文字を区別します。 |
|
作成する表を格納するスキーマを定義します。現行のスキーマの場合は |
|
ステージング表を格納する表領域を指定します。現行ユーザーのデフォルト表領域の場合は |
|
ステージング表の形式を決定するデータベース・バージョンを指定します。 また、古いデータベース・バージョンのステージング表を作成して、STSを古いデータベース・バージョンにエクスポートすることもできます。以下の値のいずれかを使用します。
|
例
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;
182.4.5 DELETE_SQLSETプロシージャ
このプロシージャは、SQL文のセットをSQLチューニング・セットから削除します。
構文
DBMS_SQLSET.DELETE_SQLSET (
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
パラメータ
表182-7 DELETE_SQLSETプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLチューニング・セットの名前を指定します。 |
|
SQLチューニング・セットからSQLをフィルタするためのSQL述語を指定します。この基本フィルタはSQLチューニング・セットのコンテンツ上でwhere句として使用され、目的のSQLのサブセットをSQLチューニング・セットから選択します。 |
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
例
-- 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');
182.4.6 DROP_SQLSETプロシージャ
このプロシージャは、SQLチューニング・セットがアクティブでない場合はそれを削除します。
構文
DBMS_SQLSET.DROP_SQLSET (
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL);
パラメータ
表182-8 DROP_SQLSETプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLチューニング・セットの名前を指定します。 |
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
使用上のノート
SQLチューニング・セットは、1つ以上のクライアントが参照している場合は削除できません。
例
-- Drop the sqlset.
EXEC DBMS_SQLSET.DROP_SQLSET ('my_workload');
182.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);
パラメータ
表182-9 LOAD_SQLSETプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
ロードするSQLチューニング・セットの名前を指定します。 |
|
ロードするSQLチューニング・セットへのカーソル参照を指定します。 |
|
SQLチューニング・セットにロードする文を指定します。可能な値は次のとおりです:
|
|
既存のSQL文の更新方法を指定します。 このパラメータは、
|
|
更新を実行するタイミングを指定します。 このプロシージャは、指定した条件が満たされている場合にのみ更新を実行します。条件によって、データのソースまたは宛先のいずれかを参照できます。条件では、次の接頭辞を使用してソースまたは宛先の属性を参照する必要があります。
|
|
マージまたは更新時に更新するSQL文の属性のリストを指定します。 可能な値は次のとおりです:
|
|
新しい値が
|
|
DML後に文をコミットするかどうかを指定します。 値が指定されている場合、指定された数の文が挿入されるたびにロードがコミットされます。 この引数に値を指定すると、SQLチューニング・セットのロード操作の進行状況を |
|
SQLチューニング・セットの所有者または現行のスキーマ所有者を定義します。現行の所有者の場合は |
例外
-
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;
/
182.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
プロシージャの場合と同じです。
表182-10 PACK_STGTAB_SQLSETおよびPACK_STGTABプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
パックするSQLチューニング・セットの名前を指定します。この名前は大文字と小文字を区別します。ワイルドカード文字( |
|
パックするSQLチューニング・セットのカテゴリを指定します。この名前は大文字と小文字を区別します。ワイルドカード文字( |
|
使用する表の名前を指定します。値では大文字と小文字が区別されます。 |
|
表が存在するスキーマ、または現行のスキーマの場合は |
|
ステージング表の形式を決定するデータベース・バージョンを指定します。また、古いデータベース・バージョンのステージング表を作成して、STSを古いデータベース・バージョンにエクスポートすることもできます。次のいずれかの値を使用します。
|
使用上のノート
-
複数の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;
182.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プロシージャの場合と同じです。
表182-11 REMAP_STGTAB_SQLSETおよびREMAP_SQLSETプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
再マップ操作の対象となるチューニング・セットの名前を指定します。ワイルドカード文字( |
|
再マップ操作の対象となるチューニング・セット所有者の名前を指定します。現行のスキーマ所有者の場合は |
|
チューニング・セットの新しい名前を指定します。チューニング・セット名を変更しない場合は |
|
チューニング・セットの新しい所有者を指定します。所有者を変更しない場合は |
|
再マップ操作を行う表の名前を指定します。値では大文字と小文字が区別されます。 |
|
ステージング表の所有者の名前を指定します。現行のスキーマ所有者の場合は |
|
新しいコンテナDBIDに再マップされる古いコンテナDBIDを指定します。 同じコンテナDBIDを使用するには |
|
古いコンテナDBIDと置き換える新しいコンテナDBIDを指定します。 同じコンテナ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;
182.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
プロシージャの場合と同じです。
表182-12 REMOVE_SQLSET_REFERENCEおよびREMOVE_REFERENCEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLチューニング・セットの名前を指定します。 |
|
削除する参照の識別子を指定します。 |
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
|
他のユーザーの参照を削除できるか( このパラメータを |
例
特定のSQLチューニング・セットの使用を終了した後、そのSQLチューニング・セットに関する参照を削除して、再度書込み可能にすることができます。次の例では、my_workload
への参照を削除します。
EXEC DBMS_SQLSET.REMOVE_REFERENCE(sqlset_name => 'my_workload', -
reference_id => :rid);
DBA_SQLSET_REFERENCES
ビューを使用して、特定のSQLチューニング・セットへのすべての参照を確認します。
182.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;
パラメータ
表182-13 SELECT_CURSOR_CACHEファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
|
|
現在はサポートされていません。 |
|
選択したSQLに |
|
提供されたランキング・メジャーに応じて先頭のn%を選択するフィルタを指定します。 この値は、1つのランキング・メジャーが指定されている場合にのみ適用されます。 |
|
ランキング・メジャーでランク付けしたフィルタ済のソースの先頭から数件取得されるSQL文を定義します。 |
|
結果として戻すSQL文の属性リストを指定します。 使用可能な値は次のとおりです。
|
|
SQLチューニング・セットに再帰的SQLを含めるフィルタ( |
戻り値
このファンクションは、各データ・ソースで検出されるSQL_ID
またはPLAN_HASH_VALUE
の組合せごとに1つのSQLSET_ROW
を戻します。
使用上のノート
-
このファンクションで提供されるフィルタは、現行のユーザーが実行するSQLの一部として評価されます。したがって、それらのフィルタは、ユーザーのセキュリティ権限で実行され、ユーザーがアクセスできるすべての構成メンバーおよび副問合せを含んでいますが、それ以外のものは含んでいません。
-
ユーザーには、共有SQL領域ビューの権限が必要です。
例182-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;
例182-2 文に関するすべての情報
次の問合せは、SQL ID 4rm4183czbs7j
のSQL文に関するすべての情報を取得します。
SELECT * FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));
例182-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;
例182-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;
/
例182-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;
/
例182-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;/
例182-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;
/
例182-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;
/
例182-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;/
例182-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;
/
例182-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;
/
182.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;
パラメータ
表182-14 SELECT_SQL_TRACEファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
トレース・ファイルを含むディレクトリ・オブジェクトを定義します。このフィールドは必須です。 |
|
トレース・ファイルの名前の全体または一部を指定します。
|
|
マッピング表名を指定します。 マッピング表名は大/小文字が区別されないことに注意してください。マッピング表名が |
|
マッピング表の所有者を指定します。
|
|
トレースからSQLを選択するためのモードを指定します。 使用可能な値は次のとおりです。
|
|
戻されるSQL文のタイプを指定します。
|
|
対象とするトレース・ファイル・セクションの区切りパターンを指定します。現在使用できません。 |
|
処理するトレース・ファイル・セクションの終了区切りパターンを指定します。現在使用できません。 |
|
除外されたソースの上位SQLを指定します。 |
戻り値
このファンクションは、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言語リファレンスを参照してください。
182.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;
パラメータ
表182-15 SELECT_SQLPA_TASKファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
SQLパフォーマンス・アナライザ・タスクの名前を指定します。 |
|
SQLパフォーマンス・アナライザ・タスクの所有者を指定します。 |
|
指定したフィルタの適用元になるSQLパフォーマンス・アナライザ・タスクの実行の名前(タイプ |
|
SQL文のどのサブセットを含めるかを指定します。
|
|
レベル・フィルタに加えてSQLをフィルタするためのSQL述語を指定します。 |
|
現在はサポートされていません。 |
|
結果として戻すSQL文の属性を定義します。 使用可能な値は次のとおりです。
|
戻り値
このファンクションは、SQLチューニング・セット・オブジェクトを戻します。
使用上のノート
たとえば、このファンクションを使用して、SQLパフォーマンス・アナライザ(SPA)の試用中に低下したSQL文のサブセットを含むSQLチューニング・セットを作成できます。また、他の任意のフィルタを指定することもできます。
182.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;
パラメータ
表182-16 SELECT_SQLSETファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
問い合せるSQLチューニング・セットの名前を指定します。 |
|
|
|
現在はサポートされていません。 |
|
選択したSQLに |
|
提供されたランキング・メジャーに応じて先頭のn%を選択するフィルタを指定します。 このパラメータは、1つのランキング・メジャーが指定されている場合にのみ適用されます。 |
|
ランキング・メジャーでランク付けしたフィルタ済のソースの先頭から数件取得されるSQL文。 |
|
結果として戻すSQL文の属性を定義します。 可能な値は次のとおりです:
|
|
計画フィルタを指定します。 このパラメータにより、文に複数の計画がある場合に1つの計画を選択できます。使用可能な値は次のとおりです。
|
|
SQLチューニング・セットの所有者を指定します。現行のスキーマ所有者の場合は |
|
SQLチューニング・セットに再帰的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;
/
182.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;
パラメータ
表182-17 SELECT_WORKLOAD_REPOSITORYファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
開始AWRスナップショット(この値を含めない)を定義します。 |
|
終了AWRスナップショット(この値を含む)を定義します。 |
|
AWRベースライン時間の名前を指定します。 |
|
ワークロード・リポジトリからSQLをフィルタするためのSQL述語を指定します。フィルタは
|
|
現在はサポートされていません。 |
|
選択したSQLに |
|
提供されたランキング・メジャーに応じて先頭のn%を選択するフィルタを指定します。このパーセンテージは、1つのランキング・メジャーが指定されている場合にのみ適用されます。 |
|
提供されたランキング・メジャーに応じてソースの先頭から数件取得されるSQL文を指定します。 |
|
結果として戻すSQL文の属性を指定します。可能な値は次のとおりです:
|
|
SQLチューニング・セットに再帰的SQLを含めるフィルタ( |
|
インポート済またはPDBレベルのAWRデータの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;
/
182.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
プロシージャの場合と同じです。
表182-18 UNPACK_STGTAB_SQLSETおよびUNPACK_STGTABプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
アンパックするチューニング・セットの名前(NULL以外)を指定します。 ワイルドカード文字( |
|
チューニング・セットの所有者の名前を指定します。現行のスキーマ所有者の場合は |
|
既存のSQLチューニング・セットを置き換えるかどうかを指定します。 |
|
|
|
ステージング表の所有者の名前(大/小文字区別)を指定します。現行のスキーマ所有者の場合は |
例
-- 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');
182.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);
パラメータ
表182-19 UPDATE_SQLSETプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
SQLチューニング・セットの名前を指定します。 |
|
更新するSQL文の識別子を指定します。 |
|
SQL文の実行計画のハッシュ値を指定します。 このパラメータは、文のすべての計画ではなく、文の特定の計画の属性を更新する場合に使用します。 |
|
変更する属性の名前を指定します。
文に複数の計画がある場合、プロシージャはすべての計画の属性値を変更します。 |
|
属性の新しい値を指定します。 |