Transcript Real Application Testing

Real Application
Testing
Julian Dyke
Independent Consultant
Web Version
1
© 2010 Julian Dyke
juliandyke.com
Real Application Testing
Introduction



2
Introduced in Oracle 11.1
Licensable option
Includes
 Database Replay
 SQL Performance Analyzer

Database Replay
 Captures database workload on production system
 Replays captured workload on test system
 Optionally includes concurrency and timing characteristics
 Generates reports and recommends changes

SQL Performance Analyzer
 Identifies performance divergence between workloads on source and
target platforms
 Generates tuning recommendations
© 2010 Julian Dyke
juliandyke.com
Real Application Testing
Database Replay
3

Requires the following steps:
 Capture workload into capture files
 Copy files to test system and preprocess them
 Replay files on test system
 Perform detailed analysis of workload capture and replay using reports
generated by Database Replay

Potential applications
 Test operating system upgrades
 Test database upgrades and migrations
 Test parameter changes
 Evaluate migration from single-instance to RAC
 Test storage changes
 Debugging
 Test database manageability features
© 2010 Julian Dyke
juliandyke.com
Real Application Testing
Workload Capture

To capture workload on an existing system:
 Create a operating system directory e.g.
[oracle@server14]$ mkdir /home/oracle/rat/test1

Create an Oracle directory e.g.:
SQL> CREATE DIRECTORY dir1 AS '/home/oracle/rat/test1';

Start the capture process:
dbms_workload_capture.start_capture
(
name => 'TEST4',
dir => 'DIR4'
);

Run the workload:

Finish the capture process:
dbms_workload_capture.finish_capture;
4
© 2010 Julian Dyke
juliandyke.com
Real Application Testing
Replay Preparation


Copy the capture files to the target database server
On the target database
 Preprocess the captured workload
dbms_workload_replay.process_capture
(
capture_dir => 'DIR1'
);

Initialize the replay
dbms_workload_replay.initialize_replay
(
replay_name => 'REPLAY1',
replay_dir => 'DIR1'
);

Prepare the replay
dbms_workload_replay.prepare_replay
(
synchronization => FALSE
);
5
© 2010 Julian Dyke
juliandyke.com
Real Application Testing
Workload Replay


Copy the workload files to the client system. For example:
 /home/oracle/rat/test1
On the client
 Run the workload client to calibrate the replay
$ wrc tpcc/tpcc@cluster1-scan mode=CALIBRATE \
replaydir=/home/oracle/rat/test1


The calibration process recommends the number of client processes
required to perform the replay
Replay the workload
$ wrc tpcc/tpcc@cluster1-scan mode=REPLAY \
replaydir=/home/oracle/rat/test1

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

On the target database
 Start the replay process
dbms_workload_replay.start_replay;

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 => 'DIR4');
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

8
Can analyze impact on SQL performance of
 Database upgrade
 Configuration changes to operating system or hardware
 Schema changes
 Changes to database initialization parameters
 Refreshing optimizer statistics
 SQL tuning
© 2010 Julian Dyke
juliandyke.com
Thank you for your interest
[email protected]
9
© 2010 Julian Dyke
juliandyke.com