Oracle Database Replay: Full-Scale Workload Testing for Changes and Migrations :

Introduction :

Every DBA eventually faces a change that is too risky to test with a handful of SQL statements. A database upgrade, a platform migration, an operating system change, a storage replacement, a parameter change at the system level – these changes affect the entire workload, not just one query. SQL Performance Analyzer (SPA) can test a few hundred SQL statements, but it cannot reproduce concurrency, transaction ordering, or the interaction between sessions. Database Replay solves this problem.

Database Replay captures the complete workload of a production system every SQL statement, every PL/SQL call, every transaction commit, every connection, and the timing between them, and replays it on a test system with the same concurrency and sequencing. The result is a workload that behaves as close to production as a test environment can achieve.

This is the tool Oracle uses internally to validate every database release. It is part of Real Application Testing (RAT), and it is the only way to answer the question “will this change break anything?” with statistical confidence rather than hope.

Theoretical Foundation :

How Database Replay Works :

Database Replay operates in four distinct phases :

The critical advantage over SPA is concurrency. SPA runs SQL statements one after another. Database Replay runs them exactly as they arrived 500 sessions connecting at once, 200 transactions committing in parallel, locks being taken and released in the same order. This is what makes it capable of reproducing blocking, deadlocks, and resource contention that SPA cannot test .

What is Captured :

Database Replay captures at the network layer (the client-server protocol), not at the SQL layer. This means it captures:

• SQL and PL/SQL text

• Bind variable values

• Result sets returned to clients

• Session connect/disconnect events

• Transaction boundaries (COMMIT, ROLLBACK)

• Timing of each request relative to others

• User names, service names, module/action names

This level of capture is what allows replay to reproduce the exact workload behavior, not an approximation .

What is NOT Captured :

• Data changes made by external processes (e.g., direct path loads, Data Pump)

• Changes made by database links to remote systems

• Changes made by advanced replication

• Scheduler jobs (unless they are triggered by a client)

• Any operation that bypasses the client-server protocol

For a complete test, these must be handled separately .

Licensing :

Database Replay is part of the Real Application Testing option. It is a separately licensed feature on on-premises databases. On Oracle Autonomous Database (ADB), RAT is included at no extra cost. On OCI Database Cloud Service, it may be available depending on the edition .

Step 1 – Prepare the Capture Environment :

Prerequisites :

The capture system must satisfy these conditions :

Verify Licensing :

SELECT value FROM v$option WHERE parameter = 'Real Application Testing';

Expected result: TRUE.

Create a Directory for Capture Files :

CREATE OR REPLACE DIRECTORY replay_dir AS '/u01/replay';
GRANT READ, WRITE ON DIRECTORY replay_dir TO system;

Check Disk Space :

Capture files are stored in the directory above. For a moderately busy OLTP system, expect 2–5 GB per hour of capture. Ensure the filesystem has at least 3× the expected size to account for processing overhead.

Step 2 – Start the Workload Capture :

Create the Capture :

BEGIN
    DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
        name           => 'CAPTURE_2024_10',
        dir            => 'REPLAY_DIR',
        duration       => 3600,   -- Capture for 1 hour (3600 seconds)
        default_action => 'INCLUDE'
    );
END;
/

Parameters Explained :

Monitor the Capture :

SELECT name, status, start_time, duration_secs, dir_path FROM dba_workload_captures WHERE name = 'CAPTURE_2024_10';

Theoretical Note :

During capture, the database writes a small amount of metadata to the capture files for each client request. The overhead is typically 3-5% CPU. This is acceptable in most production environments, but should be tested during a low-traffic window first if the system is already at capacity.

Step 3 – Stop the Capture and Preprocess :

Stop the Capture :

If you set a duration, the capture stops automatically. If you did not set a duration, stop it manually :

BEGIN
    DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(
        name => 'CAPTURE_2024_10'
    );
END;
/

Verify the Capture is Complete :

SELECT name, status, duration_secs FROM dba_workload_captures WHERE name = 'CAPTURE_2024_10';

Status should be COMPLETED.

Preprocess the Capture :

Preprocessing performs two critical tasks: it converts the raw capture data into a re-playable format, and it remaps external references, for example, database links, directory objects, and external tables that reference the source system.

BEGIN
    DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
        capture_dir   => 'REPLAY_DIR',
        capture_name  => 'CAPTURE_2024_10'
    );
END;
/

Theoretical Note :

Preprocessing is where you can change things. If the source system uses a database link called PROD_LINK and the test system uses TEST_LINK, preprocessing can remap them. If a directory object points to a production path, preprocessing can redirect it. This is important for test integrity, you do not want the replay writing to production locations .

Step 4 – Prepare the Replay System :

Restore the Test Database to the Same Starting Point :

This is the most important step for validity. The test database must be at the same state as the source database at the moment the capture started. Use RMAN to restore the test database to the same SCN or time, or use a snapshot of the source system.

If the test database state is different, the replay will produce different results, and you will not be able to tell whether a divergence is due to the change you are testing or due to the different starting state.

Verify the Replay Directory :

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'REPLAY_DIR';

Copy the Replay Files to the Test System :

The preprocessing step creates a set of files in the capture directory. Copy them to the test system’s replay directory, preserving the file names and structure.

Step 5 – Run the Replay :

Step 5a – Initialize the Replay :

BEGIN
    DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
        replay_name   => 'REPLAY_2024_10',
        replay_dir    => 'REPLAY_DIR'
    );
END;
/

Step 5b — Prepare the Replay :

BEGIN
    DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
        synchronization    => TRUE,
        connect_time_scale => 100,
        think_time_scale   => 100,
        think_time_auto_correct => TRUE
    );
END;
/

Parameters Explained :

Step 5c – Remap Connections (If Needed) :

If the test system uses different service names or connection strings, remap them :

SELECT DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO('REPLAY_2024_10') FROM dual;

Then remap each connection :

BEGIN
    DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
        connection_id   => 1,
        replay_connection => 'TEST_SERVICE'
    );
END;
/

Step 5d – Start the Replay :

BEGIN
    DBMS_WORKLOAD_REPLAY.START_REPLAY(
        replay_name => 'REPLAY_2024_10'
    );
END;
/

Monitor Replay Progress :

SELECT name, status, replay_start_time, replay_end_time FROM dba_workload_replays WHERE name = 'REPLAY_2024_10';

Status will be IN PROGRESS until complete.

Theoretical Note :

The replay clients can be either synchronized or un-synchronized. In synchronized mode (the default), the replay respects the original commit ordering, which is necessary for reproducing blocking and deadlocks. In un-synchronized mode, replay runs faster but may produce different results. For valid results, always use synchronized mode.

Step 6 – Generate the Replay Report :

Generate the Report :

SET LONG 1000000
SET PAGESIZE 50000
SET LINESIZE 200

SELECT DBMS_WORKLOAD_REPLAY.REPORT(replay_name => 'REPLAY_2024_10', format => 'HTML') FROM dual;

Or Generate a Text Report :

SELECT DBMS_WORKLOAD_REPLAY.REPORT(replay_name => 'REPLAY_2024_10', format => 'TEXT') FROM dual;

Key Sections of the Report :

1. Replay Summary :

Shows the overall replay statistics: total requests, completed requests, errors, and divergences.

2. Divergence Summary :

This is the most important section. It lists every point where the replay result differed from the capture. Divergences are classified as:

3. Top SQL by Divergence :

Which SQL statements produced the most divergences. These are the statements that the change affected most.

4. Per-Request Detail :

For each divergence, the report shows the capture result and the replay result, allowing you to see exactly what changed.

Step 7 – Analyze the Results :

What to Look For :

Performance Comparison :

The report also compares the replay’s performance against the capture. A simple comparison is elapsed time per request:

• If the replay’s elapsed time is lower, the change is an improvement.

• If the replay’s elapsed time is higher, the change is a regression.

However, this comparison is only valid if the test system’s hardware is equivalent to the production system’s hardware. If the test system is faster or slower, the raw timing comparison will be misleading. In that case, compare CPU time and buffer gets per request, which are more stable across hardware differences.

Root Cause Analysis :

For any divergence or regression, use the tools from previous topics:

• SQL Monitor (from the previous topic) to see the actual execution plan of the diverging SQL in the replay

• AWR Compare Periods to compare the replay system’s wait events and statistics before and after

• 10053 to see why the optimizer chose a different plan in the replay

The combination of Database Replay and these diagnostics gives you a complete picture of what the change did and why.

Practical Considerations :

Capture Duration :

A 1-hour capture is usually sufficient for a busy OLTP workload. For batch workloads, capture the entire batch cycle, even if it is longer. For reporting workloads, capture a full day to include all report execution.

Test System Sizing :

The test system should be at least as powerful as the production system for valid timing comparisons. If the test system is smaller, the replay will take longer, and some requests may time out. In this case, use think_time_scale to reduce the load, or scale the results proportionally.

Concurrency :

The number of replay clients should match the number of concurrent sessions in the capture. If the capture had 500 concurrent sessions, the replay should use at least 500 clients. Running fewer clients will produce a lower load and may miss concurrency-related issues.

Data Masking :

The capture files contain real data, including sensitive information. If you are testing in a non-secure environment, use Oracle Data Masking or restrict access to the replay directory.

Capture and Replay on Different Platforms :

Database Replay can capture on one platform and replay on another, but only if the database versions are compatible. This is useful for platform migrations (e.g., AIX to Linux, or on-premises to OCI).

Summary :

Database Replay is the definitive tool for validating changes at scale. It captures the real workload, replays it with real concurrency, and reports real divergences. It is not a substitute for SPA, SPA tests SQL changes, Database Replay tests system changes. Used together, they cover both the SQL-level and the workload-level validation that a responsible DBA needs before making a production change.