Oracle Real Application Testing (RAT) is the authoritative tool for validating changes to an Oracle database before production deployment. It enables you to replay a real production workload against a test system and measure the performance impact of changes such as database upgrades, parameter changes, hardware migrations, or schema modifications. This article walks through both RAT components — Database Replay and SQL Performance Analyzer (SPA) — with step-by-step procedures.

RAT Component Overview

Component What It Tests Best For
Database Replay Entire production workload (concurrent sessions, timing, interactions) Database upgrades, hardware changes, OS upgrades
SQL Performance Analyzer (SPA) Individual SQL statements from AWR SQL Tuning Set Optimizer changes, parameter tuning, index changes

Both require the Oracle Real Application Testing licence option in addition to Enterprise Edition.

Part 1: Database Replay — End-to-End Walkthrough

Database Replay captures all external database calls at the production source and replays them on a test database with accurate timing and concurrency.

Step 1: Prepare the Capture Environment

Choose a representative workload period — typically 30–60 minutes capturing a peak workload window. The capture runs on the production database and records everything in a capture directory.

-- 1.1 Create a capture directory on the production server (as SYSDBA)
CREATE OR REPLACE DIRECTORY db_replay_dir AS '/u01/db_replay/capture';

-- 1.2 Grant access to the directory
GRANT READ, WRITE ON DIRECTORY db_replay_dir TO system;

-- 1.3 Estimate the size of the capture before starting
-- (optional but recommended — captures can be large)
EXEC DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
  fname       => 'exclude_sys',
  fattribute  => 'USER',
  fvalue      => 'SYS'
);

EXEC DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
  fname       => 'exclude_sysman',
  fattribute  => 'USER',
  fvalue      => 'SYSMAN'
);

Filtering out SYS and SYSMAN is standard practice — their background workload is not relevant to your application testing.

Step 2: Start the Capture

-- 2.1 Start workload capture
BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
    name            => 'PROD_CAPTURE_2026_03_01',
    dir             => 'DB_REPLAY_DIR',
    duration        => 3600,          -- capture for 3600 seconds (1 hour)
    capture_sts     => TRUE,          -- also capture SQL Tuning Set (for SPA)
    sts_cap_interval => 300           -- flush STS every 5 minutes
  );
END;
/

-- 2.2 Monitor capture progress
SELECT id,
       name,
       status,
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
       dir_path,
       ROUND(user_calls / 1000000, 2) AS user_calls_M,
       ROUND(dbtime_total / 1000000 / 60, 2) AS db_time_mins
FROM dba_workload_captures
ORDER BY id DESC
FETCH FIRST 5 ROWS ONLY;

Step 3: Stop the Capture

The capture stops automatically after duration seconds. To stop manually:

EXEC DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();

Step 4: Prepare the Test Database

The test database must be a copy of production at the SCN when the capture started. Use RMAN to restore/duplicate the production database to the test host up to the capture start SCN.

# RMAN: restore production backup to test host at the capture start SCN
# (get capture start SCN from DBA_WORKLOAD_CAPTURES.START_SCN)
rman target sys/password@test_db

RMAN> RESTORE DATABASE UNTIL SCN 123456789;
RMAN> RECOVER DATABASE UNTIL SCN 123456789;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Apply the changes you want to test (upgrade, parameter change, new indexes, etc.) on the test database after the restore.

Step 5: Pre-Process the Capture Files

The capture files must be pre-processed for the test database:

-- 5.1 On the test database: create directory pointing to the capture files
-- (copy or NFS-mount the capture directory from production to test host)
CREATE OR REPLACE DIRECTORY db_replay_dir AS '/u01/db_replay/capture';

-- 5.2 Pre-process the capture
BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
    capture_dir => 'DB_REPLAY_DIR'
  );
END;
/

Pre-processing analyses the capture files, remaps user/schema names if needed, and prepares the replay metadata.

Step 6: Configure and Start the Replay

-- 6.1 Initialise the replay
BEGIN
  DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
    replay_name => 'REPLAY_POST_UPGRADE',
    replay_dir  => 'DB_REPLAY_DIR'
  );
END;
/

-- 6.2 Remap connection strings if needed (test DB has different service names)
BEGIN
  DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
    conn_id      => 1,
    replay_connection => 'test-host:1521/PDB_TEST'
  );
END;
/

-- 6.3 Prepare the replay (creates the replay client calibration)
BEGIN
  DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
    synchronization  => 'SCN',   -- SCN-based sync (most accurate)
    capture_sts      => TRUE,    -- enable STS capture during replay for SPA comparison
    sts_cap_interval => 300
  );
END;
/

Step 7: Start Replay Clients (wrc)

The Workload Replay Client (wrc) is an executable that simulates the original connections and submits captured SQL to the test database. Run it on one or more client hosts.

# Calibrate: how many wrc clients are needed?
wrc mode=calibrate replaydir=/u01/db_replay/capture

# Output example:
# Recommendation: use 4 replay clients

# Start the replay clients (one per host, adjust to recommendation)
wrc \
  userid=system/password@test-host:1521/PDB_TEST \
  mode=replay \
  replaydir=/u01/db_replay/capture \
  workdir=/u01/db_replay/logs &

wrc \
  userid=system/password@test-host:1521/PDB_TEST \
  mode=replay \
  replaydir=/u01/db_replay/capture \
  workdir=/u01/db_replay/logs &

Step 8: Start the Replay

-- Start the replay (this waits for wrc clients to connect, then begins)
EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY();

Step 9: Monitor Replay Progress

-- Monitor active replay
SELECT name,
       status,
       progress_percentage,
       awr_begin_snap,
       awr_end_snap
FROM dba_workload_replays
ORDER BY id DESC
FETCH FIRST 3 ROWS ONLY;

-- Real-time divergence monitoring (errors during replay)
SELECT *
FROM dba_workload_replay_divergence
WHERE replay_id = (SELECT MAX(id) FROM dba_workload_replays)
ORDER BY divergence_time
FETCH FIRST 20 ROWS ONLY;

Step 10: Generate the Comparison Report

After replay completes, generate the Database Replay report:

-- Generate comparison report (capture vs replay performance)
DECLARE
  v_report CLOB;
BEGIN
  v_report := DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT(
    replay_id1    => (SELECT MAX(id) FROM dba_workload_replays),
    replay_id2    => NULL,   -- compare against capture baseline
    format        => DBMS_WORKLOAD_REPLAY.TYPE_HTML
  );

  -- Write to file
  DBMS_XPLAN.DISPLAY_CURSOR();  -- placeholder; write v_report to file via UTL_FILE
END;
/

Or use Enterprise Manager (OEM Cloud Control) which provides an interactive Database Replay comparison dashboard — the recommended interface for reviewing results.

Part 2: SQL Performance Analyzer (SPA)

SPA tests the performance of individual SQL statements from an AWR SQL Tuning Set. It is faster and lighter than Database Replay and ideal for targeted SQL-level change validation.

Step 1: Create a SQL Tuning Set from AWR

-- 1.1 Create a SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'PROD_STS_2026_03',
    description => 'Production workload STS for SPA testing'
  );
END;
/

-- 1.2 Populate from AWR (last 7 days)
BEGIN
  DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
    begin_snap       => (SELECT MIN(snap_id) FROM dba_hist_snapshot WHERE begin_interval_time >= SYSDATE - 7),
    end_snap         => (SELECT MAX(snap_id) FROM dba_hist_snapshot),
    basic_filter     => 'elapsed_time > 1000000',   -- only SQL > 1 second
    ranking_measure1 => 'elapsed_time',
    result_percentage => 0.90,    -- top 90% by elapsed time
    result_limit     => 300,      -- max 300 SQL statements
    sqlset_name      => 'PROD_STS_2026_03',
    sqlset_owner     => 'SYSTEM'
  );
END;
/

Step 2: Stage the STS on the Test Database

Pack the STS into a staging table on production, export with Data Pump, and import to the test database:

-- Production: pack STS into a staging table
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
    sqlset_name        => 'PROD_STS_2026_03',
    sqlset_owner       => 'SYSTEM',
    staging_table_name => 'STS_STAGING',
    staging_schema_owner => 'SYSTEM'
  );
END;
/

# Export staging table with Data Pump
expdp system/password tables=SYSTEM.STS_STAGING directory=DATA_PUMP_DIR dumpfile=sts_staging.dmp

# Transfer and import on test database
impdp system/password tables=SYSTEM.STS_STAGING directory=DATA_PUMP_DIR dumpfile=sts_staging.dmp

-- Test database: unpack the STS
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
    sqlset_name         => 'PROD_STS_2026_03',
    sqlset_owner        => 'SYSTEM',
    replace             => TRUE,
    staging_table_name  => 'STS_STAGING',
    staging_schema_owner => 'SYSTEM'
  );
END;
/

Step 3: Create and Execute the SPA Task

-- 3.1 Create the SPA task
DECLARE
  v_task_name VARCHAR2(100);
BEGIN
  v_task_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
    sqlset_name  => 'PROD_STS_2026_03',
    sqlset_owner => 'SYSTEM',
    task_name    => 'SPA_UPGRADE_TEST'
  );
  DBMS_OUTPUT.PUT_LINE('Task created: ' || v_task_name);
END;
/

-- 3.2 Execute BEFORE trial: collect baseline execution statistics
BEGIN
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
    task_name        => 'SPA_UPGRADE_TEST',
    execution_type   => 'TEST EXECUTE',     -- actually executes each SQL
    execution_name   => 'BEFORE_UPGRADE',
    execution_params => DBMS_ADVISOR.ARGLIST(
      'time_limit', 3600,        -- max 1 hour for all SQL
      'test_execute_trials', 2   -- execute each SQL 2 times for stable stats
    )
  );
END;
/

-- 3.3 Apply the change (e.g., upgrade, parameter change, new index)
-- Example: change optimizer parameter
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE = '19.1.0' SCOPE=SESSION;

-- 3.4 Execute AFTER trial: collect post-change execution statistics
BEGIN
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
    task_name      => 'SPA_UPGRADE_TEST',
    execution_type => 'TEST EXECUTE',
    execution_name => 'AFTER_UPGRADE'
  );
END;
/

-- 3.5 Compare the two trials
BEGIN
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
    task_name        => 'SPA_UPGRADE_TEST',
    execution_type   => 'COMPARE PERFORMANCE',
    execution_name   => 'COMPARISON',
    execution_params => DBMS_ADVISOR.ARGLIST(
      'workload_impact_threshold', 1,    -- flag SQL with > 1% impact
      'sql_impact_threshold',      10    -- flag individual SQL with > 10% regression
    )
  );
END;
/

Step 4: Review SPA Report

-- Generate SPA report
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name      => 'SPA_UPGRADE_TEST',
  type           => 'TEXT',   -- or 'HTML' for richer output
  section        => 'SUMMARY'
) AS report
FROM DUAL;

Example summary output:

SQL Performance Analyzer Report: SPA_UPGRADE_TEST

Comparison: BEFORE_UPGRADE vs AFTER_UPGRADE

Overall Performance Change: -3.2% (improved)

SQL Statements Analyzed: 248
  Improved (>= 10%):  35  (14%)
  Unchanged:         198  (80%)
  Regressed (>= 10%): 15   (6%)

Top 5 Regressed SQL:
  sql_id        elapsed_before  elapsed_after  change
  ------------- --------------- -------------- ------
  8gx2n1yp4c3zw     0.12 sec        3.45 sec  +2775%
  ...

For regressed SQL, drill into the plan comparison:

-- Compare execution plans for a regressed SQL
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name    => 'SPA_UPGRADE_TEST',
  type         => 'TEXT',
  section      => 'ALL',
  level        => 'ALL',
  object_id    => (SELECT object_id FROM dba_advisor_objects
                   WHERE task_name = 'SPA_UPGRADE_TEST'
                     AND attr1 = '8gx2n1yp4c3zw')
) FROM DUAL;

Step 5: Fix Regressions with SQL Plan Management

For SQL that regressed, pin the pre-change plan using SQL Plan Baselines:

-- Load baseline from the BEFORE_UPGRADE STS execution
DECLARE
  v_cnt PLS_INTEGER;
BEGIN
  v_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name      => 'PROD_STS_2026_03',
    basic_filter     => 'sql_id = ''8gx2n1yp4c3zw''',
    fixed            => 'YES',   -- pin the plan
    enabled          => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE('Baselines loaded: ' || v_cnt);
END;
/

The pinned baseline ensures the pre-upgrade plan is used regardless of the parameter change, protecting application performance during the upgrade.

RAT Best Practices

  • Always capture during peak load — a capture during off-peak gives optimistic results.
  • Capture for at least 30 minutes — shorter captures miss batch jobs and intermittent workloads.
  • Run SPA before Database Replay — SPA is faster and identifies obvious regressions early; Database Replay then validates the full concurrent workload.
  • Fix SPA regressions with SQL Baselines before replay — clean up plan regressions first so they don’t inflate divergence numbers during full replay.
  • Store replay results in a dedicated schema — replay reports are stored in SYSTEM by default; move to a dedicated RAT_ADMIN user for cleaner retention.