Command Line Scripts for Database Replay

One of new exciting features of Oracle 11g is the Real Application Testing (RAT). The RAT has two solutions – Database Replay and SQL Performance Analyzer (SPA) to address two different issues. Both have the same concept (capture then replay), but are scoped differently. Database Replay applies at database-level workload for all activities (exclusion is possible), but SPA is more granular at a specific SQL statement or its set.

Oracle extends the capture capability to the earlier versions. Note that the replay capability can only be done on Oracle 11g or higher. The minimum requirement to make 9i and 10g capable of capturing is stated in the Metalink note 560977.1 – Real Application Testing Now Available for Earlier Releases.

In our case, we would like to use the Database Replay and we do have Oracle 9.2.0.8 on Solaris which is the minimum required database version for capture, so the only patch needed is one-off patch number 6973309 (for non-Windows system).

Note that after patch, you may need to run catwrr.sql to create needed tables, views and package for workload capture.

Oracle provides sample of the command line interface scripts for Database Replay. Search Metalink for note 742645.1 – Database Replay: Command Line Interface (CLI) usage examples/scripts. I find it very useful because it is categorized based on execution tasks in order. This collection of scripts gives more flexibility and control especially when you want to automate tasks or where there is no Enterprise Manager interface for database replay (in 9.2.0.8/10.2.0.2 and 10.2.0.3).

Seven scripts provided in the db_replay_cli.zip file are self-explanatory and customizable based on your environments.

The first two scripts are executed on the capture system (in this case is 9i), and the rest on the replay system (11g).

1_start_capture.sql – Set of commands to create capture directory, create capture filters and start workload capture

2_finish_capture.sql – Set of commands to stop workload capture and export AWR

3_prepare_replay.sql – Set of commands to initialize replay, re-map connections and install replay parameters

4_start_replay_client.sql – Set of OS commands to calibrate and start replay clients

5_start_replay.sql – Command to start replay

6_reports.sql – Set of commands to import AWR, input arguments/data for capture/replay/ASH/AWR/Compare Period reports and generate these reports. In this script you can find text of PL/SQL procedure, which help you create reports easily with minimal input.

x_cancel_replay.sql – Command to cancel wokload replay in progress.