Businesses need to adapt to changes to stay competitive, compliant, and evolve AND save time and money. How can Oracle Database technology help mastering significant changes in Oracle Database environments? The answer is Oracle Database Replay, a component of Real Application Testing. You can use Database Replay to test any significant system changes, including:

  • Database and operating system upgrades

  • Configuration changes, such as conversion of a database from a single instance to an Oracle Real Application Clusters (Oracle RAC) environment, Multitenant etc.

  • Storage, network, and interconnect changes

  • Operating system and hardware migrations

  • Infrastructure shift to cloud environments

  • Workload and Capacity changes

Real Application Testing is – to put it very simply – a tool for the Oracle Database that can record a workload and replay it in a test environment. The term “tool” is somewhat misleading since no additional installation of a separate tool software is necessary. As with Partitioning, Compression, Edition Based Redefinition and other techniques in Oracle Database, Oracle Real Application Testing is available out-of-the-box in any Oracle Database installation and can even be used in Oracle Database Cloud EE environments for free. 
Basically, Real Application Testing comes in two flavors – Database Replay (DB Replay) and SQL Performance Analyzer (SPA). Both can be used independently of each other but are also a good complement. With SQL Performance Analyzer you can check the SQL performance. The posting “Smooth transition to Autonomous Database using SPA” explains the usage of SQL Performance Analyzer in the context of Autonomous Database. However it can also be used in other scenarios.  

Database Replay can be used to capture a real workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to test the effects of a system change without affecting the production system. Database Replay supports workload capture on a system running Oracle Database 10g Release 2 and newer releases. Because the database workload capture is stored in a platform-independent format, you can capture workload on one OS platform, e.g. Windows NT, and replay on a different one, e.g. Linux. Keep in mind it should be used only within the Oracle Database. Other external components like application server, middleware or client software cannot be considered when testing with Real Application Testing. 

My colleagues and I used Database Replay in different customer projects independent of industry and branch affiliations such as automotive company, telecommunication, trading, entertainment or banking industry.

Here is an example of one of our recent projects:
“A bank was utilizing a platform to host its mission critical Card Management System (CMS) for almost 6 years. The specific CMS is handling millions of transactions per day and the size of the underlying database is more than 60 TBs. The bank had to refresh this hardware because it seemed to be undersized and decided to move the database off its CMS to a new platform. Before they go into production, they wanted to be certain that the performance of the new system would be equivalent or better than the old one. We recommended the use of Real Application Testing to capture the workload for some hours and then replay it on the new system.

The process included the creation of a data guard configuration between the old production system and the test system. Before initializing capture data guard redo apply was stopped. The capture process ran for 1 hour. The replay of capture file was executed on the test system with the use of 4 WRC clients. All actions were performed from command line executing the corresponding DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY packages.

The result of the replay was that the database at the target new system was heavily underperforming which was a result of a wrong CPU sizing. The top wait time event was CPU. SQL Performance Analyzer (SPA) was also used which showed again deterioration of performance on the new target system.  8 CPU cores were initially assigned and after the Real Application Testing tests the number of CPU cores was increased to 32.

Real Application Testing saved this bank from a very bad situation where the customers using their cards would face severe delays with their card transactions and this could have meant huge negative publicity!”

Note (Autonomous Database): Real Application Testing is also available for Autonomous Database. It can be used to replay a recorded (captured) workload from On-Premises or cloud installations in an Autonomous Database. Only one(!) statement is required to replay it. If you are interested in this scenario, you may find more information in the posting Autonomous Database. Replay I outlined all the steps that are necessary to replay a workload – starting from a capture and the copy of the captured files to the replay in an Autonomous Database.

In the last months I received more and more questions concerning database replay, so I decided to put together the required steps also in English.
If you do not want to read this posting and want to see database replay “in action” first, check out the recent posting Real Application Testing Database Replay Demo from my colleague Rob Watson. He demonstrates in 2 videos – the capture and the replay phase running a Swingbench workload in a PDB.

The overall workflow

Database Replay consists mainly of the two PL/SQL packages DBMS_WORKLKOAD_CAPTURE and  DBMS_WORKLOAD_REPLAY as well as the associated data dictionary views and client WRC (Workload Replay Client) software. Either the graphical interface via Enterprise Manager Cloud Control or the command-line API can be used. In this posting I’d like to demonstrate the whole workflow using the API. Database Replay in Cloud Control uses the same steps in the background, so that it should be easy to adopt and use it. 

The following steps are required: 

If you want to try it yourself, you can download the scripts here.

Capture a workload

The Capture feature is implemented as a lightweight trace on the user process which writes session specific information to a single .rec file per session using buffered writes. The capture files are binary and independent of the operating system, so that the replay is possible on different platforms. During a workload capture, a variety of information such as connection strings, SQL text and bind values are stored. The overhead of capture is workload dependent and is proportional to the amount of data sent from the client; the more data sent by the client to the server, the more overhead there will be. 

Note: Space required for capture can be approximated using: 2 * Bytes received via SQL*Net from client (from AWR).

It is possible to setup optional filters, for example, by user, instance ID or services, so that not all operations are recorded and fewer capture files are created. Typical filters exclude for example Enterprise Manager Cloud Control and RMAN activities. Capture filters can be included or excluded. Whether a filter is used as an INCLUSION or EXCLUSION filter is determined at capture startup. Filtering is optional and applies only to the current recording. The check can be done via the view DBA_WORKLOAD_FILTERS. By the way, you can also use special filters afterwards – during the replay – to play only a part of the recording.
Typical examples for filters are:

execute DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname=>'ORACLE MANAGEMENT SERVICE (DEFAULT)', fattribute=>'Program', fvalue=>'OMS');
execute DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname=>'ORACLE MANAGEMENT AGENT (DEFAULT)', fattribute=>'Program', fvalue=>'emagent%');
execute DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname=>'M_RMAN', fattribute=>'Module', fvalue=>'rman%');

Now the recording (captureing) can be initiated with a single call. There are certain restrictions concerning the commands that are recorded. These should be checked in advance in the manual. The dependency on external services like database links, web services etc. must also be considered and clarified. Start the capture at low load to get as few in-flight transactions as possible. Of course, the best option would be to shut down the database, but this is not possible in most environments.

To start with the capture you only need an empty logical database directory (here CAPDIR) and you are ready to go. 

execute DBMS_WORKLOAD_CAPTURE.START_CAPTURE(name=>'&capturename', dir=>'CAPDIR', plsql_mode=>'EXTENDED');

The argument DEFAULT_ACTION can be either INCLUDE or EXCLUDE. It determines whether, by default, every user request should be captured or not. Also determines whether the workload filters specified should be considered as INCLUSION filters or EXCLUSION filters.

  • If INCLUDE (this is the standard default setting), by default all user requests to the database are captured, except for the part of the workload defined by the filters. In this case, all the filters specified using the ADD_FILTER procedure are treated as EXCLUSION filters, determining the workload that is not captured. (DEFAULT, and so all the filters specified are assumed to be EXCLUSION filters.)

  • If EXCLUDE, by default no user request to the database is captured, except for the part of the workload defined by the filters. In this case, all the filters specified using the ADD_FILTER Procedures are treated as INCLUSION filters, determining the workload that is captured. For example, if you only want to capture the user SCOTT, specify EXCLUDE in the DEFAULT_ACTION and the value SCOTT in the ADD_FILTER procedure. 

The CAPDIR logical directory must be empty and have enough space to store the captured files.

Note (PL/SQL): Database Replay has always had the ability to capture and replay PL/SQL. It has done so through capturing and replaying the top level PL/SQL calls. As of 12.2, Database Replay gives you the choice of replaying the top level PL/SQL calls or the recursive SQL called within the PL/SQL procedures. Depending on the workload, the new replay mode can perform replays with more accuracy and less divergence. Enhanced PL/SQL support gives DBAs the ability to perform database replays with less divergence, allowing faster, easier, and more complete testing of workloads having a lot of PL/SQL. For more information see also “Database Replay Enhanced PL/SQL Support” (Doc ID 2166850.1)

If no time period is specified as in our case, the capture is terminated as follows:

execute DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();

The last action on the capture system is the export of the corresponding AWR snapshot. Export the AWR data using the directory that was used before. The files in this directoy are needed for the replay afterwards. 

/*  find the capture id
select id, name 
from dba_workload_captures where name like '%&capturename%';
*/

-- export AWR
execute dbms_workload_capture.export_awr(capture_id =>&captureid);

To evaluate and monitor the capture run, you can either query the DBA_WORKLOAD_CAPTURES view or generate a report with the DBMS_WORKLOAD_CAPTURE.REPORT function. This will give you interesting information about the characteristics of the capture, such as duration and size, number of user calls and transactions, etc.

SQL> set lines 132  
SQL> SELECT id, start_scn, status, errors, awr_exported, dbtime_total,
            transactions_total, capture_size/1024/1024 CAPSIZE_MB,           
            user_calls_total, user_calls_unreplayable 
     FROM dba_workload_captures; 

  ID START_SCN      STATUS      ERRORS AWR_EXPORTED DBTIME_TOTAL TRANSACTIONS_TOTAL 
---- -------------- --------- -------- ------------ ------------ ------------------ 
CAPSIZE_MB USER_CALLS_TOTAL USER_CALLS_UNREPLAYABLE 
---------- ---------------- -----------------------
  23 13821838964905 COMPLETED    28833 YES           53929939800             386655 
1275.49682 8563239          432574

A capture report via the REPORT function is available in TEXT or HTML format.

set pagesize 0 long 30000000 longchunksize 1000
select DBMS_WORKLOAD_CAPTURE.REPORT(capture_id=>&id, format=>'TEXT') from dual;

The resulting report looks like …

Database Capture Report For ORCL

DB Name         DB Id    Release     RAC Capture Name               Status
------------ ----------- ----------- --- -------------------------- ----------
ORCL          1258625022 11.2.0.3.0  NO  T_TEST                   COMPLETED

                   Start time: 15-Jan-19 12:39:26 (SCN = 310491322)
                     End time: 15-Jan-19 13:10:56 (SCN = 310494735)
                     Duration: 31 minutes 30 seconds
                 Capture size: 82.82 KB
             Directory object: RAT
               Directory path: /home/oracle/rat_test1
      Directory shared in RAC: TRUE
                 Filters used: 4 EXCLUSION filters

Captured Workload Statistics                      DB: ORCL  Snaps: 45621-45622
-> 'Value' represents the corresponding statistic aggregated
      across the entire captured database workload.
...

In addition you may review the My Oracle Support FAQ (Doc ID 1920275.1) to get answers on common questions about the capture process.
Note (documentation): You will find more information on the usage of the PL/SQL package in the documentation of Package DBMS_WORKLOAD_CAPTURE.

Process the captured workload

The second step is to perform processing of the capture files in preparation for the replay(s). The corresponding program analyzes the workload capture found in the capture file directory and creates new workload replay specific metadata files that are required to replay the given workload capture. The process only creates new files and does not modify any files that were originally created during the workload capture. Therefore, this procedure can be run multiple times on the same capture directory, such as when the procedure encounters unexpected errors or is cancelled by the user.

It is a one-time operation and must be performed on the system where the replay will be executed.

execute DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir=>'&dir', parallel_level=>&parallelism, plsql_mode=>'EXTENDED');

The time taken to preprocess a workload will vary from system to system and from workload to workload. You can get an idea/estimate of how much preprocessing has been done and how much time is left by running the following:

set serveroutput on
declare
 p number;
 z number;
begin
 p:=dbms_workload_replay.process_capture_completion;
 z:=dbms_workload_replay.process_capture_remaining_time;
 dbms_output.put_line('percentage' ||p);
 dbms_output.put_line('remaining time:'||z);
end;
/

Replay the workload

To perform a replay, it is necessary to create a test system beforehand. It is important that the database on the capture and replay system have the same consistency state. RMAN, Data Guard, Flashback Database, Snapshot Standby, Data Pump, etc. can be used for this purpose. Flashback Database in combination with a so called “Guaranteed Restore Point” is for example an easy way to reset the database quickly. 

In addition the captured files must be made available or moved to the target system.

Now the replay can be initialized. For this purpose, a replay name is assigned and the directory in which the copied data from the recording is located.

execute DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name=>'&Replay_name', replay_dir=>'&DIR', plsql_mode=>'EXTENDED');

Note (Multitenant): For releases 12.1 to 18c your replay needs to be against the container. For this you need to be connected to a common user with sufficent privileges. If you are using user SYSTEM then only  “Grant become user to C##ruser” needs to have “container=all” as well. Before you prepare you need to remap the connections to the PDB (see explanation later).
For 19c and higher you can either replay against the CDB (it is same as for 12.1 to 18c) or the PDB. For PDB replay you just need a local user and you can connect to the PDB instead of the CDB.

After the replay data is initialized, connection strings used in the workload capture need to be remapped so that user sessions can connect to the appropriate databases and perform external interactions as captured during replay. In the case if the replay is performed at the container level (CDB) the connection strings for each workload must be remapped to its respective PDB. For Oracle Real Application Clusters (Oracle RAC) databases, you can map all connection strings to a load balancing connection string. This is especially useful if the number of nodes on the replay system is different from the capture system. Alternatively, if you want to direct workload to specific instances, you can use services or explicitly specify the instance identifier in the remapped connection strings.

To change a connection you can use DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id,replay_connection) where connection_id is the connection_id of the connection you wish to change and replay_connection is the new connection string.

Here is an example. First check the DBA_WORKLOAD_CONNECTION_MAP view to get the connection_ids then execute the required DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION.

SQL> set lines 132 
SQl> SELECT conn_id,replay_id, replay_conn 
     FROM dba_workload_connection_map;

CONN_ID    REPLAY_ID
---------- ----------
REPLAY_CONN
---------------------------------------------
1          1

Then execute the required mapping command:

execute DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id=>1, replay_connection=>'PDB1');

In the next step some replay characteristics are defined with the PREPARE command. The replay can run in the different synchronization variants such as TIME or SCN. The synchronization TIME (also clock based) is based on the time in which the action took place during the capture. The synchronization SCN (this is the default setting in 19c) on the other hand is based on the commit time during the capture; the commit order remains here.Using all the above defaults, typically we would just run:

execute DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();

For different purposes there are some additional parameters available. Here are some examples: If you want to record a SQL tuning set at the same time, use the parameter CAPTURE_STS. Parameters like CONNECT_TIME_SCALE (default 100) and THINK_TIME_SCALE (default 100) are responsible for the scaling of connection and think time respectively. By reducing these parameters, the recorded workload can run at different “speeds”. Use the parameter SCALE_UP_MULTIPLIER to increase the statement execution (only SELECT commands).

Before we can start the replay, workload replay clients (WRC) need to be started – either on the server itself or on an additional client machine. The replay client is a separate program (an executable named wrc located in the $ORACLE_HOME/bin directory) where each thread submits a workload from a captured session. Before replay begins, the database will wait for replay clients to connect. At this point, you need to set up and start the replay clients, which will connect to the replay system and send requests based on what has been captured in the workload.

Note: The WRC software can either be found in $ORACLE_HOME/bin or can be loaded as instant client software from OTN.

Try the following command to get a description of the WRC program.

[oracle@by19c ~]$ wrc help=yes
Workload Replay Client: Release 19.3.0.0.0 - Production on Tue Apr 21 08:31:52 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

FORMAT:
=======
  wrc [user/password[@server]] [MODE=mode-value] KEYWORD=value
Examples:
=========
  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
VERSION         Print the wrc version
CALIBRATE       Estimate the number of replay clients and CPUs
                needed to replay the workload in REPLAYDIR
...

To get information about the sufficient number of clients, it is convenient to use the WRC program itself or to execute the DBMS_WORKLOAD_REPLAY.CALIBRATE function.

wrc user/password@pdb1 replaydir= mode=calibrate

Then start the required number of Workload Replay Clients in a separate script. 

wrc user/password@pdb1 replaydir= mode=REPLAY
wrc ...
wrc ...

Now the replay can be executed.

execute DBMS_WORKLOAD_REPLAY.START_REPLAY();

The view DBA_WORKLOAD_REPLAYS provides information about the status of the replay at any time. After the replay is finished, you can start to evaluate and compare the results. 
In any case you can even pause and resume the replay with DBMS_WORKLOAD_REPLAY.PAUSE and DBMS_WORKLOAD_REPLAY.RESUME.

Note: You will find more information on the usage of the PL/SQL package in the documentation of Package DBMS_WORKLOAD_REPLAY.

Evaluations and Reports

Similar to the capture report you may use the view DBA_WORKLOAD_REPLAYS, to get information about the replay. There are also special reports and of course the possibility to get further information via an associated AWR report. All reports can be generated in linemode as TEXT or HTML format. It is recommended to generate the so-called Replay and also the Compare Period Report.

The Replay Report gives information about the settings of the replay run (like synchronization, scaling etc.), replay statistics (like number of user calls and DB time),duration, top events, workload profiles and divergences. For example a large number of divergences can indicate gross violations and replay issues and should be investigated further and resolved. A replay report can be generated as follows.

set pagesize 0 long 30000000 longchunksize 1000 
col tt format a120 
select DBMS_WORKLOAD_REPLAY.REPORT(replay_id=>&replayid, format=>'HTML') tt 
from dual;

Database Replay Report
 

The Compare Period Report is often even sufficient to draw conclusions about feasibility and overall performance. A downstream analysis with an AWR Report or an AWR Difference Report usually confirms the results from the Compare Period Report and can give even more details about the performance. The prerequisite for this is the import of the capture AWR with the IMPORT_AWR function, which requires the capture Id and a staging scheme.
 

DECLARE 
 dbid NUMBER; 
 BEGIN 
  dbid := DBMS_WORKLOAD_REPLAY.IMPORT_AWR(CAPTURE_ID=>&captureid, STAGING_SCHEMA =>'&schema');
END;
/

Compare Period Report

The Compare Period Report now provides a good overview of the overall performance in comparison e.g. Capture with Replay1, Replay1 with Replay2 etc. and gives decisive information about the performance differences. Optimizer and memory settings, important initialization parameters, performance statistics and top statements are compared and additionally some hardware statistics like I/O or CPU usage are given. It also gives an assessment of the divergences – LOW means negligible divergence, for example.

variable comp_report clob
begin
 DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT(
 replay_id1 => &replayid,
 replay_id2 => null,
 format     => 'HTML',
 result     => :comp_report);
end;
/
set heading off set long 100000 set pagesize 0
spool comparereport.html rep
print comp_report
spool off

The following example shows a small section of a Compare Period Report.

If you want to get more detailed information about the individual performance metrics, you can add an AWR Difference Report at the end or generate individual AWR reports.

Note: You will find more information on the usage of the PL/SQL package in the documentation of Package DBMS_WORKLOAD_REPLAY.

Conclusion and Use Case Scenarios

The technology was used for various purposes such as platform change, migration, upgrade, architecture change or patch testing. The time to create a test system with backup, upgrade, migration and the choice of the method to reset must of course always be taken into account when planning the tests. After an initial learning curve to become familiar with the tool, it quickly became clear in each project how little effort is required with Real Application Testing and what the benefits are. Ususally the results were evaluated by database performance experts. Replay Report and Compare Period Report were often sufficient for the analysis. The Replay Report helps to evaluate the quality of the run first. If run times, divergences and number of calls are comparable and realistic, the overall performance can already be evaluated very well with a Compare Period Report, since the main performance characteristics are already recorded there. If necessary, an AWR Difference Report can then be used as a final confirmation.  
Summarized Database Replay can bring security and confidence for migrations/upgrades, provide a better understanding of one’s own applications or even deliver the decisive arguments for a platform or architecture change. And not to forget: Real Application Testing can also be an extremely valuable tool when a change to the cloud is imminent. The flow here in the blog shows an example of what the common thread of a DB replay procedure can look like. Further variants of the scripts can be derived from the manual.

Further Reading