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 tool XCONS SAP MaxDB Database Console (XCONS) for interpreting general performance problems during a detailed analysis. XCONS output is also considered in the database analyzer and is issued in the log file in a form that is generally intelligible.
You can access the database analyzer log using DBA Cockpit (transaction DBACOCKPIT) -> Performance -> Database Analyzer.
You can access via DBA Cockpit (transaction DBACOCKPIT) -> Performance -> Kernel Threads or if you want to use the XCONS via DBA Cockpit (transaction DBACOCKPIT) -> 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. See also: Command Monitor, Resource Monitor, SAP notes 819324 and 725489.
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 database. This portion is determined by using the column Database time 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 SAP 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 transaction SM50 or SM66 and search in DBA Cockpit (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 column Task Stateshows 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 SAP MaxDB documentation (SAP 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 column Task State provides further information about the reason for the queue. In the SAP MaxDB documentation (SAP 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.
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 DBA Cockpit (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 DBA Cockpit (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.