HowTo - SAP MaxDB Performance
This section should help you to start a performance analysis of a SAP MaxDB OLTP-System. Here you will find all checks/information which have to be executed/collected to optimize the SAP MaxDB database.
Especially after a heterogeneous system copy of an SAP MaxDB OLTP-System with R3load these steps have to be checked. Performance problems based on incorrect administration/configuration will be found after you executed all the steps below. See also information in Tuning SAP MaxDB, Performance Analysis Tools and SAP note 819641 and 1173395.
The following list should be checked step by step.
General Checks and Information
- Volume Configuration
- Memory Sizing
- Recommended SAP MaxDB Version
- Check Correctness of Database Parameters
- Update Statistics
- Additional Checks
Tools for Performance Analysis
- General Performance Problems in a SAP System
- Poor Performance in the MaxDB Database System
- Poor Performance when executing Individual SQL Statements
The volume configuration of a SAP MaxDB database can have impact to the database response time.
Please check the volume configuration (data and log) and compare customer's configuration with SAP MaxDB recommendation Volume Configuration.
Of course if customer's configuration is different from SAP's recommendation it is not urgently neccessary and possible to change volume configuration in short term. But it should be discussed with the customer.
Correct volume configuration is important if database analyzer loggs bottlenecks during volume accesses (check I/O time for read and write).
The sizing of the memory areas of the database is influenced of several factors. Use SAP MaxDB Memory Sizing to compare customer's memory configuration with the SAP MaxDB recommendations.
Notice customer's current memory configuration:
- available address space (32 bit or 64 bit)
- Estimate the memory requirements of all components running on the database server
- database parameter CacheMemorySize (CACHE_SIZE)
If customer's configuration (CacheMemorySize) is smaller than SAPs recommendation and the machine resources allow changes you should discuss possible changes with the customer. The recommendation should be followed as soon as possible.
Recommended SAP MaxDB Version
There is no general recommendation which SAP MaxDB version should be used. It depends on the available database version which is approved for customer's version of product suit he is currently using.
- To find out which database version is avialble for customer's system use Product Availability Matrix (PAM) in SWDC.
- To check which database version the customer uses use XCONS: x_cons <database_name> show version or check version in DBA Cockpit (transaction DBACOCKPIT) or database assistant (transaction DB50).
Upgrade of a major database version can not be executed in short term. Therefore you should only recommend this upgrade if the used database version is out of maintenance.
For OLTP systems use the following table to check:
SAP MaxDB Version
out of maintenance use note 422058
out of maintenance, should only be used on 32 bit hardware
out of maintenance use note 832863
end-of-support dates described in note 1178367
end-of-support dates described in note 1178367
Of course you should recommend to use the current patch level of the used database version.
For SAP MaxDB BW systems you will find all information using the following link: SAP MaxDB BW Feature Pack
Important to Know
Take care: SAP MaxDB prefetch mechanims is not availble in database version 7.7 < SP 07.
If a customer uses database version 7.6 and activated SAP MaxDB prefetch mechanism (READAHEAD_TABLE_THRESHOLD>0) he will loose this functionality when he upgrades the database to a database version 7.7 < SP 07.
Check Correctness of Database Parameters
You should ask the customer for implementing note 1111426 (Database parameter check for SAP MaxDB/liveCache). After this note - including the current database analyzer config file - has been implemented the database analyzer checks the correct database parameter settings and list the recommendation in the database analyzer log file.
Compare this output with the database parameter recommendations of the notes and discuss possible changes with the customer.
SAP MaxDB Version < 7.7.07.20: Assure that note 808060 "Changing the estimated values for Update Statistics" has been installed in the database system.
SAP MaxDB Version >= 7.7.07.20: Assure that note 1466524 "MaxDB Sample Sizes for Update Statistics" has been installed in the database system.
- Check database parameter value UPDATESTAT_SAMPLE_ALGO = 1
- SAP MaxDB Version < 7.7.07.20 only: Run the Report ZZ_SET_SAMPLE_SIZES with 5% and mark Verbose to check if all SAMPLE sizes are set correctly. If not ask the customer for running the report with 'adjust' when there is low activity on the system.
- Check when the last UPD_COND or UPD_UNCOND has been executed in DBA planning calendar (transaction DB13).
Notice: UPD_UNCOND is strongly recommended after following actions (when there is low activity on the system):
- a system copy via R3load
- a system copy via LOADERCLI
- a major release upgrade
- Check database analyzer log file if the database analyzer recommends an Update Statistics.
The time measurement has to be explicitly activated in all database versions < 7.7 when you start a general performance analysis. As of SAP MaxDB version 7.7 the time measurement is implicitly activated.
Ask the customer for switch on the time measurement. There are different possibilities to switch on the time measurement:
- use DBA Cockpit (transaction DBACOCKPIT) or database assistant (transaction DB50): Kernel Threads -> Task Manager -> activate time measurement
- use database console program XCONS and execute the following command: x_cons <database_name> time enable
If the time measurement is deactivated during the performance analysis you don't get any information about I/O times etc. in database analyser expert analysis.
After you finished your performance analysis the time measurement in SAP MaxDB versions < 7.7 has to be switched off again.
There are different possibilities to switch off the time measurement:
- use DBA Cockpit (transaction DBACOCKPIT) or database assistant (transaction DB50): Kernel Threads -> Task Manager -> deactivate time measurement
- use database console program XCONS and execute the following command: x_cons <database:name> time disable
See also: Task Manager
The Database Analyzer is used to analyze performance bottlenecks in the database, when e.g. you don't know special transactions which have bad response time. By default the database analyzer creates every 15 minutes a snapshot. During the database analysis you should change this default to 60 or 120 seconds.
Command and Resource Monitor
The Command Monitor enables you to target long-running SQL statements.
You are using the command monitor in the following situations:
- you have some special transactions with bad response time
- the database analyzer tells you that you have high number of rows read and bad data cache hit rate
- the database analyzer tells you a lot of scans (table or index).
For SQL statements which are logged in the command monitor you can execute the EXPLAIN and decide if an additional index will solve the performance issue.
This tool is intended for short analyses, since it records a limited number of SQL statements. Do not activate the command monitor for several days or weeks when there is nobody available who analysis the output of the command monitor.
See also: Command Monitor, Command Monitor (Overview), SAP note 1603165 (FAQ)
By analyzing the resource consumption, you can identify the most costly SQL statements with the Resource Monitor. The resources used by an SQL statement are measured (runtime and I/O accesses, for example).
You should always use the resource monitor in combination with the command monitor.
See also: Resource Monitor, Resource Monitor (Overview)
SQL-Trace Transaction ST05
When the customer is able to reproduce the performance problem of a special transaction the SQL-Trace transaction ST05 can be used to create an SQL trace for a special user. You are able to do a SQL analysis with the output.
See also: Traces of the SAP System
Expert-Analysis with Database Console (XCONS)
The SAP MaxDB console program XCONS can be used to monitor current operations on the database.
The database console program is an expert tool which is e.g. used when you need to have snapshots in intervals < 60 seconds.