Oracle 19c Real Application Testing in Database Systems

Real Application Testing (RAT) in modern database systems is a powerful tool that allows database administrators to test system changes in a production-like environment. By simulating real workloads, RAT provides insights into how changes will impact the system, minimizing the risk of negative consequences. This blog will delve into the details of using RAT, demonstrating its implementation and benefits.

Introduction to Real Application Testing

Real Application Testing is designed to help DBAs make proactive changes by providing a clear picture of what to expect when those changes are implemented. It is particularly useful for testing hardware replacements, database or operating system upgrades, storage changes, and significant configuration changes. The ability to capture and replay real database workloads makes RAT an invaluable tool for ensuring system stability and performance.

Key Features of Real Application Testing

Real Application Testing includes several components that facilitate comprehensive testing:

SQL Performance Analyzer (SPA): This tool allows DBAs to analyze the impact of changes on SQL performance.

Database Replay: This feature captures a workload from a production system and replays it on a test system to assess the impact of changes.

SPA Quick Check: Provides a quick analysis of SQL performance impact.

Concurrent Database Replay: Allows multiple workload replays to be run concurrently.

Migration Planner: Assists in planning and executing database migrations.

📢 You might also like: SQL Performance Analyzer in Oracle 19c to Test the Impact of Database Changes (Category: Performance Management and Tuning)

Implementation of Real App Testing in Modern Databases

Modern databases enhance RAT capabilities by allowing more granular workload capture and replay. This granularity leads to better testing, reduced downtime, and more effective change management.

Initial Setup

First, ensure that the RAT environment is set up correctly. This involves creating directories for capturing and replaying workloads.

Create a capture directory on the source database server:

SQL> CREATE OR REPLACE DIRECTORY mycapture_dir AS '/path/to/capture';

Create a replay directory on the target database server:

SQL> CREATE OR REPLACE DIRECTORY replay_dir AS '/path/to/replay';

Capturing Workloads

To capture workloads, use the appropriate workload capture package. Here is an example script to start the capture process:

SQL> BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (
    name     => 'Test_Capture',
    dir      => 'mycapture_dir',
    duration => 900
  );
END;
/

Monitor the capture process:

SQL> SELECT id, name, status FROM dba_workload_captures WHERE name='Test_Capture';

Finish the capture once the workload has been sufficiently recorded:

SQL> BEGIN
  DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/

Preparing for Replay

After capturing the workload, transfer the capture files to the target server and preprocess them using the workload replay package.

Process the capture:

SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture('replay_dir');
END;
/

Initialize the replay:

SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.initialize_replay(
    replay_name => 'Replay_Test',
    replay_dir  => 'replay_dir'
  );
END;
/

RAT: Remapping Connections

Remap the connections to ensure that the workload replay connects to the correct database instances. Use the connection remap procedure for this purpose:

SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.remap_connection(
    connection_id     => 1,
    replay_connection => 'DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=NEW_SERVICE_NAME)(ADDRESS=(PROTOCOL=TCP)(HOST=new_host)(PORT=1521)))'
  );
END;
/

Verify the remapped connections:

SQL> SELECT conn_id, capture_conn, replay_conn FROM dba_workload_connection_map;

Starting the Replay

Once the connections are remapped, prepare and start the replay. The replay preparation procedure prepares the replay environment:

SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.prepare_replay;
END;
/

Start the workload replay:

SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

Monitor the progress using the workload replay views:

SQL> SELECT id, name, status FROM dba_workload_replays WHERE name='Replay_Test';

Real Application Testing – Analyzing Replay Results

After the replay completes, generate and review the replay reports to analyze the impact of changes. Use the replay report procedure to generate the report:

SQL> DECLARE
  rep_rpt CLOB;
BEGIN
  rep_rpt := DBMS_WORKLOAD_REPLAY.report(
    replay_id => 1,
    format    => DBMS_WORKLOAD_REPLAY.TYPE_HTML
  );
  DBMS_OUTPUT.put_line(DBMS_LOB.SUBSTR(rep_rpt, 32767, 1));
END;
/

Benefits of Using Real Application Testing

Real Application Testing offers numerous benefits, including:

• Minimized Risk: By simulating real workloads, DBAs can identify potential issues before they affect the production environment.
• Improved Performance: Fine-tuning system changes based on real workload data leads to optimized performance.
• Enhanced Planning: Better planning and execution of database upgrades, migrations, and configuration changes.
• Reduced Downtime: Thorough testing ensures smoother transitions and less downtime during maintenance windows.

Conclusion

Real Application Testing is a powerful feature that enables database administrators to simulate real-world scenarios and assess the impact of changes in a controlled environment. By leveraging RAT, DBAs can ensure the stability, performance, and reliability of their databases, making it an essential tool for modern database management.

See more on Oracle’s website!

Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!