Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

SAP® MaxDB - FAQ Performance

 This section answers questions about global SAP MaxDB performance issues.

Moderators: Christiane Hienger

WIKI Space Editor: Thiago Lüttig.

 

 

Details

Access Performance Data

Database Level

All performance-relevant data is stored in SAP MaxDB system tables, known as monitoring tables. There is more information about the monitoring tables in the MaxDB documentation:

Use the Database Analyzer to access the performance data. The Database Analyzer tries to write an informative message into the log files to allow you to identify the cause of the problem, and to remove it. If required, you can carry out a detailed analysis by using the Expert Analysis of the Database Analyzer.

In addition, you can use the 'x_cons' database consol tool for interpreting general performance problems during a detailed analysis. x_cons output is also considered in the Database Analyzer and is issued in the log file in a form that is generally intelligible.

Database Console Commands in SAP MaxDB Version 7.9 Database Console Commands in SAP MaxDB Version 7.8 Database Console Commands in SAP MaxDB Version 7.7 Database Console commands in SAP MaxDB Version 7.6

back to top

Via the SAP System

You can access the Database Analyzer log by going to Transaction DBACockpit  -> Performance -> Database Analyzer.

You can access Transaction DBACockpit -> Performance -> Kernel Threads or if you want to use the database console (x_cons) in transaction DBACockpit by going to Diagnostics -> Database Console.

You can use the command monitor and ressource monitor if you need to look in the special SQL statements to find the cause of the performance problems. To do this, see notes 819324 and 725489.

back to top

How can I determine whether the general database performance can be optimized?

You can use Transaction ST03 or ST03N to determine how much of your system's overall response time is taken up with accessing the MaxDB database. This portion is determined by using the "Database time" column and should not exceed 40% of the overall response time.

If in addition you see Warning 3-type messages in the Database Analyzer log - especially poor data cache hit rates - then you should start a database analysis.

back to top

How can I tell how much time a particular transaction spends on the database and on which activity?

You can derive the database time of a transaction step from the respective statistical data record in Transaction STAD (formerly: STAT).
Note that for extremely long-running programs, there overflows may occur in the components of database time (for values > 2^32 microseconds = 71 minutes), and the subsequent time is wrongly recorded as processing time.
To determine which table accesses are mainly responsible for the database response time, you can set the stat/tabrec R/3 parameter (see Note 579462) to a value greater than 0 (-> number of tables to be logged).
If this parameter is used in connection with background jobs, the additional statistical data is not stored in the statistical record of the actual job, but in a statistical record of the RSBTCRTE report logged at the time.

back to top

Transaction SM50/SM66

The first step should be to determine the database task (and possibly the database session as well) that belongs to the work process. To do this, note the PID from SM50 or SM66 and search in  Transaction DBACockpit -> Performance -> Kernel Threads -> Task Manager

First, find your application PID under Active Tasks. Under ID, you are given the database task and under the session ID column, the database session connected to the work process.        

The Task State column shows you how the database task of your application is currently occupied, and can provide the first indicators for the cause of the performance problems.               In the MaxDB documentation (Note: 767598), you can branch directly to the description of the task states by going to the glossary and following the term task states.    

For the detail analysis, you can then use the Database Analyzer and the command monitor. Note that the command monitor must first be activated before you can send an SQL statement to the database for it to be logged in the command monitor. 

If you cannot find your application PID in this list, then your application is currently not active in the database. It could be waiting for database resources. You should therefore look for your application PID under 'Executable tasks'. If it is there, your application is if waiting for a database resource.  The Task State column provides further information about the reason for the queue. In the MaxDB documentation (Note: 767598), you can search in the glossary for the term task states and branch directly to the description of the task states and find out the cause of the wait situation. The output of the database console can also help you in this case to analyze the cause of the resource problem. For information about the database console, see the MaxDB documentation (Note 767598767598@SapNote). 

However, if you cannot find your application PID under executable tasks either, then the application is not active in the database at all. You can see whether it still has any connection to the database by looking under User Tasks.  Here too, you need to check the status of the corresponding database task. If the task belonging to your application PID is in the COMMAND WAIT status, then the application has not sent an SQL statement to the database. This means that the performance problems are not to do with the database.                                                  

If you cannot find your application PID in this overview either, then the application is no longer connected to the database. The cause of the problem is not the database. The database connection could have been terminated due to network problems. For information about the causes of this, see the Developer Trace of the work process in the Appl-Diag or in the x_server log. 

back to top

Prerequisites for a detailed Performance Analysis

To carry out a global performance analysis, the system needs to be balanced. To this end, the reads in the data cache (see Transaction DBACockpit) should have reached at least 20,000 000. Otherwise, statements concerning the buffer quality must be limited.

The prerequisite for starting a performance analysis is that the database monitoring is active. This database monitoring is started by default when you start the database. You can check the status of the database monitoring by using Transaction DBACockpit Current Status -> System information. The MONITORING status information must be set to 'on'. Make sure that you have started the Database Analyzer. The Database Analyzer is started implictely if an application server is started. You can start the Database Analyzer manually by using Transaction DBACockpit -> Performance -> Database Analyzer -> Bottlenecks. The Database Analyzer collects all data relevant to performance in an interval of 15 minutes by default and stores it in special files. You can display the output of the Database Analyzer directly in the Bottleneck Analysis. For a detailed analysis, the Expert Analysis is also available.

back to top

Transaction DBACockpit

In Transaction DBACockpit (DB50), the time units for time specifications are generally specified in the menu.

back to top

Command Monitor

In the command monitor, the measurement values are given in seconds, although you should note that you need to specify milliseconds for the monitoring settings.

back to top

Resource Monitor

In the resource monitor, the performance data is always specified in seconds.

back to top

Database Analyzer

In the Database Analyzer, all time specifications are in milliseconds.

back to top

x_cons

In the database console (x_cons), all time specifications are in milliseconds.

back to top

Transaction ST05

In the SQL trace (Transaction ST05) the time specifications are in microseconds.

back to top