Database

Real Application Testing Now Available for Earlier Releases

Oracle Database 11g introduced Database Replay and SQL Performance Analyzer as part of the Real Application Testing option to enable businesses identify issues with system changes before production deployment. The main goal of making Real Application Testing functionality available in earlier releases is to enable customers to upgrade to higher database releases.

SQL Performance Analyzer allows users to identify SQL performance regressions caused by system changes such as adding an index, gathering optimizer statistics, implementing partitioning, RDBMS upgrades, etc. SQL Performance Analyzer assesses the impact of change on SQL response times by executing each SQL serially with production context (bind variables, optimizer environment, etc.) before and after a change and then provides a report highlighting any performance divergences.

Database Replay allows users to perform comprehensive testing of database and infrastructure changes using real application workloads. It allows capture of production workload including concurrency, think time, and transactions dependencies, and allows users to replay the workload on a test system with the exact same production characteristics so that all problems can be identified and remediate in test before deploying the change to production.

Please check the “Licensing Information” manual on OTN for details regarding licensing of Real Application Testing Option.

What you need to do to use Real Application Testing functionality in earlier releases?

Real Application Testing functionality for pre-11g database releases is installed using the “opatch” utility and following instructions for the generic and platform specific “README” for the patches. If you want to only use Database Replay or SQL Performance Analyzer, then only the patch for that particular functionality needs to be applied. If you want to use both Database Replay and SQL Performance Analyzer, the patches for both the functionalities need to be applied.

Database Replay

To help customers upgrade to Oracle Database 11g, the “capture” functionality of Database Replay has been made available in previous releases as shown in the table below. The replay of the captured workload can only be done on Oracle Database 11g and higher.

Enabling Workload Capture
By default, the workload capture is disabled on the pre-11g database releases. Database workload capture functionality is enabled on the system by specifying the PRE_11G_ENABLE_CAPTURE initialization parameter. To enable workload capture, run the wrrenbl.sql script at the SQL prompt as sys or system:
@$ORACLE_HOME/rdbms/admin/wrrenbl.sql

The wrrenbl.sql script calls the ALTER SYSTEM SQL statement to set the PRE_11G_ENABLE_CAPTURE initialization parameter to TRUE. Please check the OTN documentation mentioned above for more details.

SQL Performance Analyzer

SQL Performance Analyzer functionality has been enhanced and made available in earlier releases to help customers upgrade their databases from versions Oracle 9.x, 10.1.x, and 10.2.x to higher release. Applying the one-off patches as shown below actually installs the SQL Performance Analyzer functionality for that release. After the functionality is installed, customers can then leverage Oracle Database 11g SQL Performance Analyzer functionality to upgrade from any database releases 9.x, 10.1.x, 10.2.x to 10.2 or higher release.

This note 560977.1 provides information on the Real Application Testing functionality available for pre-11g database releases.

Other reference:

Note 562899.1 - TESTING SQL PERFORMANCE IMPACT OF AN ORACLE 9i TO ORACLE DATABASE 10g RELEASE 2 UPGRADE WITH SQL PERFORMANCE ANALYZER

© 2008 OracleInsight.net.