SQL Performance Analyzer

(1)

SQL Performance Analyzer

Oracle ホワイト・ペーパー

(2)

SQL Performance Analyzer

はじめに ... 3

一般的なシナリオ ... 3

SQL PERFORMANCE ANALYZER の使用... 4

SQL ワークロードの取得 ... 4

SQL Tuning Set ... 5

SQL ワークロードの転送... 6

SQL Pre-Change の実行... 6

変更... 7

SQL Post-Change の実行 ... 7

SQL Performance の比較 ... 7

比較レポート SQL ... 7

繰返し... 8

ユースケースの例 ... 8

データベースのアップグレード ... 9

Parameter Change ... 11

Guided Workflow ... 14

DBMS_SQLPA パッケージ... 15

実際のベスト・プラクティス... 18

SQL ワークロードの取得... 18

テスト・システム ... 18

パフォーマンスの比較 ... 18

リグレッション修正 ... 18

本番のチューニング ... 18

結論 ... 19

(3)

SQL Performance Analyzer

はじめに

データベースの更新や新しい索引の追加など、SQL 文の実行計画に影響を与える変更により、SQL 文のパフォーマンスが重大な影響を受けることがあります。結果として、DBA は、変更によって効率の低下した SQL 文の特定と修正に多大な時間と労力を費やすことになるのです。SQL Performance Analyzer（SPA）は、Oracle Database 11g で導入された Oracle Real Application Testing オプションの主要な機能

で、システム変更によって発生する SQL 文の実行パフォーマンスの低下を予測し、未然に防ぐことができます。 SPA は、環境の変更前と変更後で SQL 文を分離して実行することで、環境の変更が SQL の実行計画と実行統計情報に及ぼす影響をより細かく表示します。また、変更前と変更後に SQL 文の実行結果を比較し、効率の低下した SQL 文だけでなく、ワークロードに対してシステムの変更によってもたらされた利点を説明したレポートも生成します。効率の低下した SQL 文については、適切な実行計画の詳細と SQL 文を修正するときのリコメンデーションが提示されます。

SPA は、既存の SQL Tuning Set（STS）、SQL Tuning Advisor、および SQL Plan Management の各機能と密接に連携して動作します。非常に大きな（数千にのぼる SQL 文）SQL ワークロードにシステム変更が及ぼす影響を査定する作業は、これまで手動で行っていたため大変時間のかかる作業でした。SPA を使用すれば、こうした作業が完全に自動化され、簡素化できます。DBA は、SQL 計画ベースラインと SQL Tunung Advisor を使用して、効率の低下した SQL 文をテスト環境で修正し、最適に機能する実行計画を生成できます。これらの計画は、本番環境にエクスポートされ、SQL 文の以降の実行に使用されます。このように、SPA を使用することで、DBA は、これまでより低いコストで、本番環境に対するシステム変更が実際にプラスの効果をもたらすことを極めて高い信頼度で確認できます。

一般的なシナリオ

SPA は、SQL 実行計画および実行時の統計に影響を及ぼす可能性のある、システム変更の影響を分析するために使用できます。一般的なシステム変更には次のような例があります。 • パッチの配置を含むデータベースのアップグレード：データベースをアップグレードすると、オプティマイザの新バージョンがインストールされ、SQLパフォーマンスに直接影響を与えます。DBAは、SPAにより変更前と変更後のOracleデータベースのSQLパフォーマンスを比較できます。このようにして、DBAは、変更前のデータベースが本番システムに実際に配置された後で効率が低下するSQL文を事前に特定し、調整できます。

(4)

• データベース初期化パラメータの変更：データベース・パラメータの値を変更すると、予測外の結果が生じることがあります。たとえば、特定の初期化パラメータは、パフォーマンスを改善するために有効化できますが、この変更により、システム制約が変わる可能性があり、予測外の結果が生じる場合があります。 • スキーマの変更：索引の変更、新しい索引の作成などの変更は、ほぼ確実にSQLパフォーマンスに影響を与えます。 • オプティマイザ統計のリフレッシュ：統計が古いまたは欠落しているオブジェクトに対する新しい統計を収集すると、オプティマイザのコスト計算アルゴリズムが大きな影響を受け、生成する実行計画が相当の影響を受ける可能性があります。DBAは、これらの計画によってシステム・パフォーマンスが低下することはないと保証する必要があります。 • オペレーティング・システムおよびハードウェアの変更：新しいオペレーティング・システムのインストール、CPUまたはメモリーの追加、あるいは単一インスタンスのデータベース環境からのOracle Real Application Clustersへの移行といった変更は、SQLパフォーマンスにも重大な影響を与えます。SPAによってこのような変更を行う際、SQLパフォーマンスが向上または劣化するかを容易に判定できるようになります。

• チューニングのリコメンデーションの実装：アドバイザ（ADDM、SQL Tuning Advisor、SQL Access Advisorなど）からのリコメンデーションを受け入れると、実装する前にユーザーがチューニングのリコメンデーションの影響を検証することが必要になる場合があります。たとえば、SQL チューニング・アドバイザが、パフォーマンスを向上させるために、特定のSQL文のSQLプロファイルを受け入れることを推奨する場合があります。しかし、推奨されたプロファイルを受け入れる前に、ユーザーは SPAを起動して、プロファイルの実装によって得られるパフォーマンスの向上を測定し、推奨を受け入れるかどうかを決定できます。

SQL PERFORMANCE ANALYZER の使用

図 1 に示すように、SPA は、5 つの主要なステップを通して、システム変更が SQL パフォーマンスに与える影響を評価します。

SQL ワークロードの取得

SPA を実行する前に、ユーザーは、分析の対象となる SQL ワークロードを示す本番システムの SQL 文のセットを取得する必要があります。ワークロードで取得する SQL 文が多いほど、ワークロードは、アプリケーションまたはシステムの状況をよく表し、パフォーマンス変更の予測が正確になります。したがって、ユーザーは、行う予定の変更のパフォーマンス分析から最良の結果を得るために、興味のある期間（月末のクローズ、四半期末のクローズ、日中の OLTP、夜間のバッチなど）に関して、できる限り多くの SQL 文を取得する必要があります。アプリケーションが発行した SQL 文またはシステム上で実行される SQL 文のすべてを取得することが理想的です。

(5)

図1：SQL Performance Analyzerワークフロー

SQL Tuning Set

テストする SQL 文の代表的なセットが取得され、SQL Tuning Set に保存されます。 SQL Tuning Set は、SQL ワークロードの管理に使用されるデータベース・オブジェクトです。SQL 文のテキスト、SQL 文のコンパイル可能な解析スキーマ、バインド値、実行カウント、計画と統計などを含む完全な実行コンテキストとともに、1 つ以上の SQL 文を取得し、永続的に保存するために使用できます。SQL 文の完全な実行コンテキストにより、設定アプリケーションおよび中間層の環境なしに、それらを再実行できます。この結果、顧客はハードウェアと時間を節約できます。カーソル・キャッシュ、Automatic Workload Repository（AWR）、既存の SQL Tuning Set、またはユーザーが提供するカスタムの SQL を含む多様な SQL ソースから、 SQL Tuning Set を投入できます。ユーザーが上記のソースから SQL Tuning Set を構築できるように、SQL Tuning Set インフラストラクチャが提供するサービスの中では、増分 SQL ワークロード取得がもっとも重要です。これは、システムの SQL ワークロードすべてを取得できるからです。増分取得は、ある期間に渡ってカーソル・キャッシュを繰り返しポーリングし、SQL Tuning Set に保存されたワークロード・データを更新することにより機能します。システムのワークロードを完全に取得するために必要な期間中は実行可能です。指定された期間に実行され、指定された基準（ユーザー、サービス、アクション・モジュールなど）を満たす文だけが取得されます。増分のカーソル・キャッシュによってシステムにかかるパフォーマンス・オーバーヘッドは、ごくわずかです。内部で行われた広範なテストでは、増分取得のオーバーヘッドが 1%未満であることが明らかになりました。

(6)

また、SQL Tuning Set は、データベース間で転送可能です。データベースに取得された SQL Tuning Set は、エクスポートして、他のデータベースにインポートできるので、たとえばリモートのパフォーマンス診断およびチューニングのために、データベース間で SQL ワークロードを転送できるようになります。

SQL ワークロードの転送

SPA は、本番データベースまたはテスト・データベース上で実行できます。テスト・データベースを使用する場合は、本番システムのデータベース環境にできる限り一致するようにテスト・データベース環境を構成する必要があります。このようにして、SPA は、SQL パフォーマンスに対するシステム変更の影響をより正確に予測できます。テスト・データベースを作成する方法は多数あります。たとえば、Oracle Recovery Manager（Oracle RMAN）の DUPLICATE コマンド、Oracle Data Pump、またはトランスポータブル表領域を使用する方法があります。最良の結果を出すには、テスト・データベースが、可能な限り本番システムに似ている必要があります。適切なSQLワークロードでSQL Tuning Setを作成した後、SQL Tuning Setを本番システムからエクスポートして、考慮中のシステム変更をテストするテスト・システムにインポートします。Oracle Enterprise Manager Grid Control 11gでは、特定の SQL Tuning Setをデータベースからデータベースへコピーするための簡単なインタフェースを提供しています。SQL Tuning Setは、APIを使用して手動でもコピーできます。詳細は、Oracle Technology Network（OTN）の文書『Optimizing the Optimizer: Essential SQL Tuning Tips and Techniques』1を参照してください。

SQL Pre-Change の実行

SQL ワークロードが取得され、SQL Tuning Set がテスト・システムに転送された後、システム変更を行う前に SPA を使用し、変更前のパフォーマンス・データを作成できます。SPA は、SQL Tuning Set に取得された SQL 文を実行し、各文に対して実行計画と実行の統計を生成します。データベースへの副作用を回避するために、問合せおよび DML 文の問合せの部分だけが実行されます。SPA は、最初の実行順や同時実行性とは無関係に、SQL 文を順番にそれぞれ分離して実行します。ただし、SPA が SQL 問合せを実行する順序はカスタマイズできます。たとえば、応答時間の点でもっともコストのかかる SQL 文から開始するように設定することが可能です。SQL ワークロードのサイズと SQL 文の複雑性により、SQL 文の実行がリソース集約的になり、時間がかかる場合があります。SPA は、実際に SQL 文を実行せずに、（EXPLAIN PLAN オプションにより）実行計画だけを生成するモードを提供します。この手法は、実行時間およびリソース・システムのリソース消費を軽減しますが、比較分析の結果はあまり正確でない場合があります。 1 _{http://www.oracle.com/technology/products/manageability/database/pdf/ow05/PS_S997_273997_106-1_FIN_v2.pdf} (英語)

(7)

変更

変更前のパフォーマンス・データを作成した後、テストするシステム変更を実装できます。前述したように、この変更は、データベースのアップグレード、新しい索引の作成、初期化パラメータの変更、オプティマイザの統計のリフレッシュなど、SQL 文のパフォーマンスに影響を与える可能性のある、あらゆる種類の変更になる場合があります。

SQL Post-Change の実行

テスト・システムで計画した変更を実装した後、SPA を再起動して SQL 文を再実行し、各 SQL 文の実行計画および実行の統計を再生成できます。この実行結果は、 SPA がパフォーマンス・データの変更前のバージョンと比較するために使用する変更後のパフォーマンス・データを表します。

SQL Performance の比較

SPA は、変更の前後の SQL 文のパフォーマンス・データを比較し、実行計画または SQL 文の実行の変更を特定するレポートを生成します。パフォーマンスの分析および比較中に、SPA はパフォーマンスへの変更の影響を計測する際、SQL 文の実行回数も考慮に入れます。この情報は、SQL ワークロードの取得中に収集され、各 SQL 文の SQL Tuning Set に保存されます。SPA は、実行回数を使用して、SQL ワークロード内の各 SQL の重要性（重み）を決定します。数秒で終わるが実行回数の多い SQL 文が、実行時間は長いが 1 回しか実行されない SQL 文と比較して、システムの性能に及ぼす影響が同等または大きくなることがあるためです。SPA は、こうした要因を考慮に入れて、全体的なパフォーマンスの向上と低下を予測します。デフォルトにより、SPA は、経過時間を比較のメトリックとして使用しますが、ユーザーは、ディスク読取り、CPU タイム、bugger gets などの代替のメトリックを分析のために選択できます。SPA の API を使用する場合は、分析用の比較メトリックなど、ユーザー定義の機能を指定できます。

比較レポート SQL

このSPAの比較レポート（図 3）では、提示されたシステム変更の後、SQLワークロード全体のパフォーマンスが大幅に向上しますが、効率の低下した実行計画もいくつかあります。SPAは全体のパフォーマンス向上および劣化を予測する際、 SQL文の実行回数も考慮に入れます。劣化が起きた場合、SPAは、ユーザーがSQL Tuning AdvisorまたはSQL Plan Baselineを使用して、それらを修正できるようにします。SQL Plan Baselineは、Oracle Database 11gに導入された新機能で、プラン・スタビリティの問題に対処します。詳細は、OTNのテクニカル・ホワイト・ペーパー『SQL Plan Management in Oracle Database 11g』2を参照してください。SQL Plan

Baselineを使用して以前の実行計画に戻り、効率の低下したSQL文の結果を修正します。SQL Tuning Advisorを使用して、効率の低下したSQL文を修正すると、その結果、オプティマイザが、効率の低下したSQL文のパフォーマンスの改善に役立つ代替の実行計画を選択することがあります。次の項の"データベースのアップグ 2_{http://www.oracle.com/technology/products/manageability/database/pdf/ow07/spm_whit} e_paper_ow07.pdf（英語）

(8)

レード"のユースケースの例で、SPA比較レポートの主要な情報を解釈する方法を説明します。

繰返し

パフォーマンスの比較により、効率が低下した SQL 文が明らかになると、問題を修正するために、さらに変更を行うことができます。たとえば、SQL Tuning Advisor を実行するか SQL 計画ベースラインを使用して、効率の低下した SQL 文を修正できます。次に、SQL Tuning Set を実行して、パフォーマンスを最初の実行と比較するプロセスを繰り返すことができます。分析の結果に満足するまで、これらのステップを繰り返します。

ユースケースの例

この項では、Oracle Enterprise Manager を使用して、アプリケーションの SQL ワークロードのパフォーマンスに対する一般的な変更の影響をテストする方法について説明します。Oracle Enterprise Manager インタフェースは、次のワークフローを通して、SQL ワークロードのパフォーマンスで変更の影響に関する評価をユーザーに説明します。

• Optimizer Upgrade Simulation：データベースのアップグレードをシミュレートし、SQL ワークロードへの影響を測定するために使用できます。 • Parameter Change：データベースの初期化パラメータの変更がどのように SQL パフォーマンスに影響を与えるかをユーザーが簡単に判定できるようにします。 • Guided Workflow：さまざまな変更に対する SQL 実行のパフォーマンスを比較するために使用できます。

SPAの機能は、Software and SupportタブのReal Application Testingカテゴリの下にあ るOracle Enterprise Managerインタフェースを使用してアクセスできます。図 2 に、 Oracle Enterprise Managerインタフェースで使用できる 3 つのワークフローを示します。

(9)

データベースのアップグレード

SPA は、SQL パフォーマンスに対するデータベース・アップグレードの影響を自動的にシミュレートできます。データベースの初期化パラメータ OPTIMIZER_FEATURES_ENABLE により、データベース・アップグレードのオプティマイザ・コンポーネントのシミュレーションが可能になります。すべての SQL 文は、オプティマイザを使用します。これは、Oracle Database の一部で、指定されたデータにアクセスするもっとも効率的な方法を判定します。10.2.0.2 および 11.1.0.1 バージョンのオプティマイザを使用する場合、SQL 実行のパフォーマンスの比較に SPA を使用できます。 SQL Tuning Set と比較メトリックを選択すると、2 つのリプレイ・トライアルが作成されます。最初のトライアルでは、ユーザーが選択した以前のリリースのオプティマイザをシミュレートして SQL パフォーマンスが取得され、2 番目のトライアルでは現在のリリースのオプティマイザが使用されます。システムが生成するリプレイ・トライアル比較レポートは、SQL リグレッションを評価します。パフォーマンスが劣化した場合、SQL Tuning Advisor を使用して、効率が低下した SQL 文に対する任意の SQL プロファイル・リコメンデーションを実装できます。図 3 では"Optimizer Upgrade Simulation"のユースケースを説明します。

このワークフローは、パッチセットのアップグレード（10.2.0.2 から 10.2.0.3）や小規模なアップグレード（10.1.0.3 から 10.2.0.3）など、Oracle Database 10g アップグレードにも使用できます。この場合、オプティマイザのバージョン 1 は、現在実行されているデータベース・リリースのバージョンで、オプティマイザのバージョン 2 は、シミュレートするバージョンです。11g の SPA 機能を利用するために、テスト・システムのコピーが 11g にアップグレードされていると仮定します。

(10)

図4：Optimizer Upgrade Simulationのワークフロー：比較レポート生成された SPA レポート（図 4）は 3 つのセクションで構成されています。最初のセクションでは、タスク情報、使用した SQL Tuning Set、リプレイ・トライアルの名前、および SQL 文がエラーに遭遇したかどうかを示します。2 番目のセクションは、分析に使用した比較メトリックに基づくグローバルな統計と、ワークロードの全体的な向上およびリグレッションの影響から構成されます。ワークロードのパフォーマンスは向上していますが、一部の SQL 文は効率が低下しています。SQL 文カウントでは、効率の低下した SQL 文がさらに詳しく分類されています。効率の低下した SQL 文を修正するために、2 つのオプション、SQL Plan Baseline および SQL Tuning Advisor を使用できます。SQL Tuning Advisor は、効率の低下した SQL 文の実行パフォーマンスを改善するために使用でき、SQL Plan Baseline は、既知の実行計画に戻すための支援を行います。任意のオプションを使用して、効率の低下した SQL 文を修正できます。3 番目のセクションでは、ワークロードに影響を及ぼす SQL 文のトップ 10 位までを表示し、ドリルダウンして SQL 文の詳細な統計を取得する機能を提供します。このユースケースでは、SQL Plan Baseline を使用して効率の低下した SQL 文を修正し、別の SPA トライアルを作成して効率の低下した SQL 文の修正後のトライアルと元の 10g パフォーマンスを比較しました。図 5 に示すように、リグレッションは存在しないので、本番リリースを安全にアップグレードできます。

(11)

図5：Optimizer Upgrade Simulation：効率の低下したSQL文をSQL Plan Baselineにより修正

した後の比較レポート

Parameter Change

データベースの 1 つの初期化パラメータを 2 つの値の間で変化させる場合、Parameter Change ワークフローにより、SQL Tuning Set へのパフォーマンスの影響をテストできます。たとえば、ソート領域のサイズを 1MB から 2MB へ増加する場合に、 SQL パフォーマンスを比較できます。図 6 に、顧客がレガシー・アプリケーションをルールベース・オプティマイザからコストベース・オプティマイザに移行するユースケースを示します。

SQL Tuning Set と比較メトリックを選択すると、SPA はタスクを作成し、初期化パラメータ・セットを元の値に設定して、トライアルを実行します。次に、パラメータ・セットを新しい値に設定して、2 番目のトライアルを実行します。システムが生成するリプレイ・トライアル比較レポートは、リグレッションを評価します。

(12)

図6："Parameter Change"用のOracle Enterprise Managerインタフェース

図 7 に、パラメータ変更のユースケースの SPA 比較レポートを示します。ワークロードに対する全体の影響が+24%と表示されていますが、効率の低下した SQL 文がいくつか存在します。SQL Plan Baseline または SQL Tuning Advisor を使用して、効率の低下した SQL 文を修正できます。説明のために、ここでは後者を選択します。「Run SQL Tuning Advisor」ボタンをクリックして、ワークロードのパフォーマンスが向上する可能性のある他の任意の計画を調べることができます。このケース（図 8 を参照）では、SQL Tuning Advisor が、効率の低下した SQL 文の 4 つに対して SQL Profiles を推奨しています。効率の低下した SQL 文を調整するためにそれらを実装できます。SQL Profiles を実装した後、別の SQL リプレイ・トライアルを作成して、SQL Profiles 実装後のパフォーマンスを評価できます。図 9 には、パフォーマンスのリグレッションは示されていません。そして、全体の影響が実際に 24%から 33%に増加しています。この 9%の増加は、問合せのパフォーマンスを改善する SQL Profiles によるものです。DBA は、ワークロードのパフォーマンスが向上することを確認し、本番で SQL Profiles とともにパラメータ変更を安全に実装できます。

(13)

図7：Parameter Changeワークフロー：パラメータ変更後の比較レポート

図8：Parameter Changeワークフロー：効率の低下したSQL文に対するSQL Tuning Advisor

(14)

図9：Parameter Changeワークフロー：効率の低下したSQL文をSQL Profilesで修正した後の比較レポート

Guided Workflow

"Guided Workflow"リンクを使用して、SQL ワークロードのパフォーマンスに影響を与える可能性のある、さまざまなシステム変更前後の SQL 文のパフォーマンスを比較できます。ここで使用する例では、DBA は、キー索引がいくつか欠落していると考え、本番データベースで作成しようとします。しかし、そうする前に、作成した索引が他の SQL 文に悪影響を及ぼさないことを確認したいと考えています。

(15)

図10："Guided Workflow"用のOracle Enterprise Managerインタフェース図 11 には、作成された索引が他の SQL 文に影響を及ぼさず、ワークロードに+8% の全体的な影響があることを示しています。したがって、この変更は、本番システムに安全に配置できます。図11：Guided Workflow：スキーマ変更後の比較レポート

DBMS_SQLPA パッケージ

SPA のプライマリ・インタフェースは Oracle Enterprise Manager ですが、SQL パフォーマンスに対するシステム変更の影響をテストする場合、DBMS_SQLSPA パッケージへのコマンドライン・インタフェースも使用できます。DBMS_SQLPA は、Oracle 11g に追加された新しいパッケージで、SPA の使用に必要な API を含んでおり、 SQL 文の実行計画の統計の生成、パフォーマンスの相違の比較などの機能を備えています。

(16)

他のすべての管理性アドバイザと同様に、SPA は共通の Advisory Framework をベースに構築されています Advisory Framework は、さまざまな管理性機能が生成する勧告を作成、保存、検索するための共通のインフラストラクチャ・サポートを提供します。したがって、すべての SPA プロシージャは、分析タスクと呼ばれるアドバイザ・タスク・オブジェクトとともに動作します。SPA の分析タスクは、実行の入力および結果のコンテナとして使用します。 DBMS_SQLSPAパッケージを使用してSPAと相互に作用する場合、最初のステッ プは、常にcreate_analysis_taskプロシージャをコールして、分析タスクを作成する ことです。このプロシージャは、アドバイザ・タスクを作成し、ユーザーが提供した入力引数に応じて、対応するパラメータを設定します。次の例で、このプロシージャのコールを説明します。 dbms.sqlpa.create_analysis_task(sqlset_name => ‘my_sts’, task_name => ‘my_spa_task’, description => ‘test upgrade from 10g to 11g’);

例では、タスクを作成する前に、パフォーマンス分析に使用する SQL ワークロードが、'my_sts'と呼ばれる SQL Tuning Set の形式で、システムで使用可能であると仮定します。作成する分析タスクの名前は'my_spa_task'です。分析タスクが正常に作成された時点が初期状態となります。次にタスクを 2 回実行する必要があります。1 回目にはテストの対象である変更を行う前のSQLパフォーマンス・データを構築し、2 回目には変更後の対応するSQLパフォーマン ス・データを生成します。これには、次のようにexecute_analysis_taskプロシージャ を起動して実行します。 dbms_sqlpa.execute_analysis_task(task_name => ‘my_spa_task’, execution_type => ‘test execute’, execution_name => ‘before_change’); 上記の例は、システム変更前のプロシージャ・コールを示しています。このプロ シージャは、'test execute'にセットされたexecution_type引数を使用して起動されま す。この引数は、実行の統計および実行計画を生成するために、SQLチューニン グ・セットのすべてのSQL文を実行するようSPAに要求します。execution_type引 数を'explain plan'にセットすると、アナライザは実行計画だけを生成します。その結果として生じる計画と統計は、分析タスク内で"before change"と呼ばれるコンテナに保存されます。"before change"は、その特定のタスク実行に付与された名前です。Oracle Enterprise Managerでは、このタスク実行コンテナをSQLトライアルと呼びます。

(17)

変更を行った後、同じ引数を使用して同じプロシージャが再びコールされますが、タスク実行の名前は、次に示すように（'after_change'など）異なっています。

dbms_sqlpa.execute_analysis_task(task_name => ‘my_spa_task’, execution_type => ‘test execute’, execution_name => ‘after_change’); タスク実行が始まった後、ユーザーは適切な DBMS_SQLPA プロシージャを使用して、タスクの取消し、中断、リセットをいつでも行うことができます。また、ユーザーは、V$ADVISOR_PROGRESS ビューを利用することによって、タスク実行のステータスを確認できます。このビューには、タスク実行のそれまでの経過に関する情報が表示されます。この情報には、タスク実行の残り時間、検出事項の数、SQL Tuning Set で処理された文の数が含まれます。 'after_change'SQL実行データが作成された後、ユーザーはそれをパフォーマンス・データの'before-change'バージョンと比較できます。これは、次のようにプロシー ジャexecution_analysis_taskの 3 回目のコールを行うことで実行できます。 dbms_sqlpa.execute_analysis_task( task_name => ‘my_spa_task’,

execution_type => ‘compare performance’, execution_name => ‘analysis_results’, execution_params => dbms_advisor.arglist(‘execution_name1’, ‘before_change’, ‘execution_name2’, ‘after_change’, ‘comparison_metric’, ‘buffer_gets’)); 上記のプロシージャ・コールでは、SPAを入力してパフォーマンスを比較するた めに、"execution type"が'compare performance'に設定されています。また、プロシー ジャ・コールは引数execution_paramsを使用して、比較に対して特異的ないくつか のタスク・パラメータを設定します。execution_paramsパラメータは、指定された タスク実行のための（名前と値の）ペアとして指定されます。この例では、パラメータのexecution_name1 が、'before_change'実行の名前に設定されています。これは、SQLワークロードのパフォーマンス・データのベースラインを構成します。パラメータexecution_name2 は、実行'after_change'内に保存されているパフォーマンス・データを示し、パラメータ'comparison_metric'は、SQLパフォーマンスの比較にSPAが使用する必要のあるメトリックを決定する'buffer_gets'を示しています最後のパラメータに利用可能な値を以下に示します。elapsed_time（デフォルト）、 cpu_time、buffer_gets、disk_reads、direct_writes、optimizer_cost 最後に、分析タスクの実行が完了すると、以下に示すようにreport_analysis_taskプ ロシージャをコールすることで、比較結果を生成できます。

var rep clob; set long 10000

:rep := dbms_sqlpa.report_analysis_task(task_name => ‘my_spa_task’); print :rep

(18)

report_analysis_taskは、デフォルトで最後のタスク実行のテキスト・レポート（タ イプCLOB）を生成します。この例ではパフォーマンス比較に使用されます。レポートには、SQL文のパフォーマンス向上または効率の低下とは無関係に、パフォーマンスの変更がワークロードにもっとも影響を及ぼしたSQL文のトップ 100 位までの結果の要約と詳細が含まれています。また、効率の低下したSQL文の分析結果とチューニングのリコメンデーションも表示しています。

実際のベスト・プラクティス

SQL ワークロードの取得

"増分 STS 取得"メソッドを使用して、代表的な SQL ワークロードを正確に取得します。増分 STS 取得によって生じるオーバーヘッドは、ごくわずか（1%未満）であるため、問題なく本番システムに使用できます。

テスト・システム

SPA 分析は、リソース集約的になる可能性があるので、テスト・システムでの実行を推奨します。本番システムで実行する場合は、必ずメンテナンス時間枠で実行し、システムが予備のシステム・リソースを使用して SPA タスク・ロードを処理できることを確認します。また、テスト・システムを使用する場合は、構成が本番システムに類似し、オプティマイザ統計が同等であるかどうかを確認することが重要です。

パフォーマンスの比較

信頼できる結果を得るために、経過時間や CPU 時間など、いくつかの異なるメトリックを使用して、変更前後のパフォーマンスを比較することを推奨します。

リグレッション修正

SQL Tuning Advisor と SQL Plan Baseline は、両方とも SQL リグレッションの修正に使用できます。SQL Tuning Advisor は、代替の実行計画を調べることによってパフォーマンスを改善でき、SQL Plan Baseline の機能は、以前の計画に戻します。

本番のチューニング

チューニングのリコメンデーションを実装するバリデーション・フェーズでの可視性を制限するために、セッションまたはプライベート・テストの使用を推奨します。SQL Profiles は、指定された"sqltune_category"セッション属性を使用して、プライベート・セッションでテストできます。alter session コマンドを使用して、これを実行する方法を以下から参照してください。バリデーション・フェーズで、SQL Profiles が、効率の低下した SQL 文のためになることが明らかになると、"sqltune_ category"を'DEFAULT'に変更できます。これは、すべてのユーザーが実装された SQL Profile を使用できることを意味します。

(19)

結論

このホワイト・ペーパーでは、Oracle Database 11g で導入された SPA の機能を説明しました。SPA を使用して、ユーザーは SQL 文のパフォーマンスに対するシステム変更の影響を予測し、潜在的なパフォーマンス低下を修正できます。SPA は、 SQL Tuning Set、SQL Plan Management、SQL Tuning Advisor の機能とうまく統合され、SQL ワークロードのパフォーマンス管理のための包括的なソリューションをユーザーに提供します。SQL ワークロードの入力として、SQL Tuning Set を使用し、SQL パフォーマンス・データのさまざまなバージョンの構築と比較を支援し、潜在的なパフォーマンス上の問題を克服するためのチューニング・リコメンデーションを生成します。効率の低下した SQL 文を修正するために、ユーザーは SQL Plan Baseline を作成するか、SQL Tuning Advisor が推奨する SQL Profiles を実装できます。次の表に、SQL パフォーマンスに影響を与える変更のテスト・プロセスを SPA がどのように簡素化するかについて示します。 Oracle 11g 以前 Oracle 11g アプリケーションの SQL ワークロードを手動で取得するアプリケーションの SQL ワークロードを自動的に取得する一部の SQL ワークロード完全な/全体的な SQL ワークロード SQL を手動で実行して、実行計画および実行の統計を手動で生成する SQL 実行計画および実行の統計を自動的に生成する数か月に及ぶ手動の分析数分間の自動化された分析 SQL リグレッションの手動修正 SQL リグレッションの自動修正高リスク、高コスト低リスク、低コスト

上の表からわかるように、SPA を使用した Oracle 11g と比較すると、Oracle 11g 以前のリリースでは、DBA はこのような基本的なタスクに非常に多くの労力と時間を費やしていました。SPA は、アプリケーション変更をテストするために、包括的でありながら簡単に使用できるソリューションを提供します。エキスパート・ユーザーだけでなく、経験の浅いユーザーも同様に効果良く使用できます。

(20)

SQL Performance Analyzer 2007 年 11 月

著者：Khaled Yagoub、Prabhaker Gongloor（GP）

Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. 海外からのお問合せ窓口：電話：+1.650.506.7000 ファクシミリ：+1.650.506.7200 www.oracle.com

本文書は情報提供のみを目的として提供されており、ここに記載される内容は予告なく変更されることがあります。本文書は一切間違いがないことを保証するものではなく、さらに、口述による明示または法律による黙示を問わず、特定の目的に対する商品性もしくは適合性についての黙示的な保証を含み、いかなる他の保証や条件も提供するものではありません。オラクル社は本文書に関するいかなる法的責任も明確に否認し、本文書によって直接的または間接的に確立される契約義務はないものとします。本文書はオラクル社の書面による許可を前もって得ることなく、いかなる目的のためにも、電子または印刷を含むいかなる形式や手段によっても再作成または送信することはできません。Oracle は米国 Oracle Corporation およびその子会社、関連会社の登録商標です。その他の名称はそれぞれの会社の商標です。