Metalink Note: 560977.1: Real Application Testing for Earlier Releases
What is being announced?
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. This note
provides information on the Real Application Testing functionality available
for pre-11g database releases. 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 do you need to do?
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.
Please refer to Oracle documentation on OTN for information on the capture part
of Database Replay available for the earlier releases.
Table 1: Database Replay Availability Information for All Platforms Except Windows.
Enabling Workload Capture
This step is required for 10.2.0.4.0 only.
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: This step
is NOT required for version lower then 10.2.0.4.0.
@$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. The one-off
patches that need to be applied depend on the version of the source and the
destination databases as given in the table below.
Further information on this topic is available in the technical white paper “Oracle
Real Application Testing: Testing the SQL Performance Impact of Oracle
9i/10g Release 1 to Oracle Database 10g Release 2 upgrade with SQL
Performance Analyzer”. It is recommended to review this document before
using SQL Performance Analyzer functionality for earlier releases.
Patch 6865809 should be applied on the Oracle Database Release 11.1.0.6 test
database that is used to orchestrate SPA on Oracle Database 10.2. The patch
enables Oracle 11g test database to consume SQL trace files from previous
database releases and generate a SQL Tuning Set (STS). The STS can then be
used as input for the SQL Performance Analyzer task.
Patches applied on Oracle Database 10.2 source or destination databases
enable “Remote SQL Test Execution” functionality on that release for more
accurate analysis of performance data.
Table 3: SQL Performance Analyzer Availability Information. For Windows
platform, see additional notes below:
Note for Windows Platforms:
* For Windows 32-bit Platform patch number on top of 11.1.0.6.0 database is
7044721.
* For Windows 64-bit AMD64 patch number on top of 11.1.0.6.0 database is
7044728.
Note for 11.1.0.7.0 Database
If you're using 11.1.0.7.0 database for SPA testing, you do not need to apply any
of the above-mentioned patches on the 11g SPA system that is used for
analysis. All the fixes are already in there. On 10.X database side, you do need
the patch as mentioned above.
Who to contact for more information?
Please create a Service Request with Oracle Support
References
Note 466181.1 - 10g Upgrade Companion
Note 562899.1 - TESTING SQL PERFORMANCE IMPACT OF AN ORACLE 9i
TO ORACLE DATABASE 10g RELEASE 2 UPGRADE WITH SQL
PERFORMANCE ANALYZER
Note 601807.1 - Oracle 11g Upgrade Companion
Keywords
SQL~PERFORMANCE~ANALYZER ; UPGRADE ; SQL~TUNING ; UPGRADE~
TO~10.2.0.3.0 ; MIGRATE~DATABASE ; PATCH~UPGRADE~TO10.sp;SQL~T
UNING~ADVISOR ; TUNING~PACK ; UPGRADE~FROM~9.2.0 ; UPGRADE~T
O~9.2.0 ;
No comments:
Post a Comment