Dear Readers,

Trust you are doing well.

In my last post, I focused on RAT preprocessing. Now, let’s dive into the RAT replay process, which is executed on the pre-production or target system to simulate the captured workload.

Once a workload is captured and preprocessed, it can be replayed multiple times on a replay system running the same version of Oracle Database using guaranteed restore points.

Before replaying a workload, the application data state on the replay system must be logically equivalent to that of the capture system at the start of workload capture. This ensures minimal replay divergence. The method for restoring the database depends on the backup technique used before capturing the workload. Various methods such as RMAN, Data Guard, Flashback Database, Snapshot Standby, and Data Pump can be employed for this purpose. In addition the captured files must be made available or moved to the target system.

Setting Up the Replay Directory

The captured workload must be transferred to the replay system. Additionally, a directory object corresponding to the location where the pre-processed workload is stored must be present on the replay system.

SQL> CREATE directory rat_replay AS '/SHAREDFILE/RAT_REPLY';
SQL> GRANT read, write ON directory rat_replay TO PUBLIC;

After completing the preprocessing as explained in the previous post, references to external systems should be removed, rectified, or resolved. This includes reconfiguring or dropping any database links, external tables, and directory objects.

Initialize Replay

Once the workload capture is preprocessed and the test system is adequately prepared, the replay data can be initialized. This process involves loading the essential metadata into tables required for the workload replay.

SQL> exec dbms_workload_replay.Initialize_replay(replay_name => 'RAT_REPLAY', replay_dir => 'RAT_REPLAY');

Skipping SQL_ID during Replay (Optional)

You can specify SQL statements to be skipped or replaced during a database replay operation using below method:

SQL> exec dbms_workload_replay.Set_sql_mapping(sql_id => '', operation => 'SKIP', replacement_sql_text => NULL);
SQL> COMMIT;

You can verify skipped SQL using below query:

SQL> SELECT replay_id, sql_id, operation, replacement_sql_text FROM dba_workload_sql_map;

If you are unable to skip the required SQL_ID from the replay process, you can refer to the MOS note provided below.

Replay using DBMS_WORKLOAD_REPLAY.SET_SQL_MAPPING not skipping a SQL_ID In 19c (Doc ID 2694508.1)

Specifying Replay Options

Setting the Replay Timeout Action:

You can set a replay timeout action to abort user calls that are significantly slower during replay or cause a replay to hang. This can be controlled by parameter DBMS_WORKLOAD_REPLAY.SET_REPLAY_TIMEOUT

Specifying the Synchronization Method

The synchronization parameter determines the synchronization method used for database replay. It can be set to TIME, SCN, OBJECT_ID, or FALSE.

Controlling Request Rate Within a Session

User think time is the elapsed time while the replayed user waits between issuing calls within a single session. To control replay speed, use the THINK_TIME_AUTO_CORRECT parameter to scale user think time during replay. If user calls are being executed slower during replay than during capture, this should be set to TRUE.

Controlling wait time Within a Session

CONNECT_TIME_AUTO_CORRECT controls the waiting time for a new session to be connected is automatically reduced when the replay proceeds faster than its capture. There is no impact when the replay proceeds slower than the capture.

So normally I follow below command to prepare replay process:

SQL> exec dbms_workload_replay.set_advanced_parameter('DO_NO_WAIT_COMMITS', TRUE);
SQL> exec dbms_workload_replay.set_advanced_parameter('DISABLE_DB_LINKS', TRUE);
SQL> exec dbms_workload_replay.set_replay_timeout (min_delay=> 1, max_delay=> 3, delay_factor=> 3);
SQL> exec dbms_workload_replay.Prepare_replay (synchronization => FALSE, think_time_auto_correct => FALSE, connect_time_auto_correct => FALSE);

Setting up WRC client processes

The replay client is a multithreaded program (an executable named wrc located in the $ORACLE_HOME/bin directory), with each thread submitting a workload from a captured session. Before replay begins, the database will wait for replay clients to connect. At this stage, you need to set up and start the replay clients, which will connect to the replay system and send requests based on the captured workload.

To estimate the number of replay clients and hosts needed to replay a specific workload, run the wrc executable in calibrate mode.

wrc mode=calibrate replaydir=<Replay Directory>

Once the number of required client processes is determined, you can start the wrc clients using nohup. For a RAC system, start the wrc process on each RAC DB server.

wrc system/password@test mode=replay replaydir=<Replay Directory> CONNECTION_OVERRIDE=TRUE

Starting RAT replay

Once the WRC client processes are successfully started, we can start the replay using the following command:

SQL> execute DBMS_WORKLOAD_REPLAY.START_REPLAY();

The view DBA_WORKLOAD_REPLAYS provides real-time information about the status of the replay. Once the replay is finished, you can begin evaluating and comparing the results. Use the following query to check the progress:

SQL> SET lines 200 col status format a15 col NAME format a15 col dbversion forma t a12
SQL> SELECT id,
NAME,
dbid,
dbname,
dbversion,
parallel,
status,
To_char(start_time, 'dd/mm/yyyy hh24:mi'), To_char(end_time, 'dd/mm/yyyy hh24:mi'), duration_secs / 60,
awr_begin_snap,
awr_end_snap,
awr_exported
FROM dba_workload_replays;

Pausing/resuming/cancelling running replay process

Pausing a workload replay will halt all subsequent user calls issued by the replay clients until the workload replay is either resumed or cancelled.

SQL> exec DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY ();

To resume pasued replay we can run below command.

SQL> exec DBMS_WORKLOAD_REPLAY.RESUME_REPLAY ();

In case replay has be cancelled for any reason below command can be used.

SQL> exec DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();

Caputing replay reports for completed run

SQL> SET pagesize 0 long 30000000 longchunksize 2000 linesize 155
-- For HTML format
SQL> SELECT dbms_workload_replay.Report(1, 'HTML') FROM dual;
-- For Text format
SQL> SELECT dbms_workload_replay.Report(1, 'TEXT') FROM dual;

Setup environment for next replay run

This will require database to be flashed back to guranteed restore point taken after RAT preprocessing & then continue with steps mentioned in this post.

Important MOS references

DB 19c: RAT Replay with replace_literals=true Option Causesd High Memory Usage when Capture has LOBs (Doc ID 2788248.1)
How to Setup and Run a Database Testing Replay in an Oracle Multitenant Environment (Real Application Testing – RAT) (Doc ID 1937920.1)
How to Find the Offending Workload Capture File ( rec file ) when a Replay is Cancelled or Crashes (Doc ID 1536695.1)

Let me know for any questions and any further information in comments or LinkedIn.

Regards
Adityanath.