Database Workload Replay in Oracle 11g

I recently attended RMOUG and saw a presentation for Database Workload Replay, a feature I’ve been meaning to take through its paces for quite awhile but never made time to test it out. Tonight I decided to give it a shot to see how difficult the process was, and as I discovered, it wasn’t too bad at all.   Below I’m going to walk through the steps to do some simple Workload Replay tests.  Nothing earth-shattering about any of this, it’s pretty well documented.
What is Database Replay?
Database Replay allows you to capture a database workload and replay it.  At a high level, it works like this:
– You initiate a “capture” process on a source database
– You run a workload on your source database.  In my tests, I did some testing with Oracle EBS 12.1.3
– The capture processes records the workload and writes changes to “capture files” in a “capture directory”
– When done, you stop the capture process – indicating that the workload capture is complete.
– You transfer the capture files to a different system, or choose to replay on the current environment, into a replay directory
– Then you run a replay process to replay the workload
Database Replay doesn’t rely on synthetic SQL statement or code-generation techniques – it basically intercepts SQL from the redo stream and writes in a proprietary format to capture files, which are then processed by the replay.  As many sources indicate, Workload Replay is good for testing a workload on a new or different system, testing potential configuration changes, and so forth.
Below I’m going to show some code for how to get all this done.
Setup the Source System
First you need to set aside a capture directory to store the capture files:
[email protected] u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 00:28:58 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory replay_dir as
  2  ‘/u01/replay’;
Directory created.
SQL>
Capture Workload on Source 
Now we’ll capture a workload:
SQL> @capture
SQL> set serveroutput on size 10000
SQL> declare
  2   v_capture_name varchar2(20) :=’ebsinv_replay’;
  3  begin
  4   dbms_workload_capture.start_capture(name=>v_capture_name,
  5       dir=>’REPLAY_DIR’,
  6       duration=>null,
  7       default_action=>’INCLUDE’,
  8       auto_unrestrict=>TRUE);
  9  end;
 10  /
PL/SQL procedure successfully completed.
Generate Workload
To generate our workload, I’m going to load almost 100,000 rows into MTL_TRANSACTIONS_INTERFACE and wait for the transaction managers to complete processing.  In other words, our workload will be an Oracle Inventory transaction processing workload.
Stop Capture and Copy Workload to Target
After an hour or so, my workload completed on the source so we’ll need to stop the capture:
SYS @ PROD> exec dbms_workload_capture.finish_capture();
PL/SQL procedure successfully completed.
Elapsed: 00:00:40.39
SYS @ PROD>
All of the capture files are now in my capture directory, which I’ve actually named “replay” due to lack of attention to detail.  So we’ll copy the files to a location on the database server that we want to replay on:
[[email protected] replay]$ pwd
/u01/replay
[[email protected] replay]$ scp -r * [email protected]:/u01/app/oracle/replay
[email protected]’s password:
wcr_fcapture.wmd  100%  339     0.3KB/s   00:00
wcr_cr.text       100%   11KB  11.1KB/s   00:00
wcr_cr.html       100%   29KB  29.5KB/s   00:00
(etc)
Process Captured Workload
On the target, which happens to be a databse called VISX running on Exadata:
SQL> create directory replay_dir as ‘/u01/app/oracle/replay’;
Directory created.
SQL> grant all on directory replay_dir to public;
Grant succeeded.
SQL> !ls /u01/app/oracle/replay
SQL>
When this is complete, we need to process the captured the workload.  This step could take quite a bit of time depending on the duration and activity of the workload capture on the source:
SQL> set echo on
SQL> begin
  2   dbms_workload_replay.process_capture(‘REPLAY_DIR’);
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL>
Initialize Replay
SQL> begin
  2   dbms_workload_replay.initialize_replay(‘ebsinv_replay’,
  3       ‘REPLAY_DIR’);
  4  end;
  5  /
PL/SQL procedure successfully completed.
SQL>
Prepare Replay
SQL> begin
  2    dbms_workload_replay.prepare_replay(THINK_TIME_SCALE=>0,
  3       synchronization=> FALSE);
  4  end;
  5  /
PL/SQL procedure successfully completed.
SQL>
In the above PL/SQL call, I’m choosing to use a think_time_scale=0, which means that user calls won’t wait between calls. You can dial this up to introduce some think time if appropriate.  I also tried a “connect_time_scale=0” in the preparation phase and this had the effect of starting all my sessions at once during the replay – I was able to get a 700+ AAS value for this before realizing that it’d have been just a well to run a forkboy program, so lesson learned …
Calibrate Workload
Next I calibrated the workload – this basically recommends a number of replay clients to start on the reply database host(s):
[[email protected] replay]$ wrc replaydir=/u01/app/oracle/replay \
> mode=calibrate
Workload Replay Client: Release 11.2.0.3.0 – Production on Thu Mar 1 21:39:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Report for Workload in: /u01/app/oracle/replay
———————–
Recommendation:
Consider using at least 2 clients divided among 1 CPU(s)
You will need at least 116 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
– max concurrency: 61 sessions
– total number of sessions: 1627
Assumptions:
– 1 client process per 50 concurrent sessions
– 4 client process per CPU
– 256 KB of memory cache per concurrent session
– think time scale = 100
– connect time scale = 100
– synchronization = TRUE
Start Workload Replay Clients
Since our calibrate indicated to use 2 connections, I ran the following from two different sessions:
[[email protected] replay]$ wrc connection_override=TRUE \
> replaydir=/u01/app/oracle/replay/
Workload Replay Client: Release 11.2.0.3.0 – Production on Thu Mar 1 21:42:06 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username:system
Password:
Wait for the replay to start (21:42:10)
Start Replay
From another terminal session, launch the replay by running:
SQL> begin
  2   dbms_workload_replay.start_replay;
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL>
If you look back on the session where you ran “wrc”, it will look like this:
[[email protected] replay]$ wrc connection_override=TRUE \
> replaydir=/u01/app/oracle/replay/
Workload Replay Client: Release 11.2.0.3.0 – Production on Thu Mar 1 21:42:06 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username:system
Password:
Wait for the replay to start (21:42:10)
Replay started (21:43:39)
Monitor Replay
While running, you can check the status by querying DBA_WORKLOAD_REPLAYS:
SQL> @status
SQL> col id format 999
SQL> col name format a20 head ‘Replay Name’
SQL> col startt format a20 head ‘Start Time’
SQL> col et format a20 head ‘End Time’
SQL> col num_clients format 99999 head ‘Clients’
SQL> col think_time_scale format 999 head ‘Think Time’
SQL> col elapsed_time_diff format 99999999999999 head ‘Elps|Diff’
SQL> set lines 120
SQL> set echo on
SQL> select id, name,
  2   to_char(start_time,’mm/dd/yyyy hh24:mi:ss’) startt,
  3   to_char(end_time,’mm/dd/yyyy hh24:mi:ss’) et,
  4   num_clients,think_time_scale, ELAPSED_TIME_DIFF
  5  from dba_workload_replays
  6  /
 Elps
  ID Replay Name  Start Time       End Time    Clients Think Time  Diff
—- ——————– ——————– ——————– ——- ———- —————
   1 ebsinv_replay  03/02/2012 02:43:39  1     0   36328924720
SQL>
Generate Capture and Replay Reports
You can use dbms_workload_capture.report and dbms_worklaod_replay.report to provide workload reports – I happen to like the one at http://oramrmessin.blogspot.com/2010/02/real-application-testing-databas… for Workload Replay.  Here’s what it looks like:
Lessons Learned
All in all, Workload Replay was pretty easy to do and worked as advertised.  Some things to think about are:
– Understand what think_time_scale and connect_time_scale are all about and how to apply them to generate, amplify, or otherwise change your replay experience
– After a negative, self-inflicted experience with connect_time_scale=0 and a subsequent dbms_workload_replay.cancel_replay (followed by a shutdown abort), I had some data integrity issues on my EBS environment that I needed to deal with.  To prevent this sort of thing, it’s probably a good idea to clone the source to the target prior to capturing the workload and have a good restore, flashback, or “undo” plan in place
– Think about what else, if anything, is running in your replay database that may interfere with the workload.  For example, since I was replaying an Oracle EBS environment, I left the concurrent managers down on the replay database.  I figured it’d probably make things go haywire if we had active inventory managers transacting data via the normal means at the same time replay clients were doing so
– There are a number of other cool things you can do with Workload Replay that impact replay processing – I plan on tinkering with some of these when I get a chance.