Oracle Real Application Testing – Solution to Test Oracle DB Before Moving to Production

We have reached the ultimate segment of our Oracle Database Testing sequence.

Thus far, we have delved into various techniques for scrutinizing the Oracle database. Focusing on this aspect, let’s go into further specifics about Oracle Real Application Testing.

Today we are going to uncover the facts of Oracle Real Application Testing – a robust strategy for validating changes in a controlled environment before pushing them to the production stage.

Oracle Real Application Testing is Oracle’s prime solution for recording the production environment DB’s workload and reproducing it on a test setup.

As we have stated multiple times, it’s of utmost importance to examine the database across all parameters to eradicate instabilities and to ascertain that no unexpected problems emerge in the production scenario.

There are two primary components to Oracle Real Application Testing:

1. SQL Performance Analyzer
2. Database Replay

Do note that SQL Performance Analyzer and Database Replay call for extra licensing and come at additional charges in the form of the Enterprise Edition option.

SQL Performance Analyzer

To gain access to SQL Performance Analyzer and Database Replay, we use the Enterprise Manager GUI, as depicted below:

To utilize SQL Performance Analyzer, just click on the “SQL Performance Analyzer” link

(To view it in a larger resolution, click on the image)

SQL Performance Analyzer grants us the ability to analyze the potential consequences of any alterations to the infrastructure that could impact SQL execution and efficiency.

Some of the main use-cases for this include:

• Patching and Upgrading Databases
• Adjustments in the operating system’s configuration (both hardware and software)
• Changes in Oracle Optimizer statistics
• Alterations in User/Schema

It’s encouraged to run SQL Performance Analyzer on a testing or UAT (User Acceptance Testing) setup rather than the live production system. With the help of testing the potential performance impacts of modifications, we prevent accidentally introducing issues that affect users in the production module. Furthermore, by running it on a testing setup, we ensure that ongoing processes in production are not interrupted.

The fundamental workflow for SQL Performance Analyzer is as follows:

The process of SQL Performance Analysis includes the following steps:

Step #1) SQL Workload Capture

Make note of the SQL statements that reflect the workload you plan to analyze from your production module. An ideal workload should suitably mirror that of the production stage.

These statements are grouped into a SQL Tuning Set and supplied to the SQL Performance Analyzer.

Due to the Analyzer’s high resource demand, it should ideally be run on a testing system or UAT setup. To accomplish this on a testing setup, you’ll need to export the SQL Tuning set created in production to the testing setup.

Step #2) The Creating of a SQL Performance Analyzer Task

For the Analyzer to operate, you need to generate a SQL Performance Analyzer task. This task is essentially a repository that compiles all the data analyzed by the SQL Performance Analyzer. The SQL Tuning Set is utilized as input for the Analyzer.

Step #3) Preparing for Pre-Change SQL Performance Trial

With the SQL Performance Analyzer task and SQL Tuning Set created, we have to set up the framework on the Test system.

When planning to employ a testing setup, it’s pivotal to see that it closely imitates the production setup regarding hardware, software, and storage to recreate a matching environment.

With the correct configuration of the Test system, we can establish the pre-change data version using SQL Performance Analyzer.

This operation can be executed using the Enterprise Manager or integrated APIs.

Step #4) Post-Change SQL Performance Trial

Once system modifications have been performed, a post-change trial is conducted on the testing setup.

At this juncture, we’d possess two SQL trials – pre-change and post-change – for assessment purposes.

Similar to the pre-change SQL performance trial, we can construct the post-change SQL performance trial using the Enterprise Manager or inherent APIs.

Step #5) Report Generation

Once the pre-change and post-change trials have been conducted, the gathered performance data can be contrasted by executing a comparison analysis using the SQL Performance Analyzer.

Upon completion of the comparison task, a report can be generated for assessing the performance of the SQL statements that comprised the tested workload.

By scrutinizing the report, we can make informed decisions and infer the performance of the SQL statements, and finally, advance with implementing the system alterations in production.

Similarly, we can assess different workloads with assorted system modifications and guarantee that each one is assessed before being deployed in production.

The aforedescribed workflow can be symbolically depicted as follows:

Database Replay

For operation through Enterprise Manager:

(Click on the image to view it in high resolution)

Database Replay enables practical testing of system alterations by mimicking your production setup on a testing system. It records the desired workload on the live system and then replays it on a testing system that reproduces the same resource attributes as the initially recorded workload, including transactions, SQL execution, extracts, and procedures.

This guarantees that all potential impacts of a change are accounted for, including unwanted outcomes like product complications, incorrect results, or performance regression.

Comprehensive reporting and analysis are produced to recognize any possible problems encountered, such as adverse situations and performance divergences.

As a consequence, enterprises can manage change competently and effectively gauge the overall success of system alterations, substantially minimizing risks when introducing changes in the production stage. Seeing as change is unavoidable, extensively testing each facet of the change from all angles enhances the sturdiness and dependability of the production environment.

The core workflow of Database Replay is as follows:

Modifications supported by Database Replay encompass:

• Oracle Database Software Patching and Upgrades
• User/Schema and Database Instance Parameters (e.g., I/O, memory)
• Hardware/software alterations to RAC (Real Application Cluster) nodes
• Operating System Patching and Changes
• Storage, Memory, and CPU

Database Replay affords us the ability to evaluate the numerous effects of potential adjustments to the system by replaying the functional load of an actual production setup on a testing setup before making it live in production. The workload in production is equipped with monitoring, analysis, and recording capabilities over a specific time frame. This data is then deployed to replay the workload on testing systems.

In doing so, we can efficiently test the features of the workload before implementing any modifications that could adversely affect production.

The workflow is tabulated as follows:

Step #1) Capturing Workload

We capture all requests from the client and preserve them in files called “Capture files” within the file system (storage). These files bear all essential information about the client requests, such as details about transactions, procedures, binds, and SQL. These files can then be transported to any system for workload replay.

Step #2) Preprocessing Workload

After capturing the information and storing them in “Capture files,” we must preprocess them. This step involves the creation of metadata, which outlines all the data needed for the replaying of the workload.

Since this step demands a considerable amount of system resources, it’s best to execute it on a system separate from production, where you can replay the workload. If production is your only option, ensure to run it during periods of low user activity to minimize interference with processes and users operating in production.

Step #3) Replay Workload

Now that we have completed capturing and preprocessing, we can replay the workload on the testing setup. In this stage, we replay all context, procedures, transactions, and SQL statements that were documented during the initial capture stage, documenting data as each procedure undergoes this transition.

Step #4) Report Generation

Like with the Performance Analyzer, you have the ability to create and view reports to evaluate the diverse tests you have conducted.

In wrapping things up, here are some handy tips for the testing of Database Replay:

1. Always use an identical testing setup if possible
2. Examine one change at a time to recognize its impact
3. Initiate with default replay options and apply changes if required based on your necessities
4. Before initializing the second replay, ensure to comprehend all testing aspects thoroughly
5. Document any changes or testing actions needed and retain test results
6. Confirm that no other users or workload are using the system during testing runs

The Bottom Line:

Given the diverse aspects and techniques of Oracle Database and Application Testing, always make sure to conduct periodic and thorough tests. Know the application and user surroundings well before deploying any changes or introducing new parameters in Production.