概要
Oracle DatabaseのReal Application Testing(RAT)は、Oracle Databaseに対するテストを効率よく実施するためのOracle Databaseの機能です。RATに関しては、下記のような紹介記事が参考になります。
シンジ＆アヤノの実践データベース性能テストの極意：Oracle Real Application Testingを使ってみよう
https://www.oracle.com/technetwork/jp/database/articles/rat/rat01-2166962-ja.html
【12c対応】とにかく苦労しない「RAT」簡単攻略テクニック
https://www.atmarkit.co.jp/ait/articles/1705/31/news004.html
RATの主要機能であるSQL Performance Analyzer(SPA)を利用することで、Oracle Databaseをアップグレードした場合に、利用しているSQLへどのような影響が出る可能性があるかをテストすることができます。より具体的には、利用している1つ1つのSQLがアップグレードによりエラーにならないか、実行計画に変更があるか、実行時の性能はどう変わるか、ということをテストできます。
本記事では、RATのSPAの評価をはじめる方（使ってみよう、という方）を対象に、評価を始めるためのスタートとなる手順（SQL,PL/SQLの実行環境でできるもの）を紹介します。本記事を参照いただく前に上記の記事などを参照して、RATのSPAがどのようなものか、どのように動作するのか、おおよそ理解いただいていると、本記事の内容・手順も理解しやすいと思います。
2019年12月ごろの情報・環境を元に本記事は記載しています。
0. Real Application Testing(RAT)に必要なエディション・オプション
RATのSPA機能を利用して、テストを実施する対象となるデータベースには、Oracle DatabaseのEnterprise EditionにRATオプションが必要です。ライセンスの購入はもちろん、インストールされている必要があります。
本記事では、2つのデータベース(11g R2と19c)に対してSPAでテストを実施して比較をしますが、その両方にRATがインストールされていることが必要です。
SPAでSQL群をテストする対象のデータベースにはRATオプションが必要ですが、テストするSQL群を格納しているSQL Tuning Set(STS)は、Oracle DatabaseのEnterprise Editionであれば利用できオプションは不要です。つまり、テストするためのSQLを収集・取得するためのデータベースはEnterprise EditionであればOKです。
（注意）RATにある(SPAではない)もう一つの機能Database Replayでは、RATオプションが必要となるデータベースの考え方が違います。Database ReplayではワークロードをキャプチャするデータベースにもRATオプションが必要です。下記の記事が参考になります。
データベースのバージョンアップはなぜ「大変」なのか──「SQLテスト」の3大課題とは (2/2)
この記事の、SPAとDatabase Replayでは、「ライセンスの考え方」が少し違う、が参考になります。
利用しているデータベースにRATがインストールされているか否かは、次のSQLで確認できます。
col PARAMETER format A30
col VALUE format A10
SELECT * FROM v$option WHERE parameter = 'Real Application Testing';
(実行結果)
PARAMETER VALUE CON_ID
------------------------------ ---------- ----------
Real Application Testing TRUE 0
RATがインストールされている環境であれば、ここでTRUEが表示されます。RATを追加でインストールするには、choptツールを利用することができます。ただし、choptツールを実行する前にデータベースを停止し、データベース・オプションを追加した後でデータベースを起動する必要があります。
（参考）マニュアルOracle Database Databaseインストレーション・ガイド 19c for Linux の「インストール後のOracle Databaseオプションの有効化および無効化 choptツール」
Oracle CloudのPaaSのデータベースサービスであるOCI Databaseでは、RATはEnterprise Edition以上に含まれています。下記マニュアルの「Oracle Real Application Testing」の箇所で、付属オプションとしてDBCS EEが入っていることを確認ください。
（参考）Oracle Databaseデータベース・ライセンス情報ユーザー・マニュアル 19c：1.4 Oracle Databaseオプションおよび許可される機能
1. 本記事の作業の前提
本記事で紹介しているシナリオでは、Oracle Database 11g R2(11.2.0.4)の環境で取得済みのSTSを、Oracle Database 19cと11g R2(11.2.0.4)の2つのインスタンスでテストして比較をします。11g R2から19cへアップグレードするケースを想定します。
実行するには下記が必要です。
- RATがインストールされている19cの環境のOracle Databaseインスタンス
- RATがインストールされている11g R2の環境のOracle Databaseインスタンス
- SQLの実行環境(SQL*PlusやSQL Developer)
- テストの対象となるSTS(11g R2の環境で取得)
SPAによるテスト実施は、2つデータベースインスタンスが用意できない環境（1つのインスタンスしか用意できない環境）で実施する方式も考えられます。本記事で紹介する方法は、SPAを理解いただくにあたりもっとも "基本的" なシナリオです。
1つのデータベースインスタンスだけでSPAによるテストを実施する方式に関しては、本記事の「データベースリンク先のデータベースへテストを実行させる代わりにSTSから情報を取り出す方法（CONVERT SQLSETの利用）」で説明します。
STSの取得方法、コピーの方法に関しては下記の記事が参考になります。本記事の手順は、下記の記事の手順に続けて実施しています。
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSをデータベース間で移動（コピー）する
2つのデータベースインスタンスに対して、SPAの実行前に実施する必要がある内容は「SQL Performance Analyzerの実行前の準備」に記載します。
2. 本記事の作業の流れ
本記事の作業の流れを3つのステップごとに説明します。まず、事前の準備です。
本記事では11g R2から19cでアップグレードに向けたテストを想定して、それぞれのバージョンのデータベースインスタンスをテスト用に用意します。19cのデータベースはマルチテナント環境が多いと考えられますが、テスト用の1つのPDBを用意しています。
テストするSQL群を格納したSTSは取得済みとします(本記事のシナリオでは11g R2の実環境で取得していることになります)。そのSTSは19cのPDB環境にコピーしておきます。
テストのためのSQL群がアクセスするスキーマ（表など）とデータは2つのデータベースに入れておきます。統計情報も取得しておきます。データがない空の表であってもテストは実行できますが、SQL実行時の性能比較はできません（データがないため実行性能が意味のあるデータにならない）。データを入力していなくても統計情報があれば、SQL文の解析時にエラーにならないか確認することと、実行計画の変動の確認（比較）は実施することができます。
本記事の例と図では、テストのためのスキーマとSTSのオーナーがSOEというユーザーになっています。それぞれ2つのデータベースに作成して入れておきます。また本記事ではSPAを実行するユーザーをspaadminというユーザー名で作成し、DBAロールを付与しておきます。
SPAで使用するデータベース・ユーザー
テスト環境でSPAを実行する際のデータベースユーザーに関しては、SPAの専用のユーザーを作成して、DBAロールを付与するとよい、とされています。
参考:シンジ＆アヤノの実践データベース性能テストの極意：Oracle Real Application Testingを使ってみよう
最後に19cのデータベース上で11g R2のデータベースインスタンスに対するパブリックデータベースリンクを作成しておきます。
これらの事前準備は、一般的な（普通の）Oracle Database上の手順で、本記事では具体的には説明していません。
本記事ではSPAのタスクを2段階で実施します。最初にテスト対象のSQL群から、19cと11g R2で実行計画が異なるものだけ抽出し別のSTSに格納します。次に抽出されたSQL群を対象に、19cと11g R2で実行させて性能の比較を実行します。
まずは最初のSPAタスクの流れを説明します。
基本1回のSPAタスクは、次のような流れになります（上記の図の①～③です）。
「1回目の試行 (STSを1つ目のデータベースに対して動作させる)」
「2回目の試行 (STSを2つ目のデータベースに対して動作させる)」
「2回の試行の結果を比較してレポートを作成する」
ここで"試行"とはSTSにあるSQL群をデータベースに対して動作させます。ここでいくつかオプションがあり、実行計画を生成させるだけ、あるいは実際にSQLを実行させるのどちらかを選択できます。デフォルトでは、SPAの試行の対象は、DMLの問合せ部分のみです。データ変更を伴う処理はSPAでは実施しません(*)。
*参考Oracle Database Testingガイド 19c:2.4 変更前のSQLパフォーマンスの測定
ここでのシナリオの1回目のSPAタスクでは「実行計画の変動があるSQLの抽出」ですので、実行計画の生成だけを実施します。2つのデータベースで生成した実行計画に違いがあるSQLだけを抽出して2回目のSTSに格納します。そのSTSを使って2つめのSPAタスクに続けます。またアップグレードにより構文エラーとなってしまうSQLがあった場合、1回目のSPAタスクでエラーになると考えられます。
2回目のSPAタスクでは、前の段階で抽出されたSQLを使って、実際にSQLを2つのデータベースで実行して、実行時の性能を比較します。実行時の性能をテスト・比較するので、対象になっている表に関して、それぞれのデータベースに同等のデータが入力されている必要があります。
ここでは2段階のSPAタスクのシナリオを紹介しています。1つ目のSPAタスクだけでも「実行計画が変わるSQLの抽出」「構文エラーとなってしまうSQLの発見」ができます。たとえばテスト環境に性能評価のためのデータを用意できなかったとしても、1つ目のSPAタスクは実行できます（ただし、比較して意味のある実行計画を生成させるには統計情報をテスト環境にコピーする必要があります）。
続けて実際の作業手順を紹介していきます。
3. SQL Performance Analyzerの実行前の準備
1つ前のセクションで説明したように、SPAを実行する前に、2つのデータベースに対して「テスト対象のスキーマ・データの準備（本記事ではSOEというユーザーのスキーマです）」「SPA実行用のspaadminというユーザーの作成とDBAロールの付与」を実施しておきます。19cのデータベースはマルチテナント環境が多いと考えられますが、テスト用の1つのPDB上に、上記のスキーマ・データやspaadminユーザーの作成を実施します。
さらに「19cのデータベースから11g R2のデータベースへのパブリックデータベースリンクを作成」します。テストで利用する「STSは19cのデータベースのSOEユーザーをオーナーとしてコピー」します。
下記は19cのデータベースに接続して、パブリックデータベースリンクとSTSの確認をしているものです。
SELECT banner FROM v$version;
(実行結果)
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
SELECT banner FROM v$version@(パブリックデータベースリンク名);
(実行結果)
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
col owner FORMAT a10
col name FORMAT a30
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS01 60
SYS SYS_AUTO_STS 0
今回利用するのはSTS01というSTSです。60のSQLが含まれています。（SYS_AUTO_STSという名前のSTSは19cの新機能用に作成されているSTSのようです。）
4. SQL Performance Analyzerの1回目のタスク（2つのバージョンで実行計画が変わるSQLの抽出）
1回目のSPAタスクを実行します。本記事でこの1回目のSPAタスクの一番の目的は、19cと11g R2で実行計画を生成して違いがあるSQLを抽出することです。
ここからSQL,PL/SQLを実行して作業を進めますが、19cのデータベースにspaadminで接続して実行しています。
4.1. SPAタスクの作成
1回のSPAの実行は複数のステップからなりますが、タスク、として管理されています。まずはSPAのタスクを作成します。ここではSPATASK01という名前で作成します。SPAのタスクを作成するときに利用するSTSを指定します。ここではSOEがオーナーになっているSTS01というSTSを指定しています。
VARIABLE stmt_task VARCHAR2(64)
BEGIN
:stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
task_name => 'SPATASK01',
sqlset_owner => 'SOE',
sqlset_name => 'STS01'
);
END;
/
SPAタスクが作成されたことを確認します。
col owner FORMAT a10
col task_name FORMAT a20
SELECT
owner,
task_name,
status
FROM
dba_advisor_tasks
WHERE
advisor_id = 8;
(実行結果)
OWNER TASK_NAME STATUS
---------- -------------------- -----------
SPAADMIN SPATASK01 INITIAL
SYS SYS_AI_VERIFY_TASK INITIAL
spaadminがオーナーとなりSPATASK01が作成されていることが確認できます。（SYS_AI_VERIFY_TASKという名前のタスクは19cの新機能用に作成されているタスクのようです。）
4.2. 1回目の試行
SPAの1回目の試行をします。1回目は11g R2へのデータベースリンクを利用して実施します。実際の試行の前にパラメータでデータベースリンクを設定します。次のように実行します。
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPATASK01',
parameter => 'DATABASE_LINK',
value => '(パブリックデータベースリンク名)'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
ここでパブリックデータベースリンク名は、グローバル名で指定します。パブリックデータベースリンク名は、インスタンスパラメータのGLOBAL_NAMESがTRUEの場合、接続先のデータベースのグローバル名と同じになります。データベースのグローバル名は、(DB_NAME).(DB_DOMAIN)になり、SELECT * FROM GLOBAL_NAME で確認できます。
単純なSELECT文の中でデータベースリンクを利用する場合は、グローバル名( (DB_NAME).(DB_DOMAIN)の形式 ) でなくてもエラーとなりませんが、ここで指定するパラメータはグローバル名で指定します。
パラメータとしてデータベースリンクを設定したら、次のように1回目の試行を実行します。これでデータベースリンク先の11g R2のデータベースで、STSにあるSQL群の実行計画を生成させます。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK01',
execution_type => 'EXPLAIN PLAN',
execution_name => 'first trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
execution_type => 'EXPLAIN PLAN'と指定することで、ここでの試行は「実行計画の生成」であることを指定しています。SQLの実行はしません。
execution_name => 'first trial'とあるのは、この試行の名前です。後のステップでこの名前を使います。
ここで、下記のようなエラーとなった場合は、データベースリンクの指定に間違いがあると考えられます（たとえば、グローバル名になってないなど）。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK01',
execution_type => 'EXPLAIN PLAN',
execution_name => 'first trial'
);
END;
エラー・レポート -
ORA-02019: 指定されたリモート・データベースは存在しません。
ORA-06512: "SYS.PRVT_ADVISOR", 行3546
ORA-06512: "SYS.PRVT_ADVISOR", 行932
（以下略）
このようなエラーになった場合など、タスクの作成からやり直したいときは、タスクを削除します。タスクの削除は下記のように実行します。
BEGIN
DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name => 'SPATASK01'
);
END;
/
4.3. 2回目の試行
2回目の試行は、接続している19cのデータベースに対して実行します。まず設定しているデータベースリンクのパラメータを外します。次のように実行してください。
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPATASK01',
parameter => 'DATABASE_LINK',
value => ''
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
続けて2回目の試行を実行します。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK01',
execution_type => 'EXPLAIN PLAN',
execution_name => 'second trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
1回目の試行と同じく、実行計画の生成(EXPLAIN PLAN)を実行します。また試行の名前としてsecond trialと指定しています。
4.4. 2回の試行の比較とレポート生成
11g R2での1回目の試行と、19cでの2回目の試行の比較をしてレポートに出力させます。まずは比較を実行します。比較の前にパラメータを設定します。
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPATASK01',
parameter => 'comparison_metric',
value => 'optimizer_cost'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
parameter => 'comparison_metric', value => 'optimizer_cost'と指定することで、各SQLの2つの試行結果の比較を何を基準に比較するか、という指定をしています。ここでは「11g R2と19cのオプティマイザが算出したコスト」で比較する、としています。ここで指定できるパラメータに関しては、マニュアルの下記が参考になります。
Oracle Database Testingガイド19c : 6.2.1 APIを使用したSQLパフォーマンスの分析
下記が上記のマニュアルからの抜粋です。
comparison_metricパラメータを設定して、パフォーマンスに対する影響の分析に使用する実行統計の式を指定します。指定可能な値は、elapsed_time(デフォルト)、cpu_time、buffer_gets、disk_reads、direct_writes、optimizer_costおよびio_interconnect_bytesの各メトリックまたはこれらの組合せです。
ここでの試行では実行計画の生成のみでした。実際にSQLは実行していません。したがって、elapsed_time(SQL実行の経過時間)を指定しても、試行の際に取得していません。そのような指定をすると、次のステップでエラーとなります。
「比較の基準のパラメータ」を適切に設定したら実際に比較を実行します。次のように実行します。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK01',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'COMPARED RESULT1',
execution_params => dbms_advisor.arglist(
'execution_name1','first trial',
'execution_name2','second trial',
'workload_impact_threshold',0,
'sql_impact_threshold',0)
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
execution_type => 'COMPARE PERFORMANCE'と指定して比較を実行します。
比較する2つの試行の名前は'execution_name1' と 'execution_name2'で指定しています。名前は各試行を実施した際につけたものです。この比較の実行には、execution_name => 'COMPARED RESULT1'で名前をつけています。
なお、2回の試行で実行計画の生成しかしていないにも関わらず、比較の基準のパラメータとしてelapsed_time(SQL実行の経過時間)などを指定した場合、この段階で次のようなエラーとなります。その場合、パラメータを設定し直して、比較を再実行してください。再実行の際は、名前(execution_name)がエラーとなったものと重ならないようにします。
エラー・レポート -
ORA-13793: 比較メトリックが無効です
比較の実行の際に指定できるパラメータに関しては、マニュアルのOracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c:161.3 DBMS_SQLPAサブプログラムの要約も参照ください。
比較したらファイルに出力します。次のように実行します。下記のステップは、本記事ではDBサーバーのSQL*PLUSで実行したので、出力結果がここまでのフォーマット（SQL DEVELOPERを使っていた）と異なる点はご了承ください。DBサーバー上で実施したのは、ファイルの出力をDBサーバー上にしたかったからです。
SQL> show user
ユーザーは"SPAADMIN"です。
SQL> set echo on
SQL> set pagesize 0
SQL> set linesize 150
SQL> set long 10000000
SQL> set trimspool on
SQL> var rep clob
SQL> col rep for a150
SQL>
SQL> BEGIN
2 :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
3 task_name => 'SPATASK01',
4 type => 'HTML',
5 level => 'ALL',
6 section => 'ALL',
7 execution_name => 'COMPARED RESULT1'
8 );
9 END;
10 /
PL/SQLプロシージャが正常に完了しました。
SQL> spool /tmp/SPATASK01.html
SQL> print :rep
SQL> spool off
DBMS_SQLPA.REPORT_ANALYSIS_TASKで、前のステップで比較の実行の名前を指定してファイルに出力させています。出力の形式（HTMLかテキストか）や、出力のレベルなどはパラメータで指定できるます。必要に応じて該当するマニュアルの箇所を参照ください。
上記のprint :repと指定している箇所で、実際には画面上に大量に出力されます。それをSQL*PLUSでスプールしてファイルに出力しています。これで、2回の試行の比較レポートが/tmp/SPATASK01.htmlに出力されます。(HTMLファイルの先頭には、SQL> print :repと出力されていると思いますが、ブラウザの表示上は問題ないと思います)
4.5. 比較レポートの確認
作成されたレポートの中身を確認してみます。ここの実行例では、/tmp/SPATASK01.html に出力されています。ブラウザで確認します。
レポートには多くの情報が記載されています。ここですべてを説明はできないですが、今回の実行例で出力されたレポートを元にいくつかポイントを説明します。マニュアルでは、下記が参考になります。
Oracle Database Testingガイド 19c:6.2.2 コマンドラインを使用したSQLパフォーマンス・アナライザ・レポートの確認
レポート(HTML形式)の一番上は、こののようになっています。
赤枠で囲っているところを説明します。まず、このレポートで利用したSTSがどれか、がわかるようになってます。1回目、2回目、それぞれの試行がどこまで実施したかもわかるようになっています。今回はEXPLAIN PLANと指定して「実行計画の生成」まで実施しました。
またデータベースリンク先で実施した試行に関しては、そのデータベースリンクの名前が記載されています。レポートではデータベースリンク名がグローバル名で記載されていないですが、実行したときにはパラメータで（データベースリンク名）.（データベースドメイン名）の形式で指定します。
（なお上記の例では、データベースリンク名が 12cと誤解しやすい名前になってますが、当方の環境の事情によるもので、今回は11g R2へ接続するデータベースリンク経由でテストしています。）
レポートの次の箇所は、このようになっています。
このレポートで2回の試行に関して、2回目の試行が「よくなった(Improved)」「悪くなった（Regressed）」を判定していますが、その基準が何か、というのを表示しています。今回の実行例では、比較を実施するときに「比較の基準のパラメータ」としてoptimizer_costを指定しています。そのことが記載されています。このレポートでの「よくなった、わるくなった」の基準は、あくまで「オプティマイザが算出したコスト値」で、実際にSQLを実行したときの性能データではありません。
上記の図の下部の赤枠が、このレポートのSPAタスクの全体感になります。全部で60のSQLがテスト対象です。そのうち実行計画がかわったものが19あったことがわかります。比較して「よくなった（ここではオプティマイザのコスト値が小さくなった）」ものが5つあります。5つのSQLのうち3つが実行計画が変わったSQLです。
比較して「悪くなった」ものが20あります。その20のうち実行計画が変わったものは11です。
まずは「実行計画が変わるSQL」を抽出することが、1回目のSPAタスクの目的としていましたから、ここでは19のSQLを抽出することが、次のSPAタスクへのステップになります。
レポートの次の箇所は、このようになっています。
テストしたSQL群の「ワークロードへの影響度が大きい順」に並べて、2回の試行の結果をリストにしています。ここのリストでは、1行に1つのSQLに関する情報が出力されています。「Execution Frequency」に表示されている情報は、そのSQLがSTSに格納されたときの情報で、そのSQLの実行回数です。
「Metric Before」「Metric After」が、それぞれ2回の試行で指定した比較の基準値（今回の例ではオプティマイザによるコスト値）を出力しています。
「Plan Change」は、そのSQLの実行計画が変わったか（y）、変わらなかったか（n）を表示しています。
各SQLの「sql_id」のところはリンクになっていて、クリックするとレポート下部の該当するSQLの詳細情報を表示します。ここでは、実行計画が変わっているc13sma6rkr27cというsql_idの詳細情報を確認してみます。次のようになっています。（1つの画面でキャプチャできなかったので3つにわけています）
まず、SQL文とSQL文のバインド変数の値を確認できます。今回の2回の試行でのオプティマイザコスト値(cost)と、パース時間(parse_time)も確認できます。今回はSQLの実行はしていないので、それ以上のデータ（SQL実行時間など）はありません。
この下に1回目、2回目それぞれの試行で得られた実行計画が出力されています。まずは1回目(11g R2)での実行計画。
2回目(19c)での実行計画。
「PRODUCT_INFORMATION」と「PRODUCT_DESCRIPTIONS」という表の結合方法に違いがあるようです。実行計画に違いがあるので、実際にSQLを実行してみたら性能がどのように変わるか比較してみたいところです。本記事では2回目のSPAタスクとしてその点を実施します。
このようにSPAタスクの出力のレポートから、対象のSQL群に関して、2回の試行の比較を全体から個別の比較まで確認することができます。
4.6. 実行計画に変動のあったSQLを新しいSTSに格納する
1回目のSPAタスクの結果から、テスト対象としたSTS中のSQL群の中で、実行計画に変動があるSQLがあるものを抽出できます。そのSQL群を新しいSTSに格納して、2回目のSPAタスクにつなげます。1回目のSPAタスクの継続で、19cのデータベースにspaadminで接続して、次のように実行します。
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_owner => 'SOE',
sqlset_name => 'STS02'
);
OPEN sqlset_cur FOR
SELECT value(p)
FROM table(
DBMS_SQLTUNE.SELECT_SQLPA_TASK(
task_name => 'SPATASK01',
execution_name => 'COMPARED RESULT1',
level_filter => 'CHANGED_PLANS')) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_owner => 'SOE',
sqlset_name => 'STS02',
populate_cursor=> sqlset_cur,
load_option => 'INSERT');
CLOSE sqlset_cur;
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
上記のPL/SQLにおいては、まずDBMS_SQLTUNE.CREATE_SQLSETを実行してSTS02というSTSを作成しています。続けてDBMS_SQLTUNE.SELECT_SQLPA_TASKで、先に比較を実行した際に指定した名前 execution_name => 'COMPARED RESULT1' を指定し、さらに level_filter => 'CHANGED_PLANS'と指定することで、先の比較の実行結果から「実行計画が変わったSQL」を抽出することができます。
取り出したSQLをDBMS_SQLTUNE.LOAD_SQLSETで、作成したSTS(STS02という名前にしています)にロードします。
ここでのポイントはlevel_filter => 'CHANGED_PLANS'という指定で、実行計画が変わったSQLを抽出していることです。ほかにどのような指定ができるかは、マニュアルの下記が参考になります。たとえば「エラーが発生したSQL」「比較の基準とした数値が悪くなったSQL」を指定することができます。
PL/SQLパッケージおよびタイプ・リファレンス:164.5.37 SELECT_SQLPA_TASKファンクション
Oracle Database Testingガイド 19c:6.2.5 APIを使用した、リモートSQL試行からのパフォーマンスが低下したSQL文のチューニング
実行結果が変更されたSQLを抽出した結果を確認してみます。次のように実行します。
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS01 60
SOE STS02 19
SYS SYS_AUTO_STS 0
先に確認したレポートにあったようにSTS01にあった60のSQLには実行計画の変更があったSQLが19あり、それがSTS02に格納されていることがわかります。
5. SQL Performance Analyzerの2回目のタスク（2つのバージョンでSQLを実行して性能を比較する）
2回目のSPAタスクを実行します。本記事でこの2回目のSPAタスクの目的は、前の段階で実行計画の変動があることが確認できたSQLに関して、実際に11g R2と19cで実行させて、実行時の性能を比較することです。SPAタスクの実施の流れは、1回目と変わりはありません。設定のパラメータを変えるだけです。
ここまでの作業と同じく19cのデータベースにspaadminで接続して実行しています。
5.1. SPAタスクの作成
2回目のSPAタスクを作成します。STSには、前の段階で抽出したSQLが格納されているSTS02を指定します。
VARIABLE stmt_task VARCHAR2(64)
BEGIN
:stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
task_name => 'SPATASK02',
sqlset_owner => 'SOE',
sqlset_name => 'STS02'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
SPATASK02という名前のSPAタスクが作成されたことを確認します。
SELECT
owner,
task_name,
status
FROM
dba_advisor_tasks
WHERE
advisor_id = 8;
OWNER TASK_NAME STATUS
---------- -------------------- -----------
SPAADMIN SPATASK01 COMPLETED
SPAADMIN SPATASK02 INITIAL
SYS SYS_AI_VERIFY_TASK INITIAL
5.2. 1回目の試行
2つ目のSPAタスクの、11g R2に対する1回目の試行を実施します。まず11g R2のデータベース対するデータベースリンクをパラメータとして指定します。
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPATASK02',
parameter => 'DATABASE_LINK',
value => '(パブリックデータベースリンク名)'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
続けて1回目の試行を実行します。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK02',
execution_type => 'TEST EXECUTE',
execution_name => 'first trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
2つ目のSPAタスクは、実際にSQLの実行まで実施します。そのためにexecution_type => 'TEST EXECUTE'とパラメータを設定します。ここが2つ目のSPAタスクのポイントです。
5.3. 2回目の試行
続けて19cに対する2回目の試行を実施します。まずはデータベースリンクの設定を外します。
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPATASK02',
parameter => 'DATABASE_LINK',
value => ''
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
続けて2回目の試行を実施します。ここでもexecution_type => 'TEST EXECUTE'と指定します。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK02',
execution_type => 'TEST EXECUTE',
execution_name => 'second trial'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
5.4. 2回の試行の比較とレポート生成
2つめのSPAタスクの2回の試行の比較を実行します。比較の際の基準を設定します。1つ目のSPAタスクではオプティマイザのコスト値を基準としました。2つめのSPAタスクでは、実際にSQLを実行させた際の性能のデータを利用できます。ここではvalue => 'buffer_gets'という指定をして、バッファからの読み取り回数を基準とします。
このように実行時の性能データを比較して意味がある結果にするための前提は、1回目と2回目の試行を動作させたそれぞれのデータベースのデータ量などが同一であることです。データ量が異なれば、正当な比較にならない、と考えられます。
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPATASK02',
parameter => 'comparison_metric',
value => 'buffer_gets'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
buffer_gets以外で指定できるパラメータは、下記のマニュアルが参考になります。
Oracle Database Testingガイド19c : 6.2.1 APIを使用したSQLパフォーマンスの分析
2回目のSPAタスクの比較を実行します。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK02',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'COMPARED RESULT2',
execution_params => dbms_advisor.arglist(
'execution_name1','first trial',
'execution_name2','second trial',
'workload_impact_threshold',0,
'sql_impact_threshold',0)
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
続けてレポートを生成します。ここの例では、/tmp/SPATASK02.htmlというファイルに生成させています。
SQL> show user
ユーザーは"SPAADMIN"です。
SQL> set echo on
SQL> set pagesize 0
SQL> set linesize 150
SQL> set long 10000000
SQL> set trimspool on
SQL> var rep clob
SQL> col rep for a150
SQL> BEGIN
2 :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
3 task_name => 'SPATASK02',
4 type => 'HTML',
5 level => 'ALL',
6 section => 'ALL',
7 execution_name => 'COMPARED RESULT2'
8 );
9 END;
10 /
PL/SQLプロシージャが正常に完了しました。
SQL> spool /tmp/SPATASK02.html
SQL> print :rep
SQL> spool off
5.5. 比較レポートの確認
作成されたレポートの中身を確認してみます。ここの実行例では、/tmp/SPATASK02.html に出力されているので、ブラウザで確認します。
レポートの見方は1回目のSPAタスクと同じです。1回目のレポートとの違いのポイントに関して説明します。
レポートの上部を確認すると、今回のSPAタスクの試行はTEST EXECUTEとなっており、実際にSQLを実行していることを示しています。
レポートのこの箇所を確認すると、今回の比較の基準はBUFFER_GETSであることがわかります。19のSQLに関して比較して、すべて実行計画が変わっています。これは前段階で実行計画が変わるSQLだけ抽出したので当然です。
BUFFER_GETSを基準として、よくなった(BUFFER_GETSが少なくなった)SQLが7つ、悪くなったSQLが7つあることがわかります。ここで悪くなった7つのSQLが「許容出来ないくらい」悪くなっているようであれば、なんらか対策を検討する、ということになります。
次の画面では19のSQLそれぞれのデータをリストで確認できます。
例えば赤枠で囲ったSQLは、メトリック(ここではBUFFER_GETS)が、4から1222に大きく増えています。sql_idをクリックして詳細を確認してみます。出力が長いので複数の図で表示します。
SQL Textに表示されているSQL文を確認すればわかるのですが、この例で対象となっているSQL文は、ユーザーのアプリケーションから発行されたものではなく、なんらかの作業・動作の過程で実行されたSQL文が元STSにキャプチャされています(TABビューへの検索となっています)。本来、このようなSQLは、SQLテストの結果で考察する対象からは外した方がよいと考えられますが、ここではSPAレポートの出力例として利用します。
かなりシンプルなSQLです。確かにBUFFER_GETSは大きく変わっており、実行時間(elapsed_time)も大きくなっていますが、.000458秒が.006613秒に増えている、というもので絶対値としては大きくないことも確認できます。
ここにあるように、該当のSQLの実行に関して、いくつかポイントもレポートされています。ここの例では2回目の試行（19cで実行）に関しては「実行計画が適応的」とあります。Oracle Database 12c以降で導入された機能の「適応計画(ADAPTIVE_PLANS)」が動作しているかもしれません。
(参考)津島博士のパフォーマンス講座 第63回 Oracle Database 12cR2のオプティマイザについて
2つの実行計画も確認しておきましょう。次のようになります。確かに実行計画が違います。
もし新機能を利用による実行計画の変動が問題になるようであれば、たとえば新機能を利用しないような設定にする、といった対応策が考えられます。
1回目のSPAタスクの後と同じように、2回目のSPAタスクの後で対象としたSTS（ここの例では19のSQLが含まれたSTS02）から、さらに次の対応に向けてSQLを抽出したSTSを作成する、という事も考えられます。本記事の例にある STS01 -> STS02 への抽出（4.6. 実行計画に変動のあったSQLを新しいSTSに格納する）を参考にしながら、次は「比較の基準（本記事の例ではbuffer_gets）とした数値が悪くなったSQL」を抽出する、といったことができます。
以上で2つのSPAタスクの実行例の紹介は終わりです。多数のSQLを対象に、実行計画の変動があるSQLの抽出、抽出されたSQLの実行時の性能比較をまとめて実施、ということが割に簡単に実行できそう、と思っていただけましたでしょうか？
5.6. (参考)STSからSQL_IDを指定してSQLを抽出する
前の節「5.5. 比較レポートの確認」にあるように、ここの例でのSTS02には19のSQLが含まれていましたが、中にはアプリケーションから発行されたものではないものも含まれていました。（上記の例で挙げたのはTABビューへの検索でした。）レポートの中のSQL文を目視で確認したところ、ほかにもデータディクショナリへの検索に該当するものが含まれており、アプリケーションから発行されたSQLは4つだけでそのSQL_IDも確認できました。
SQLテストの結果で考察する対象はアプリケーションから発行されたSQLだけにした方がよいかもしれません。STS02からSQL_IDを指定したSQLだけを抽出して新たにSTS03を作成するには、次のように実行します。
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_owner => 'SOE',
sqlset_name => 'STS03'
);
OPEN sqlset_cur FOR
SELECT value(p)
FROM table(
DBMS_SQLTUNE.SELECT_SQLSET(
sqlset_owner => 'SOE',
sqlset_name => 'STS02',
basic_filter =>
' sql_id IN ( ''0y1prvxqc2ra9'' , ''7hk2m2702ua0g'' , ''c13sma6rkr27c'' , ''1qf3b7a46jm3u'' ) ' )) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_owner => 'SOE',
sqlset_name => 'STS03',
populate_cursor=> sqlset_cur,
load_option => 'INSERT');
CLOSE sqlset_cur;
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
DBMS_SQLTUNE.SELECT_SQLSETというパッケージを利用します。パラメータなどに関しては下記が参考になります。
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c :164.5.38 SELECT_SQLSETファンクション
DBMS_SQLTUNE.SELECT_SQLSETのbasic_filterにSQL_IDを指定したwhere句で、ここの例で該当する4つのSQLのSQL_IDを指定しています。
basic_filterの設定がポイントですが、sql_idの指定以外にもさまざまな設定ができます。下記が参考になります。
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c :164.3.1 SQLSET_ROWオブジェクト・タイプ
Oracle Database SQLチューニング・ガイド 19c:23 SQLチューニング・セットの管理
該当の4つのSQLが抽出されたSTS03が作成されているか、確認してみます。
SELECT
owner,
name,
statement_count
FROM
dba_sqlset;
(実行結果)
OWNER NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
SOE STS02 19
SOE STS03 4
SOE STS01 60
SYS SYS_AUTO_STS 0
さらにSQLを抽出したSTS03が作成されています。
6. SPAタスクの削除
今回の例では、2つのSPAタスクを実行しました。SPAタスクを削除する場合、次のように実行します。
BEGIN
DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name => 'SPATASK01'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
BEGIN
DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name => 'SPATASK02'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
なおSPAタスクを削除しないと、SPAタスク内で利用しているSTSも削除できません。
7. データベースリンク先のデータベースへテストを実行させる代わりにSTSから情報を取り出す方法（CONVERT SQLSETの利用）
本記事で解説したSPAの手順は、11g R2と19cと2つのデータベースを利用して、それぞれのインスタンスへデータベースリンクをつかって接続してテストを実施し比較するものでした。SPAが利用するSTSを取得するときには、そのSQLの実行計画などの情報も格納されています。SPAの1回目の試行で、実際にデータベースに対して実行するのではなく、STSに格納されている情報を取り出して試行の結果の代用として、2回目の試行（これは実際にデータベースに対して実行する）と比較してレポートさせることができます。
次の図のような手順になります。
この方式の利点は、SPAの実行時にSTSを取得したバージョン（今回の例では11g R2）のデータベースインスタンスがなくてもいい事です。
SPAタスクの実行手順に関して、本記事の「4. SQL Performance Analyzerの1回目のタスク（2つのバージョンで実行計画が変わるSQLの抽出）」との違いは、1回目の試行での、次の2点だけです。
- 試行の実施の前にデータベースリンクを指定しない
- 試行の際に
execution_type => 'CONVERT SQLSET'と指定する。
実際の試行において、下記のように実行します。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK01',
execution_type => 'CONVERT SQLSET',
execution_name => 'first trial'
);
END;
/
あとは、同じように実施して実行計画の変更があるSQLを取り出すことができます。作成されたレポートには、下記のように出力され、1回目の試行結果としたものがSTSからデータ取り出したものであったことがわかります。
8. この次に考えられること
本記事においては、データベースのアップグレード（バージョンアップ）の際に、RATのSPA機能を活用してSQLの性能テストするシナリオを想定して、手順を説明しました。なかでは、1回目のSPAタスクで対象としたSTS（60のSQL群）から「アップグレードで実行計画の変わるSQL」を抜き出し、2回目のSPAタスクで抜き出したSQL群を対象に実行した際の性能比較を2つのバージョンのデータベースで実施し比較しました。
この次に考えられる対応は、2回目のSPAタスクで性能劣化が許容できないレベルと考えられたSQLに対して個別に対策を考える、ということです。その対策の検討には、次のような点に留意することになると考えられます。
- 性能劣化すると考えられたSQLはどの場面で利用されているものか？
- 性能劣化が許容できないレベルか？
- 実行計画の変動が性能劣化の原因か？実行計画の変動を制御する方法は何か適用できるか？
- 実行計画の制御以外の方法でチューニングする方法はあるか？
ここで一般的には「実行計画の変動をなくすこと」が目的ではないことは留意した方がよいと考えられます。アップグレード（バージョンアップ）にともない新機能（オプティマイザの動作が少し変わったというレベルのものもあります）により実行計画が変わることは十分考えれます。その影響で、性能がよくなったり、性能の劣化があっても許容できる範囲であれば、実行計画を制御する必要はない、と考えられます。
上記のような検討をするにあたり、アップグレード（バージョンアップ）にともなう影響を受けるSQLを簡単に絞り込めることがSPAの大きなメリットです。
本記事の続きとして、下記の記事をアップしました。
Oracle DatabaseのSQL Plan Management(SPM)とSQL Tuning Set(STS)の活用：データベースのアップグレード（バージョンアップ）でのSQL実行計画の変動を制御する
アップグレードの際、下位のバージョンで取得したSTS上にあるSQLと実行計画の組み合わせを、上位のバージョンに読み込ませておいて、SQL Plan Management(SPM)という機能を活用してアップグレードに伴う実行計画の変動を制御できます。これはアップグレードに伴うSQL性能問題の対策の1つとなります。この記事ではSPMとSTSを組み合わせて利用する方法に関して説明します。
9. 参考
本記事の内容を一通り理解いただいたのちに参照いただくと、より理解が深まるドキュメントを紹介します。
SPAの動作の全体像を理解した上で、あらためて確認すると利用時のオプションなどに関して理解が深まると思います。
【12c対応】とにかく苦労しない「RAT」簡単攻略テクニック
下記のマニュアルを参照すると、より詳細な設定のバリエーションを確認できると思います。
Oracle DatabaseTestingガイド : 第I部 SQLパフォーマンス・アナライザ
本記事の「2. 本記事の作業の流れ」の中で、SPAの試行の対象が、デフォルトではDMLの問合せ部分のみであることを説明しました。SPA実行時のパラメータの設定によって、すべてのDMLを対象にすることもできます。Oracle Database 19c PL/SQLパッケージおよびタイプ・リファレンス:161.3.9 SET_ANALYSIS_TASK_PARAMETERプロシージャのEXECUTE_FULLDMLの記載が参考になります。