日本オラクル株式会社 SE環境におけるRAT利用手順

Copyright © 2021, Oracle and/or its affiliates 2 1 Standard EditionにおけるRATの利用 RAT概要（Real Application Testing） アジェンダ 2

Copyright © 2021, Oracle and/or its affiliates ◼ Oracle Database自身によるテスト機能 (*) ◼ 移行・アップグレード・新機能導入など インフラ変更に伴うテストを自動化 ◼ 実SQL・ワークロードに基づくテストにより 高い網羅度とテストの正確性を実現 ◼ アプリケーションチームのテスト作業負荷を 大幅に軽減 Oracle Real Application Testing 現行環境(本番環境) テスト環境 AP DB SQL・ワークロード を取得 AP不要 取得したSQL・ ワークロード 実SQL・ワークロードを取得すること で、高い網羅度と正確性を実現 DB機能による自動化テストにより、 低作業負荷で効率的にテスト 3

4 Oracle Real Application Testing 機能 Database Replay データベース統合 ワークベンチ SQL Performance Analyzer SPA クイック・ チェック ワークロー ドのストレス・テスト、 容量計画、スケールアップテスト 問題検出と修復のために設計 統合テストとキャパシティ・ プランニング・ソリューション エンドツーエンドの 統合ソリューション エンドユーザに影響を及ぼさず、 本番データベース上でシステム 変更を素早く、簡単に検証 環境の変更が SQL の実行計画と 実行統計情報に及ぼす影響を詳 細に評価 Lower Risk Automated Cut Costs Copyright © 2021, Oracle and/or its affiliates

5 • 本番環境 SQL Performance Analyzer クライアント AP サーバ DB サーバ / ストレージ ・・・ 指定期間に実行され た SQL 群の情報 ①SQL ワークロード取得 ②変更前の SQL 実行 ③変更後の SQL 実行 ④分析 STS STS 実行計画 ( 変更後 ) 実行計画 ( 変更前 ) 比較分析 STS : SQL Tuning Set 実行計画 ( 変更後 ) 実行計画 ( 変更前 ) 本番環境とできる限り一致するようにテスト環境を構成する システム変更が SQL に与える影響を SQL パフォーマンス・アナライ ザでより正確に予測できる • テスト環境 Copyright © 2021, Oracle and/or its affiliates

6 1. 本番環境で SQL ワークロードを取得し STS ( SQL Tuning Set ) に格納 SQLワークロード情報は、一定時間 ( 指定可能 ) ごとにカーソルキャッシュや AWR ( 自動 ワークロードリポジトリ ) など複数のソースから情報を収集して格納 2. STS を本番環境からテスト環境に Export / Import で移動 STS に対して SPA を実行することで、変更前のワークロードのパフォーマンスを測定 3. データベースのアップグレードやオプティマイザ統計情報の更新などの変更を実施 4. STS に対して SPA を実行することで、変更後のワークロードのパフォーマンスを測定 5. STS、テスト環境での2回の実行におけるパフォーマンス比較、リグレッションが発生した SQL 文、改善された SQL 文、変更されなかった SQL 文を特定 SQL Performance Analyzer 実行ステップ Copyright © 2021, Oracle and/or its affiliates

Copyright © 2021, Oracle and/or its affiliates 実行計画比較レポートのサンプル トータルのSQL数や、実行計画が変化したSQL数、実行時 エラーが発生したSQL数などが分かる 具体的にどのSQLの実行計画が変化したかを 特定できる 7

STSとは STS（SQL Tuning Set）とは… ・下記の情報を含むデータベース・オブジェクトです(10g R2～) -SQL文 -実行コンテキスト（スキーマ、アプリケーション・モジュール名、バインド値など） -実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など） -実行計画 ・RATのSPAやアドバイザ等に対するINPUTとして利用できます ・エキスポート、インポートでデータベース間を移動できます カーソル・キャッシュ AWR SQLトレース 他のSTS STS SPA（SQL Performance Analyzer） SQL Tuning Advisor SQL Access Advisor SQL Plan Management Copyright © 2021, Oracle and/or its affiliates 8

CREATE TYPE sqlset_row AS object ( sql_id VARCHAR(13), force_matching_signature NUMBER, sql_text CLOB, object_list sql_objects, bind_data RAW(2000), parsing_schema_name VARCHAR2(30), module VARCHAR2(48), action VARCHAR2(32), elapsed_time NUMBER, cpu_time NUMBER, buffer_gets NUMBER, disk_reads NUMBER, direct_writes NUMBER, rows_processed NUMBER, fetches NUMBER, executions NUMBER, end_of_fetch_count NUMBER, optimizer_cost NUMBER, optimizer_env RAW(2000), priority NUMBER, command_type NUMBER, first_load_time VARCHAR2(19), stat_period NUMBER, active_stat_period NUMBER, other CLOB, plan_hash_value NUMBER, sql_plan sql_plan_table_type, bind_list sql_binds) 【参考】STSには、「SQLSET_ROW」というオブジェクト型でSQLの情報が格納されています。 SQLSET_ROWの構文 STSとは Copyright © 2021, Oracle and/or its affiliates 9

Copyright © 2021, Oracle and/or its affiliates 2 1 Standard EditionにおけるRATの利用 RAT概要（Real Application Testing） アジェンダ 10

基本的な考え方 • RATの機能が使用されるサーバーに対してライセンスが必要 「RATの機能」の主な例 • SPA試行 (リモート、ローカルの両方)、SPAレポートの作成 • DB Replayのキャプチャ、プリプロセス、リプレイ、リプレイレポートの作成 「RATの機能」に含まれないものの例 • SQL Tuning Setの使用 (Enterprise Editionの基本機能 *2017年3月より) • SPA試行後のSQLチューニングアドバイザの使用 (Tuning Pack) • DB Replayのリプレイ後のAWRレポート作成 (Diagnostics Pack) • DB Replay用のリプレイクライアント (Oracle Client(無償)) RATライセンスについての考え方 詳細は「Oracle® Databaseライセンス情報」を参照 Copyright © 2021, Oracle and/or its affiliates 11

ライセンスについての考え方(STS/SPA) AP 本番環境 (またはSQL取得が可能な同等の環境) テスト環境 変更前/ 変更後 環境 リモートDB DB Link STS STS STSを利用するためには Enterprise Editionが必要 (Optionは不要) RATが必要 (SPA試行元、試行先、 SPAレポート作成のため) SPA Copyright © 2021, Oracle and/or its affiliates 12

13 TraceファイルからSTSを作成する SQL Performance Analyzer (本番環境がSEの場合) クライアント AP サーバ DB サーバ / ストレージ ・・・ ①SQL ワークロード取得 ②変更前の SQL 実行 ③変更後の SQL 実行 ④分析 Trace Trace 実行計画 ( 変更後 ) 実行計画 ( 変更前 ) 比較分析 実行計画 ( 変更後 ) 実行計画 ( 変更前 ) 本番環境とできる限り一致するようにテスト環境を構成する システム変更が SQL に与える影響を SQL パフォーマンス・アナライ ザでより正確に予測できる • テスト環境 STS • 本番環境 Copyright © 2021, Oracle and/or its affiliates

Copyright © 2021, Oracle and/or its affiliates テスト環境 DBCS 19c EE HP 本番環境 11gR2 SE SQL Performance Analyzer (本番環境がSEの場合) トレースファイルの出力設定 ワークロード処理 トレースファイル出力停止 マッピングテーブルの作成 ※ トレースファイルを本番環境にコピー 本番環境のデータベース移行 ※トレース→STSへの変換のために作成 マッピングテーブルの作成 マッピングテーブルのデータ移行 トレースファイルからSTSを作成 STSから1回目のSPAを実行 テスト環境内での処理で2回目のSPAを実行 2回の試行結果からレポート作成 14

Copyright © 2021, Oracle and/or its affiliates 利用環境によりますが、SEでSPAを実行する場合以下のような事象が発生する可能性があります。 • 生成されたレポートに欠損がある (エラー確認はできるが、性能情報の出力はできない など) • バインド変数がセットされたSQLの抽出ができない 注意事項 SE環境でのSPA利用について 欠損のあるレポート 正常なレポート 15

Trace,STS, Oracle Enterprise Managerを使用してSQLチューニング・セットの管理方法(作成、ロード、転送) Oracle Database 2日でパフォーマンス・チューニング・ガイド 19c - 12.2 SQLチューニング・セットの管理 APIを使用してSQLチューニング・セットを管理方法(作成、ロード、転送) Oracle Database SQLチューニング・ガイド 19c - 22 アプリケーショントレースの実行 - 23 SQLチューニング・セットの管理 SPA Oracle Database Testingガイド 19c - 2 SQLパフォーマンス・アナライザの概要 ～ 6 SQL試行の比較 参考 Trace,STS,SPAマニュアル Copyright © 2021, Oracle and/or its affiliates 16

Copyright © 2021, Oracle and/or its affiliates SQL*Plusからテスト対象のワークロードが実行可能な場合 参考 SE RAT手順 トレースファイル出力1 SQL> conn scott/tiger ##処理を実行するユーザに接続 ##SQLトレースを有効に設定 SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE; #トレースファイルに時刻を出力する設定 SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED; #トレースファイルの最大サイズを指定(ここでは無制限に指定しているため、該当ディレクトリの空き容量に注意してください) #トレースファイルに'SQLTRACE'という名前を付与し分かりやすくする SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='SQLTRACE'; #レベル4のセッションレベルトレースを有効化(レベル4はLEVEL 1 + バインド変数) SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4' 変更前環境(本番環境) 17

Copyright © 2021, Oracle and/or its affiliates アプリケーションからテスト対象のワークロードを実行する場合 参考 SE RAT手順 トレースファイル出力2 SQL> conn scott/tiger ##処理を実行するユーザに接続 ##ログオン時にSQLトレースを取得するトリガーを作成 SQL> CREATE OR REPLACE TRIGGER logon_sqltrace AFTER LOGON ON SCHEMA BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS=TRUE'; EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''; EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''SQLTRACE'' '; EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED'; END; / ##ログオフ時にSQLトレースの取得を停止するトリガーを作成 SQL> CREATE OR REPLACE TRIGGER logoff_sqltrace BEFORE LOGOFF ON SCHEMA BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT OFF'' '; END; / 変更前環境(本番環境) 18

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 トレースファイルの出力場所を確認 ##トレースファイルの場所 • デフォルトはこちら [ORACLE_BASE]/diag/rdbms/[db_name]/[SID]/trace/ • 確認方法 SQL> SHOW PARAMETER USER_DUMP_DEST SQLTRACEと名前の付いたト レースファイルが出力されて いる #ディレクトリの確認 変更前環境(本番環境) 19

Copyright © 2021, Oracle and/or its affiliates • SQL*Plusもしくはアプリケーションからワークロード処理を行ってください • ワークロードに応じてトレースファイルが更新されます 参考 SE RAT手順 ワークロード処理 変更前環境(本番環境) 20

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 マッピングテーブルの作成 SQL> create table mapping_table as select object_id id, owner, substr(object_name, 1, 30) name from dba_objects where object_type NOT IN ('CONSUMER GROUP','EVALUATION CONTEXT','JAVA DATA','JAVA RESOURCE','LIBRARY','LOB','OPERATOR','PACKAGE','PACKAGE BODY','PROCEDURE','QUEUE','RESOURCE PLAN','TRIGGER','TYPE','TYPE BODY‘) union all select user_id id, username owner, null name from dba_users; ##トレースファイルをSTSに変換するためのマッピングテーブルを作成 テーブル作成SQLとテーブル内のデータはテスト環境でも利用します。 変更前環境(本番環境) 21

Copyright © 2021, Oracle and/or its affiliates • Data Pumpなどの機能を用いてテスト環境を作成 • テスト環境にもマッピングテーブルを作成しテーブル内のデータを移行 • 本番環境で取得したトレースファイルをテスト環境へコピー (本手順では/home/oracle/STS_WORKへコピーしています) 参考 SE RAT手順 テスト環境作成 変更後環境(テスト環境) 22

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 トレースファイルからSTSを作成 変更後環境(テスト環境) SQL> create or replace directory MYDIR as '/home/oracle/STS_WORK'; #SQLトレースファイルの保存場所のディレクトリを宣言 #CREATE_SQLSET API を使用して STS を作成し、SELECT_SQL_TRACE から返されるカー ソルを LOAD_SQLSET に渡す DECLARE cur sys_refcursor; BEGIN dbms_sqltune.create_sqlset('trace_toSTS'); open cur for select value(p) from table(dbms_sqltune.select_sql_trace( directory => 'MYDIR', file_name => '%DB0908_ora', mapping_table_name => 'MAPPING_TABLE')) p; dbms_sqltune.load_sqlset( sqlset_name => 'trace_toSTS', populate_cursor => cur, commit_rows => 1); close cur; END; / 出力されたトレースファイルの名前に 合わせて編集してください 23

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 STSから1回目のSPAを実行 変更後環境(テスト環境) SQL> begin dbms_sqlpa.execute_analysis_task( task_name => 'SPA_TASK', execution_name => 'NewTrial', execution_type => 'CONVERT SQLSET', execution_desc => 'fromSTS'); end; / #STSからSPAを実行 24

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 テスト環境内で2回目のSPAを実行 変更後環境(テスト環境) begin dbms_sqlpa.execute_analysis_task( task_name => 'SPA_TASK', execution_name => 'New19Trial', execution_type => 'TEST EXECUTE', execution_desc => '19Execute'); end; / #テスト環境内でSPAを実行 25

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 テスト環境内で2回目のSPAを実行 変更後環境(テスト環境) begin dbms_sqlpa.execute_analysis_task( task_name => 'SPA_TASK', execution_name => 'New19Trial', execution_type => 'TEST EXECUTE', execution_desc => '19Execute'); end; / #テスト環境内でSPAを実行 26

Copyright © 2021, Oracle and/or its affiliates 参考 SE RAT手順 2回のSPA実行を比較するレポートを出力 set heading off long 1000000000 longchunksize 10000 echo off; set linesize 1000 trimspool on; spool "/home/oracle/STS_WORK/SPA_Report.html" select xmltype(dbms_sqlpa.report_analysis_task( 'SPA_TASK', /* task_name */ 'html', /* type */ 'typical', /* level */ 'ALL', /* section */ null, /* object_id */ 100, /* top_sql */ null) /* execution_name */ ).getclobval(0,0) from dual; spool off #レポート出力処理 変更後環境(テスト環境) 27