An Oracle White Paper March Real Application Testing Certified for SAP - PDF

Please download to get full document.

View again

of 9
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report



Views: 4 | Pages: 9

Extension: PDF | Download: 0

Related documents
An Oracle White Paper March 2010 Real Application Testing Certified for SAP Real Application Testing Certified for SAP Making a change to the SAP database can represent a significant risk to an organisation.
An Oracle White Paper March 2010 Real Application Testing Certified for SAP Real Application Testing Certified for SAP Making a change to the SAP database can represent a significant risk to an organisation. The effects of an unexpected problem could be widely felt and have serious consequences. For this reason, testing of changes prior to production deployment is considered critical. The cost involved in testing using traditional methods can be very high both in time and money. Even after testing, many problems will only be discovered in production, and as a result end-users will suffer from poor performance or possibly even outages. Oracle now has a new and unique testing tool for the Oracle Database called Real Application Testing. This Database option comprises of two complementary features, SQL Performance Analyzer (SPA) and Database Replay. Together, these features can help ensure that database changes do not negatively impact SAP availability and performance. They significantly improve the quality and lower the cost of testing changes to the SAP database system. 1 SQL Performance Analyzer (SPA) One of the key tasks for a DBA is to tune SQL statements for optimal performance. Changes to database systems (such as optimizer statistics refresh, database upgrades, and patches) can cause the performance of previously well tuned SQL statements to regress, which can dramatically impact system performance for end-users. Until now, it was difficult if not impossible to predict how day to day operational changes to the database will impact application SQL performance. With SQL Performance Analyzer, or SPA, you can now know exactly how SQL will behave after routine system changes. SPA automatically captures production SQL statements from your SAP database and re-executes them following a change to measure the impact. The statements are executed serially, in isolation from each other and regressed statements are automatically highlighted. Integration with SQL Plan Management and SQL Tuning Advisor enables automatic tuning of these statements to address any regressions prior to a change being implemented in production. Orchestration of the entire SPA process can be done using Enterprise Manager, which provides a number of workflows for testing common operations such as Database Upgrades. There is also a Guided Workflow which enables custom experiments to be conducted. Real Application Testing was introduced in the 11g release of the Oracle Database, however, SQL Performance Analyzer is also available for previous releases of the Oracle Database. Consequently, SQL Performance Analyzer can be used to test changes in SAP environments even if there are no immediate plans to move to Oracle Database 11g Release 2. 2 Some examples of changes that could be assessed using SQL Performance Analyzer in SAP environments are: Upgrade SAP Database from Oracle Database 10g Release 2 to 11g Release 2 Install Database Patchset Migration from single instance to RAC Implement Partitioning Implement Advanced Compression Changes to _FIX_CONTROLS parameter settings Creation of Histograms on SAP tables Some of these changes can actually be tested safely on the production system, therefore removing the need for establishing a separate test environment. There are no SAP specific patches required to use SQL Performance Analyzer. Further general information on using SQL Performance Analyzer can be found in the following documents: Technical White Paper: SQL Performance Analyzer Viewlet: SQL Performance Analyzer in Oracle Database 11g Oracle Database Real Application Testing User s Guide 3 Database Replay Load testing today is generally done using tools that allow testing teams to generate synthetic workloads based on what they expect users to do on a system. These workloads can then be replayed by application virtual users, which simulate the end users by submitting requests to the application. Although widely used, this approach has a number of shortcomings when it comes to testing database level changes: Creating the synthetic workload can take a considerable time and requires programming expertise User behaviour is not well understood, so many possible workflows are often missed in the synthetic tests Production scale database concurrency is near impossible to simulate with these tools A full application stack is required for testing as these tools simulate end users Database Replay makes it possible to capture a workload on a production system with negligible performance overhead and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. Database Replay is suitable for testing changes to the database tier and anything below it, such as storage or OS platform. This also means that tiers such as the Web and Application Server tier are not required in the test environment. The Database Replay capture process has been backported and certified for SAP databases running on Oracle Database 10g Release 2 ( ). Changes that Database Replay could be used to validate include: SAP Database upgrade from 10gR2 ( ) to 11gR2 Platform migration such as from Windows to Oracle Solaris Database Patchset install Changes to _FIX_CONTROLS parameter settings Schema changes Migration from single instance to RAC Implement Partitioning Implement Advanced Compression Database storage changes (e.g. Direct NFS) Tablespace encryption Object Reorganization 4 Results of Testing The Oracle/SAP Development Team recently concluded comprehensive testing of SAP applications using both SQL Performance Analyzer and Database Replay. The testing consisted of SAP R/3 and BW workloads running in-house on the ABAP stack. Further testing was undertaken at a large utilities customer running the SAP IS-U (Industry Specific Utilities) module. The objective was to use Real Application Testing to measure the effects of Advanced Compression for OLTP and SecureFiles on DML operations for SAP R/3. The testing activity consisted of using Database Replay to capture a production workload from a SAP database. This workload was then replayed multiple times against both compressed and noncompressed Oracle Database 11g Release 2 databases. Key AWR metrics from the replays are shown below. KEY AWR METRICS REPLAY WITHOUT COMPRESSION REPLAY WITH COMPRESSION %DIFF 5 In order to further analyze the effects of Advanced Compression on DML operations, SQL Performance Analyzer (SPA) was used concurrently with Database Replay. As the workload was being replayed, SPA s automatic cursor cache capture feature was used to capture the SQL statements into a SQL Tuning Set. SQL Tuning Set contains the text of the SQL statements along with their bind values, execution plans and statistics. This was done for both replays, with compression enabled and without compression. In all over 5000 SQL statements were analyzed. The table below shows the SQL Tuning Set comparison of some the most frequently executed DML operations in the SAP workload. SAMPLE SQL TUNING SET COMPARISON Compressio n SQL ID SQL Text Executions N g9makkh1cnq94 INSERT INTO ALCLASTOOL Elapsed Time CPU Time Buffer Gets Disk Reads Module ,29 152, SAPLSAU Y g9makkh1cnq94 INSERT INTO ALCLASTOOL ,54 164, SAPLSAU N 6kxdz6nknay0m INSERT INTO ALGRPCUSGE ,88 71, SAPLSAU Y 6kxdz6nknay0m INSERT INTO ALGRPCUSGE ,12 74, SAPLSAU N c42810chs804v INSERT INTO ALGRPCUSPF ,51 16, SAPLSAU Y c42810chs804v INSERT INTO ALGRPCUSPF ,07 17, SAPLSAU N 8wz2ywwcrh7c7 INSERT INTO ALGRPCUSMC ,48 9, SAPLSAU Y 8wz2ywwcrh7c7 INSERT INTO ALGRPCUSMC ,18 7, SAPLSAU Overall the test results were very instructive. Key findings are described as follows (note: results will vary according to your workload): No significant overhead observed during capture process Advanced Compression reduced database size by 50% Redo generated increased by ~25% Physical reads reduced by 60% CPU usage stayed flat 6 Problems encountered: During the testing bug # was encountered. Patch for this bug must be applied before using Real Application Testing with SAP systems. Conclusion Real Application Testing proved a vital tool for validating upgrade from Oracle Database 10g Release 2 to 11g Release 2. The ability to test with production workloads and SQL statements is essential for testing business critical applications like SAP and this feature will significantly mitigate upgrade and change risk when used by experienced DBAs. 7 Real Application Testing Certified for SAP March 2010 Author: Mark McGill Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. Worldwide Inquiries: Phone: Fax: Copyright 2010, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 0310
View more...
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks

We need your sign to support Project to invent "SMART AND CONTROLLABLE REFLECTIVE BALLOONS" to cover the Sun and Save Our Earth.

More details...

Sign Now!

We are very appreciated for your Prompt Action!