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 Replay
You 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.
1) Capture the workload from the automatic workload repository (AWR)
shell> sqlplus / as sysdba
SQL> exec dbms_workload_repository.create_snapshot();
SQL> exec dbms_workload_repository.create_snapshot();
2) Create a sql tuning set (STS) with the needed SQL statements
Open the oracle database control and navigate to Performance -> SQL Tuning Sets
Create a STS named SE16_TEST_MARA from the last two snapshots
3) Create a staging table, insert the previous created STS into it and export the staging table with oracle utilities
shell> sqlplus system/<pass>
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('STAGING_TABLE','SYSTEM','SYSTEM');
SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('SE16_TEST_MARA','SYSTEM','STAGING_TABLE','SYSTEM');
shell> exp system/<pass> FILE=STAGING_TABLE.dmp TABLES=STAGING_TABLE
4) Import the staging table into the SPA system and extract the STS out of the staging table
shell> imp system/<pass> FILE=STAGING_TABLE.dmp FULL=Y
shell> 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
7) Make your changes on the target system
shell> sqlplus SAPSR3/<pass>
SQL> drop index "MARA~O";
😎 Replay the statements remotely on the target system and measure again (second capture)
9) Compare the measured values between the first and the second replay
The 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-19114: XPST0003 - error during parsing the XQuery expression
The remote execution feature of the SPA requires "JServer Java Virtual Machine" and "Oracle XDK". Install JVM and XDK on your Oracle 10g SAP database. For more information about installing JVM and XDK, please read the metalinknotes #435999.1 and #276554.1
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.
References
- SAP Managed Tags:
