Real Application Testing Julian Dyke Independent Consultant Web

Real Application Testing Introduction u u u 2 Introduced in Oracle 11. 1 Licensable option Includes u Database Replay u SQL Performance Analyzer u Database Replay u Captures database workload on production system u Replays captured workload on test system u Optionally includes concurrency and timing characteristics u Generates reports and recommends changes u SQL Performance Analyzer u Identifies performance divergence between workloads on source and target platforms u Generates tuning recommendations © 2010 Julian Dyke juliandyke. com

Real Application Testing Database Replay 3 u Requires the following steps: u Capture workload into capture files u Copy files to test system and preprocess them u Replay files on test system u Perform detailed analysis of workload capture and replay using reports generated by Database Replay u Potential applications u Test operating system upgrades u Test database upgrades and migrations u Test parameter changes u Evaluate migration from single-instance to RAC u Test storage changes u Debugging u Test database manageability features © 2010 Julian Dyke juliandyke. com

Real Application Testing Workload Capture u To capture workload on an existing system: u Create a operating system directory e. g. [oracle@server 14]$ mkdir /home/oracle/rat/test 1 u Create an Oracle directory e. g. : SQL> CREATE DIRECTORY dir 1 AS '/home/oracle/rat/test 1'; u Start the capture process: dbms_workload_capture. start_capture ( name => 'TEST 4', dir => 'DIR 4' ); u Run the workload: u Finish the capture process: dbms_workload_capture. finish_capture; 4 © 2010 Julian Dyke juliandyke. com

Real Application Testing Replay Preparation u u Copy the capture files to the target database server On the target database u Preprocess the captured workload dbms_workload_replay. process_capture ( capture_dir => 'DIR 1' ); u Initialize the replay dbms_workload_replay. initialize_replay ( replay_name => 'REPLAY 1', replay_dir => 'DIR 1' ); u Prepare the replay dbms_workload_replay. prepare_replay ( synchronization => FALSE ); 5 © 2010 Julian Dyke juliandyke. com

Real Application Testing Workload Replay u u Copy the workload files to the client system. For example: u /home/oracle/rat/test 1 On the client u Run the workload client to calibrate the replay $ wrc tpcc/tpcc@cluster 1 -scan mode=CALIBRATE replaydir=/home/oracle/rat/test 1 u u The calibration process recommends the number of client processes required to perform the replay Replay the workload $ wrc tpcc/tpcc@cluster 1 -scan mode=REPLAY replaydir=/home/oracle/rat/test 1 u 6 The client will not start to replay the workload until the replay has been started on the target database © 2010 Julian Dyke juliandyke. com

Real Application Testing Workload Replay u On the target database u Start the replay process dbms_workload_replay. start_replay; u When the replay process completes on the client, run the replay report SET SERVEROUTPUT ON TRIMSPOOL ON LONG 500000 LINESIZE 200 VAR v_rep_rpt CLOB; DECLARE l_cap_id NUMBER; l_rep_id NUMBER; BEGIN l_cap_id : = dbms_workload_replay. get_replay_info (dir => 'DIR 4'); SELECT MAX (id) INTO l_rep_id FROM dba_workload_replays WHERE capture_id = l_cap_id; : v_rep_rpt : = dbms_workload_replay. report ( replay_id => l_rep_id, format => DBMS_WORKLOAD_CAPTURE. TYPE_TEXT ); END; / PRINT : v_rep_rpt 7 © 2010 Julian Dyke juliandyke. com

Real Application Testing SQL Performance Analyzer u 8 Can analyze impact on SQL performance of u Database upgrade u Configuration changes to operating system or hardware u Schema changes u Changes to database initialization parameters u Refreshing optimizer statistics u SQL tuning © 2010 Julian Dyke juliandyke. com