Oracle Real Application TestingのSQL Performance Analyzer(SPA)のExplain PlanとConvert SQLSETを実行してみた

はじめに

Oracle Real Application Test(RAT) は、Oracle Database 11gから新機能として実装されているもので、データベースを中心とした移行、アップグレード、パッチ適用などのシステムの変更に伴うリスクを削減するためのテストオプションです。Enterprise Editionでオプションのライセンスを購入することで使用できます。
RATにはSQL Performance Analyzer(SPA) という本番環境のSQLワークロードをテスト環境で再現し、SQL単位のパフォーマンス比較、実行計画の変更確認、SQL互換性(エラー有無)のチェックができる機能があります。SPAでは基本的なワークフローは、変更前の環境で1回目のSPA試行、変更後の環境で2回目のSPA試行を実行し、レポートを出力してそれぞれの結果を比較するといった順番で実行します。また、SPA試行には3つのテストモードがあります。

SPA試行の3つのテストモード

テストモード名	内容	備考
Test Execute	STS内のSQL文を実行し、実行計画や性能を記録する	テストデータが必要
Convert SQLSET	STS内の実行計画、統計情報をテスト結果として使用する	変更前環境と同等の環境を準備できない場合に使用する
Explain Plan	STS内のSQL文の実行計画を生成し、記録する	テストデータが使用できない場合に使用し、性能劣化が検出できない

今回は、1回目のSPA試行でConvertSQLSET、2回目のSPA試行でExplain Planを実行します。
この2つのテストモードの組み合わせは、本番(変更前)環境と同様の環境の準備とテスト環境にテストデータの準備ができない場合に実行するテストモードの組み合わせで、システム変更による性能劣化を検出することはできませんが、SQLの互換性や実行計画の変動を検出したいという場合に実行できるテストモードになります。テストデータは不要ですが、データベースオブジェクトの定義情報とオプティマイザ統計情報の準備は必要となります。

検証概要

検証環境とワークフロー

• Oracle Cloud Infrastructure(OCI) MarketplaceのOracle Enterprise Manager 13c Workshopの環境を使用します
• 本番環境とテスト環境は実際はそれぞれ別環境に構築されることが想定されますが、今回は検証を目的とするため、同一コンピュート上に構築されています
• STSの取得とインポート、SPAの実行操作(上図の5., 6., 7., 8.)はEnterprise Manager(EM)を使用して実行します
• データベース・オブジェクト定義情報とオプティマイザ統計情報の移行(上図の1., 2., 3., 4.)はコマンド操作で実行します

データベース・オブジェクト定義情報の移行

本項の作業概要図

STSに取得するSQL文に含まれるスキーマやオブジェクトの定義情報をテスト環境に移行します。
移行ツールとしてOracle Data Pumpを使用します。
expdpコマンドとimpdpコマンドのオプションにcontent=metadata_onlyを指定することで、定義情報のみをエクスポート、インポートするようにできます。
Data Pumpによる移行方法の詳細な手順については本記事のテーマとは少し外れるため、ここでは記載しません。
以下に実行したexpdpコマンドとimpdpコマンドを示します。（本記事では、STSを取得する際に実行するワークロードに含まれるappsスキーマとscottスキーマの定義情報を移行します。）

expdp dpuser/dpuser@hr directory=dp_dir dumpfile=dmp_file_mto.dmp logfile=exp_1.log schemas=apps content=metadata_only
expdp dpuser/dpuser@hr directory=dp_dir dumpfile=dmp_file_2_mto.dmp logfile=exp_2.log schemas=scott content=metadata_only
impdp dpuser/dpuser@oltp_cl2 directory=dp_dir dumpfile=dmp_file_mto.dmp logfile=imp_1.log
impdp dpuser/dpuser@oltp_cl2 directory=dp_dir dumpfile=dmp_file_2_mto.dmp logfile=imp_2.log

以下のようにテスト環境に定義情報のみが移行できているか確認をします。（以下の例では、本番環境とテスト環境のそれぞれでappsスキーマとscottスキーマが所有するテーブル数の合計を確認し、scottスキーマのADM_CC_FEATUREVALUEテーブルにレコードが含まれていないことを確認しています。)

/* 本番環境のテーブル数 */
SELECT count(*) 
FROM   ALL_TABLES
where owner='APPS' or owner='SCOTT'
ORDER BY OWNER,TABLE_NAME;
  COUNT(*)
----------
        20
/* テスト環境のテーブル数 */
SELECT count(*) 
FROM   ALL_TABLES
where owner='APPS' or owner='SCOTT'
ORDER BY OWNER,TABLE_NAME;
  COUNT(*)
----------
        20

/* 本番環境のscottのADM_CC_FEATUREVALUEテーブルのレコード数 */
SQL> select count(*) from scott.ADM_CC_FEATUREVALUE; 
  COUNT(*)
----------
      3047
/* テスト環境のscottのADM_CC_FEATUREVALUEテーブルのレコード数 */
SQL> select count(*) from scott.ADM_CC_FEATUREVALUE;
  COUNT(*)
----------
         0

出力結果からテスト環境のscottのテーブルにレコードが含まれていないことから定義情報のみが移行できていることが確認できます。上記ではscottスキーマの1つのテーブルのみを確認対象としていますが、他のテーブルやappsスキーマのテーブルなどもいくつか確認することをおすすめします。

オプティマイザ統計情報の移行

本番環境のオプティマイザ統計情報をテスト環境に移行します。この統計情報は、2回目のSPA試行のExplain Planの実行に必要になります。正確なテスト結果を得るには、オプティマイザ統計情報は本番環境とテスト環境で同一であることが望ましいです。そのため、STSを取得する直前にオプティマイザ統計情報の収集と移行を実施します。

統計情報を保持するためのステージング表(opt_stats)を作成します。別途作成した管理ユーザdba1で以下のコマンドを実行します。dba1ユーザは本番環境とテスト環境の両方に存在します。

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE ( 
    ownname => 'dba1' 
,   stattab => 'opt_stats'
);
END;
/

続いて、統計情報を収集します。今回の検証で収集する対象の統計情報は、STSを取得する際に実行するワークロードに含まれるスキーマの統計情報です。

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('apps');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('scott');

収集したそれぞれの統計情報を作成したステージング表(opt_stats)に格納します。

BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS ( 
    ownname => 'apps' 
,   stattab => 'opt_stats'
,   statown => 'dba1'
);
END;
/

BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS ( 
    ownname => 'scott'  
,   stattab => 'opt_stats'
,   statown => 'dba1'
);
END;
/

Oracle Data Pumpを使用して本番環境にあるステージング表をテスト環境に移行します。
実行したexpdpコマンド

expdp dba1/dba1@hr DIRECTORY=dump_dir DUMPFILE=stats.dmp TABLES=opt_stats logfile=export_1.log

実行結果:

$ expdp dba1/dba1@hr DIRECTORY=dump_dir DUMPFILE=stats.dmp TABLES=opt_stats logfile=export_1.log
Export: Release 18.0.0.0.0 - Production on Thu May 11 09:42:25 2023
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "DBA1"."SYS_EXPORT_TABLE_01":  dba1/********@hr DIRECTORY=dump_dir DUMPFILE=stats.dmp TABLES=opt_stats logfile=export_5.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "DBA1"."OPT_STATS"                          257.6 KB    2083 rows
Master table "DBA1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBA1.SYS_EXPORT_TABLE_01 is:
  /tmp/datapump/stats.dmp
Job "DBA1"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 11 09:42:32 2023 elapsed 0 00:00:07

実際のテストでは、エクスポートしたdumpファイルをsftpなどを使用して転送します。※今回の検証環境では本番環境(HR)とテスト環境(OLTP_CL2)が同一のコンピュート上に存在するため、ファイル転送作業は実施しません。

実行したimpdpコマンド

impdp dba1/dba1@oltp_cl2 DIRECTORY=dump_dir DUMPFILE=stats.dmp TABLES=opt_stats logfile=import_1.log

実行結果:

$ impdp dba1/dba1@oltp_cl2 DIRECTORY=dump_dir DUMPFILE=stats.dmp TABLES=opt_stats logfile=import_1.log
Import: Release 19.0.0.0.0 - Production on Thu May 11 09:43:46 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DBA1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "DBA1"."SYS_IMPORT_TABLE_01":  dba1/********@oltp_cl2 DIRECTORY=dump_dir DUMPFILE=stats.dmp TABLES=opt_stats logfile=import_4.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBA1"."OPT_STATS"                          257.6 KB    2083 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "DBA1"."SYS_IMPORT_TABLE_01" successfully completed at Thu May 11 09:43:53 2023 elapsed 0 00:00:06

テスト環境で本番環境から移行してきたステージング表から統計情報をデータ・ディクショナリにインポートします。

BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS( 
    ownname => 'apps' 
,   stattab => 'opt_stats'
,   statown => 'dba1' 
);
END;
/

BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS( 
    ownname => 'scott'  
,   stattab => 'opt_stats'
,   statown => 'dba1' 
);
END;
/

実行例:

SQL> BEGIN
  2    DBMS_STATS.IMPORT_SCHEMA_STATS(
  3      ownname => 'apps'
  4  ,   stattab => 'opt_stats'
  5  ,   statown => 'dba1'
  6  );
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL> BEGIN
  2    DBMS_STATS.IMPORT_SCHEMA_STATS(
  3      ownname => 'scott'
  4  ,   stattab => 'opt_stats'
  5  ,   statown => 'dba1'
  6  );
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL Tuning Set(STS)の作成とインポート

本項の作業概要図

事前作業

※STSを取得する前に、Swingbenchなどのベンチマーク・ツールで作成した負荷テスト用のワークロードを変更前環境に実行します。ワークロードの実行方法の詳細な手順については本記事のテーマとは少し外れるため、ここでは記載しません。

本番環境でSTSの作成

Enterprise Managerにログインします。

ターゲット → データベースをクリックします。

データベースの一覧から変更前システムの対象のデータベースをクリックします。（本記事では、sales.subnet.vcn.oraclevnc.com_HR）

パフォーマンス → SQL → SQLチューニング・セットをクリックします。

資格証明などを指定してデータベースにログインします。

作成をクリックします。

任意のSQLチューニング・セット名を入力し、次へをクリックします。

STSのロード・メソッドを指定し、次へをクリックします。（本記事では、カーソルキャッシュを選択します。）

フィルタ条件を指定して、取得するSTSのフィルタリングを行えます。（本記事では、SCOTTとAPPSスキーマが含まれるSQL文のみが対象となるようにフィルタリングをかけています。）完了後、次へをクリックします。

STSを作成するジョブ名とスケジュールを指定します。（本記事では、ジョブ名はデフォルトでスケジュールは即時とします。）完了後、次へをクリックします。

設定項目を確認後、発行をクリックします。

STSの作成が完了すると、以下のように表示されます。（本記事では、50件のSQL文が取得できていることが確認できます。）

本番環境からテスト環境へSTSを転送

転送する対象のSTSのラジオボタンをクリックし、ファイルにエクスポートをクリックします。

内部ではOracle Data Pumpのエクスポートが動作します。STSのエクスポート先の情報などの入力を含む設定を行います。

• ディレクトリ・オブジェクト - エクスポートするディレクトリ・オブジェクトを指定します。必要に応じて、ディレクトリ・オブジェクトを新規に作成します。
• エクスポート・ファイル - エクスポートするdumpファイルの任意の名前を入力します。
• ログ・ファイル - 任意のログ・ファイル名を入力します。
• 表スキーマのステージング表 - STSを一旦保管するステージング表を指定します。
• ステージング表の出力に使用される表領域を選択します。（デフォルトではSYSAUX）
• エクスポートを実行するスケジュールを指定します。

各項目の入力完了後、OKをクリックします。

本番環境のコンピュートに接続してlsコマンドでdumpファイルとログ・ファイルがそれぞれ指定したディレクトリに出力されていることを確認します。

$ ls temp
EXPDAT_HR_WORKLOAD.DMP  EXPDAT_HR_WORKLOAD.LOG

実際のテストではsftpなどを使用してdumpファイルを転送します。※今回の検証環境では本番環境とテスト環境が同一のコンピュート上に存在するため、ファイル転送作業は実施しません。

続いて、テスト環境にアクセスします。

パフォーマンス → SQL → SQLチューニング・セットをクリックします。

ファイルからインポートをクリックします。

内部ではOracle Data Pumpのインポートが動作します。インポートするファイル情報の入力を含む設定を行います。

• ディレクトリ・オブジェクト - インポートするdumpファイルが配置されているディレクトリ・オブジェクトを指定します。
• インポート・ファイル - インポートするdumpファイルの名前を入力します。
• ログ・ファイル - 任意のログ・ファイル名を入力します。
• インポート操作でデータの格納に一時使用される表領域を選択します。（デフォルトではSYSAUX）
• エクスポートを実行するスケジュールを指定します。

各項目の入力完了後、OKをクリックします。

インポートされたSTSが一覧に表示されます。

SQL Perfomance Analyzer(SPA)の実行

本項の作業概要図

EMのテスト環境の管理画面で、パフォーマンス → SQL → SQLパフォーマンス・アナライザ・ホームをクリックします。

ガイド付きワークフローをクリックします。

ガイド付きワークフローでは、以下の画面のようにSPA試行の一連のステップをガイドに沿って実行することができます。まずは、ステップ1のSQLチューニング・セットに基づくSQLパフォーマンス・アナライザのタスクの作成を実行します。

名前に任意のタスク名を入力します。完了後、虫眼鏡のアイコンをクリックします。

本番環境からテスト環境にインポートしたSTSを選択し、選択をクリックします。

作成をクリックします。

次のステップの実行ボタンをクリックします。

1回目のSPA試行を実行します。このSPA試行では18cのシステムのテスト結果を作成します。
SQL試行名に任意の名前を入力します。
STS内の実行計画などの情報をテスト結果として使用するConvert SQLSETを実行するため、作成方法として、SQLチューニング・セットから作成を選択します。

スケジュールはデフォルトのままで、試行環境設定済みのチェックボックスにチェックを付け、発行をクリックします。

次のステップの実行ボタンをクリックします。

2回目のSPA試行を実行します。このSPA試行では19cのシステムのテスト結果を作成します。
SQL試行名に任意の名前を入力します。
STS内のSQL文の実行計画を生成するExplain Planを実行するため、作成方法として、計画をローカルで実行を選択します。

スケジュールはデフォルトのままで、試行環境設定済みのチェックボックスにチェックを付け、発行をクリックします。

次のステップの実行ボタンをクリックします。

今回の検証では、テスト環境にテストデータが準備されていないため、比較メトリックはオプティマイザ・コスト以外は選択できません。発行をクリックします。

次のステップの実行ボタンをクリックします。

SPA試行の比較レポートが表示されます。上位10位のSQL文が表示されます。
新規計画が実行計画に変更があったSQLを確認できます。(Yと表示されているSQL文)
また、互換性などによってエラーのあったSQL文の数も表示されます。

各SQL IDをクリックするとそれぞれのSQL文の詳細を確認することができます。

1回目のSPA試行(18c)と2回目のSPA試行(19c)での実行計画の変更部分などを詳細に分析できます。

まとめ

今回は、Oracle Real Application Test(RAT)の機能であるSQL Performance Analyzer(SPA)のConvert SQLSETとExplain Planを実行した手順を紹介しました。このSPA試行の組み合わせパターンはセキュリティ、工数、費用などの事情によりテスト環境に本番環境のテストデータや本番環境と同等の環境が準備できないが、アップグレードやパッチ適用などのシステム変更による実行計画の変動や互換性によるエラー発生を確認したい利用者におすすめするパターンになります。安く、早く、簡単に構成可能です。デメリットとしては性能劣化が検出できないという点です。テストデータを実際に使用したTest Executeによるテスト・モードを使用した場合はCPU時間、ユーザーI/O時間、バッファ読取りなどの比較メトリックを選択したテストの実行が可能で、性能劣化の検出も可能です。より正確なテストを実行したい場合はTest Executeの利用をおすすめします。

今回の内容は以上です。

参考文献

• SQL Performance Analyzerマニュアル
• オプティマイザ統計情報の移行に関するガイド
• Oracle Data Pumpマニュアル
• Oracle Enterprise Manager 13c Workshopの概要