Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 30, 2008

Oracle Database 11g: Database Replay, Part 3 - Page 2

By Jim Czuprynski

Phase 3: Replaying the Workload

My RAC database is now finally ready to accept execution of the previously captured workload. As the previous article illustrated, there are several steps that must be followed in precise order when using Oracle 11g Enterprise Manager to start the replay, and those same steps must be followed when calling DBMS_WORKLOAD_REPLAY procedures to initiate the captured workload’s replay, remap any connections, adjust any custom replay frequency settings, and start gathering replay performance and regression statistics collection.

Initiating Database Workload Replay. To initiate the replay of the captured workload, I’ll invoke procedure DBMS_WORKLOAD_REPLAY.INITIATE_REPLAY. This places the RACDB database into INIT FOR REPLAY state – a prerequisite to moving the database into the PREPARE state via procedure DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY.

Remapping Connection Strings. To insure that all sessions that participated in workload generation against the DB10G connection on my Oracle 10gR2 single-instance database are remapped to the corresponding TESTLBA load-balanced connection on the RACDB database, I’ll use procedure DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION.

See Listing 3.7 for examples of how to invoke these two procedures and how to view the resulting remapped connection strings.

Customizing Workload Replay Options. As I described in the Database Replay primer article, Oracle 11g permits the DBA to alter the frequencies at which a workload may be played back with extreme granularity. The workload replay client’s behavior can be tightly controlled by setting several additional parameters via procedure DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY:

Table 3.1. Replay Client Options
Replay Option



Defines whether synchronization will be used during workload generation:

  • TRUE: This is the default. The order of COMMITs in the captured workload will be preserved during replay; all replay actions are executed only after all dependent COMMIT actions are completed.

  • FALSE: The original order of COMMITs will not be honored. This most likely will result in a large data divergence, but it is useful for load or stress testing.


Determines the elapsed time between two successive user calls within the same session, so it drives the replay speed:

  • The default value is 100, or 100% of the original workload generation speed.

  • If set to zero (0), calls are sent to the replay database in succession as rapidly as possible.

  • If set to > 100%, then the replay speed will decrease proportionally.


Corrects the THINK_TIME_SCALE between user calls, based on the specified percentage value. Setting this parameter to TRUE forces the Replay Client to shorten the “think time” between calls so that the total elapsed time of Database Replay more accurately matches that which was initially gathered.


Scales the elapsed time from when the workload capture started to when the session connects with the specified value. This is interpreted as the percentage of time that a user session should remain connected.

Note that Database Replay does differentiate between workload capture time and workload replay time:

  • During the capture of the workload, the elapsed time is measured by user time (the total elapsed time of a user call to the database) and user think time (the time the user waited between issuing another call).
  • During the replay of the workload, however, the elapsed time is measured by user time, user think time, and synchronization time.

As in the previous article, I’ve simply accepted the default options shown for each of these parameters by invoking procedure DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY as shown in Listing 3.8.

Initiating a Workload Replay Client. It’s time to start the Workload Replay Client (WRC) to play back the previously captured workload. Just as in the prior article, I’ll start up a replay session by opening a terminal session and invoking the WRC client executable on just one node - RACNODE2 – of my clustered database:

$>  wrc replaydir=/home/oracle/DBRControl
Workload Replay Client: Release - Production on Mon Jun 23 21:27:47 2008
Wait for the replay to start (21:27:48)

Once the WRC is started on RACNODE2, I need to tell Oracle 11g that it should take control of any active database replay operations. As shown in Listing 3.9, I’ll do this by invoking procedure DBMS_WORKLOAD_REPLAY.START_REPLAY. The successful execution of this procedure will be reflected within the WRC terminal session’s output:

Wait for the replay to start (21:27:48)
Replay started (21:28:16)

Monitoring Active Replay Operations. Listing 3.10 shows a query against the DBA_WORKLOAD_REPLAY view that produces a simple report of the current DBR workload replay state; Report 3.2 shows the result of executing this query during several different phases of the DBR workload replay until the replay operation is completed (which will also be reflected in the WRC’s session output):

Wait for the replay to start (21:27:48)
Replay started (21:28:16)
Replay finished (21:48:40)

The successful startup and completion of the DBR workload replay sessions will be recorded in the alert logs of both instances, as shown below:

>>> From RACDB1's alert log:
Tue Jun 24 21:28:01 2008
DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 06/24/2008 21:28
Tue Jun 24 21:31:04 2008
Thread 1 advanced to log sequence 92
  Current log# 2 seq# 92 mem# 0: +DATA/racdb/onlinelog/group_2.262.649041349
  Current log# 2 seq# 92 mem# 1: +FRA/racdb/onlinelog/group_2.259.649041351
Tue Jun 24 21:48:39 2008
DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 06/24/2008 21:48:40
>>> From RACDB2's alert log:
Tue Jun 24 21:28:01 2008
DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 06/24/2008 21:28
Tue Jun 24 21:48:39 2008
DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 06/24/2008 21:48:40

Phase 4: Regression Analysis

The playback of the captured workload against my RAC test environment is complete, so it’s time to turn my attention to what performance issues may arise if I decide to migrate this application code to a Real Application Clusters environment.

The Database Replay Report is probably most useful for a “macroscopic” view because it compares execution statistics between the captured and replayed workloads. I used the code in Listing 3.11 to create the report output in simple text format as shown in Report 3.3.

In addition, the Automatic Workload Repository (AWR) report summarizes and analyzes the database’s overall performance between the database replay operation’s starting and ending time periods. I’ve generated two versions of this report (one for each of the two RAC instances) using the code in Listing 3.12; the corresponding report output is shown in Report 3.4.

As these two reports demonstrate, I’ve got some analysis to perform before I implement this application in an 11gR1 Real Application environment because they indicate that there’s a lot of contention for a few data and index segments – most likely due to serialization of these resources and others. I’ll spend time in a later article deconstructing these performance issues with the new enhancements to the Automatic Database Diagnostic Monitor (ADDM) tool set.

Data Dictionary Views. Oracle 11gR1 also provides several data dictionary views that describe the results of database workload capture and replay activities, configuration details, and active DBR sessions, as shown in Table 3.2 below:

Table 3.2. Database Replay Performance and Metadata Views
Data Dicttonary View



Describes statistics from DBR Workload Capture operations


Tells which types of filters were applied during DBR Workload Capture operations


Describes results from DBR Workload Replay operations


Explains how connection mapping will affect any pending DBR Workload Replay operations


Summarizes data and error divergence encountered during DBR Workload Replay operations


Shows which database sessions are actually executing DBR Workload Replay operations

I’ve provided SQL*Plus formatted queries against the remainder of these views in Listing 3.13.


Oracle 11g’s new Database Replay capabilities provide extremely granular analysis of potential performance problems, data divergence, and error divergence during regression testing. These features have the potential to limit if not eliminate one of the biggest headaches for an Oracle DBA: the inability to tell with a sufficient degree of certainty how proposed changes to a database environment – regardless of the vector of the changes – will precisely impact the current database environment.

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

B28254-04 Oracle Database 11gR1 Real Application Clusters Configuration and Administration Guide

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM