>> 連載トップページに戻る


～オラクルコンサルタントが語る～

シンジ＆アヤノの
実践データベース性能テストの極意：
Oracle Real Application Testingを使ってみよう


第3回　SQL Performance Analyzer～SQLパフォーマンスを比較して評価する～

第2回では、SQL Performance Analyzerを操作するにあたり、はじめに本番環境でSQLチューニングセットを作成し、そのSQLチューニングセットをエクスポートしてテスト環境にインポートするまでの手順について実施しました。
第3回では、次にそのSQLチューニングセットを使用して、SQL Performance Analyzerを動かし、システム変更前後でのSQLパフォーマンスを比較する手順について紹介します。
今回ご紹介するパートを全体のワークフロー図で示すと、以下（図１）のようになります。


図１　SPAワークフロー全体イメージ
pic 1

※注意
本記事に記載している内容は、Oracle Enterprise Manager Cloud Control 12cを用いたReal Application Testing機能のおおまかな操作手順とその動作結果イメージを理解していただくことを目的としています。使用するOracle Enterprise Manager のバージョンによって、操作手順が異なることがあります。システムおよびパッケージの開発や実行環境で使用する際には、関連ドキュメントを参照の上、実施してください。また、本記事は単に情報として提供されるものであり、内容に誤りがないことの保証や弊社サポート部門へのお問い合わせはできませんのでご理解ください。

1. システム変更前のSQL実行

ターゲットの中からSQLチューニングセットをインポートした（SQLのパフォーマンス比較を実施するテスト環境）対象のデータベースインスタンスを選択し、[パフォーマンス] タブから、[SQL] → [SQLパフォーマンス・アナライザ]をクリックします。
pic2

データベース・ログイン画面に遷移したら、DBA権限を与えたSPA専用に作成したユーザー（本記事では「apps」ユーザー）でログインします。
pic3

SQLパフォーマンス・アナライザの画面で、「SQLパフォーマンス・アナライザ・ワークフロー」の中から、「ガイド付きワークフロー」をクリックします。
pic4

 

コラム：SPAワークフローの選択

アヤノ SPAワークフローの選択の画面では、様々な手順が用意されていますが、例えば、定期メンテナンス等でDatabaseのパラメータ変更の影響をテストしたいケースは『パラメータの変更』を選択できますか？
シンジ そうですね。『パラメータの変更』のワークフローでは、EMの画面上で変更する初期化パラメータとその値の設定ができ、パラメータ変更によるパフォーマンスへの影響をテストできます。そういった使い分けもできますが『ガイド付きワークフロー』は、システム変更の種類を問わない汎用的に使用できるフローになっているので、まずはこの手順を覚えておくのがおススメです。
アヤノ なるほど！


◇ポイント◇


実際に行うシステム変更の内容に合わせてワークフローを選択することで、画面上でそれぞれのケースに合わせた手順がナビゲートされます。
  • 9iまたは10.1からのアップグレード
  • 10.2または11gからのアップグレード
  • パラメータの変更
  • オプティマイザ統計
  • Exadataシミュレーション
  • ガイド付きワークフロー


次に、システム変更前後のSQLパフォーマンス比較レポートの表示までのステップが表示された画面に遷移するので、ステップ１「SQLチューニング・セットに基づくSQLパフォーマンス・アナライザのタスクの作成」の「実行」欄をクリックします。
pic5

SQLパフォーマンス・アナライザの実行のために、使用するSQLチューニングセットを指定してタスクを作成します。
「名前」欄に、任意のタスク名「SPAT1」を入力し、SQLチューニング・セットの「名前」欄の右にある虫眼鏡をクリックします。
pic6

SQLチューニング・セットの一覧が表示されるため、前回インポートしたSQLチューニングセット「SPA_TEST01」を選択し、「選択」をクリックします。
pic7

前の画面に戻り、SQLチューニング・セットの名前が入力されていることが確認できます。次に「作成」をクリックします。
pic8

ガイド付きワークフローのステップ一覧の画面で、ステップ１「SQLチューニング・セットに基づくSQLパフォーマンス・アナライザのタスクの作成」の「ステータス」欄がチェックマーク(完了)に変更されていることが確認できます。次に、ステップ２「初期環境へのSQL試行の作成」の「実行」欄をクリックします。
pic9

システム変更前のSQL試行として任意の名前を「SQL試行名」に入力します。（本記事では、「SPAT1_BEFORE_CHANGE」としています。）
次に、「試行環境による結果の決定付け」の下にある「試行環境設定済み」にチェックを入れます。これは、SQLパフォーマンス比較のための測定をするにあたり、環境の設定が正しいあるべき状態であることを確認するためのものです。
次に、スケジュールは「即時」を選択した状態で、「発行」をクリックします。
pic10

 

コラム：SQL試行名の命名

アヤノ うーん、SQL試行名を何にすればいいか迷ってしまいます。日付とか処理名とかそういったものがいいんでしょうか？
シンジ そうですね。そのSQL試行がシステム変更前（BEFORE）なのか、変更後（AFTER）なのか、どのSTSを対象とした何の変更テスト(試験項目)なのかを識別できるような名前をつけてはどうでしょうか。例えば、「<STS名>_<試験項番>_<BEFORE or AFTER>_<実施日付>」などです。
アヤノ なるほど！テスト実施前に、プロジェクト側で命名規則などを設けると統一できていいかもしれませんね。
シンジ そうですね。特にテストフェーズ等は複数回テストを実施する場合があるので、何のテストをいつ実施したかが明確になるようにしたほうがいいですね。ただし、SQL試行名の長さは「30byteまで」という制限があるので、長くなりすぎないようにも注意が必要です。

ayano

◇ポイント◇


SPAを実行する際のSQL試行名も、命名規則を設けて設定すると、あとあと比較や確認をするときにわかりやすく便利です。ちょっとした工夫ですが、煩雑にならないように管理するためには重要な事項と言えるでしょう。
※ただし、SQL試行名は 「30byteまで」 という制限があります。
補足情報を追加したい場合は、「SQL試行の説明」欄(256byteまで)が使用できます。


SQL試行が作成されSQLが実行されます。ステータス欄が時計マーク(実行中)になっているので、「リフレッシュ」をクリックします。
pic11

ステータスがチェックマーク(完了)に変更されたことを確認します。
pic12

 

2. システム変更を実施

ステップ２「初期環境へのSQL試行の作成」が完了したら、ステップ３に移る前に、この環境に対して、アップグレードやパッチ適用、初期化パラメータ変更など、実際に計画されているシステム変更を実施します。
※システム変更については、SQL Performance Analyzerの手順とは別のものであるため、本記事では手順は割愛します。
システム変更を実施したら、システム変更前と同様に、データベース・インスタンスを起動している状態にします。（インデックス作成や、オプティマイザ統計の変更など、データベース・インスタンス停止を伴わない変更の場合は、あらためてデータベース・インスタンスを再起動する必要はありません。）

再びEnterprise Manager Cloud Control 12c で、対象のデータベースインスタンスを選択し、[パフォーマンス] タブから、[SQL] → [SQLパフォーマンス・アナライザ]をクリックすると、「SQLパフォーマンス・アナライザのタスク」のところに、ステップ２まで終了したさきほどのタスクが表示されているので、その名前「SPAT1」をクリックします。
pic13

 

3. システム変更後のSQL実行

ステップ３「変更された環境へのSQL試行の作成」の「実行」欄をクリックします。
pic14

システム変更前のときと同様に、SQL試行として任意の名前を「SQL試行名」に入力し（本記事では、「SPAT1_AFTER_CHANGE」としています）、「試行環境による結果の決定付け」の下にある「試行環境設定済み」にチェックを入れます。スケジュールは「即時」を選択した状態で、「発行」をクリックします。
pic15

ステップ３「変更された環境へのSQL試行の作成」が完了したことを確認したら、次にステップ４「ステップ2とステップ3を比較」の「実行」欄をクリックします。
pic16

 

4. システム変更前後のパフォーマンス比較

「試行1の名前」にステップ２で作成したSQL試行名「SPAT1_BEFORE_CHANGE」を、「試行2の名前」にステップ３で作成したSQL試行名「SPAT1_AFTER_CHANGE」をそれぞれ選択し、比較メトリックで「バッファ読取り」を選択し、スケジュールは「即時」を選択した状態で、「発行」をクリックします。

pic17

コラム：比較メトリックの選択

アヤノ 比較メトリックに様々な選択肢がありますね。どうして『バッファ読取り』を選択しているのですか？
シンジ SPAは主にシステム変更における個々のSQLのパフォーマンス変動のチェックのために利用しますが、SQL単体でのパフォーマンス変動の要素として一番注目すべき点は「実行計画の変動」なんです。「実行計画の変動」において、「改善」しているのか、「劣化」しているのか、または「変動なし」なのか、をざっくり簡単に判断するのに使える基準が『バッファ読取り』なんです。
アヤノ なるほど！『バッファ読取り』が減少していれば「改善」、増加していれば「劣化」、同じであれば、「変動なし」ということですね。実行計画の変動とその優劣を判断するのに一番わかりやすい比較メトリックが『バッファ読取り』なのですね。
シンジ そうです。それに、SQL試行比較を実施した後に、EMの比較レポート画面上からSQL_IDをクリックすると、『経過時間』や『CPU時間』『ディスク読取り』などの比較結果も表示されるので、そこでも確認できるんですよ。

◇ポイント◇


shinji比較メトリックには、「バッファ読取り」の他にも、「経過時間」や「CPU時間」「物理I/O」「オプティマイザ・コスト」など複数の項目が用意されており、さまざまな観点でSQLワークロードを比較することができます。ただし純粋に「SQLの実行計画の変動有無や、変動した場合はその優劣」という観点で比較する場合は、「バッファ読取り」を使用するのがよいでしょう。（「経過時間」や「物理I/O」で比較した場合、たとえば、バッファキャッシュの状況や、DBインスタンスやDBサーバ自体の利用状況によって、誤差が生じるため、適切に判断がしにくいことがあります。） なお、比較メトリックに指定した項目以外についても、ステップ５でSQL単位では比較表示して確認することができます。


ステップ４「ステップ2とステップ3を比較」が完了したことを確認したら、次にステップ５「試行比較レポートの表示」の「実行」欄をクリックします。
pic18

「予測されるワークロードバッファ読取り」のところで全体のSQL合計でのパフォーマンスの優劣と、「SQL文の数」のところでSQL個別での「改善」「低下」「変更なし」の件数がそれぞれグラフ化されます。SQL試行2のほうが、全体のSQL合計で、「バッファ読取り」が多くなっていることが確認できます。問題となっているSQLを確認するために、「SQL文の数」の「低下」のグラフ部分をクリックします。
pic19

「低下」したSQLに限定して、リスト表示されます。詳細内容を見るために、SQL_IDをクリックします。
pic20

SQL文と各項目での比較結果、さらに下にスクロールすると、システム変更前とシステム変更後の実行計画が表示されており、比較することができます。
pic21 pic22 pic23

 

5. まとめ

第２回で作成したSQLチューニングセットを使用して、今回はSQL Performance Analyzerで、システム変更前後でのSQLパフォーマンスを比較する手順について紹介しました。いかがでしたでしょうか。システム変更前後でのSQLパフォーマンスの違いが、グラフィカルに確認でき、さらにSQL単位でドリルダウンすることで、各項目レベルで詳細に差分を確認できることを理解いただけたかと思います。

今回のポイントとして挙げた点をおさらいすると、以下のとおりです。

  • SPAのワークフローは、『ガイド付きワークフロー』の手順を覚えておくと、 汎用的に使用できる
  • SQL試行名は、識別しやすい命名規則を設けておくと管理しやすい
  • SQL試行比較の比較メトリックは、「実行計画変動」の観点では『バッファ読取り』 を選択するのがよい

比較ができたら、次はパフォーマンスが低下したSQLについて、改善させましょう。 次回は、「SQL Performance Analyzer～SQLチューニング・アドバイザでチューニングする～」です。 是非ご覧ください。


<< 第２回へ戻る　I　第４回へ進む >>

>> 連載トップページに戻る