27 SQLプロファイルの管理
保証されている場合、SQLチューニング・アドバイザがSQLプロファイルを推奨します。DBMS_SQLTUNE
を使用して、SQLプロファイルを実装、変更、削除および転送できます。
この章のトピックは、次のとおりです:
27.1 SQLプロファイルについて
SQLプロファイルは、SQL文に固有の補足統計を含むデータベース・オブジェクトです。
概念上、SQL文に対するSQLプロファイルの役割は、表または索引に対するオブジェクト・レベルの統計情報の役割と同じです。DBAによりSQLチューニング・アドバイザが起動されると、SQLプロファイルが作成されます。
この項では、次の項目について説明します。
関連項目:
27.1.1 SQLプロファイルの目的
SQL文をプロファイルする場合、SQLチューニング・アドバイザは特定のバインド値のセットを入力として使用します。
アドバイザは、データ・サンプルの文のフラグメントの実行によって取得した値とオプティマイザの見積りを比較します。差異が大きいと検出された場合、SQLチューニング・アドバイザはSQLプロファイルに修正アクションをバンドルし、その受入れを推奨します。
SQLプロファイルで統計が修正されることにより、オプティマイザのカーディナリティの予測が改善され、より適切な計画を選択できるようになります。SQLプロファイルには、他の計画改善の方法より優れた次の利点があります。
-
SQLプロファイルは、ヒントおよびストアド・アウトラインとは異なり、オプティマイザを特定の計画またはサブプランに結び付けません。SQLプロファイルは、不適切な見積りを修正し、それぞれの状況に合った最適な計画を選択する柔軟性をオプティマイザに提供します。
-
SQLプロファイルを使用する場合は、ヒントとは異なり、アプリケーション・ソース・コードの変更は不要です。データベースによるSQLプロファイルの使用は、ユーザーに対して透過的です。
27.1.2 SQLプロファイルの概念
SQLプロファイルは問合せに関する補助統計のコレクションであり、問合せで参照される表および列のすべてが含まれます。
プロファイルはデータ・ディクショナリに内部形式で格納されます。ユーザー・インタフェースは、DBA_SQL_PROFILES
ディクショナリ・ビューです。オプティマイザは、最適化時にこの情報を使用して、最適な計画を特定します。
この項では、次の項目について説明します。
関連項目:
DBA_SQL_PROFILES
についてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
27.1.2.1 SQLプロファイルの統計
SQLプロファイルには、統計の中でも特に、一連のカーディナリティの調整が含まれます。
カーディナリティの測定は、統計予測ではなくWHERE
句のサンプリングに基づきます。プロファイルは問合せの各部分を使用して、見積られたカーディナリティが実際のカーディナリティに近いかどうかを判断します。一致しない場合、修正されたカーディナリティを使用します。たとえば、SQLプロファイルがSELECT * FROM t WHERE x=5 AND y=10
に対して存在すると、プロファイルには、実際に戻された行数が格納されます。
Oracleデータベース18c以降では、SQLチューニング・アドバイザはExadata対応SQLプロファイルを推奨できます。Oracle Exadata Database Machineでは、スマート・スキャンのコストは、システム統計I/Oシーク時間(ioseektim
)、マルチブロック読取りカウント(mbrc
)およびI/O転送速度(iotfrspeed
)によって異なります。これらの統計の値は、通常はExadataで異なるため、計画の選択に影響を与えることがあります。システム統計が失効している場合や、統計を収集するとパフォーマンスが向上する場合、SQLチューニング・アドバイザは、Exadata対応SQLプロファイルを受け入れることを推奨します。
関連項目:
-
システム統計について学習するには、Oracle Databaseパフォーマンス・チューニング・ガイドを参照してください
27.1.2.2 SQLプロファイルおよび実行計画
SQLプロファイルには、個々の計画ではなく、すべての文に対する補助統計が含まれます。プロファイル自体が特定の計画を判断することはありません。
SQLプロファイルは、内部では、異なるタイプの問題を解決するヒントを使用して実装されます。これらのヒントは特定の計画を指定しません。ヒントは、最適ではない計画が生成される原因となる、オプティマイザの見積りアルゴリズムのエラーを修正します。たとえば、統計が欠落している場合や失効している場合、プロファイルは、TABLE_STATS
ヒントを使用して表にオブジェクト統計を設定します。
オプティマイザには、計画を選択する際に、次の情報ソースがあります。
次の図は、SQL文と、この文に対するSQLプロファイルの関係を示しています。オプティマイザは、SQLプロファイルと環境を使用して実行計画を生成します。この例では、文の計画は、SQL計画ベースラインにあります。
オプティマイザの環境またはSQLプロファイルが変化した場合、オプティマイザは新しい計画を作成できます。表が大きくなり、索引が作成または削除されると、SQLプロファイルの計画が変化する可能性があります。対応する文のデータ配分やアクセス・パスに変更があっても、プロファイルは引き続き関連付けられています。
一般に、SQLプロファイルをリフレッシュする必要はありません。ただし、長い間に、プロファイルの内容が古くなる可能性があります。この場合、SQL文のパフォーマンスが低下する場合があります。文は、負荷の高いSQLまたは上位SQLとして現れる場合があります。この場合、自動SQLチューニング・タスクが文を高負荷のSQLとして再び取得します。文の新しいSQLプロファイルを実装できます。
関連項目:
-
SQLヒントについて学習するには、『Oracle Database SQL言語リファレンス』を参照してください
27.1.2.3 SQLプロファイルの推奨事項
SQLチューニング・アドバイザは、自動チューニング・オプティマイザを起動してSQLプロファイルの推奨事項を生成します。
SQLプロファイルを実装する推奨事項は、結果で生成され、SQLチューニング・アドバイザ・レポートの個別のセクションに表示されます。SQLプロファイルを実装(または承諾)した場合、プロファイルが作成されてデータ・ディクショナリに永続的に保存されます。ただし、SQLプロファイルの情報は、通常のディクショナリ・ビューには表示されません。
例27-1 SQLプロファイルの推奨事項
この例では、コストの高いいくつかの結合を使用するSELECT
文に対して、よりよい計画が検出されています。プロファイルを実装するDBMS_SQLTUNE.ACCEPT_SQL_PROFILE
の実行が推奨されています。これにより、文の実行が98.53%高速化します。
---------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
---------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement. Choose
one of the following SQL profiles to implement.
Recommendation (estimated benefit: 99.45%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task',
object_id => 3, task_owner => 'SH', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original
plan and measuring their respective execution statistics. A plan may
have been only partially executed if the other could be run to
completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: PARTIAL COMPLETE
Elapsed Time(us): 15467783 226902 98.53 %
CPU Time(us): 15336668 226965 98.52 %
User I/O Time(us): 0 0
Buffer Gets: 3375243 18227 99.45 %
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 109
Fetches: 0 109
Executions: 0 1
Notes
-----
1. The SQL profile plan was first executed to warm the buffer cache.
2. Statistics for the SQL profile plan were averaged over next 3 executions.
SQLチューニング・アドバイザは、自動並列度(自動DOP)機能を使用するプロファイルの実装を推奨する場合もあります。パラレル問合せプロファイルは、元の計画がシリアルの場合で、パラレル実行により実行時間の長い問合せの経過時間を大幅に削減できる場合にのみ推奨されます。
SQLチューニング・アドバイザが自動DOPを使用するプロファイルを推奨する場合、SQL文にパラレル実行を使用した場合のパフォーマンスのオーバーヘッドの詳細がレポートで提供されます。パラレル実行の推奨では、SQLチューニング・アドバイザは、2つのSQLプロファイルの推奨(シリアル実行とパラレル実行に各1個)を提供する場合があります。
次の例は、パラレル問合せの推奨を示します。この例では、並列度が7の場合、レスポンス時間が大幅に改善されますが、リソースの消費がほぼ25%増加します。ユーザーは、データベース・スループットの削減がレスポンス時間の増加に値するかどうかを判断する必要があります。
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel
execution for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'gfk_task',
object_id => 3, task_owner => 'SH', replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 7 will improve its response time
82.22% over the SQL profile plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource
consumption by an estimated 24.43% which may result in a reduction of
system throughput. Also, because these resources are consumed over a
much smaller duration, the response time of concurrent statements
might be negatively impacted if sufficient hardware capacity is not
available.
The following data shows some sampled statistics for this SQL from the
past week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity .29
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 76.51
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 95.21
関連項目:
-
自動DOPについてさらに学習するには、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください
-
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
27.1.3 SQLプロファイルのユーザー・インタフェース
Oracle Enterprise Manager Cloud Control (Cloud Control)は、通常、自動SQLチューニングの一部としてSQLプロファイルを処理します。
コマンドラインでは、DBMS_SQLTUNE
パッケージを使用してSQLプロファイルを管理できます。APIを使用するには、ADMINISTER SQL MANAGEMENT OBJECT
権限が必要です。
関連項目:
-
DBMS_SQLTUNE
パッケージの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照 -
Cloud Controlを使用してSQLプロファイルを管理する方法を学習するには、Oracle Database 2日でパフォーマンス・チューニング・ガイドを参照してください
27.1.4 SQLプロファイルの基本タスク
基本タスクには、SQLプロファイルの承諾(実装)、変更、リストおよび削除が含まれます。
次の図は、基本ワークフローを示しています。
通常、SQLプロファイルを管理する順序は次のとおりです。
-
推奨されたSQLプロファイルを実装します。
このタスクについては、「SQLプロファイルの実装」を参照してください。
-
データベースに格納されているSQLプロファイルの情報を取得します。
このタスクについては、「SQLプロファイルのリスト」を参照してください。
-
オプションで、実装されたSQLプロファイルを変更できます。
このタスクについては、「SQLプロファイルの変更」を参照してください。
-
実装されたSQLプロファイルが必要ない場合は、削除します。
このタスクについては、「SQLプロファイルの削除」を参照してください。
別のデータベースでSQL文をチューニングするには、別のデータベースにSQLチューニング・セットおよびSQLプロファイルを転送します。このタスクについては、「SQLプロファイルの転送」を参照してください。
関連項目:
DBMS_SQLTUNE
パッケージの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照
27.2 SQLプロファイルの実装
SQLプロファイルを実装した場合、データベースに永続的に格納されます。
プロファイルを実装するのは、それを受け入れることと同じです。プロファイルは、計画の生成時にオプティマイザが入力として使用する前に受け入れる必要があります。
この項では、次の項目について説明します。
27.2.1 SQLプロファイルの実装について
通常では、SQLチューニング・アドバイザが推奨するSQLプロファイルを実装します。
索引およびSQLプロファイルが推奨されている場合は、両方とも使用するか、SQLプロファイルのみを使用します。索引を作成した場合、オプティマイザは、新しい索引を選択するためにプロファイルが必要になる場合があります。
場合によっては、SQLチューニング・アドバイザは、改善されたシリアル計画よりも、さらによいパラレル計画を検出する場合もあります。この場合は、標準およびパラレルのSQLプロファイルが推奨され、文に対する最善のシリアル計画と最善のパラレル計画のいずれかを選択できます。パラレル計画は、レスポンス時間の増加がスループットの低下に値する場合にのみ実装します。
SQLプロファイルを実装するには、DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
プロシージャを実行します。重要なパラメータは次のとおりです。
-
profile_type
パラレル実行を変更しないSQLプロファイルの場合は
REGULAR_PROFILE
、またはパラレル実行を変更するSQLプロファイルの場合はPX_PROFLE
にこのパラメータを設定します。 -
force_match
このパラメータは、文の一致を制御します。通常、SQLプロファイルを受け入れると、ハッシュ関数を使用して生成されるSQLシグネチャを通じてSQL文に関連付けられます。このハッシュ関数は、シグネチャを生成する前に、SQL文を大文字に変更し、余分な空白をすべて削除します。したがって、大/小文字および空白のみが異なるすべてのSQL文で同じSQLプロファイルを使用できます。
force_match
をtrue
に設定した場合、WHERE
句のリテラル値をバインド変数に置換した後、同じテキストを持つすべてのSQL文がSQLプロファイルの追加の対象となります。この設定により、テキストのリテラル値のみが異なるSQLでは、SQLプロファイルの共有が可能になります。そのため、この設定は、リテラル値のみを使用するアプリケーションで役に立ちます。SQLテキストにリテラル値とバインド変数の両方が使用されている場合、またはforce_match
パラメータがfalse
(デフォルト値)に設定されている場合、WHERE
句のリテラル値はバインド変数に置換されません。
関連項目:
ACCEPT_SQL_PROFILE
プロシージャの詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください。
27.2.2 SQLプロファイルの実装
SQLプロファイルを実装するには、DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
プロシージャを使用します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
SQLチューニング・アドバイザ・タスク
STA_SPECIFIC_EMP_TASK
には、SQLプロファイルを作成する推奨事項が含まれます。 -
SQLプロファイルの名前は、
my_sql_profile
です。 -
PL/SQLブロックは、パラレル実行を使用するプロファイルを受け入れます(
profile_type
)。 -
プロファイルは強制一致を使用します。
SQLプロファイルを実装するには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
ACCEPT_SQL_PROFILE
ファンクションを実行します。たとえば、次のPL/SQLを実行します。
DECLARE my_sqlprofile_name VARCHAR2(30); BEGIN my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => 'STA_SPECIFIC_EMP_TASK' , name => 'my_sql_profile' , profile_type => DBMS_SQLTUNE.PX_PROFILE , force_match => true ); END; /
関連項目:
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
27.3 SQLプロファイルのリスト
データ・ディクショナリ・ビューDBA_SQL_PROFILES
では、データベースにSQLプロファイルを永続的に格納します。
プロファイル統計は、Oracle Database内部フォーマットであるため、プロファイルを直接問い合せることはできません。ただし、プロファイルをリストできます。
SQLプロファイルをリストするには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
DBA_SQL_PROFILES
ビューを問い合せます。たとえば、次の問合せを実行します。
COLUMN category FORMAT a10 COLUMN sql_text FORMAT a20 SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
出力例は次のように表示されます。
NAME SQL_TEXT CATEGORY STATUS ------------------------------ -------------------- ---------- -------- SYS_SQLPROF_01285f6d18eb0000 select promo_name, c DEFAULT ENABLED ount(*) c from promo tions p, sales s whe re s.promo_id = p.pr omo_id and p.promo_c ategory = 'internet' group by p.promo_na me order by c desc
関連項目:
DBA_SQL_PROFILES
ビューについて学習するには、『Oracle Databaseリファレンス』を参照してください。
27.4 SQLプロファイルの変更
ALTER_SQL_PROFILE
プロシージャのattribute_name
パラメータを使用して、既存のSQLプロファイルの属性を変更できます。
CATEGORY
属性により、プロファイルを適用できるユーザー・セッションが決まります。DBA_SQL_PROFILES.CATEGORY
への問合せによって、CATEGORY
属性を参照します。デフォルトでは、すべてのプロファイルはDEFAULT
カテゴリです。これは、SQLTUNE_CATEGORY
初期化パラメータがDEFAULT
に設定されたすべてのセッションがプロファイルを使用できることを意味します。
SQLプロファイルのカテゴリを変更することで、どのセッションが作成するプロファイルの影響を受けるかを確認します。たとえば、カテゴリをDEV
に設定すると、SQLTUNE_CATEGORY
初期化パラメータがDEV
に設定されているセッションのみがプロファイルを使用できます。他のすべてのセッションは、SQLプロファイルにアクセスできないため、SQL文の実行計画がSQLプロファイルの影響を受けることはありません。この方法によって、限定的な環境でプロファイルをテストしてから、他のセッションで使用することができます。
この項の例では、SQLプロファイルのカテゴリがTEST
に設定されているセッションでのみ使用されるようにSQLプロファイルのカテゴリを変更し、SQL文を実行して、プロファイルのカテゴリをDEFAULT
に戻します。
SQLプロファイルを変更するには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
ALTER_SQL_PROFILE
プロシージャを使用して、attribute_name
を設定します。たとえば、次のコードを実行し、
CATEGORY
属性をTEST
に設定します。VARIABLE pname my_sql_profile BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname , attribute_name => 'CATEGORY' , value => 'TEST' ); END;
-
現在のデータベース・セッションの初期化パラメータ設定を変更します。
たとえば、次のSQLを実行します。
ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';
-
プロファイルされたSQL文をテストします。
-
ALTER_SQL_PROFILE
プロシージャを使用して、attribute_name
を設定します。たとえば、次のコードを実行し、
CATEGORY
属性をDEFAULT
に設定します。VARIABLE pname my_sql_profile BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname , attribute_name => 'CATEGORY' , value => 'DEFAULT' ); END;
関連項目:
-
SQLTUNE_CATEGORY
初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください -
ALTER_SQL_PROFILE
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
27.5 SQLプロファイルの削除
DROP_SQL_PROFILE
プロシージャを使用して、SQLプロファイルを削除できます。
前提条件
この項では、次のことを前提にしています。
-
my_sql_profile
を削除します。 -
名前が存在しない場合に発生するエラーを無視します。
SQLプロファイルを削除するには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
DBMS_SQLTUNE.DROP_SQL_PROFILE
プロシージャを使用します。次の例では、
my_sql_profile
というプロファイルを削除します。BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE ( name => 'my_sql_profile' ); END; /
関連項目:
-
DROP_SQL_PROFILE
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください -
SQLTUNE_CATEGORY
初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください
27.6 SQLプロファイルの転送
あるデータベースのSYS
スキーマからステージング表にSQLプロファイルをエクスポートした後、ステージング表から別のデータベースにSQLプロファイルをインポートできます。SQLプロファイルは、同じリリースまたはそれ以降のリリースで作成された任意のOracleデータベースに転送できます。
表27-1に、SQLプロファイルを管理する主なプロシージャおよびファンクションを示します。
表27-1 SQLプロファイルの転送API
プロシージャまたはファンクション | 説明 |
---|---|
|
システム間でのSQLプロファイルのコピーに使用されるステージング表を作成します。 |
|
プロファイル・データを |
|
ステージング表に格納されたプロファイル・データを使用して、システムでプロファイルを作成します。 |
次の図に、SQLプロファイルの転送の基本ワークフローを示します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
my_profile
を本番データベースからテスト・データベースに転送します。 -
dba1
スキーマにステージング表を作成します。
SQLプロファイルを転送するには:
-
適切な権限でSQL*Plusをデータベースに接続し、
CREATE_STGTAB_SQLPROF
プロシージャを使用して、SQLプロファイルを保持するステージング表を作成します。次の例では、
dba1
スキーマにmy_staging_table
を作成します。BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name => 'my_staging_table' , schema_name => 'dba1' ); END; /
-
PACK_STGTAB_SQLPROF
プロシージャを使用して、SQLプロファイルをステージング表にエクスポートします。次の例では、SQLプロファイル
my_profile
をdba1.my_staging_table
に移入します。BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name => 'my_profile' , staging_table_name => 'my_staging_table' , staging_schema_owner => 'dba1' ); END; /
-
SQLプロファイルの解凍先となるデータベースにステージング表を移動します。
適切なユーティリティを使用して表を移動します。たとえば、Oracle Data Pumpまたはデータベース・リンクを使用します。
-
SQLプロファイルのインポート先となるデータベースで、
UNPACK_STGTAB_SQLPROF
を使用してステージング表からSQLプロファイルを解凍します。次の例は、ステージング表にあるSQLプロファイルを解凍する方法を示しています。
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF( replace => true , staging_table_name => 'my_staging_table' ); END; /
関連項目:
-
DBMS_SQLTUNE
の詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
Oracle Data Pumpを使用する方法を学習するには、『Oracle Databaseユーティリティ』を参照してください。