Database, SQL and PL/SQL

Better Workload, Better Testing

Use database replay to record and replay database activities for better testing.

By Arup Nanda Oracle ACE Director

January/February 2008

One of the most important processes for testing the impact of system changes—both hardware and software—on a database is setting up a test environment that faithfully represents the database's actual workload. Although it's possible to write SQL statements that reflect business functionality, it's a task that demands an enormous time commitment and it's very possible that these SQL statements may not represent the database activities accurately. During any environmental change, it is important to run the actual SQL statements—as opposed to a sample of (hopefully) representative statements—against the proposed new system configuration while maintaining their concurrency and load characteristics.

Oracle Database 11g includes Oracle Real Application Testing, a suite of tools that record, replay, and analyze actual database transactions. One tool in the suite—database replay—can record transactions as they occur in the database. Later the captured workload can be replayed against a copy of the database on the proposed new platform. Most important, database replay can replay with the exact timing, concurrency, and transaction characteristics of the original workload.

Figure 1 shows the capture and replay processes of database replay. When database replay is activated, the capture process records the database operations as they occur (1) in several Workload Capture (WC) files. These files are stored in a directory specified by the DBA (2). Later, after the capture is complete, the files are moved to the remote system by some mechanism such as FTP (3). When activated, a replay driver (4) reads the WC files and a replay client replays them against the target database (5).


Database replay can be used via both the command line and Oracle Enterprise Manager. To use database replay from Oracle Enterprise Manager,

1. First create a directory object in the database where these capture files will be stored. For example,

create directory replaydir as 

2. Open the Oracle Enterprise Manager Database Control, and click the Software and Support tab, as shown in Figure 2. On that page, click the Database Replay link (under Real Application Testing).

3. The main Database Replay page displays the three high-level tasks—Capture Workload, Preprocess Captured Workload, and Replay Workload—as shown in Figure 3. Click the Capture Workload task.

4. On the next page, answer questions such as whether you want to shut down the database before capturing. Shutting down is not necessary but is recommended, because it prevents transactions from being captured in midflight and allows for a clean starting point for capture. Also enter the directory—REPLAYDIR, in this example—that will be used to store the capture files. Set a filter so that activities by certain users such as SYS and SYSMAN and certain commands such as perl and sqlplus are excluded. You can filter out activities based on program, users, module, and so on. Conversely, you can also record specific types of activities. 5. Click Submit to submit the capture job. The next screen shows the status of the capture as "In Progress." The activities on the database are being captured now. Note the starting time for the capture; in this example, the starting time is 11:30:04 a.m. on July 11, 2007. 6. Let the process run long enough for most, if not all, activities to be captured. In this example, a period of two days is likely to be enough. 7. Navigate to the Database Replay main page (as shown in step 2), and click the link for the current workload. Click Stop to stop the recording for the capture. 8. Navigate to the /home/oracle/replaydir directory. The captured workload files are located here. Transfer the files to the target system (using FTP or some other means). The database takes two Automatic Workload Repository (AWR) snapshots—one before and one after the capture process—and exports them at the end of the capture process automatically.


While the capture is going on, create a copy of the production database on the target system. Restore a backup from the production database, and recover it up to the minute the capture started—in this example, July 11, 2007 11:30:04 a.m.—using Recovery Manager (RMAN), as follows:

RMAN> run {
2> set until time '2007-07-11 11:30:04';
3> restore database;
4> recover database;
5> }

After the recovery completes, the database on the target system is exactly as the production database was when the workload capture started. Now replay the captured workload, as described in the following steps:

1. First, preprocess the captured files. Preprocess is required only once per capture; it gets the captured files ready to be replayed. Bring up Oracle Enterprise Manager on the new server, and navigate to the main Database Replay page, as shown in step 2 of the capture. From the Oracle Enterprise Manager screen shown in Figure 3, choose Preprocess Captured Workload (the second task). Provide the information requested—including the directory name, the OS username ("oracle," most likely), OS password, database userid and password, and so on—and click Submit . The preprocessing is done by a job, and once the job completes, the workload displays as "preprocessed" and ready for replay. 2. Navigate to the Database Replay main page, and choose Replay Workload (the third task). Answer the same types of questions as in the preprocessing step, and click Submit at the end. 3. The database is now ready for replay. The screen appears as shown in Figure 4. This screen indicates that the replay driver has been started but the actual replay has not.

4. The replay runs in a process called a replay client . Start a replay client from the command line, using the wrc command, as shown in Listing 1.

Code Listing 1: Replay clients

$ wrc userid=system password=<Password> replaydir=/home/oracle/replay
Workload Replay Client: Release - Production on  ....
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Wait for the replay to start (02:50:03)
Replay started (02:51:34)

5. When the replay finishes, the replay client displays

Replay finished (04:53:03)

Oracle Enterprise Manager shows the status as "Completed." Click Report to show statistics about the capture and replay.


Now that the workload captured earlier has been replayed on the target system, check for errors. To do so, click View Workload Replay Report . Examine the report for any errors due to bugs or other factors. Once that is done, note how the new system's performance compares to the old one's.

To compare the old and new systems, compare the AWR reports for the periods of capture and replay. AWR reports show various performance metrics of the database over a period of time. In Oracle Enterprise Manager, navigate to the Performance tab (shown in Figure 2), select the snapshots for the capture and replay times, and perform a side-by-side comparison of the two periods. Figure 5 is part of the AWR report, showing events taking the highest percentage of database time side by side.

This comparison shows a simple analysis of the performance of the new and the old systems. Key metrics include CPU time, which is only 8.47 percent of the database time on the new system, compared to almost 18 percent on the old. The db file sequential read is about 11 percent of the database time on the new system, compared to about 19 percent on the old. These metrics are key indicators of how the new system performed, and all the metrics are based on the actual operations of the production database, not synthetic operations, so it can be said with a very high degree of confidence that the new system will outperform the old.

figure 1
Figure 1: Architecture of database replay
figure 2
Figure 2: Main Oracle Enterprise Manager Database Control page, Software and Support tab
figure 3
Figure 3: Main Database Replay page
figure 4
Figure 4: Start of the replay driver
figure 5
Figure 5: Key metrics on the old and new systems


More Changes

If you want to make changes to the new system and retest, you can replay the captured workload. There is no need to recapture the workload or perform the preprocessing again. For example, first create a restore point:

create restore point pre_change;

Now make the changes to the new system, and finally run the replay on the new system. If, after analyzing the results of the new replay, you need to further modify the new system and run another replay, first roll back all the changes, by issuing

flashback database to restore point 

You can perform this change/analyze/rollback cycle as many times as you need to until you determine the best configuration for your specific workload.


Change is inevitable—parameters change, patches need to be applied, hardware is replaced, and on and on. Like death and taxes, change is unavoidable, but that's where the analogy stops. If the exact outcome of a change can be predicted with certainty, the risk of implementation disappears or at least drops significantly to an acceptable level. Database replay enables DBAs, without involving any other group, to assess the exact impact of a change with a few simple commands while using the actual database workload. The reduction in risk allows for a true high-availability infrastructure.

Next Steps

 Read more about Oracle Real Application Testing

Database replay


Photography by Ricardo Gomez, Unsplash