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

This post is all about Oracle Real Application Testing. We have come to the final part of the series of Oracle Database Testing.

So far, we have dealt with different methods of testing the Oracle database. To continue, we shall dive into further details concerning Oracle Real Application Testing in this article.

Today, we will learn Oracle Real Application Testing, an effective change assurance system that assesses the system change in the test environment itself before introducing it to production.

It is a leading solution by Oracle to capture the real production environment DB workload and replace it in a test environment.

Oracle Real Application Testing

As stated on numerous occasions, we always need to make sure we test the database in every way to eradicate instabilities and ensure that we do not encounter any unforeseen issues in our production instance.

We can categorize Oracle Real Application Testing

1. SQL Performance Analyzer
2. Database Replay

Before we go any further, please note that SQL Performance Analyzer and Database Replay require additional licensing. It is available at an extra cost and is an Enterprise Edition option.

SQL Performance Analyzer

The GUI used to access SQL Performance Analyzer and Database Replay is Enterprise Manager, which is as shown below:

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

(Click on the image below to get an enlarged view)

SQL Performance Analyzer enables us to gauge the performance impact of any change to the system that might impact SQL execution and performance.

They are extremely useful in cases such as:

• Database Upgrade, Patching
• Configuration changes to the operating system–Software or hardware
• Oracle Optimizer statistics changes
• User/Schema changes

It is always advised to run the SQL Performance Analyze on a test or a UAT (User Application Testing) system rather than on a Production system. Since, while testing the effects of a change in terms of performance, we could inadvertently affect the users running in the production instance.

Also, running it on a test will ensure we do not tamper with any currently running processes in production.

Let’s take a look at a basic overview of the SQL Performance Analyzer workflow below.

SQL Performance Analysis involves the following steps:

Step #1: Capturing SQL Workload

Determine the SQL statements that would be part of your SQL workload from your production instance, which you would like to analyze. This workload should ideally represent the workload that you might have in your production.

We capture these statements in a SQL Tuning Set and feed this SQL Tuning Set to the SQL Performance Analyzer.

Since the Analyzer consumes a lot of resources in your system, we always recommend having them run on a test or a UAT system. To run it on a test system, we would have to export the SQL Tuning set which we have already created in production into the test system.

Step #2: Creating a SQL Performance Analyzer Task

To run the Analyzer, you need to first create a SQL Performance Analyzer task. This task is nothing but a repository that consolidates all the data about the analysis which is executed by the SQL Performance Analyzer. As indicated earlier, the SQL Tuning Set is fed as a stimulant to the Analyzer.

Step #3: Pre-Change SQL Performance Trial

After having created the SQL Performance Analyzer task and the SQL Tuning Set, we need to build the infrastructure on the Test system.

Please note that when we plan to use a system to test, we need to make sure it is very similar to the production system in terms of hardware, software, and storage so that we can replicate a similar environment.

Once the Test system is appropriately configured, we can build the pre-change version of the data using SQL Performance Analyzer. This can be achieved by using either Enterprise Manager or APIs (inbuilt procedures).

Step #4: Post-Change SQL Performance Trial

The Post-Change trial is performed on the test system after making some changes to the system. Once this is completed, we will have two SQL trials, pre-change, and post-change to compare.

Similar to the Pre-Change SQL performance Trial, we can create a Post-Change SQL performance Trial using either Enterprise Manager or APIs (in-built procedures).

Step #5: Generating a Report

After executing the Pre-change and Post-change trials, the performance data collected in them can be compared by running a comparison analysis using the SQL Performance Analyzer.

Once this comparison task is completed, we can generate a report to identify the SQL statement’s performance which was part of the workload we intended to test. By reviewing the report, we can judge and make conclusions on the performance of the SQL Statements and then deploy the system changes in production.

Similarly, we can test various workloads with different system changes and make sure that we test each one of them before having them implemented in production.

The workflow illustrated above can be graphically represented as shown below.

Database Replay

To run the tool through Enterprise Manager:

(Click on the image to get an enlarged view)

Database Replay allows realistic testing of system changes by essentially replicating your production environment on a test system. It does this by capturing a desired workload on the production system and replaying it on a test system with the exact resource characteristics of the original workload, such as SQL execution, transactions, extracts, and procedures.

This is performed to make sure we consider all possible impacts of any change, including undesired results such as product bugs, inappropriate results, or performance regression.

Extensive analysis and reporting generated also help to identify any potential problems, such as erroneous circumstances encountered and performance divergences.

As a result, organizations can confidently deal with change and profitably assess the overall success of the system change. This will significantly reduce any risk when we want to implement the changes in production. Change is inevitable, and ensuring that we test every aspect of this change from all angles will make the production more robust and sturdy.

A basic workflow of the Database replay is as shown below:

Changes Supported by Database Replay are:

• Oracle Database Upgrades, Software Patching
• User/Schema, Database instance Parameters such as memory, I/O
• Hardware/software changes to RAC (Real Application Cluster) nodes
• Operating System Changes, Operating System Patching
• CPU, Memory, Storage

Database Replay allows us to test the various effects of possible changes to the system by replaying the practical load of an actual production system on a test system before it is exposed to the former. We keep track of, analyze and record the workload on production over a quantitative fixed period. The data is logged and later utilized to simulate the workload on test systems.

By performing this, we can successfully test the implications of the workload before implementing any changes that might adversely affect production.

The workflow is as follows:

Step #1: Workload Capture

We record all requests made by clients into files termed “Capture files” on the file system (storage). These files contain all the vital information regarding the client requests such as SQL, binds, procedures, and transaction information. These files can then be exported to any system in case we want to replay them on another system.

Step #2: Workload Preprocessing

After having captured the information in the “Capture files” we need to preprocess them. In this step, we create metadata that describes every data required for replaying the workload.

Since this step uses a huge amount of resources from the system, it is advised to run on another system apart from production where the load can be replayed. If you do not have another system to test and would like to run them in production, run them during non-peak hours so that users and processes running in production aren’t affected.

Step #3: Workload Replay

Now, we can replay them on the test system. At this time we replay all of the transactions, context, procedures, and SQL that were captured initially during the capture phase accumulating data as every process undergoes this transition.

Step #4: Generating Reports

Similar to the Performance Analyzer, you can also generate and view reports to compare each of the tests that you have executed.

Conclusion

To conclude, we offer a couple of quick tips while testing Database Replay:

1. Use an identical Test system as and when possible.
2. Test one change at a time to understand its impact.
3. Make sure to start with the default replay option and then make changes if necessary based on your requirements.
4. Before performing the second replay, make sure to understand all of the testing aspects.
5. Make sure to save your test results and document any changes/testing actions required.
6. Make sure no other workload or users are using the system during any of the test runs.

When dealing with different aspects and methods of Oracle Database and Application Testing, be sure to test as frequently and thoroughly as possible.

Please post your feedback and suggestions in the comments section below. We would love to hear from you.