Database administrators are often asked by users to find out why the performance of their SAP system is poor. There are many factors to take into account when tracking down these types of problems.
There are some specific notes for SAP liveCache technology in Performance Analysis in the CCMS: SAP liveCache Technology.
Your research can take a long time and you should therefore make sure you have as much background information as possible before starting.
Has the application/transaction always been slow, or has it only just started to slow down?
Is the problem in a new program or application/transaction?
Is the slowdown only during peak periods or is it fairly constant?
Have there been recent changes in workload?
Is only one application/transaction slow or are other transactions or applications also now performing poorly?
Attempt to localize the performance bottleneck using the information you obtained. If you feel this issue may be isolated to one particular transaction or application, you may also need assistance from the application developers. These developers understand the process flow of the application, and can help you to change and test SQL statements in the program.
Use the Workload Monitor to get an initial overview of the response times of all components in your SAP system. In particular, note the average response time/dialog step (ms) for the dialog task in the workload overview. The average time for the dialog task should be under one second. If this value is critical, you can check the Of response time, database time (%) statistic to find out the proportion of the total response time of the dialog task which was used for database requests. If database requests take up more than 50% of the total response time, this may indicate that a database problem is causing performance bottlenecks.
Carrying out a database performance analysis means that you can check a wide range of settings, access times and hit rates of your database system. If necessary, the applications can then be included in further analyses. As each performance analysis runs individually, the following suggestions are merely a collection of ideas.
A series of CCMS transactions are available for you to help analyze the performance bottlenecks, in particular the DBA Cockpit, the Database or liveCache Assistants and the Database or liveCache Alert Monitor. Experts can also use additional database tools, such as the Database Studio, Database Analyzer, and XCONS. To set up and use your database optimally, you can automate many necessary DBA activities: Automating Database Administration Activities.
The documentation for the SAP MaxDB database system also generally applies to SAP liveCache technology. If there are functional differences, these are explained.
Database monitoring is automatically activated when the database is started so that the statistical data from the last database system start is determined. You can reset the monitoring data, if necessary.
See also: Database Monitoring
To carry out a meaningful performance analysis, this should be a running system. The read actions in the data cache should have reached at least 20,000,000.
See also: Caches
The Database Analyzer must always be activated. When a SAP system or the liveCache starts up, the Database Analyzer is automatically started for the system's basis database. If necessary, you should start the Database Analyzer explicitly.
See also: Database Analyzer
Update the SQL Optimizer statistics regularly. Use performance analyses of individual SQL statements (particularly Join statements) only after the statistics, if any, have been been updated. Schedule regular updates of the SQL Optimizer statistics in the DBA Planning Calendar. Ensure that the scheduling has been successfully executed and thus that the SQL Optimizer statistics are current.
See also: Updating the SQL Optimizer Statistics
Check your database parameter settings. For more information, refer to the explanations of database parameters and SAP Note 814704 . If necessary, make corrections to the database parameter settings.
See also: Parameters
Check the disk access (for example, the read and write operations), especially on the data volumes. High I/O times can point to a heavy system load. Therefore, check the database system activities constantly (running backup, many read actions on the disk since the last database start, among other things).
See also: I/O Operations
Check the hit rates in the data cache and the catalog cache. Database performance can suffer if too much data has to be read from the disks. It is therefore important that most accesses to database tables and other data from the database be covered by data in the caches.
See also: Caches
Check logging activities. Specifically, there should be no log I/O queue overflows.
See also: Activities Overview
You can examine the cache hit rates and other database information in more detail by analyzing the bottlenecks with the Database Analyzer. Bottleneck analysis provides a variety of measurement data covering a lengthy period of time.
See also: Bottlenecks
If necessary, you can also temporarily activate DB time measurement in the Task Manager and thereby get current measurement data on I/O and execution times.
See also: Task Manager
If a performance problem is currently being reported, you can specifically check the user tasks. Activate Automatic Refresh for the Task Manager and observe the user tasks and their task states.. Most user tasks should generally have the status Running. The status I/O Wait should be much rarer; statuses like Vwait, Vbegexlc and Vsuspend should not occur at all.
See also: Task Manager
Display the open database transactions and examine these thoroughly.
See also: Transactions
Determine if exclusive wait situations have occurred. Exclusive locks mean that other users cannot access the locked entry. These locks can significantly reduce the database performance.
See also: SQL Locks: Wait Situations Locks
Use the Command Monitor to perform runtime analyses of any long-running SQL statements.
See also: Command Monitor
Indexes are essential for fast and efficient access to the data of the database. You should therefore make regular checks to determine whether indexes are missing from the database system or are defective. Always recreate a missing index immediately, since it is very important for database system functionality.
See also: Index Analysis
Check the SQL statements that cause the most disk accesses and optimize these statements, if necessary, based on these SQL statements.
SAP Note 819641 (FAQ: MaxDB Performance)
SAP Note 819324 (FAQ: MaxDB SQL Optimization)
Concepts of the Database System, Tasks