[Oracle] Real Application Testing with SAP

Introduction

Oracle introduced a feature called “Real Application Testing” with its newest database version Oracle 11gR1. Real Application Testing is an extra option for the Oracle 11g Enterprise edition.

The Real Application Testing feature can be split in two main components:

Database ReplayYou can capture the whole workload (SELECTs, INSERTs, UPDATEs, etc.) on an Oracle database and replay it on another or on the same database again. The limitation of this feature is that you can only replay the captured workload on an Oracle 11g database.

SQL Performance Analyzer (SPA)You can capture SELECT statements on an Oracle 9i, 10g or 11g database and replay them remotely on an Oracle 10g or 11g database via a “SPA system” or replay them locally on an Oracle 11g database.

So we can only use the SQL Performance Analyzer in a SAP environment, because of Oracle 11g is not supported until yet.

What is the benefit of the SQL Performance Analyzer in a SAP environment?

Evaluate the impact of a patchset installation or database upgrade on the most used transactions in your SAP system (only SELECTs)

Try different tuning trials and compare them very easily

Capture SELECT statements in your productive environment and replay them in your quality system to reproduce performance problems

Check and compare the execution plans without running the SQL statements

Requirements for SPA

Metalinknote #560977.1 includes all required patches and useful links for the Real application testing feature.

In a SAP environment you will mostly need these ones:

Oracle 10.2.0.2 + Patch 6903322

Oracle 10.2.0.4 (includes needed patches)

Oracle 11.1.0.6 + Patch 6865809

Oracle 11.1.0.7 (includes needed patches)

Test environment and how it will work

In my test environment i want to show you how you can compare tuning trials, so the source and the target system is the same SAP system.

Source system is an Oracle 10.2.0.2 + Patch 6903322, SAP ERP 2005

Target system is an Oracle 10.2.0.2 + Patch 6903322, SAP ERP 2005

SPA system is an Oracle 11.1.0.7

The following graphic illustrates the different steps, that are needed get a performance comparison via the SPA system.A detailed step-by-step guide with the corresponding numbers follows below.

To create a simple test example i will execute the transaction SE16 and select all data from MARA where ERSDA is between 24.09.2004 and 24.09.2006 with max 200 rows.

4) Import the staging table into the SPA system and extract the STS out of the staging tableshell> imp system/<pass> FILE=STAGING_TABLE.dmp FULL=Yshell> sqlplus system/<Pass>SQL> exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(‘%’,’SYSTEM’,FALSE,’STAGING_TABLE’,’SYSTEM’);

After that the STS from the source database should be visible in the database control of the SPA database.Navigate to Performance -> SQL Tuning Sets

With the Oracle 11g database control you have the possibility to delete unwanted SQL statements from the STS – so i delete all statements except the SELECT on MARA.

5) Create a PUBLIC database link on the SPA to your target system

6) Replay the statements remotely on the target system and measure (first capture)Open the oracle database control on your SPA and navigate to Performance -> SQLPerformance Analyzer -> Guided Workflow

8) Replay the statements remotely on the target system and measure again (second capture)

9) Compare the measured values between the first and the second replayThe first part of the report is an overview of the SQL statements and SQL execution plans.

If you click on the SQLs you can go a way deeper to see what have improved and why

Problems in a SAP environment with the SPA

ORA-04060: insufficient privileges to execute SYS.DBMS_SQLPA (remote)The user that you use for the database link to your SAP system needs the following additional privileges EXECUTE ON DBMS_SQLPA and ADVISOR

ORA-00904: : invalid identifier (SQL statistics and/or execution plans are not visible in the report)Oracle describes this error in the metalinknotes #605317.1 and #235530.1. This problem occurs in a SAP environment, because of SAP is manually creating the PLAN_TABLE in the SAP schema and is not using the sql script catplan.sql. If you drop the PLAN_TABLE in the SAP schema and use the script catplan.sql it will only work temporarily, because of SAP recreates the PLAN_TABLE with a DDL statement every time when you use ST05 (for example). You can fix this issue by applying the sapnote #1303908.

Summary

In my opinion the Real Application Testing feature is a really great extension for tuning SQL statements or whole oracle databases.Unfortunately database replay can not be used for SAP systems until yet, but Oracle 11g will be certified by SAP in the future – so you can use the whole package. Keep in mind that you will need an Oracle 11g Enterprise Edition + Option “Real Application Testing”.

If you have some questions or need some help, please feel free to contact me.