Testing infrastructure changes

Real Application Testing (RAT) is an Oracle Database Enterprise Edition option that allows you to test out the effects of major or minor infrastructure changes on your database.

This may include any changes at the database layer or below, such as database upgrades from 9.2, 10.1, or 10.2 to 11g, moving from a single-instance database to a RAC database, migrating to a different OS, using a different storage subsystem, moving to Exadata, and so on. It may also include database patches, initialization parameter changes, optimizer setting changes, and so on.

RAT includes Database Replay as well as SQL Performance Analyzer (SPA). Both of these components of RAT can be fully executed via Enterprise Manager Cloud Control 12c.

Database Replay can be accessed by going to Performance | Database Replay on the database menu in Enterprise Manager. The screen appears as follows:

Database Replay is a unique feature available for Oracle databases. You can capture an entire workload from a production system, move it to a test system, and replay it there with exactly the same timing, concurrency, and transaction characteristics of the production database.

Database Replay then analyses the effect of the replay on the test system and reports extensively if any errors are encountered, if SQL statements have regressed in performance, or if there are new contention issues. These issues can then be fixed by using other tools such as the Enterprise Manager Tuning Pack.

Testing infrastructure changes in this way gives your company and your DBA greater confidence that the application database will have a guaranteed performance in production after the infrastructure change. For example, Oracle 9i databases upgrading to 10g in the past, without proper testing, were known to have performance regressions due to outdated parameter settings. Such upgrades from older databases will produce a higher degree of confidence if RAT has been used.

Any infrastructure changes from the database tier and below can be tested with this method. The performance impact to the production system during the capture phase has been estimated to be less than 5 percent, and you can start and stop the capture when you please.

The capture files are moved to the test system (where the infrastructure change has taken place). In the database there, they are preprocessed, and then replayed.

The entire process from capture to preprocess to replay is orchestrated in a workflow by Enterprise Manager. Multiple replay clients can be set up to replay the workload, so the mid-tier is not required by the test system.

The RAT option has been enhanced in May 2012 to support consolidated database replay. This allows two or more captured production workloads from the same or different systems to be replayed concurrently on a single test database. The idea is to assist in assessing the impact of consolidating the database workload of multiple databases on a single database.

The consolidated replay is possible only on Oracle Database Release 11.2.0.2 or higher. The multiple-workload captures can be from pre-11.2.0.2 database releases.

At the time of writing, the Enterprise Manager GUI interface could be used for the capture and replay of workloads on single databases as before, but could not be used for the replay of consolidated workloads from multiple databases onto a single database. Only the API can be used for this purpose. For the database patches required, and the actual procedure steps, please see the My Oracle Support note ID 1453789.1: Real Application Testing: Consolidated Database Replay Feature.

SQL Performance Analyzer, the other component of Real Application Testing, can be accessed by going to Performance | SQL | SQL Performance Analyzer on the database menu. The SPA screen appears as follows:

You can capture the SQL workload in production (in most cases done via a SQL tuning set), and the same SQL will be reexecuted serially (not concurrently like Database Replay) on the test database by SPA. The captured SQL output includes statistics and bind variables.

The analysis and report generated by SPA enables you to see if your SQL statements have improved or regressed in the new environment. Any regression can be fixed using other tools such as the SQL Tuning Advisor or SQL plan baselines, in a seamless flow from the SPA task-result screen.

The main difference between SPA and Database Replay (the two components of Real Application Testing) is that the latter captures the entire database workload in a specified time period and tests it out, whereas SPA can be used to test a limited set of SQL statements. While Database Replay runs the workload concurrently, SPA uses serial execution and tests each SQL statement one-by-one on its own.

SPA can also be used to test minor performance changes, such as the effects of optimizer statistics changes on SQL tuning set performance, or the effects of new indexes or other objects such as materialized views, as recommended by the tuning advisors. As such, SPA usage can go hand in hand with the Enterprise Manager diagnostics and tuning packs.

Interestingly, due to popular demand, RAT has been back ported to earlier releases of the Oracle database.

In the case of the Database Replay component of RAT, this back port is with respect to the capturing of the database workloads. Database Replay can capture workloads on 9.2 or 10.2 database versions, but the actual replay of the workload can only be done on 11g versions such as 11.1 (replay was first introduced in 11.1.0.6) and 11.2. Note that the replay cannot be done on 10g databases (for upgrades of 9i to 10g) because RAT is essentially an 11g product. Hence, there will be no back port for the replay.

On the other hand, the SPA component of RAT can use captured SQL from 9.2, 10.1, or 10.2 database releases and SQL can be tested by SPA in database versions 10.2 or 11g (11.1 and 11.2).

This means that SPA can be used to test an upgrade from 9.2 to 10.1 or 10.2, in case you want to do that, but this is not recommended as you should instead be upgrading to the latest 11.2 database version (to avoid the extra support costs for older versions, and to be able to use the new features of the most recent database version).

By the way, SQL Trace is used to capture the SQL statements for SPA in a 9.2 database since there is no SQL tuning set capability in this older version of the database.

For further details, you can refer to the MOS note ID 560977.1: Real Application Testing for Earlier Releases.