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.
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.
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.
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.
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.