総当たりSQLテストはもう終わり！SPA×STAで実現するリグレッションテストの省コスト化

2025年5月23日（金）に開催された Developer Day 2025 の [T2-4] 総当たりSQLテストはもう終わり！SPA×STAで実現するリグレッションテストの省コスト化 の登壇資料です。

Transcript

1. 総当たりSQLテストはもう終わり！ SPA×STAで実現するリグレッションテストの省コスト化 Developer Day 2025 黒沢 茉由 ストラテジック・カスタマーコンサルティング第二事業本部 ソーシャルインフラ カスタマー本部 May 23, 2025
2. 2 Copyright © 2025, Oracle and/or its affiliates 黒沢 茉由（Mayu Kurosawa） ストラテジック・カスタマーコンサルティング第二事業本部 ソーシャルインフラ カスタマ―本部 スタッフコンサルタント • 2022年日本オラクル新卒入社 • アカウントフォーカスのコンサルタントとして、データの持ち方やテストによる 品質確保、データ移行の技術支援に至るまで、お客様に合った幅広い 支援を担当 • 最近のマイブームはお菓子とパン作り（オーブンレンジを入手）
3. 本セッションの目的 Copyright © 2025, Oracle and/or its affiliates 3 SPA、STAそれぞれの技術紹介
4. 本セッションの目的 Copyright © 2025, Oracle and/or its affiliates 4 SPA、STAそれぞれの技術紹介 ✓ 皆様のテストに関する困りごとを解決する手段の一つとして、 SPA、STAが有用であることを認識していただくこと ✓ 「SPA×STA」という技術の掛け合わせでより強力な効果を 発揮することを実感いただくこと
5. 本日のアジェンダ Copyright © 2025, Oracle and/or its affiliates 5 3 STA(SQL Tuning Advisor)とは 2 SPA(SQL Performance Analyzer)とは 4 「SPA×STA」の 利用イメージ 1 昨今のビジネス ニーズ/ITの関 係とそれを実現 する上での課 題・解決策
6. 昨今のビジネスニーズ/ITの関係と それを実現する上での課題・解決策 6 Copyright © 2025, Oracle and/or its affiliates
7. お客様のビジネスのニーズから、ITで実現すべき対応策が見えてくる 7 Copyright © 2025, Oracle and/or its affiliates 昨今のビジネスのニーズとITの関係 競争力強化 コスト削減 リスク管理 より速いスピードで新規事業の 立ち上げや事業再構築を行う 無駄なコストや業務を省き、 リソースの最適化を図る あらゆる脅威から企業の 資産や評判を守る 事業拡大に伴う機能の 新規開発や変更の迅速な対応 柔軟な課金体系を選択できるク ラウドの活用や、運用負荷を軽 減する仕組みづくり サイバーセキュリティ強化のための 定期的なパッチ適用
8. 8 Copyright © 2025, Oracle and/or its affiliates ニーズを実現する上での課題 ニーズを実現する上で不可欠な「高品質、低コスト、短納期のテスト」は実現が容易ではない 品質をあげようとすると コストが増え、納期が延びる 納期を守ろうとすると 品質が低下し、コストが増える コストを抑えようとすると 品質が低下し、納期が延びる Cost コスト Quality 品質 Delivery 納期 QCDのトレードオフの関係 頻繁な構成・機能変更に対応するには「高品質、低コスト、短納期のテスト」が必要不可欠だが、 三要素を両立することは容易ではなく、結果的にどれかが犠牲となる
9. 三要素の両立が 難しい 「高品質、低コスト、短納期のテスト」の実現のために、網羅性や効率性を高める必要がある 9 Copyright © 2025, Oracle and/or its affiliates 課題の深堀とあるべき姿 高品質 低コスト 短納期 網羅性を高めたいが、人力のテストでは 時間やコストが膨大になる テストで発覚した問題対応を有識者に 頼らざるを得ないが、有識者が少ない、 あるいは多忙で手がつかない 頻繁な構成変更の度にテスト方式や 評価観点の見直しに時間がかかる 機能追加や変更のスピードが求められ る中で、十分なテスト期間が確保でき ない 影響範囲を適切に設定した 網羅性の高いテストの実施 属人化しない迅速かつ正確 なテストの実施、結果対応 定められたテスト方式や評価 観点の用意 効率良く時間と人員を使うた めのテストの自動化 課題 課題の深堀 テストのあるべき姿 下記を両立するテストの実施
10. テスト効率化の手法は様々だが、Oracle Databaseでは「SPA×STA」の解決策がある 10 Copyright © 2025, Oracle and/or its affiliates あるべき姿を実現するための解決策 テストを効率化する一般的な手法 テストケースの 再利用 影響の大きい 箇所に絞った テストケースの 最適化 CI/CDツール などを使用した テスト自動化 Oracle Databaseでの解決策 SQL Performance Analyzer （SPA） SQL Tuning Advisor （STA）
11. 11 Copyright © 2025, Oracle and/or its affiliates SPA／STAの利点 SPAとSTAを組み合わせることで、テスト計画から実行、チューニングまで省コスト化が可能 ✓ テスト方式を一から決める必要がない • 確立したSPA実行手順が存在する • 「エラーが発生するか」「実行計画が変動するか」「実行統計が変動するか」といった、テスト で確認すべき項目が明確 ✓ 高い網羅性を確保 • 本番環境でSQLをキャプチャするため、業務で良く使われるSQLを対象とすることが可能 • SPAで自動でテストが実施できるため、人力でのテストに比べより多くのSQLを網羅するこ とが可能 SQL Performance Analyzer （SPA） SQL Tuning Advisor （STA） ✓ SPAの結果をインプットにできる • SPAの結果、性能劣化の懸念があるSQLを対象として、STAのインプットとして活用するこ とが可能 ✓ 属人化を防ぐ • 自動でチューニング推奨案を導出し、適用まで支援 • 高い技術力を持った専用技術者がいないとチューニングが進まない、といった属人化による テスト期間の長期化を防ぐ 本番環境で取得したSQL情報をテ スト環境で再現し、SQL単位のパ フォーマンスや実行計画を比較 SQLのパフォーマンスを向上させる推 奨事項の確認やチューニング案の実 装支援
12. 机上調査＋人力テストから、SPA+STAを使用したテストにシフトすることで工数を大幅に削減 12 Copyright © 2025, Oracle and/or its affiliates 従来のリグレッションテストとの作業イメージ比較 例）構成変更のリリースまで想定した作業イメージ比較 調査・計画 テスト リリース 調査・計画 テスト リリース 適用前調査 適用前机上調査 環境 構築 構成 変更 STA実行・ チューニング SPA 実行 チューニング リグレッションテスト 構成変更前机上調査 単体テストシナリオ検討・ サンプリング 構成変更 前机上調査 構成 変更 環境 構築 従 来 SPA ＋ STA 対処までの期間を短縮 ※リグレッションテスト、チューニングの期間はイメージであり、実際は対象SQL本数により左右されます。 ※有識者工数イメージ SPA 計画
13. SPA(SQL Performance Analyzer)とは 13 Copyright © 2025, Oracle and/or its affiliates
14. 本番環境で取得されたSQLをもとに2回のSPA試行を比較するレポートを出力 14 Copyright © 2025, Oracle and/or its affiliates SPAの利用イメージ STS SPA STS 3. SPA試行1回目 STSから本番環境での 実行計画・実行統計を抽出 Data Pump 4. SPA試行2回目 STSからSQLを抽出し テスト環境にて実行 2.STSを テスト環境へコピー 1.SQLチューニングセット (STS)を取得 テスト環境 本番環境 5. SPA試行 2回分の結果を比較し、 レポートを出力 構成変更前 構成変更後
15. 構成変更前後を比較し、エラーとなったSQLや実行計画・実行統計に変化があったSQLを可視化 15 Copyright © 2025, Oracle and/or its affiliates SPAレポートのイメージ トータルのSQL数や、実行計画が変化したSQL数、実行 時エラーが発生したSQL数などが表示される SQLごとのメトリック（実行統計値）の変化や 実行計画変動有無が確認可能 ワークロード全体として劣化傾向にあるか、改善傾向にあるかを確認
16. 本番環境からSQL情報やオブジェクト・統計情報などを収集 16 Copyright © 2025, Oracle and/or its affiliates SPAを実行するために必要な情報（Input） STS（SQL Tuning Set） • 本番環境（比較元環境）で実行されたSQL文や実行計画、実行統計等を記録 • SPAのインプットとなる情報 オブジェクト定義 • 調査対象SQLでアクセスしているオブジェクトの定義一式 オプティマイザ統計情報 • 調査対象SQLでアクセスしている表、索引、パーティションなどの統計情報一式 • SPAを使用した統計情報の取得・分析をする場合に必要 実データ • 調査対象SQLでアクセスしている表のデータ一式 • SPAを使用したSQL実行、実行統計の取得・分析をする場合に必要
17. 非互換調査にとどめるか、実行計画や実行統計の変動まで評価するかにより必要なデータが異なる 17 Copyright © 2025, Oracle and/or its affiliates SPA実行環境への移行データ種別ごとの実現可能タスク 種別 移行データ 実現できるタスク 実データ オプティマイザ 統計情報 オブジェクト 定義 ケース1 〇 〇 〇 • SQLがエラーを返すかどうかの確認（非互換調査） • 実行計画が変動するかの確認（実行計画） • 実行統計が変動するかの確認（パフォーマンス） ケース2 × 〇 〇 • SQLがエラーを返すかどうかの確認（非互換調査） • 実行計画が変動するかの確認（実行計画） ケース3 × × 〇 • SQLがエラーを返すかどうかの確認（非互換調査） 〇：テスト環境にImport可能 ×：テスト環境にImport不可
18. アップグレード時の非互換確認やパッチ適用時の実行計画・性能変動の確認に活用 18 Copyright © 2025, Oracle and/or its affiliates SPAのユースケース アップグレード時のSQL互換性チェック • SPAを使用し、数万本のSQLからエラーの発生するSQLを発見 アップグレード時/パッチ適用時の性能試験 • SPAを使用し、数万本のSQLから実行計画や性能が変化するSQLを発見 定期パッチ適用による安定運用 • SPAにより定期パッチ(セキュリティ、修正)の適用に必要なテストをルーティン化
19. 高いユーザ満足度が見込めつつ、コストが最適化される範囲で適切な網羅率を設定することが重要 19 Copyright © 2025, Oracle and/or its affiliates STSの取得設計 テスト網羅率 品質 ユーザ 満足度 コスト コスト 過剰な品質領域 品質 ユーザ満足度 【テスト網羅率と品質・コスト・ユーザ満足度の関係（イメージ）】 品質の上昇に対しユーザ 満足度の上昇が鈍化する 網羅率の考え方 • テスト網羅率を上げると品質は向上するが、あ る点を境にユーザ満足度の上昇は鈍化する （ほとんど使われていない機能のテストは、ユー ザ満足度に繋がりにくい） • コストとのバランスを考慮し、ユーザ満足度に繋 がらない過剰な品質の追究は避け、適切な 網羅率を設定することが重要
20. STSの取得時間やフィルタリングを適切に設計することで、テスト対象を最適化することが可能 20 Copyright © 2025, Oracle and/or its affiliates STSの取得設計 • STSは、本番環境上で実際に実行されているSQLを収集することができるため、高いユーザ満足度が見込める • さらに、STS取得時間の考慮やSTS取得時のフィルタリングを活用することで、テスト対象をより最適化することが可能 STS取得設計例 本社の方がオンライン時間帯をメインに使用するようなシンプルなシステムを想定 • 取得時間を、システムがよく使用されるオンライン時間帯（9:00-17:00）に絞る • 以下のフィルタリングをかけてSTSを取得 項目 設定例 parsing_schema_name 本社の方が使用するAPの実行スキーマのみを対象 plan_hash_value plan_hash_valueが“0”のものは除く （Insert文など、実行計画がないSQL） sql_text 特定の文字列を含むSQLを除く （業務IDをSQLのコメントに付与している場合などで、廃止が決定して いる業務IDが付与されたSQLは除く、など） ※カーソルキャッシュから取得する場合、CAPTURE_CURSOR_CACHE_SQLSETプロシージャのbasic_filterに設定可能
21. STA(SQL Tuning Advisor)とは 21 Copyright © 2025, Oracle and/or its affiliates
22. より効率的な実行計画やパフォーマンス改善を実現するための推奨事項を自動で出力 22 Copyright © 2025, Oracle and/or its affiliates SQLチューニング・アドバイザ（STA）とは • 負荷が高い SQLに対してパフォーマンス改善のための数々のシミュレーションを行い、以下のような項目を評価して実 行計画を最適化する方法を検索 • 失効、あるいは未取得の統計情報の検知 • より効率的な実行計画を実現するために必要なオブジェクト (索引やビュー等) の検出 • SQLの再構成 • 新しい実行計画の選択と SQLプロファイルの生成 • 使用した結果、オブジェクトの統計情報の再取得や索引などの作成が必要に応じてアドバイスされ、SQLプロファイル や推奨事項として結果を出力 STS SQL Tuning Advisor 問題のあるSQLに対し アドバイス出力 ✓ 統計情報の取得 ✓ 索引の作成 ✓ SQL文の再構成 etc.
23. 例1：オプティマイザ統計情報が収集されていない表に対し、統計情報の取得を推奨する 23 Copyright © 2025, Oracle and/or its affiliates STAのアドバイス例 実際に出力されるアドバイス例 表"APUSER"."AP_TBL1"は分析されませんでし た。 - この表に対するオプティマイザ統計の収集を検 討してください。 execute dbms_stats.gather_table_stats(ownnam e => 'AP_USER', tabname => 'AP_TBL1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); ID 1 2 3 ・・・ 表 索引 SQL文 なし 統計情報 TABLE_NAME NUM_ROWS AP_TBL2 1000000 AP_TBL3 2000000 AP_TBL4 3000000 AP_TBL1の統計情報 が存在しない SQL> select count(1) from AP_TBL1;
24. 例2：非効率なSQL構文をチェックし、SQLのリライトを推奨する 24 Copyright © 2025, Oracle and/or its affiliates STAのアドバイス例 MESSAGE=述語TO_CHAR("AP_TBL2 "."ID")='100'(実行計画の行ID2)は、索引付 けされた列"ID"に式が含まれています。 この式のためにオプティマイザは表“APUSER"." AP_TBL2"の索引を選択できません。述語が非等 価性条件の場合や、索引付けされた列に式または 暗黙的データ型変換が存在する場合、オプティマイ ザでは索引を使用できません。 索引を活用するために、述語を同等の形式にリラ イトしてください。または、式にファンクション索引を 作成してください。 SQL> select count(1) from AP_TBL2 2 where to_char(ID) = ‘100’; ID 1 2 3 ・・・ 表 索引 SQL文 IDX_TBL2 ON AP_TBL2 (ID) 統計情報 TABLE_NAME NUM_ROWS AP_TBL2 1000000 AP_TBL3 2000000 AP_TBL4 3000000 索引はあるが、SQL 文の左辺(表列側)に式 (関数)が含まれているた め、索引が使用できない 実際に出力されるアドバイス例
25. 「SPA×STA」の利用イメージ 25 Copyright © 2025, Oracle and/or its affiliates
26. 26 Copyright © 2025, Oracle and/or its affiliates 「SPA×STA」の利用の全体像 ①空のSTSの作成 ②カーソル・キャッシュからSQLを 取得し、STSへ格納 ③ステージング表へSTSをパック ④ステージング表/オブジェクト/ データをエクスポート→インポート ⑤ステージング表のアンパック ⑥SPAタスクの作成 ⑦SPA実行 ⑧レポート出力、確認 ⑨STA対象とする新たなSTSの 作成 ⑩STAタスクの作成 ⑪STA実行 ⑫レポートの出力、確認 ⑬チューニング案の実装 ⑭SPA再実行 ⑮レポートの出力、確認 STS SQL ステージング 表 STS STS ステージング 表 STS Data Pump 変更前 変更後 フィルタ STS 変更前 チューニング 後 表 CREATE INDEX・・・ STS SPA手順 STA手順
27. 27 Copyright © 2025, Oracle and/or its affiliates 「SPA×STA」の利用の全体像 ①空のSTSの作成 ②カーソル・キャッシュからSQLを 取得し、STSへ格納 ③ステージング表へSTSをパック ④ステージング表/オブジェクト/ データをエクスポート→インポート ⑤ステージング表のアンパック ⑥SPAタスクの作成 ⑦SPA実行 ⑧レポート出力、確認 ⑨STA対象とする新たなSTSの 作成 ⑩STAタスクの作成 ⑪STA実行 ⑫レポートの出力、確認 ⑬チューニング案の実装 ⑭SPA再実行 ⑮レポートの出力、確認 STS SQL ステージング 表 STS STS ステージング 表 STS Data Pump 変更前 変更後 フィルタ STS 変更前 チューニング 後 表 CREATE INDEX・・・ STS 重要な箇所に絞って ポイントをお伝えします。 ②カーソル・キャッシュからSQLを 取得し、STSへ格納 ⑥SPAタスクの作成 ⑦SPA実行 ⑧レポート出力、確認 ⑨STA対象とする新たなSTSの 作成 ⑫レポートの出力、確認 ⑬チューニング案の実装 ⑮レポートの出力、確認
28. ②カーソル・キャッシュからSQLを取得し、STSへ格納 28 Copyright © 2025, Oracle and/or its affiliates SPA利用の流れ SQL> BEGIN 2 DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( 3 sqlset_name => 'STS_TEST', 4 time_limit => 60, 5 repeat_interval => 20, 6 capture_option => 'INSERT', 7 basic_filter => 'parsing_schema_name = ''TESTUSER'' 8 AND plan_hash_value!=0' 9 ,recursive_sql=> 'no_recursive_sql' 10 ); 11 END; 12 / PL/SQL procedure successfully completed. SQL> SELECT name,statement_count,created,description FROM user_sqlset; NAME STATEMENT_COUNT CREATED DESCRIPTION -------------------- --------------- -------------------- -------------------- STS_TEST 4 21-APR-25 本番環境 basic_filterでキャプチャするSQLにフィルタリング をかけることが可能。 指定例 ✓ parsing_schema_name：取得対象ス キーマを指定 ✓ plan_hash_value：!=0を指定することで 実行計画が無いSQLを除外 ✓ recursive_sql：no_recursive_sqlを指 定することで再帰SQLを除外
29. ⑥SPAタスクの作成／⑦SPA実行 29 Copyright © 2025, Oracle and/or its affiliates SPA利用の流れ 分析タスク SPA_TASK_01 STS_TEST ①実行 SPA_EXEC_OLD ②実行 SPA_EXEC_NEW ③実行 SPA_EXEC_COMPARE STS_TESTと紐付け 構成変更前の環境の実行計画、 実行統計の取得 構成変更後の環境の実行計画、 実行統計の取得 構成変更前と構成変更後のSPA 実行結果（SPA_EXEC_OLDと SPA_EXEC_NEW）の比較 STS DBMS_SQLPAパッケージの CREATE_ANALYSIS_TASKファン クションで分析タスク 「SPA_TASK_01」を作成 DBMS_SQLPAパッケージの DBMS_SQLPA.EXECUTE_ANALYSIS_T ASKファンクションで分析タスク 「SPA_TASK_01」を実行
30. ⑧レポート出力、確認 30 SPA利用の流れ テスト環境 SPA対象のSQL全量のうち、 実行計画変動SQLや性能劣化 SQLの本数を確認 特に変化量の大きいSQL について個別で確認 比較対象となる、SPAの実行 名などの情報が確認可能 Copyright © 2025, Oracle and/or its affiliates
31. SPAの結果を基に、機械的にチューニング対象を選定することが可能 31 Copyright © 2025, Oracle and/or its affiliates SPA／STA実行対象とするSQLのイメージ SPA対象のSQL本数（本番環境から取得したSTSに含まれるSQL全量） SPAの結果 絞り込める本数 性能が変化しない／向上するSQL本数 人力でチューニング対象を選定する必要がない SPAの結果を活用してフィルタリングをかけることで、 新たなSTSの作成が可能 性能が劣化したSQL本数
32. ⑨STA対象とする新たなSTSの作成 32 Copyright © 2025, Oracle and/or its affiliates STA利用の流れ SQL> DECLARE 2 sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; 3 BEGIN 4 DBMS_SQLTUNE.CREATE_SQLSET('STS_FOR_STA', 'STS from STS(STS_TEST)'); 5 OPEN sqlset_cur FOR 6 SELECT value(p) 7 FROM table( 8 DBMS_SQLTUNE.SELECT_SQLPA_TASK( 9 task_name => 'SPA_TASK_01', 10 execution_name => 'SPA_EXEC_COMPARE', 11 level_filter => 'REGRESSED')) p; 12 DBMS_SQLTUNE.LOAD_SQLSET('STS_FOR_STA', sqlset_cur); 13 CLOSE sqlset_cur; 14 END; 15 / PL/SQL procedure successfully completed. SQL> SELECT name,statement_count,description FROM user_sqlset; NAME STATEMENT_COUNT DESCRIPTION -------------------- --------------- ------------------------------ STS_FOR_STA 2 STS from STS(STS_TEST) STS_TEST 4 テスト環境 level_filterで新たに作成するSTSに含め るSQLをフィルタリングすることが可能。 REGRESSEDを指定することで、SPAタ スクの比較実行結果のうち、性能が劣化 したもののみに絞ることができる
33. ⑫レポートの出力、確認 33 STA利用の流れ テスト環境 STAによる推奨項目 チューニング案を実装した場合の効 果（実行計画の変化）も確認可能 Copyright © 2025, Oracle and/or its affiliates
34. ⑬チューニング案の実装 34 Copyright © 2025, Oracle and/or its affiliates STA利用の流れ SQL> create index TESTUSER.IDX$$_00100002 on TESTUSER.TAB2("COL3"); Index created. SQL> SELECT index_name, table_name, uniqueness, index_type 2 FROM user_indexes 3 WHERE table_name = 'TAB2'; INDEX_NAME TABLE_NAME UNIQUENES INDEX_TYPE ----------------- ---------------- ------------ ----------- IDX$$_00100002 TAB2 NONUNIQUE NORMAL テスト環境 レポートにチューニング案の実装SQLまで 含めて出力してくれるため、それを実行す ることだけで簡単に実装し効果を確認する ことが可能
35. ⑮レポートの出力、確認 35 Copyright © 2025, Oracle and/or its affiliates STA実行後のSPA再実行 テスト環境 作成したINDEXが使用されて 実行計画が改善されているこ とを確認
36. まとめ 36 Copyright © 2025, Oracle and/or its affiliates
37. 本セッションの目的 Copyright © 2025, Oracle and/or its affiliates 37 SPA、STAそれぞれの技術紹介 ✓ 皆様のテストに関する困りごとを解決する手段の一つとして、 SPA、STAが有用であることを認識していただくこと ✓ 「SPA×STA」という技術の掛け合わせによりより強力な効果 を発揮することを実感いただくこと 再掲
38. 本日のまとめ Copyright © 2025, Oracle and/or its affiliates 38 SPAのメリット • テスト方式や確認項目が明確なため、テスト計画に多くのコストや時間を割く必要がない • 本番環境で流れるSQLをキャプチャし、自動でテストが実施できるため、網羅性の高いテストを行うことが可能 STAのメリット • 自動でチューニング推奨案を導出し、適用まで支援するため、「高い技術力を持った専用技術者がいないとチュー ニングが進まない」といった属人化によるテスト期間の長期化やコスト増加を防ぐ ✓ SPAとSTAを活用することで、 「高品質、低コスト、短納期のテスト」を実現することが可能 • SPAの結果から機械的にチューニング対象とするSQLを抽出することが可能なため、チューニング対象を選定する 期間やコストを削減 • SPAとSTAを掛け合わせることで、テストとチューニングのサイクルを回すことが可能 ✓ SPAの結果からSTA対象を絞り込み、チューニング効果を再度SPAで確認することが可能
39. Thank you 39 Copyright © 2025, Oracle and/or its affiliates お気軽にご連絡ください。 mayu.kurosawa@oracle.com