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

We have come to the final part of the series of Oracle Database Testing.

So far, we have dealt with methods of testing the Oracle database. Continuing this focus we shall dive into further details with respect to Oracle Real Application Testing.

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

This is the leading solution by Oracle to capture the real production environment DB workload and replace it on test environment.

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

We can categorize Oracle Real Application Testing into two broad sections:

1. SQL Performance Analyzer
2. Database Replay

Before we go any further, Please note that SQL Performance Analyzer and Database Replay requires additional licensing i.e. it is available at an extra cost and 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 image to view enlarged)

SQL Performance Analyzer enables us to gauge the performance impact of any change to the system that might have an impact on the 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 the 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 on production.

A basic overview of a SQL Performance Analyzer workflow is shown 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 on 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 which 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 would have two SQL trials – one pre-change and post-change trial to compare.

Similar to Pre-Change SQL performance Trial, we can create 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 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 performance of the SQL

Statements and then deploy the system changes in production.

Similarly, we can test various workloads with various system changes and make sure that we test each one of them prior to 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 image to view enlarged)

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 rest assured when dealing with change and be lucrative in assessing 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 making sure that we test every aspect of this change from all degrees 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 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. The workload on production is monitored, analyzed and recorded over a quantitative fixed period of time. This data is recorded over time and is used to replay the workload on test systems.

By performing this, we can successfully test the implications of the workload prior to 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 which provides a description of 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. In case, you do not have another system to test and would like to run them in production, make sure to run them during non-peak hours so that the users and processes running on 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.

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

1. Use identical Test system as and when possible
2. Test one change at a time to understand its impact
3. Make sure to start with default replay options and then make changes if necessary based on your requirement.
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

Conclusion:

With various aspects and various methods of Oracle Database and Application Testing, please always make sure to test as frequently and as thoroughly as possible; understand the application and the user environment prior to deploying any alterations or introducing any new parameters in Production.