Real application testing is an essential part of database management. With Oracle 19c, this testing method has become even more critical for ensuring system changes do not negatively impact performance or functionality. Testing methods like RAT (Real Application Testing) allow database administrators to capture and replay database workloads in a controlled environment. This tutorial will provide an in-depth overview of real application testing, including its components, benefits, and a detailed guide on setting it up and using it effectively. Testing techniques such as these are vital for maintaining database integrity and ensuring smooth operation during upgrades and changes.
Key Features of Real Application Testing
Real application testing in Oracle 19c consists of two main components: Database Replay and SQL Performance Analyzer (SPA). Database Replay captures the entire database workload and replays it on a test system to analyze performance changes. SPA focuses on analyzing the performance of individual SQL statements before and after changes. These testing methods ensure comprehensive analysis and validation of database changes. Implementing real application testing can significantly reduce the risks associated with system upgrades and modifications. By using these advanced testing techniques, DBAs can ensure that their systems remain stable and efficient.
Setting Up the Environment
Before you can begin real application testing, it’s crucial to set up the environment correctly. This involves creating the necessary directories for capturing and replaying workloads. Follow the steps below to ensure a smooth setup. These initial steps are critical for effective real application testing. Proper preparation ensures that the testing environment closely mirrors the production environment, allowing for accurate results.
Creating Directories
To create directories for workload capture and replay, use the following commands in SQL*Plus:
-- Create a directory for capturing workload data
CREATE OR REPLACE DIRECTORY capture_dir AS '/path/to/capture';
-- Create a directory for replaying workload data
CREATE OR REPLACE DIRECTORY replay_dir AS '/path/to/replay';
Ensure these directories have sufficient space and are accessible. Proper directory setup is crucial for the success of real application testing. This step ensures that all captured data is stored securely and can be accessed easily during the replay phase.
Capturing Workloads for RAT
Starting Workload Capture
To capture a workload, initiate the capture process using the following PL/SQL code:
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture(
name => 'Test_Capture',
dir => 'capture_dir',
duration => 900,
plsql_mode => 'extended');
END;
/
This code will start capturing the workload for 15 minutes, storing the data in the specified directory. Accurate capture of workloads is a fundamental part of real application testing. Capturing workloads accurately ensures that the replay phase can closely mimic real-world usage, providing valuable insights.
Checking Capture Status
You can check the status of the workload capture with the following query:
SELECT id, name, status
FROM dba_workload_captures
WHERE name = 'Test_Capture';
Monitoring the capture process ensures that all necessary data is collected for real application testing. By regularly checking the status, DBAs can verify that the capture is proceeding as expected and make adjustments if necessary.
đŸ“¢ You might also like: Oracle 19c Performance Issues on Shared Pool (Category: Performance Management and Tuning)
Replaying Workloads
Initializing Replay
After capturing the workload, you can replay it on a test system. Use the following PL/SQL code to initialize the replay:
BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay(
replay_name => 'Test_Replay',
replay_dir => 'replay_dir');
END;
/
Preparing and Starting Replay
Prepare the replay environment with the following command:
BEGIN
DBMS_WORKLOAD_REPLAY.prepare_replay(
synchronization => 'SCN',
connect_time_scale => 100,
think_time_scale => 100,
think_time_auto_correct => TRUE);
END;
/
Then, start the replay process:
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
Monitoring Replay
Monitor the replay status using:
SELECT id, name, status
FROM dba_workload_replays
WHERE name = 'Test_Replay';
Monitoring the replay process helps in identifying any issues during the real application testing phase. This step ensures that any deviations or errors can be quickly addressed, maintaining the integrity of the testing process.
Generating Replay Report
Generate a detailed replay report with:
DECLARE
replay_report CLOB;
BEGIN
replay_report := DBMS_WORKLOAD_REPLAY.report(
replay_id => 1,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
DBMS_OUTPUT.put_line(DBMS_LOB.substr(replay_report, 32767, 1));
END;
/
Generating reports provides insights into the effectiveness of the real application testing. Detailed reports help DBAs understand the impact of changes and make informed decisions about deploying updates.
Real Application Testing in Oracle Multitenant
Oracle 19c’s real application testing also supports Oracle Multitenant environments, allowing you to capture and replay workloads at the pluggable database (PDB) level. This feature is particularly useful for testing changes in a multitenant architecture. Using advanced testing methods ensures that each PDB maintains optimal performance and stability. Application testing in a multitenant environment requires careful setup and execution to ensure accurate results.
Capturing Workloads in PDBs
In a multitenant environment, you can capture workloads at the PDB level. Use the following PL/SQL code:
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture(
name => 'PDB_Test_Capture',
dir => 'capture_dir',
duration => 900,
plsql_mode => 'extended');
END;
/
Replaying Workloads in PDBs
Replay the captured workload in a PDB with the following code:
BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay(
replay_name => 'PDB_Test_Replay',
replay_dir => 'replay_dir');
END;
/
Preparing and Starting Replay in PDBs
Prepare the replay environment for the PDB:
BEGIN
DBMS_WORKLOAD_REPLAY.prepare_replay(
synchronization => 'SCN',
connect_time_scale => 100,
think_time_scale => 100,
think_time_auto_correct => TRUE);
END;
/
Start the replay process:
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
Monitoring and Reporting in PDBs
Monitor the replay status:
SELECT id, name, status
FROM dba_workload_replays
WHERE name = 'PDB_Test_Replay';
Generate a detailed replay report:
DECLARE
replay_report CLOB;
BEGIN
replay_report := DBMS_WORKLOAD_REPLAY.report(
replay_id => 1,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
DBMS_OUTPUT.put_line(DBMS_LOB.substr(replay_report, 32767, 1));
END;
/
Benefits of Real Application Testing
Real application testing offers numerous benefits, including reduced risk, performance assurance, and error detection. By testing changes in a controlled environment, RAT ensures that performance benchmarks are maintained and potential issues are identified before changes are implemented in production. Advanced testing techniques further ensure that system updates do not disrupt the workflow. Real application testing provides a safety net for database changes, enhancing overall system reliability. These methods help DBAs maintain high standards of performance and reliability in their database environments.
Conclusion
Real application testing in Oracle 19c is a powerful tool for database administrators. It enables them to test and validate changes in a controlled environment, ensuring that performance and functionality are not negatively impacted. By leveraging real application testing, DBAs can confidently implement upgrades, patches, and other changes, knowing they have thoroughly tested their impact on the database environment. This comprehensive testing approach helps maintain system stability and performance. As database environments grow more complex, real application testing becomes increasingly important for maintaining operational excellence.
See more on Oracle’s website!
Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!
