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

シンジ＆アヤノの

実践データベース性能テストの極意：

Oracle Real Application Testingを使ってみよう

第2回　SQL Performance Analyzer～SQLチューニング・セットを作成する～

第1回では、システムパフォーマンステストの重要性からテストソリューションの必要性、Real Application Testingの概要とポイントを紹介しました。

今回からは、実際の操作手順とその動作結果イメージを見ていきましょう。

さっそく、SQL Performance Analyzer の動作イメージを紹介していきたいと思いますが、今回ご紹介するパートを全体のワークフロー図で示すと、以下（図１）のようになります。

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

第2回では、まずSQL Performance Analyzerを動かすにあたってはじめに行う「(1) SQLチューニング・セットの作成」と「(2) SQLチューニング・セットのエクスポート・インポート」の手順について確認しましょう。
SQL Performance Analyzerを動かすには、Oracle Enterprise Manager を利用する方法の他に、DBMS_SQLTUNE やDBMS_SQLPAなどのPL/SQLパッケージを使用してコマンドラインで行うことも可能ですが、本記事では、Oracle Enterprise Manager Cloud Control 12cを利用した実行例について紹介します。

※注意

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

1. SQLチューニングセットの作成

まずOracle Enterprise Manager Cloud Control 12cにログインします。

pic 2

ターゲットの中からSQLチューニング・セットを作成する対象のデータベースインスタンス（本番環境のSQLワークロードを取得する場合は本番環境）を選択し、[パフォーマンス] タブから、[SQL] → [SQLチューニング・セット]をクリックします。

pic 3

データベース・ログイン画面に遷移したら、SQLチューニング・セットを作成するユーザーでログインします。

pic 4

コラム：SPAで使用するデータベース・ユーザー

アヤノ ここでログインするユーザーはSYSユーザーを使えばいいんですか？
シンジ 実はSYSを使うと、SQLチューニング・セットのエクスポートのところで、ORA-19381のエラーが発生するからダメなんです。でもこれは仕様上の制限なんですよ。
アヤノ じゃあ、SYSTEMユーザー？
シンジ SYSTEMユーザーを使うことも可能なのですが、SPAでは内部的にそのスキーマ内にオブジェクトを生成したりもするので、SPA(RAT)としての専用ユーザーを作成して、DBAロール権限を付与した上で使うとよいでしょう。

shinji

◇ポイント◇

  • SQLチューニング・セットのエクスポートはSYSユーザーでは実行できません。(SYSユーザーで実行しようとするとエラーが発生します。)
  • またSQL Performance Analyzerを実行するためには、さまざまな権限が必要となるため、DBAロール権限を与えた専用のユーザーを作成して、そのユーザーを使用することをお奨めします。
※本記事では、「apps」というDBA権限を与えたユーザーを使って、以降の手順を実施します。

SQLチューニング・セットの画面で、「作成」をクリックします。

pic 6

SQLチューニング・セット名と所有者を入力し、必要に応じて説明を記載し、「次へ」をクリックします。

pic 7

SQLチューニング・セットの対象となるSQLをどのように収集するかを選択します。 Oracle DatabaseではSQLを収集する方法として様々な方法が提供されていますが、ここでは現在のカーソル・キャッシュから、1度だけ収集する方法を選択し、「次へ」をクリックします。

pic 8

コラム：対象SQLの収集について

アヤノ カーソル・キャッシュから収集する、ということは、今キャッシュ上に乗っているSQLが対象になるということですよね。
シンジ そうですね。だからキャッシュからエージアウトしているような過去のSQLや、DB再起動を行った場合の再起動前に実行されたSQLなどは対象になりません。
アヤノ 過去のSQLを収集したければ、収集方法として、AWRスナップショットを選択するとか？
シンジ そのとおり。今まさに意図的に実行したSQLを収集したいようなときにはカーソル・キャッシュから、本番環境などで過去の特定期間に実行されていたSQL群を収集したいようなときにはAWRスナップショットから、という具合に使い分けができますね。

ayano

◇ポイント◇

SQLチューニング・セットの対象SQLの収集は、現在のカーソル・キャッシュ以外にも、以下の情報をもとに作成することができます。

  • カーソル・キャッシュより収集（期間、間隔を指定）
  • AWRスナップショット
  • AWRベースライン
  • ユーザー定義ワークロード

    • SQLを抽出する条件を入力します。本記事の手順では、サンプル用のSQLに限定するため、SQLテキストのLIKE条件でフィルタしていますが、通常は、スキーマ名を指定して特定の業務スキーマに限定する方法での抽出、経過時間が○秒以上という指定で一定の高負荷なSQLに限定して抽出するなど特定しやすい方法を検討することをお勧めします。

    pic 11

    ◇ポイント◇

    初期表示されているフィルタ項目以外にも、「フィルタまたは列の追加」をクリックすることで、「計画ハッシュ値」や「CPU時間（秒）」「モジュール」「バッファ読取り」「ディスク読取り」「処理された行」などさまざまな条件でSQLをフィルタすることができます。

    SQLを収集しSQLチューニング・セットを作成するためのジョブ名を指定して、スケジュールは「即時」を選択し、「次へ」をクリックします。本記事の手順では、デフォルトのジョブ名を使用します。

    pic 12

    実行前の確認画面が表示されるため、内容を確認した上で、「発行」をクリックします。

    pic 13

    ジョブが実行され、SQLチューニング・セットが作成されたことが確認できます。

    pic 14

    SQLチューニング・セットの名前をクリックすると、その中に含まれているSQLが確認できます。

    pic 15

    コラム：SQLチューニングセット（STS）に含まれる内容

    アヤノ STSに含まれる内容は、たしかSQL文や実行統計などでしたよね。
    シンジ そうですね。 EMの画面でSQLチューニング・セットを選択すると、SQL_ID毎にSQL文やその他各種実行統計などが一覧表示されていますね。
    アヤノ なるほど。このSTSの情報を元にインポート先のテスト環境でSQLワークロードを再現させて、SQL文レベルでのシステム変更前後のパフォーマンス比較ができるのですね。
    シンジ そうです。EMでは、画面から作成したSTSの情報が簡単に確認できるので、とてもわかりやすいですね。

    2. SQLチューニングセットのエクスポート

    SQLチューニング・セットの一覧画面で、対象のSQLチューニング・セットを選択し、「ファイルにエクスポート」をクリックします。

    pic 17

    SQLチューニング・セットのエクスポートファイル（ダンプファイル）を出力するため、出力ファイルを格納したい場所のディレクトリ・オブジェクトを選択します。

    入力が終わったら、「ＯＫ」をクリックします。

    pic 18

    SQLチューニング・セットのエクスポート・ジョブが実行されます。 ジョブの状況を確認したい場合は、「ジョブの詳細表示」をクリックすると、ジョブが終了しているか確認できます。

    pic 19

    指定したディレクトリ・オブジェクトの場所にエクスポートファイル（エクスポートされたダンプファイル）が出力されていることを確認したら、そのファイルをインポートする環境にコピーします。

    ※インポートの際もディレクトリ・オブジェクトを使用するため、インポートするデータベースのディレクトリ・オブジェクトで定義している場所にファイルを格納します。

    3. SQLチューニング・セットのインポート

    ターゲットの中からSQLチューニング・セットをインポートする対象のデータベースインスタンス（通常はSQLの性能テスト／比較を行うテスト環境）を選択し、[パフォーマンス] タブから、[SQL] → [SQLチューニング・セット]をクリックします。

    pic 20

    データベース・ログイン画面に遷移したら、SQLチューニング・セットを作成したときと同様に、インポートするデータベースインスタンスでもDBA権限を与えたSPA専用に作成したユーザー（本記事では「apps」ユーザー）でログインします。

    pic 21

    SQLチューニング・セットの一覧画面で、「ファイルからインポート」をクリックします。

    pic 22

    エクスポートのときと同様に、インポート・ファイル（インポート対象のダンプファイル）を格納したディレクトリ・オブジェクトを指定し、インポート・ファイルの欄に対象のダンプファイル名を指定します。 入力が終わったら、「ＯＫ」をクリックします。

    pic 23

    SQLチューニング・セットのインポート・ジョブが実行されます。

    pic 24

    「リフレッシュ」をクリックすると、SQLチューニング・セットの一覧画面にインポートしたSQLチューニング・セット「SPA_TEST01」が表示されます。

    pic 25

    4. まとめ

    いかがでしたでしょうか。今回はSQL Performance Analyzerを操作するにあたり、はじめに本番環境でSQLチューニング・セットを作成し、そのSQLチューニング・セットをエクスポートしてテスト環境にインポートするまでの手順について紹介しました。

    SQLチューニング・セットとして収集するSQLの条件を設定することで、柔軟に対象SQLを選定できることが確認できたかと思います。
    次回は、「SQL Performance Analyzer ～SQLパフォーマンスを比較して評価する～」です。

    是非ご覧ください。