11g Real Application Testing – making changes with confidence

CASE STUDY:

Using 11g Real Application Testing to analyse the impact of making the following changes:

1) Upgrade the database from 10g to 11g
2) Convert from single instance database to a two node RAC database
3) Convert from file system based storage to 11g ASM

Overview of Real Application Testing

One of the best new features in Oracle 11g is the Real Application Testing option which enables us to test the result of a change without actually implementing the same in a production environment. Changes such as database version upgrades, optimizer changes, hardware and storage changes all carry with them an element of risk and uncertainty. Using this feature much of that uncertainty and guesswork can be avoided.

Real Application Testing comprises of two components:

a) Database Replay
b) SQL Performance Analyzer (SPA)

SPA enables us to test the impact of a change at the level of a particular SQL statement to see if any improvements or regressions occurred in the execution of the SQL statement because of the change.

Database Replay enables us to capture workload real time in a production environment and then replay the same in a clone or copy of the changed production environment with the exact timings, concurrency and transaction characteristics found in the original workload. The AWR comparison and Database Replay reports can easily help us pinpoint any potential problems and errors as well any divergence in performance.

This feature will afford businesses increased confidence and surety when faced with a system change as well as significantly reducing the costs as well as resources associated with the testing phase.

Database Capture is now supported in versions 9.2.0.8 as well as 10.2.0.3 and 10.2.0.4 enabling us to test the impact of upgrades from both 9i as well as 10g to 11g. Note, however, that replay can only be performed in a Oracle 11g database.

At a very high level, the database capture and replay will involve the following steps:

1) Prepare the source database for capture
2) Capture the workload
3) Copy the workload capture files to the target machine
4) Prepare the target database for replay and process captured workload files
5) Start the workload replay clients
6) Once the workload replay has finished, examine the generated reports and make the analysis

Read More ….