| Oracle Database Real Application Testing追加情報 11gリリース1（11.1） E06169-01 |
|
 戻る |
 次へ |
SQLパフォーマンス・アナライザを使用すると、SQL実行計画およびパフォーマンス特性の変更につながる、システム変更によるパフォーマンスへの影響を評価できます。 SQLパフォーマンス・アナライザを使用できる一般的なシステム変更には次のようなものがあります。
データベースのアップグレード
オペレーティング・システム、ハードウェアまたはデータベースの構成変更
データベース初期化パラメータの変更
スキーマの変更（新しい索引やマテリアライズド・ビューの追加など）
オプティマイザ統計の収集
SQLチューニング・アクション（SQLプロファイルの作成やパーティショニングの実装など）の検証
このマニュアルでは、特に、Oracle Database 9iからOracle Database 10gリリース2へのデータベースのアップグレード時にSQLパフォーマンス・アナライザを使用する方法について説明します。SQLパフォーマンスの詳細、および他のケースで使用する方法については、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
この章の内容は次のとおりです。
SQLパフォーマンス・アナライザは、SQLチューニング・セットに格納されているSQL文の典型的なセットを入力ソースとして受け入れます。 SQLチューニング・セットはOracle Database 9iではサポートされていないため、今回のリリースでは、Oracle Database 9iからSQLトレース・ファイルを読み取り、SQLパフォーマンス・アナライザの入力ソースとして使用できるSQLチューニング・セットを作成する機能が追加されています。 作成したSQLチューニング・セットは、SQLパフォーマンス・アナライザを使用して、データベース・リンクを介しリモートでOracle Database 10gリリース2で実行できます。 この機能は、Oracle Database 9iからOracle Database 10gリリース2へのデータベースのアップグレード時に、SQLレスポンス時間にどのような影響があるかについて、SQLパフォーマンス・アナライザを使用してテストできることを目的に提供されています。図1-1を参照してください。
図1-1 Oracle Database 9iからOracle Database 10gリリース2へのデータベースのアップグレードにおけるSQLパフォーマンス・アナライザのワークフロー
アップグレード元となる本番システムでは、Oracle Database 9iが実行されている必要があります。 アップグレード先となるテスト・システムでは、Oracle Database 10gリリース2が実行されている必要があります。データベースのバージョンは、リリース10.2.0.2以上です。 また、Oracle Database 10gリリース10.2.0.2または10.2.0.3にアップグレードする場合は、手順を進める前に、個別パッチをインストールする必要があります。 SQLパフォーマンス・アナライザで行われた分析が正確であることを保証するには、本番システムで検出された本番データの正確なコピーをこのシステムに格納しておく必要があります。 さらに、ハードウェア構成は、できるだけ本番システムと同等である必要があります。
次に、Oracle Database 11gリリース1を実行する別のシステムをセットアップする必要があります。データベースのバージョンは、リリース11.1.0.6である必要があります。 また、このリリースの個別パッチをインストールする必要もあります。 このシステムは、SQLチューニング・セットを作成し、SQLパフォーマンス・アナライザを実行するために使用します。 SQLチューニング・セットは、本番システムのSQLトレース・ファイルに格納されている統計を使用して作成され、SQLパフォーマンス・アナライザのタスクは、データベース・リンクを介してテスト・システム上でリモートで実行されるため、このシステムでは本番データおよびスキーマを使用できるようにする必要はありません。
前述のとおりアップグレード環境を構成した後、次の手順を完了すると、Oracle Database 9iからOracle Database 10gへのデータベースのアップグレードでSQLパフォーマンス・アナライザを使用できます。図1-1を参照してください。
Oracle Database 9iを実行している本番システムでSQLトレース機能を有効にします。
本番システムへのパフォーマンスの影響を最小限に抑えながら、SQL文の典型的なセットを完全に取得するには、必要な期間だけセッションのサブセットに対してのみSQLトレースを有効にして、すべての重要なSQL文を1回以上取得します。
Oracle Database 9iを実行している本番システムでマッピング表を作成します。
このマッピング表は、SQLトレース・ファイルのユーザーおよびオブジェクトの識別子番号を、同等の文字列に変換するために使用します。
Oracle Database 9iを実行している本番システムからOracle Database 11gを実行しているシステムに、SQLトレース・ファイルおよびマッピング表を移動します。
Oracle Database 11gを実行しているシステムで、SQLトレース・ファイルを使用してSQLチューニング・セットを作成します。
SQLチューニング・セットには、SQLトレース・ファイルで取得されたSQL文と、それに関連する実行コンテキストおよび実行統計が含まれます。
Oracle Database 11gを実行しているシステムで、SQLパフォーマンス・アナライザを使用して、アップグレード前のSQLトライアルとアップグレード後のSQLトライアルを作成します。
SQLパフォーマンスを比較し、パフォーマンスが低下したSQLを修正します。
SQLパフォーマンス・アナライザでは、アップグレード前のSQLトライアルでSQLチューニング・セットから読み取ったSQL文のパフォーマンスと、アップグレード後のSQLトライアルでリモートのテスト実行によって取得したパフォーマンスが比較されます。 SQL文の実行計画またはパフォーマンスの変更内容を確認するためのレポートが生成されます。
パフォーマンスが低下したSQL文がレポートされた場合は、パフォーマンスが低下したSQLを修正するためにさらに変更を行います。 その後、SQLチューニング・セットの実行と、以前の実行とのパフォーマンスの比較を繰り返し、行った修正または追加の変更をテストできます。 分析結果に満足するまで、これらの手順を繰り返します。
この章の後半では、これらの手順をそれぞれ詳しく説明します。
Oracle Database 9iでは、SQLトレース機能を使用して、個々のSQL文のパフォーマンス・データを収集します。 SQLトレースによって生成された情報は、SQLトレース・ファイルに格納されます。 SQLパフォーマンス・アナライザでは、これらのファイルに格納されている次の情報を利用します。
解析が行われたSQLテキストおよびユーザー名
各実行のバインド値
CPU時間および経過時間
物理的な読取り値および論理的な読取り値
処理された行数
各SQL文の実行計画（SQL文のカーソルがクローズしている場合にのみ取得される）
インスタンスに対してSQLトレースを有効にすることは可能ですが、セッションのサブセットに対してSQLトレースを有効にすることをお薦めします。 インスタンスに対してSQLトレース機能を有効にすると、そのインスタンスで実行されたすべてのSQL文のパフォーマンス統計がSQLトレース・ファイルに格納されます。 このようにSQLトレースを使用すると、パフォーマンスに重大な影響を与えたり、システムのオーバーヘッドの増加、過度なCPU使用率、ディスク領域の不足などを引き起こす可能性があります。 トレース・レベルを4に設定して、バインド値および実行計画を取得する必要があります。
Oracle 9iを実行している本番システムでSQLトレースを有効にした後、SQLパフォーマンス・アナライザで使用するSQL文の典型的なセットの統計を含むSQLトレース・ファイルを特定します。 その後、SQLトレース・ファイルを、Oracle Database 11gを実行しているシステムにコピーできます。 SQLワークロードをSQLトレース・ファイルに取得した後、Oracle 9iを実行している本番システムでSQLトレースを無効にします。
|
参照: SQLトレース・ファイルを管理するための初期化パラメータの設定など、SQLトレースを使用する際の追加の考慮事項については、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。 |
SQLトレース・ファイルに格納されているユーザーおよびオブジェクトの識別子番号を個別の名前に変換するには、それぞれのマッピングを指定する表を用意する必要があります。 Oracle Database 11gでは、トレース・ファイルをSQLチューニング・セットに変換する際にこのマッピング表が読み取られます。
マッピング表を作成するには、Oracle Database 9iを実行している本番データベースで次のSQL文を実行します。
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;
作成したマッピング表は、データ・ポンプを使用して、Oracle Database 11gを実行しているシステムに転送できます。
|
参照: データ・ポンプの使用については、『Oracle Databaseユーティリティ』を参照してください。 |
Oracle Database 11gを実行しているシステムにSQLトレース・ファイルおよびマッピング表を移動した後、DBMS_SQLTUNEパッケージを使用してSQLチューニング・セットを作成できます。
SQLチューニング・セットを作成するには、次の手順を実行します。
Oracle Database 11gを実行しているシステム上のディレクトリにSQLトレース・ファイルをコピーします。
このディレクトリのディレクトリ・オブジェクトを作成します。
DBMS_SQLTUNE.SELECT_SQL_TRACEファンクションを使用して、SQLトレース・ファイルからSQL文を読み取ります。
次の例では、sql_trace_prodディレクトリ・オブジェクトに格納されているSQLトレース・ファイルの内容を読み取り、SQLチューニング・セットにロードします。
DECLARE
cur sys_refcursor;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET('my_sts_9i');
OPEN cur FOR
SELECT VALUE (P)
FROM table(DBMS_SQLTUNE.SELECT_SQL_TRACE('sql_trace_prod', '%ora%')) P;
DBMS_SQLTUNE.LOAD_SQLSET('my_sts_9i', cur);
CLOSE cur;
END;
/
SELECT_SQL_TRACEファンクションの構文は、次のとおりです。
DBMS_SQLTUNE.SELECT_SQL_TRACE (
directory IN VARCHAR2,
file_name IN VARCHAR2 := NULL,
mapping_table_name IN VARCHAR2 := 'mapping',
mapping_table_owner IN VARCHAR2 := NULL,
select_mode IN POSITIVE := SINGLE_EXECUTION,
options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
pattern_start IN VARCHAR2 := NULL,
parttern_end IN VARCHAR2 := NULL,
result_limit IN POSITIVE := NULL)
RETURN sys.sqlset PIPELINED;
表1-1に、SELECT_SQL_TRACEファンクションで使用できるパラメータを示します。
表1-1 DBMS_SQLTUNE.SELECT_SQL_TRACEファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQLトレース・ファイルが格納されるディレクトリを指すディレクトリ・オブジェクトを指定します。 |
|
|
処理するSQLトレース・ファイルの名前のすべてまたは一部を指定します。 指定しない場合、特定のディレクトリにある現行または最近のトレース・ファイルが使用されます。 トレース・ファイル名の照合には、%ワイルドカードを使用できます。 |
|
|
マッピング表の名前を指定します。 マッピング表のデフォルトの名前は、 |
|
|
マッピング表が存在するスキーマを指定します。 |
|
|
トレース・ファイルからSQL文を選択するためのモードを指定します。 デフォルト値は |
|
|
操作のオプションを指定します。 デフォルト値は |
|
|
対象とするトレース・ファイル・セクションの開始区切りパターンを指定します。 このパラメータは、現在使用されていません。 |
|
|
処理するトレース・ファイル・セクションの終了区切りパターンを指定します。 このパラメータは、現在使用されていません。 |
|
|
（フィルタ処理された）ソースの上位SQLを指定します。 デフォルト値は |
|
参照: DBMS_SQLTUNEパッケージについては、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
Oracle Database 11gを実行しているシステムにSQLチューニング・セットを作成した後、それを入力ソースとして使用してSQLパフォーマンス・アナライザを実行できます。 SQLパフォーマンス・アナライザの実行には、Oracle Database 9iおよびOracle Database 10gリリース2用のSQLトライアルの作成と、中央タスク・コンテナへの格納が含まれます。 SQLトライアルは、タスクにおけるパフォーマンス・データの個別のセットであり、タスクの結果を格納する場所としてEXECUTE_ANALYSIS_TASKプロシージャによって自動的に生成されます。
SQLパフォーマンス・アナライザを実行するには、次の手順を実行します。
SQLパフォーマンス・アナライザ・タスクを作成します。「SQLパフォーマンス・アナライザ・タスクの作成」を参照してください。
タスクを実行して、本番統計をSQLチューニング・セットからアップグレード前のSQLトライアルに変換します。「アップグレード前のSQLトライアルの作成」を参照してください。
テスト実行を行って、Oracle Database 10gリリース2を実行しているテスト・システムで統計および実行計画を生成し、アップグレード後のSQLトライアルを作成します。「アップグレード後のSQLトライアルの作成」を参照してください。
この項では、DBMS_SQLPA.CREATE_ANALYSIS_TASKファンクションを使用して、Oracle Database 11gを実行しているシステムで新しいSQLパフォーマンス・アナライザ・タスクを作成する方法について説明します。 タスクは、SQLパフォーマンス・アナライザの実行時の入力と結果を格納するデータベース・コンテナです。
タスクを作成する前に、そのシステムで、パフォーマンス分析に使用するSQLワークロードがSQLチューニング・セットの形式で使用できることを確認します。 次のパラメータを使用してCREATE_ANALYSIS_TASKファンクションをコールします。
task_nameを設定して、SQLパフォーマンス・アナライザ・タスクの名前を指定します。
sqlset_nameにSQLチューニング・セットの名前を設定します。
sqlset_ownerにSQLチューニング・セットの所有者を設定します。 デフォルトは、現行のスキーマ所有者です。
basic_filterを使用して、トライアルに含めないSQL文をフィルタ処理して除外します。
order_byを設定して、選択したSQLにorder-by句を指定します。
top_sqlを設定して、フィルタ処理およびランク付けの後、上位のSQL文だけが考慮されるようにします。
次の例では、入力ソースとしてmy_sts_9iというSQLチューニング・セットを使用するmy_spa_taskというSQLパフォーマンス・アナライザ・タスクを作成します。
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'my_sts_9i', -
task_name => 'my_spa_task');
|
参照: DBMS_SQLPA.CREATE_ANALYSIS_TASKファンクションについては、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
Oracle Database 11gを実行しているシステムでSQLパフォーマンス・アナライザ・タスクを作成した後、EXECUTE_ANALYSIS_TASKプロシージャをコールして、SQLチューニング・セットの実行計画と実行時の統計を取得し、それらを使用してアップグレード前のSQLトライアルを作成します。
アップグレード前のSQLトライアルを作成するには、次のパラメータを使用してEXECUTE_ANALYSIS_TASKプロシージャをコールします。
task_nameパラメータに、実行するSQLパフォーマンス・アナライザ・タスクの名前を設定します。
execution_typeパラメータにCONVERT SQLSETを設定して、SQLチューニング・セットに含まれる統計をトライアル実行として処理するようにSQLパフォーマンス・アナライザに指示します。
execution_nameパラメータを使用して、実行を識別する名前を指定します。 指定しないと、SQLパフォーマンス・アナライザよって、タスク実行の名前が自動的に生成されます。
次の例では、my_spa_taskというSQLパフォーマンス・アナライザ・タスクがトライアル実行として実行されます。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'CONVERT SQLSET', -
execution_name => 'my_trial_9i');
|
参照: DBMS_SQLPA.EXECUTE_ANALYSIS_TASKファンクションについては、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
アップグレード前のSQLトライアルを作成した後、アップグレード後のSQLトライアルを作成するために、Oracle Database 10gリリース2を実行しているテスト・システムでSQLパフォーマンス・アナライザ・タスクを実行して、SQLチューニング・セットに含まれるSQL文のテスト実行または実行計画を行う必要があります。 SQLパフォーマンス・アナライザは、Oracle Database 11gがOracle Database 10gリリース2に接続できるようにユーザーが指定する必要があるデータベース・リンクを使用してSQL文をリモートでテスト実行し、SQLトライアルの実行計画と統計を生成します。 データベース・リンクは、Oracle Database 11gを実行しているシステムに存在し、Oracle Database 10gリリース2を実行しているテスト・システムに接続している必要があります。
アップグレード後のSQLトライアルを作成するには、使用するパブリック・データベース・リンクのグローバル名をDATABASE_LINKタスク・パラメータに設定してEXECUTE_ANALYSIS_TASKプロシージャをコールし、Oracle Database 11gを実行しているシステムを使用して実行計画またはテスト実行を行います。 EXPLAIN PLANを使用すると、実行計画のみが生成されます。 その後の比較では、変更された計画のリストのみが生成され、パフォーマンスの変化に関する結果は生成されません。 TEST EXECUTEを使用すると、SQLワークロードが最後まで実行されます。 これによって、Oracle Database 10gを実行しているテスト・システムから生成された統計および実行計画が使用され、アップグレード後のパフォーマンス・データが効率的に作成されます。 ソースでSQL実行計画およびパフォーマンス・データを取得する場合は、TEST EXECUTEを使用することをお薦めします。これによって、より正確な分析が可能になります。
次の例では、データベース・リンクを介してSQL文のテスト実行をリモートで行います。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_remote_trial_10g', -
execution_params => dbms_advisor.arglist('database_link',
'LINK.A.B.C.BIZ.COM'));
|
参照: DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETERプロシージャについては、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
アップグレード前およびアップグレード後のSQLトライアルを作成した後、DBMS_SQLPA.EXECUTE_ANALYSIS_TASKプロシージャまたはファンクションをコールして比較分析を実行し、アップグレード前のパフォーマンス・データ（本番システムからのデータ）とアップグレード後のパフォーマンス・データ（テスト・システムからのデータ）を比較できます。 その後、SQLパフォーマンス・アナライザで比較結果を示すレポートを生成し、その結果を解釈できます。
変更前と変更後のSQLパフォーマンス・データを比較するには、次の手順を実行します。
次のパラメータを使用してEXECUTE_ANALYSIS_TASKプロシージャまたはファンクションをコールします。
task_nameパラメータにSQLパフォーマンス・アナライザ・タスクの名前を設定します。
execution_typeパラメータにCOMPARE PERFORMANCEを設定します。 この設定によって、2つのバージョンのSQLパフォーマンス・データが分析および比較されます。
execution_nameパラメータを使用して、実行を識別する名前を指定します。 指定しない場合、SQLパフォーマンス・アナライザによって生成され、ファンクションによって戻されます。
execution_paramsパラメータを使用して、2つのバージョンのSQLパフォーマンス・データを指定します。 execution_paramsパラメータで特定の実行を表すときは、（name, value）というペアを使用します。 SQLパフォーマンス・データの比較と分析に関連する実行パラメータを次のように設定します。
execution_name1パラメータにアップグレード前のSQLトライアルの名前を設定します。
execution_name2パラメータにアップグレード後のSQLトライアルの名前を設定します。
comparison_metricパラメータを設定して、パフォーマンスの影響分析に使用する実行統計の式を指定します。 指定可能な値は、elapsed_time（デフォルト）、cpu_time、buffer_gets、disk_reads、direct_writesおよびoptimizer_costで、これらを組み合せることもできます。
比較で設定できるその他のパラメータについては、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』のDBMS_SQLPAパッケージを参照してください。
次に、ファンクション・コールの例を示します。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'COMPARE PERFORMANCE', -
execution_name => 'my_exec_compare', -
execution_params => dbms_advisor.arglist(-
'comparison_metric', 'buffer_gets',
'execution_name1', 'my_trial_9i',
'execution_name2', 'my_remote_trial_10g',
次のパラメータを使用してDBMS_SQLPA.REPORT_ANALYSIS_TASKファンクションをコールし、レポートを生成します。
task_nameパラメータにSQLパフォーマンス・アナライザ・タスクの名前を設定します。
execution_nameパラメータにCOMPARE PERFORMANCE実行の名前を設定します。
typeパラメータを設定して、生成するレポートの形式を指定します。 指定可能な値は、TEXT（デフォルト）、HTMLおよびXMLです。
levelパラメータを設定して、推奨事項の形式を指定します。 指定可能な値は、TYPICAL（デフォルト）、BASICおよびALLです。
sectionパラメータを設定して、レポートを特定のセクションに制限します。 指定可能な値は、SUMMARY（デフォルト）およびALLです。
top_sqlパラメータを設定して、レポートに含めるSQLチューニング・セット内のSQL文の数を指定します。 デフォルトでは、システム変更の影響を受ける上位100のSQL文がレポートに表示されます。
次に、比較のサマリー・レポートの作成と表示に使用できるSQLスクリプトの一部を示します。
VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
'text', 'typical', 'summary', NULL, 100, 'my_exec_compare');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep
SQLパフォーマンス・アナライザ・レポートを確認します。
レポートを確認する際は、結果の妥当性を判断するために、次の事項を考慮する必要があります。
|
参照:
|
SQLパフォーマンス・アナライザ・レポートを確認した後、SQLパフォーマンスの比較後に識別された、パフォーマンスが低下したSQL文をチューニングする必要があります。 パフォーマンスが低下したように見えるSQL文が多数ある場合は、その問題を修正するために、根本的な原因を特定してシステムレベルでの変更を行ってみる必要があります。 パフォーマンスが低下したSQL文がわずかである場合は、次のいずれかのチューニング方法を使用して、効果的な解決策を実装することを検討します。
Oracle Database 10gリリース2を実行しているテスト・システムを使用し、パフォーマンスが低下したSQL文に対してSQLチューニング・アドバイザを実行します。
SQLチューニング・アドバイザの使用方法については、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
本番システムでストアド・アウトラインを取得し、それをテスト・システムに移動します。
ストアド・アウトラインの使用方法については、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
パフォーマンスが低下したSQL文をチューニングした後、SQLパフォーマンス・アナライザを使用して、それらの変更をテストする必要があります。 テスト・システムで新しくリモートのテスト実行を行った後、（この新しいSQLトライアルとアップグレード前のSQLトライアルの間で）2回目の比較を行い結果を検証します。 パフォーマンスが安定したことがSQLパフォーマンス・アナライザで示されれば、テストは完了です。 本番システムのアップグレード・プロセスの一環として、この手順での修正を実装します。
