プライマリ・コンテンツに移動
Oracle® Database SQLチューニング・ガイド
12c リリース1 (12.1)
B71277-09
目次へ移動
目次
索引へ移動
索引

前
次
次へ

22 SQLプロファイルの管理

この章の内容は次のとおりです。

SQLプロファイルについて

SQLプロファイルは、SQL文に固有の補足統計を含むデータベース・オブジェクトです。

概念上、SQL文に対するSQLプロファイルの役割は、表または索引に対するオブジェクト・レベルの統計情報の役割と同じです。DBAによりSQLチューニング・アドバイザが起動されると、SQLプロファイルが作成されます。

この項の内容は次のとおりです。

SQLプロファイルの目的

SQL文をプロファイルする場合、SQLチューニング・アドバイザは特定のバインド値のセットを入力として使用します。

アドバイザは、データ・サンプルの文のフラグメントの実行によって取得した値とオプティマイザの見積りを比較します。差異が大きいと検出された場合、SQLチューニング・アドバイザはSQLプロファイルに修正アクションをバンドルし、その受入れを推奨します。

SQLプロファイルで統計が修正されることにより、オプティマイザのカーディナリティの予測が改善され、より適切な計画を選択できるようになります。SQLプロファイルには、他の計画改善の方法より優れた次の利点があります。

  • SQLプロファイルは、ヒントおよびストアド・アウトラインとは異なり、オプティマイザを特定の計画またはサブプランに結び付けません。SQLプロファイルは、不適切な見積りを修正し、それぞれの状況に合った最適な計画を選択する柔軟性をオプティマイザに提供します。

  • SQLプロファイルを使用する場合は、ヒントとは異なり、アプリケーション・ソース・コードの変更は不要です。データベースによるSQLプロファイルの使用は、ユーザーに対して透過的です。

SQLプロファイルの概念

SQLプロファイルは問合せに関する補助統計のコレクションであり、問合せで参照される表および列のすべてが含まれます。プロファイルはデータ・ディクショナリに格納されます。オプティマイザは、最適化時にこの情報を使用して、最適な計画を特定します。

注意:

SQLプロファイルには、個々の計画ではなく、すべてのに対する補助統計が含まれます。プロファイル自体が特定の計画を判断することはありません。

SQLプロファイルには、統計の中でも特に、一連のカーディナリティの調整が含まれます。カーディナリティの測定は、統計予測ではなくWHERE句のサンプリングに基づきます。プロファイルは問合せの各部分を使用して、見積られたカーディナリティが実際のカーディナリティに近いかどうかを判断します。一致しない場合、修正されたカーディナリティを使用します。たとえば、SQLプロファイルがSELECT * FROM t WHERE x=5 AND y=10に対して存在すると、プロファイルには、実際に戻された行数が格納されます。

オプティマイザには、計画を選択する際に、次の情報ソースがあります。

図22-1に、SQL文とSQLプロファイルの関係を示します。オプティマイザは、SQLプロファイルと環境を使用して実行計画を生成します。この例では、文の計画は、SQL計画ベースラインにあります。

オプティマイザの環境またはSQLプロファイルが変化した場合、オプティマイザは新しい計画を作成できます。表が大きくなり、索引が作成または削除されると、SQLプロファイルの計画が変化する可能性があります。対応する文のデータ配分やアクセス・パスに変更があっても、プロファイルは引き続き関連付けられています。一般に、SQLプロファイルをリフレッシュする必要はありません。

長い間に、プロファイルの内容は古くなる可能性があります。この場合、SQL文のパフォーマンスが低下する場合があります。文は、負荷の高いSQLまたは上位SQLとして現れる場合があります。この場合、自動SQLチューニング・タスクが文を高負荷のSQLとして再び取得します。文の新しいSQLプロファイルを実装できます。

SQLプロファイルは、内部では、異なるタイプの問題を解決するヒントを使用して実装されます。これらのヒントは特定の計画を指定しません。ヒントは、最適ではない計画が生成される原因となる、オプティマイザの見積りアルゴリズムのエラーを修正します。たとえば、統計が欠落している場合や失効している場合、プロファイルは、TABLE_STATSヒントを使用して表にオブジェクト統計を設定します。

SQLプロファイルの推奨事項

「SQLプロファイリング」で説明したとおり、SQLチューニング・アドバイザは、自動チューニング・オプティマイザを起動してSQLプロファイルの推奨事項を生成します。SQLプロファイルを実装する推奨事項は、結果で生成され、SQLチューニング・アドバイザ・レポートの個別のセクションに表示されます。

SQLプロファイルを実装(または承諾)した場合、プロファイルが作成されてデータ・ディクショナリに永続的に保存されます。ただし、SQLプロファイルの情報は、通常のディクショナリ・ビューには表示されません。

関連項目:

  • 自動DOPの詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。

  • DBMS_SQLTUNE.ACCEPT_SQL_PROFILEプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

例22-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

SQLプロファイルおよびSQL計画ベースライン

SQLプロファイルは、SQL計画管理とともに使用することも、SQL計画管理なしで使用することもできます。SQLプロファイルと計画ベースラインの間に厳密な関係は存在しません。SQLプロファイルを使用することにより、オプティマイザはベースラインで最小コストの計画を選択できるため、SQL計画ベースラインに文の計画が複数存在する場合は、SQLプロファイルが役に立ちます。

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日でパフォーマンス・チューニング・ガイド』を参照してください。

SQLプロファイルの基本タスク

基本タスクには、SQLプロファイルの承諾(実装)、変更、リストおよび削除が含まれます。

次の図は、基本ワークフローを示しています。

図22-2 SQLプロファイルの管理

図22-2の説明は次にあります。
「図22-2 SQLプロファイルの管理」の説明

通常、SQLプロファイルを管理する順序は次のとおりです。

  1. 推奨されたSQLプロファイルを実装します。

    このタスクについては、「SQLプロファイルの実装」を参照してください。

  2. データベースに格納されているSQLプロファイルの情報を取得します。

    このタスクについては、「SQLプロファイルのリスト」を参照してください。

  3. オプションで、実装されたSQLプロファイルを変更できます。

    このタスクについては、「SQLプロファイルの変更」を参照してください。

  4. 実装されたSQLプロファイルが必要ない場合は、削除します。

    このタスクについては、「SQLプロファイルの削除」を参照してください。

別のデータベースでSQL文をチューニングするには、別のデータベースにSQLチューニング・セットおよびSQLプロファイルを転送します。このタスクについては、「SQLプロファイルの転送」を参照してください。

関連項目:

DBMS_SQLTUNEパッケージの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照

SQLプロファイルの実装

SQLプロファイルを実装(または承諾)した場合、データベースに永続的に格納されます。計画の生成時、オプティマイザが入力として使用する前にプロファイルを実装する必要があります。

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_matchtrueに設定した場合、WHERE句のリテラル値をバインド変数に置換した後、同じテキストを持つすべてのSQL文がSQLプロファイルの追加の対象となります。この設定により、テキストのリテラル値のみが異なるSQLでは、SQLプロファイルの共有が可能になります。そのため、この設定は、リテラル値のみを使用するアプリケーションで役に立ちます。SQLテキストにリテラル値とバインド変数の両方が使用されている場合、またはforce_matchパラメータがfalse(デフォルト値)に設定されている場合、WHERE句のリテラル値はバインド変数に置換されません。

関連項目:

ACCEPT_SQL_PROFILEプロシージャの詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください。

SQLプロファイルの実装

この項では、ACCEPT_SQL_PROFILEプロシージャを使用してSQLプロファイルを実装する方法を説明します。

前提条件

このチュートリアルでは、次のことが前提となっています。

  • SQLチューニング・アドバイザ・タスクSTA_SPECIFIC_EMP_TASKには、SQLプロファイルを作成する推奨事項が含まれます。

  • SQLプロファイルの名前は、my_sql_profileです。

  • PL/SQLブロックは、パラレル実行を使用するプロファイルを受け入れます(profile_type)。

  • プロファイルは強制一致を使用します。

SQLプロファイルを実装するには、次のようにします。

  • 適切な権限でSQL*Plusをデータベースに接続して、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パッケージおよびタイプ・リファレンス』を参照してください。

SQLプロファイルのリスト

データ・ディクショナリ・ビューDBA_SQL_PROFILESでは、データベースにSQLプロファイルを永続的に格納します。統計は、Oracle内部フォーマットであるため、プロファイルを直接問い合せることはできません。ただし、プロファイルをリストできます。

SQLプロファイルをリストするには、次のようにします。

  • 適切な権限でSQL*Plusをデータベースに接続して、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リファレンス』を参照してください。

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プロファイルを変更するには、次のようにします。

  1. 適切な権限でSQL*Plusをデータベースに接続し、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;
    
  2. 現在のデータベース・セッションの初期化パラメータ設定を変更します。

    たとえば、次のSQLを実行します。

    ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';
    
  3. プロファイルされたSQL文をテストします。

  4. 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パッケージおよびタイプ・リファレンス』を参照してください。

SQLプロファイルの削除

DROP_SQL_PROFILEプロシージャを使用して、SQLプロファイルを削除できます。

前提条件

この項では、次のことが前提となっています。

  • my_sql_profileを削除します。

  • 名前が存在しない場合に発生するエラーを無視します。

SQLプロファイルを削除するには、次のようにします。

  • 適切な権限でSQL*Plusをデータベースに接続して、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リファレンス』を参照してください

SQLプロファイルの転送

SQLプロファイルをデータベースのSYSスキーマからステージング表にエクスポートした後、ステージング表から別のデータベースにインポートできます。SQLプロファイルは、同じリリースまたはそれ以降のリリースで作成された任意のOracleデータベースに転送できます。

表22-1に、SQLプロファイルを管理する主なプロシージャおよびファンクションを示します。

表22-1 SQLプロファイルの転送API

プロシージャまたはファンクション 説明

CREATE_STGTAB_SQLPROF

システム間でのSQLプロファイルのコピーに使用されるステージング表を作成します。

PACK_STGTAB_SQLPROF

プロファイル・データをSYSスキーマからステージング表に移動します。

UNPACK_STGTAB_SQLPROF

ステージング表に格納されたプロファイル・データを使用して、システムでプロファイルを作成します。

次の図に、SQLプロファイルの転送の基本ワークフローを示します。

図22-3 SQLプロファイルの転送

図22-3の説明は次にあります。
「図22-3 SQLプロファイルの転送」の説明

前提条件

このチュートリアルでは、次のことが前提となっています。

  • my_profileを本番データベースからテスト・データベースに転送します。

  • dba1スキーマにステージング表を作成します。

SQLプロファイルを転送するには、次のようにします。

  1. 適切な権限で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;
    /
    
  2. PACK_STGTAB_SQLPROFプロシージャを使用して、SQLプロファイルをステージング表にエクスポートします。

    次の例では、SQLプロファイルmy_profiledba1.my_staging_tableに移入します。

    BEGIN
      DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (  
        profile_name         => 'my_profile'
    ,   staging_table_name   => 'my_staging_table'
    ,   staging_schema_owner => 'dba1' 
    );
    END;
    / 
    
  3. SQLプロファイルの解凍先となるデータベースにステージング表を移動します。

    適切なユーティリティを使用して表を移動します。たとえば、Oracle Data Pumpまたはデータベース・リンクを使用します。

  4. 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ユーティリティ』を参照してください。