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 workloads 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, Ill 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, Ill 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 clients 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:
As in the previous article, Ive 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. Its time to start the Workload Replay Client (WRC) to play back the previously captured workload. Just as in the prior article, Ill 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 18.104.22.168.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, Ill do this by invoking procedure DBMS_WORKLOAD_REPLAY.START_REPLAY. The successful execution of this procedure will be reflected within the WRC terminal sessions 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 WRCs 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 its 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 databases overall performance between the database replay operations starting and ending time periods. Ive 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, Ive got some analysis to perform before I implement this application in an 11gR1 Real Application environment because they indicate that theres a lot of contention for a few data and index segments most likely due to serialization of these resources and others. Ill 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:
Ive provided SQL*Plus formatted queries against the remainder of these views in Listing 3.13.
Oracle 11gs 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 Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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