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

June 30, 2008

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:

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 11.1.0.6.0 - 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:

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

Conclusion

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