805934 - FAQ: Database time

Symptom

1. What is meant by database time?

2. What makes up database time?

3. Which components of database time are logged in the Structured Query Language (SQL) trace?

4. How can I analyze the composition of database time?

5. Is the average database time a useful key performance indicator?

Other Terms

Frequently asked questions (FAQ)

Reason and Prerequisites

1. What is meant by database time?

              Database time or database request time is the time that R/3 considers necessary to access the database. The database time plays a role at the following points:

• The statistical records from STAD (formerly: STAT) and ST03N (formerly ST03) contain the database time as a component.

• The ABAP trace from ATRA or SE30 displays the database time in comparison to the ABAP time.

• In transaction SM50 or SM66, if you see work processes accessing the database (for example, "sequential read" or "update"), the database time is running for these processes.

              Only standard SQL commands, such as INSERT, UPDATE, DELETE or SELECT are included in the database time. DDL commands, such as DROP, CREATE or TRUNCATE are ignored. Instead, their times are part of the processing time in STAD or ST03N. The same applies to COMMIT and ROLLBACK that are also not considered part of the database time. As part of the statistical records (transaction STAD), at least the "Commit Time" is displayed, so that you can determine the significance of COMMITs for individual transactions.

2. What makes up database time?

              The assumption that the database time determined in R/3 is the time that is spent in the Oracle database is incorrect. In many cases, the Oracle database is the crucial factor for which time is consumed but there are also other situations in which much more than half the database time is lost outside of the Oracle server. It is therefore important to understand which components contribute to the database time:

• Oracle client: SAP calls functions of the Oracle client software, through which a connection and communication is created with the Oracle server. The time spent by the Oracle client is part of the database time.

• Network: Even the network between SAP and Oracle is included in the database time. This also includes the local communication overhead if SAP and Oracle run on one machine.

• Oracle server: Finally, the time spent by the database server is also part of the database time.

3. Which components of database time are logged in the Structured Query Language (SQL) trace?

              The SQL trace (transaction ST05) logs all components of the database time. EXEC-SQL statements are also recorded.

4. How can I analyze the composition of database time?

              You can only determine the exact details of how the database time is made up if you monitor the transaction concerned at runtime. Afterwards, you can still only make very limited assertions. In general, the following procedure is available:

• In the first step, determine whether the database server is responsible for the majority of the database time by carrying out a wait event and CPU analysis. To do this, you can either use the snapshot method described in Note 619188, or the ORADEBUG tool described in Note 613872. Generally, the database server is responsible for the majority of the database time, meaning that you can carry out an optimization in accordance with Note 619188.

• If the time consumed on the database server is significantly lower than the database time logged in R/3, the other components of the database time (Network, Oracle client) must be analyzed in more detail. This is a substantial task. However, you can use the following options to help you:

• To exclude problems in the network area between SAP and Oracle, check the network (Note 1100926).

• Resource bottlenecks (CPU, memory) may also cause delays. Therefore, check whether there is a CPU or memory bottleneck on the application server.

• Increased times in the Oracle client can be consumed if a very high number of SQL statements have to be executed. This is the case, for example, if a large number of FOR ALL ENTRIES statements are executed with long value lists and small values for rsdb/max_blocking_factor or rsdb/max_in_blocking_factor (see Note 881083).

                    Under these constraints, you can carry out a test increase of the blocking factors, of up to 100, for example (see, for example, Note 556764). If this significantly improves the performance of an SAP standard transaction, contact SAP for a further analysis, since increasing the parameters may lead to disadvantages in other areas. If the SQL statement in question is a customer-specific development, you can also assign a DBI hint to locally adjust the blocking factors for the statement (see Note 48230).

• In some cases, it is useful to analyze the CPU consumption of the R/3 work process. To do this, you can use the CPU button in transaction SM50 or SM66. When you run the transaction concerned, compare the degree to which the CPU time in transaction SM50/SM66 increases in relation to real time. The proportion of time during which CPU is consumed cannot be lost in the network.

• The fact that the total of the times in STAD is greater than the response time is also evidence of increased client activities. This constellation is triggered by the CPU time of the Oracle client being assigned simultaneously to the database time and the CPU time.

• An Oracle client trace in accordance with Note 562403 may help to analyze Oracle client activities.

• You can use operating system dependent tools such as TRUSS, TUSC or STRACE to create operating system call traces which contain information about time consumption within operating system calls. For further details, contact your operating system partner.

• You can use the SIGUSR1 and SIGUSR2 signals to take a current stack trace of the R/3 work process (Note 659856). If you take several inspection samples, you can largely identify in which routine the process spends most time.

5. Is the average database time a useful key performance indicator?

              The average database time is affected by many factors that are irrelevant to the efficient running of the core business processes. Up until SAP Kernel 6.20, for example, a large number of internal ADM messages may be included when the average database time is calculated, which significantly lowers the average. As of 6.40, this effect no longer exists, which is why the database time apparently increases. In reality, however, the database performance of the business transactions has not changed at all.
              In this respect, the average database time should not be defined as a key performance indicator (KPI). Instead, you should examine the individual database times for the most important transactions of the business process.

Header Data

Released On	13.02.2012 07:23:27
Release Status	Released for Customer
Component	BC-DB-ORA Oracle
Priority	Recommendations / Additional Info
Category	FAQ