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

Slide 1

Slide 1 text

総当たりSQLテストはもう終わり！ SPA×STAで実現するリグレッションテストの省コスト化 Developer Day 2025 黒沢茉由ストラテジック・カスタマーコンサルティング第二事業本部ソーシャルインフラカスタマー本部 May 23, 2025

Slide 2

Slide 2 text

2 Copyright © 2025, Oracle and/or its affiliates 黒沢茉由（Mayu Kurosawa）ストラテジック・カスタマーコンサルティング第二事業本部ソーシャルインフラカスタマ―本部スタッフコンサルタント • 2022年日本オラクル新卒入社 • アカウントフォーカスのコンサルタントとして、データの持ち方やテストによる品質確保、データ移行の技術支援に至るまで、お客様に合った幅広い支援を担当 • 最近のマイブームはお菓子とパン作り（オーブンレンジを入手）

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

お客様のビジネスのニーズから、ITで実現すべき対応策が見えてくる 7 Copyright © 2025, Oracle and/or its affiliates 昨今のビジネスのニーズとITの関係競争力強化コスト削減リスク管理より速いスピードで新規事業の立ち上げや事業再構築を行う無駄なコストや業務を省き、リソースの最適化を図るあらゆる脅威から企業の資産や評判を守る事業拡大に伴う機能の新規開発や変更の迅速な対応柔軟な課金体系を選択できるクラウドの活用や、運用負荷を軽減する仕組みづくりサイバーセキュリティ強化のための定期的なパッチ適用

Slide 8

Slide 8 text

8 Copyright © 2025, Oracle and/or its affiliates ニーズを実現する上での課題ニーズを実現する上で不可欠な「高品質、低コスト、短納期のテスト」は実現が容易ではない品質をあげようとするとコストが増え、納期が延びる納期を守ろうとすると品質が低下し、コストが増えるコストを抑えようとすると品質が低下し、納期が延びる Cost コスト Quality 品質 Delivery 納期 QCDのトレードオフの関係頻繁な構成・機能変更に対応するには「高品質、低コスト、短納期のテスト」が必要不可欠だが、三要素を両立することは容易ではなく、結果的にどれかが犠牲となる

Slide 9

Slide 9 text

三要素の両立が難しい「高品質、低コスト、短納期のテスト」の実現のために、網羅性や効率性を高める必要がある 9 Copyright © 2025, Oracle and/or its affiliates 課題の深堀とあるべき姿高品質低コスト短納期網羅性を高めたいが、人力のテストでは時間やコストが膨大になるテストで発覚した問題対応を有識者に頼らざるを得ないが、有識者が少ない、あるいは多忙で手がつかない頻繁な構成変更の度にテスト方式や評価観点の見直しに時間がかかる機能追加や変更のスピードが求められる中で、十分なテスト期間が確保できない影響範囲を適切に設定した網羅性の高いテストの実施属人化しない迅速かつ正確なテストの実施、結果対応定められたテスト方式や評価観点の用意効率良く時間と人員を使うためのテストの自動化課題課題の深堀テストのあるべき姿下記を両立するテストの実施

Slide 10

Slide 10 text

テスト効率化の手法は様々だが、Oracle Databaseでは「SPA×STA」の解決策がある 10 Copyright © 2025, Oracle and/or its affiliates あるべき姿を実現するための解決策テストを効率化する一般的な手法テストケースの再利用影響の大きい箇所に絞ったテストケースの最適化 CI/CDツールなどを使用したテスト自動化 Oracle Databaseでの解決策 SQL Performance Analyzer （SPA） SQL Tuning Advisor （STA）

Slide 11

Slide 11 text

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のパフォーマンスを向上させる推奨事項の確認やチューニング案の実装支援

Slide 12

Slide 12 text

机上調査＋人力テストから、SPA+STAを使用したテストにシフトすることで工数を大幅に削減 12 Copyright © 2025, Oracle and/or its affiliates 従来のリグレッションテストとの作業イメージ比較例）構成変更のリリースまで想定した作業イメージ比較調査・計画テストリリース調査・計画テストリリース適用前調査適用前机上調査環境構築構成変更 STA実行・チューニング SPA 実行チューニングリグレッションテスト構成変更前机上調査単体テストシナリオ検討・サンプリング構成変更前机上調査構成変更環境構築従来 SPA ＋ STA 対処までの期間を短縮 ※リグレッションテスト、チューニングの期間はイメージであり、実際は対象SQL本数により左右されます。 ※有識者工数イメージ SPA 計画

Slide 13

Slide 13 text

Slide 14

Slide 14 text

本番環境で取得された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回分の結果を比較し、レポートを出力構成変更前構成変更後

Slide 15

Slide 15 text

構成変更前後を比較し、エラーとなったSQLや実行計画・実行統計に変化があったSQLを可視化 15 Copyright © 2025, Oracle and/or its affiliates SPAレポートのイメージトータルのSQL数や、実行計画が変化したSQL数、実行時エラーが発生したSQL数などが表示される SQLごとのメトリック（実行統計値）の変化や実行計画変動有無が確認可能ワークロード全体として劣化傾向にあるか、改善傾向にあるかを確認

Slide 16

Slide 16 text

本番環境からSQL情報やオブジェクト・統計情報などを収集 16 Copyright © 2025, Oracle and/or its affiliates SPAを実行するために必要な情報（Input） STS（SQL Tuning Set） • 本番環境（比較元環境）で実行されたSQL文や実行計画、実行統計等を記録 • SPAのインプットとなる情報オブジェクト定義 • 調査対象SQLでアクセスしているオブジェクトの定義一式オプティマイザ統計情報 • 調査対象SQLでアクセスしている表、索引、パーティションなどの統計情報一式 • SPAを使用した統計情報の取得・分析をする場合に必要実データ • 調査対象SQLでアクセスしている表のデータ一式 • SPAを使用したSQL実行、実行統計の取得・分析をする場合に必要

Slide 17

Slide 17 text

非互換調査にとどめるか、実行計画や実行統計の変動まで評価するかにより必要なデータが異なる 17 Copyright © 2025, Oracle and/or its affiliates SPA実行環境への移行データ種別ごとの実現可能タスク種別移行データ実現できるタスク実データオプティマイザ統計情報オブジェクト定義ケース1 〇〇〇 • SQLがエラーを返すかどうかの確認（非互換調査） • 実行計画が変動するかの確認（実行計画） • 実行統計が変動するかの確認（パフォーマンス）ケース2 × 〇〇 • SQLがエラーを返すかどうかの確認（非互換調査） • 実行計画が変動するかの確認（実行計画）ケース3 × × 〇 • SQLがエラーを返すかどうかの確認（非互換調査）〇：テスト環境にImport可能 ×：テスト環境にImport不可

Slide 18

Slide 18 text

アップグレード時の非互換確認やパッチ適用時の実行計画・性能変動の確認に活用 18 Copyright © 2025, Oracle and/or its affiliates SPAのユースケースアップグレード時のSQL互換性チェック • SPAを使用し、数万本のSQLからエラーの発生するSQLを発見アップグレード時/パッチ適用時の性能試験 • SPAを使用し、数万本のSQLから実行計画や性能が変化するSQLを発見定期パッチ適用による安定運用 • SPAにより定期パッチ(セキュリティ、修正)の適用に必要なテストをルーティン化

Slide 19

Slide 19 text

高いユーザ満足度が見込めつつ、コストが最適化される範囲で適切な網羅率を設定することが重要 19 Copyright © 2025, Oracle and/or its affiliates STSの取得設計テスト網羅率品質ユーザ満足度コストコスト過剰な品質領域品質ユーザ満足度【テスト網羅率と品質・コスト・ユーザ満足度の関係（イメージ）】品質の上昇に対しユーザ満足度の上昇が鈍化する網羅率の考え方 • テスト網羅率を上げると品質は向上するが、ある点を境にユーザ満足度の上昇は鈍化する（ほとんど使われていない機能のテストは、ユーザ満足度に繋がりにくい） • コストとのバランスを考慮し、ユーザ満足度に繋がらない過剰な品質の追究は避け、適切な網羅率を設定することが重要

Slide 20

Slide 20 text

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に設定可能

Slide 21

Slide 21 text

Slide 22

Slide 22 text

より効率的な実行計画やパフォーマンス改善を実現するための推奨事項を自動で出力 22 Copyright © 2025, Oracle and/or its affiliates SQLチューニング・アドバイザ（STA）とは • 負荷が高い SQLに対してパフォーマンス改善のための数々のシミュレーションを行い、以下のような項目を評価して実行計画を最適化する方法を検索 • 失効、あるいは未取得の統計情報の検知 • より効率的な実行計画を実現するために必要なオブジェクト (索引やビュー等) の検出 • SQLの再構成 • 新しい実行計画の選択と SQLプロファイルの生成 • 使用した結果、オブジェクトの統計情報の再取得や索引などの作成が必要に応じてアドバイスされ、SQLプロファイルや推奨事項として結果を出力 STS SQL Tuning Advisor 問題のあるSQLに対しアドバイス出力 ✓ 統計情報の取得 ✓ 索引の作成 ✓ SQL文の再構成 etc.

Slide 23

Slide 23 text

例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;

Slide 24

Slide 24 text

例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 文の左辺(表列側)に式 (関数)が含まれているため、索引が使用できない実際に出力されるアドバイス例

Slide 25

Slide 25 text

Slide 26

Slide 26 text

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手順

Slide 27

Slide 27 text

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の作成 ⑫レポートの出力、確認 ⑬チューニング案の実装 ⑮レポートの出力、確認

Slide 28

Slide 28 text

②カーソル・キャッシュから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を除外

Slide 29

Slide 29 text

⑥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」を実行

Slide 30

Slide 30 text

Slide 31

Slide 31 text

SPAの結果を基に、機械的にチューニング対象を選定することが可能 31 Copyright © 2025, Oracle and/or its affiliates SPA／STA実行対象とするSQLのイメージ SPA対象のSQL本数（本番環境から取得したSTSに含まれるSQL全量） SPAの結果絞り込める本数性能が変化しない／向上するSQL本数人力でチューニング対象を選定する必要がない SPAの結果を活用してフィルタリングをかけることで、新たなSTSの作成が可能性能が劣化したSQL本数

Slide 32

Slide 32 text

⑨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タスクの比較実行結果のうち、性能が劣化したもののみに絞ることができる

Slide 33

Slide 33 text

Slide 34

Slide 34 text

⑬チューニング案の実装 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まで含めて出力してくれるため、それを実行することだけで簡単に実装し効果を確認することが可能

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

本日のまとめ Copyright © 2025, Oracle and/or its affiliates 38 SPAのメリット • テスト方式や確認項目が明確なため、テスト計画に多くのコストや時間を割く必要がない • 本番環境で流れるSQLをキャプチャし、自動でテストが実施できるため、網羅性の高いテストを行うことが可能 STAのメリット • 自動でチューニング推奨案を導出し、適用まで支援するため、「高い技術力を持った専用技術者がいないとチューニングが進まない」といった属人化によるテスト期間の長期化やコスト増加を防ぐ ✓ SPAとSTAを活用することで、「高品質、低コスト、短納期のテスト」を実現することが可能 • SPAの結果から機械的にチューニング対象とするSQLを抽出することが可能なため、チューニング対象を選定する期間やコストを削減 • SPAとSTAを掛け合わせることで、テストとチューニングのサイクルを回すことが可能 ✓ SPAの結果からSTA対象を絞り込み、チューニング効果を再度SPAで確認することが可能