11g Preparing the Testing System for Workload Replay Oracle 11g New Features Tips by Donald BurlesonJune 28, 2015

Oracle 11g New Features Tips

After the workload has been recorded in external capture files, the testing system needs to be prepared. As has already been mentioned, the testing system should be a logical copy of the original system. If the captured workload only contains SELECT statements, then the structure of the testing database must be the same as the structure of the original database.

If the captured workload contains DML, then the testing database must be in a logical state which allows running the DML statements for replay.

M Make sure that the testing system is totally isolated from any production              system so that the workload replay cannot cause any damages!

Now ship all capture files from the capture directory of the original system to the testing server. Choose the host rhas4test as test server where  the directory  /home/oracle/replay_dir  is used for the capture files. For the replay of the workload, start external replay clients on the testing system. First of all, find out how many of these clients would suffice for the replay. This depends on the number of concurrent sessions which were captured into the capture files.  For this, run the wrc executable in calibration mode:

oracle@rhas4test ~]$ wrc replaydir=/home/oracle/replay_dir mode=calibrate

A calibration report for the workload and the replay system is received:

Workload Replay Client: Release 11.1.0.6.0 Production on Mon Feb 11 12:35:35

Copyright (c) 1982, 2015, Oracle.  All rights reserved.

Report for Workload in: /home/oracle/replay_dir
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 2

Assumptions:

- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

One replay client could handle up to 50 threats, one for each session in the workload. Since only two sessions have been captured all together, start one replay client for the replay. Next, look at which arguments the wrc executable accepts:

[oracle@rhas4test ~]$ wrc -help

Workload Replay Client: Release 11.1.0.6.0 - Production on Mon Feb 11 12:33:35 2015 

Copyright (c) 1982, 2015, Oracle.  All rights reserved. 

FORMAT:
=======
 wrc [user/password[@server]] [MODE=mode-value] KEYWORD=value 

Example:
========
   wrc  REPLAYDIR=.
   wrc  scott/tiger@myserver REPLAYDIR=.
   wrc  MODE=calibrate REPLAYDIR=./capture

The default privileged user is: SYSTEM

Mode:
=====
wrc can work in different modes to provide additional functionalities.
The default MODE is REPLAY. 

Mode        Description
----------------------------------------------------------------
REPLAY      Default mode that replays the workload in REPLAYDIR
CALIBRATE   Estimate the number of replay clients and CPUs
            needed to replay the workload in REPLAYDIR.
LIST_HOSTS  List all the hosts that participated in the capture
            or replay.
 

Options (listed by mode):
========================= 

MODE=REPLAY (default)
--------------------- 

Keyword     Description
----------------------------------------------------------------
USERID      username (Default: SYSTEM)
PASSWORD    password (Default: default password of SYSTEM)
SERVER      server connection identifier (Default: empty string)
REPLAYDIR   replay directory (Default:.)
WORKDIR     work directory (Default:.)
DEBUG       FILES, STDOUT, NONE  (Default: NONE)
            FILES  (write debug data to files at WORKDIR)
            STDOUT (print debug data to stdout)
            BOTH   (print to both files and stdout)
            NONE   (no debug data)
CONNECTION_OVERRIDE  TRUE, FALSE (Default: FALSE)
            TRUE   All replay threads connect using SERVER,
                   settings in DBA_WORKLOAD_CONNECTION_MAP will be ignored!
            FALSE  Use settings from DBA_WORKLOAD_CONNECTION_MAP
SERIALIZE_CONNECTS  TRUE, FALSE (Default: FALSE)
            TRUE   All the replay threads will connect to
                   the database in a serial fashion one after
                   another. This setting is recommended when
                   the replay clients use the bequeath protocol
                   to communicate to the database server.
            FALSE  Replay threads will connect to the database
                   in a concurrent fashion mimicking the original
                   capture behavior. 

MODE=CALIBRATE
-------------- 

Keyword     Description
----------------------------------------------------------------
REPLAYDIR   replay directory (Default:.)
 
Advanced parameters:

PROCESS_PER_CPU       Maximum number of client process than can be run
                       per CPU (Default: 4)
THREADS_PER_PROCESS   Maximum number of threads than can be run within
                       a client process (Default: 50)
 

MODE=LIST_HOSTS
-------------- 

Keyword     Description
----------------------------------------------------------------
REPLAYDIR   replay directory (Default:.)

In order to run the captured workload on the testing system, start the appropriate number of replay clients (in this case, one). This will be done in a later step after everything has been prepared for replay.

Before the replay can begin, the capture files must be preprocessed. This processing can happen on any server. It does not necessarily need to be the testing server. The only constraint is the version of the replay clients used to pre-process the capture files. It must be the same version as the replay clients used for the workloads replay. 

Lutz @ orcl11gtest as sysdba SQL exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE - ('REPLAY_DIR'); 

M The preprocessing significantly consumes system resources and should not be              performed on the production system!

The preprocessing is a one time action. It creates additional files in the replay directory to where the capture files have been shipped:

oracle@rhas4test ~]$ ls -la /home/oracle/replay_dir

-rw-r--r--  1 oracle oinstall     1125 Feb 11 13:17 wcr_4gc1wu40025vf.rec
-rw-r-----  1 oracle oinstall     1182 Feb 11 13:17 wcr_4gc1wu40026k6.rec
-rw-r--r--  1 oracle oinstall     1181 Feb 11 13:17 wcr_4gc1wu80026k9.rec
-rw-r--r--  1 oracle oinstall     1111 Feb 11 13:17 wcr_4gc1wuc00268n.rec
-rw-r--r--  1 oracle oinstall      904 Feb 11 13:17 wcr_4gc1wuh0025vj.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:17 wcr_4gc1wun0025vu.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:17 wcr_4gc1wv0002688.rec
-rw-r--r--  1 oracle oinstall     4505 Feb 11 13:19 wcr_4gc1wv00026kj.rec
-rw-r--r--  1 oracle oinstall     1115 Feb 11 13:17 wcr_4gc1wvc0025vy.rec
-rw-r--r--  1 oracle oinstall     1111 Feb 11 13:17 wcr_4gc1wvn0025uy.rec
-rw-r--r--  1 oracle oinstall      899 Feb 11 13:17 wcr_4gc1wvn00269v.rec
-rw-r--r--  1 oracle oinstall      918 Feb 11 13:18 wcr_4gc1wzs00268x.rec
-rw-r--r--  1 oracle oinstall     1115 Feb 11 13:18 wcr_4gc1x3s0025wf.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:18 wcr_4gc1x3s0025wj.rec
-rw-r--r--  1 oracle oinstall     1113 Feb 11 13:18 wcr_4gc1x3s0025x6.rec
-rw-r--r--  1 oracle oinstall     4034 Feb 11 13:20 wcr_4gc1xan0026mz.rec
-rw-r--r--  1 oracle oinstall     1115 Feb 11 13:19 wcr_4gc1xbh0025wm.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:19 wcr_4gc1xbh0025yf.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:19 wcr_4gc1xbh0025yh.rec
-rw-r--r--  1 oracle oinstall     1127 Feb 11 13:20 wcr_4gc1xp40025vw.rec
-rw-r--r--  1 oracle oinstall     1020 Feb 11 13:22 wcr_4gc1xxs0026q6.rec
-rw-r-----  1 oracle oinstall 11673600 Feb 11 13:25 wcr_ca.dmp
-rw-r--r--  1 oracle oinstall    16135 Feb 11 13:25 wcr_ca.log
-rw-r-----  1 oracle oinstall    12288 Feb 11 13:26 wcr_conn_data.extb
-rw-r--r--  1 oracle oinstall    52386 Feb 11 13:22 wcr_cr.html
-rw-r--r--  1 oracle oinstall    25536 Feb 11 13:22 wcr_cr.text
-rw-r--r--  1 oracle oinstall      272 Feb 11 13:22 wcr_fcapture.wmd
-rw-r-----  1 oracle oinstall      156 Feb 11 13:26 wcr_login.pp
-rw-r-----  1 oracle oinstall       35 Feb 11 13:26 wcr_process.wmd
-rw-r-----  1 oracle oinstall 12394496 Feb 11 13:44 wcr_ra_1399125366.dmp
-rw-r--r--  1 oracle oinstall        0 Feb 11 13:44 wcr_ra_1399125366.log
-rw-r--r--  1 oracle oinstall      998 Feb 11 13:43 wcr_replay.wmd
-rw-r--r--  1 oracle oinstall    10124 Feb 11 13:43 wcr_rr_1399125366.xml
-rw-r-----  1 oracle oinstall      205 Feb 11 13:17 wcr_scapture.wmd
-rw-r-----  1 oracle oinstall    12288 Feb 11 13:26 wcr_scn_order.extb
-rw-r-----  1 oracle oinstall    12288 Feb 11 13:26 wcr_seq_data.extb

After calibration and preprocessing, perform a last preparatory step before  replaying the workload. The database must be initialized for workload replay.

Lutz @ orcl11gtest as sysdba SQL exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY - (?my_test_replay_1?,'REPLAY_DIR'); 

Now the database can accept connections from the replay clients. This reads the connection maps. It is possible to remap connections. This is necessary because the network strings are different on the testing system. The mappings can be one-to-one mappings or many-to-one mappings.

M Connection remapping comes in handy for load balancing in a RAC              environment if the workload has been captured on a single instance database              and needs to be replayed in a cluster database.

exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id, replay_connection);

The re-mappings for connections are visible in the data dictionary:

Lutz @ orcl11gtest as sysdba SQL> desc dba_workload_connection_map

Name               Null?    Type
 ----------------- -------- ---------------
REPLAY_ID          NOT NULL NUMBER
CONN_ID            NOT NULL NUMBER
CAPTURE_CONN       NOT NULL VARCHAR2(4000)
REPLAY_CONN        VARCHAR2(4000)

The replay of the workload is completely managed from outside the database through the replay clients.