Real Application Testing Load and SQL impact testing solution providing highest quality testing for the database tier
- Benefit
- Faster technology adoption
- Lower cost and risk
- Features
- Database Replay
- SQL Performance Analyzer (SPA)
- Help customers smoothly transition to Oracle Database 10g and 11g
- SQL Test Execution feature of SPA backported to 10gR2
- For more details:
- Metalink Note: 560977.1 – “Real Application Testing for Earlier Releases”
- Metalink Note: 562899.1 – “Testing Performance Impact of an Oracle 9i to Oracle Database 10g Release 2 Upgrade with SQL Performance Analyzer”
© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Current Trends in Real World Database Performance
- Data gathering for troubleshooting Oracle Real Application Cluster issues
- Benefits From Oracle Real Application Cluster
- Oracle Real Application Clusters Installation and Configuration Best Practices
- Upgrade to Oracle Real Application Clusters 11g Release 2 Key Success Factors
- Real-Time Business Intelligence using Oracle GoldenGate
- Using Real-Time Diagnostic Tools to Diagnose Intermittent Database Hangs
- Performance Tuning Guide for Siebel CRM Application on Oracle
- Oracle Database 11g: Change Management Overview eStudy
- Maximizing Database Application Performance with Flash and Solid State Disks (SSDs)
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.
Please Note This are mandatory patches.
8712466 on top of 11.1.0.7.0
Enable workload capture by following instructions below
Functionality already exists in Patchset, download from Metalink
Enable workload capture by following instructions below
8712466 is a merge patch on top of 11.1.0.7.0
Enable workload capture by following instructions below
Enable workload capture by following instructions below
No mandatory patches needed.
Functionality already exists in Patchset.
Functionality already exists in Patchset.
Table 2: Database Replay availability information Windows platform:
Please Note This are mandatory patches.
>=11.1.0.6.0
including 11.2.X
>=11.1.0.6.0
including 11.2.X
>=11.1.0.6.0
including 11.2.X
>=11.1.0.6.0
including 11.2.X
For Windows 64-bit AMD64 AND Intel EM64T XP AND 2003
>=11.1.0.6.0
including 11.2.X
(Windows 32-bit, Windows Itanium Windows 64-bit AMD64 AND Intel EM64T XP AND 2003)
>= 11.1.0.6.0
including 11.2.X
Functionality already exists in Patchset, download from Metalink.
One-off patch 8542772 needs to be requested.
Enable workload capture by following instructions
>=11.1.0.6.0
including 11.2.X
Functionality already exists in Patchset.
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.
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:
Please Note This are mandatory patches.
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.2 + one-off patch 6903322
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.3 + one-off patch 6903335
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.4 + one-off patch: 6893073
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.5 wit RAT option.
11g test database does not need to have application schema /data
Test DB not required, 11.1.0.6 is the destination db upgraded to
Test DB not required, 11.1.0.7 is the destination db upgraded to
Test DB not required, 11.2.0.1 is the destination db upgraded to
Test DB not required, 11.2.0.2 is the destination db upgraded to
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.2 + one-off patch: 6903322
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
.
AND
ii) Destination DB 10.2.0.3 + one-off patch: 6903335
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.4 + one-off patch :6893073
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) Destination DB 10.2.0.5 wit RAT option.
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
ii) 10.2.0.2 + one-off patch: 6903322
AND
iii) 10.2.0.3 + one off patch: 6903335
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) 10.2.0.2 + one-off patch: 6903322
AND
iii) 10.2.0.4 + one-off patch: 6893073
11g test database does not need to have application schema /data
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) 10.2.0.2 + one-off patch: 6903322
AND
iii) 10.2.0.5 with RAT option.
AND
ii) 10.2.0.2 + one-off patch: 6903322
AND
ii) 10.2.0.2 + one-off patch: 6903322
AND
ii) 10.2.0.2 + one-off patch: 6903322
AND
ii) 10.2.0.2 + one-off patch: 6903322
i) 11.1.0.6 + one off patch: 6865809
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) 10.2.0.3 + one off patch 6903335
AND
ii) 10.2.0.4 + one off patch: 6893073
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
ii) 10.2.0.3 + one off patch 6903335
AND
ii) 10.2.0.5 with RAT option.
10.2.0.3 + one off patch 6903335
and
11.1.0.6 db with Real Application Testing Option + one off patch: 6865809
10.2.0.3 + one off patch 6903335
and
11.1.0.7.0 db with Real Application Testing Option + one-off patch:8756594
10.2.0.3 + one off patch 6903335
and
Use 11.2.0.1.0 with Real Application Testing option +8867819
10.2.0.3 + one off patch 6903335
and
Use 11.2.0.2.0 with Real Application Testing option
Test DB not required, 11.2.0.2.0 is the destination db upgraded to.
and
ii) 11.1.0.6 + one-off patch: 6865809
or
Use 11.1.0.7.0 test db with Real Application Testing Option + one-off patch:8756594
or
Use 11.2.0.2.0 test db with Real Application Testing Option.
Using 11.2.0.2.0 is Recommended.
AND
iii) 10.2.0.5 with RAT option.
AND
ii) 10.2.0.4 + one-off patch: 6893073
AND
10.2.0.4 + one-off patch: 6893073
AND
10.2.0.4 + one-off patch: 6893073
AND
10.2.0.4 + one-off patch: 6893073
and
11) 11.1.0.7.0 db with Real Application Testing Option + one-off patch:8756594
and
ii) Use 11.2.0.1.0 with Real Application Testing option +8867819
and
ii)Use 11.2.0.2.0 with Real Application Testing option
and
ii) Use 11.2.0.1.0 with Real Application Testing option +8867819
and
ii) Use 11.2.0.2.0 with Real Application Testing option
and
ii) Use 11.2.0.2.0 with Real Application Testing option
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
Please check Note 879659.1 Real Application Testing Recommended PATCH FOR 11.1.0.7.0