Importance of SQL Trace (ST05)

An Importance of SQL Trace (ST05)

SQLTrace - which is part of the Performance Trace [transaction ST05] is the most important tool to test the performance of the database. Information on how to use the SQL Trace & especially how to interpret its results is not a part of the standard ABAP courses. We try to give you a quick introduction to the SQL Trace. This shows how we to execute a trace, which is very straightforward. It tells how we can get a very condensed overview of the results the SQL statements summary , a feature that many are not so familiar with. So the usefulness of this list becomes obvious when the results are interpreted. To short discussion of the "database explain" concludes this introduction to the SQL Trace.

A.Using the SQL Trace

To Use the SQL trace is very straight forward :

1. To Call the SQL trace in a second mode
2. To Make sure that our test program was executed at least once, or even better, a few times, to fill the buffers & caches. So Only a repeated execution provides reproducible trace results. The Initial costs are neglected in the examination
3. Lets Start the trace
4. Lets Execute your test program in the first mode
5. Switch off the trace. Notes: that only one SQL trace can be active on an application server, so always switch your trace off immediately after your are finished.
6. Lets Display the trace results
7. Interpretation of the results.

Note1 : The trace can also be switched on for a different user.

=>So in this section we showed how the SQL trace is executed and execution is very straight forward & can be performed with out any prior knowledge. To interpret of the results, however, requires some experience. To know more on the interpretation will come in the next section.

2.Trace Results - The Extended Trace List

Trace result is displayed then the extended trace list comes up. In list shows all executed statements in the order of execution [ as extended list it includes also the time stamp ]. So one execution of a statement can result in several lines, one REOPEN & one or several FETCHES. we need to note that we also have PREPARE & OPEN lines, but we shouldn't see them, because we only need to analyze traces of repeated executions. if we see a PREPARE line, then it is better to repeat the measurement, because an initial execution has also other effects, which make an analysis difficult.

We want to take the quick & easy approach, the extended trace list is much too detailed. In order to get a good overview we want to see all executions of the same statement aggregated into one line. So such a list is available & can be called by the menu "Trace List->Summary by SQL Statements".

=>Extended trace list is the default result of the SQL Trace and this shows a lot of & very detailed information. A overview it is much more convenient to view an aggregated list of the trace results. It is the Summarized SQL Statements explained in the next section.

3.Trace Results - Summarized SQL Statements

A list contains all the information we need for most performance tuning tasks.

 A key of the list are ‘Obj Name’ , i.e,. table name & ‘SQL Statement’. So when using the summarized list, keep the following points in mind :

• a).Several coding positions can relate to the same statement .
• b).Statement shown can differ from it's Open SQL formulation in ABAP.
• c).Displayed length of the field "Statement" is restricted, but some times the displayed text is identical.
• d).So in this case, the statements differ in part that is not displayed.

Each line has 4 functions are possible :

• a).Magnifying glass shows the statement details, these are the actual values that are used in the execution. So in the summary the values of the last execution were displayed as an example.
• b).DDIC information provides some useful information about the table & has links to further table detail &  technical setting.
• c).Explain shows how the statement was processed by the database, particularly which index was used. So more information about ‘Explain’ can be found in the last section.
• d).Link to the source code shows where the statement comes from & how it looks in OPEN SQL.

=>sStatement summary which was introduced here, will turn out to be a powerful tool for the performance analysiss. This contains all information we need in a very condensed form. So the next section explains what checks should be done.

4.Checks on the SQL Statements

Each line the following 5 columns should be checked, as tuning potential can be reduced from the information they contain. So Select statements & changing database statements, i.e., inserts, deletes & updates, can behave differently, therefore also the conclusions are different.

Select statements please check the following :

• a).The Entry in ‘BfTy’ = Why is the buffer not used?
  All Tables which are buffered, i.e., with entries "ful" for fully buffered, "gen" for buffered by generic region & "sgl" for single record buffer, should not appear in the SQL Trace, because we should use the table buffer. Therefore, we must check why the buffer was not used. Reasons are that the statement by passes the buffer or that the table was in the buffer during the execution of the program. The tables that are not buffered, but could be buffered, i.e., with entries starting with ‘de’ for de-activated [‘deful’, ‘degen’, ‘desgl’ or ;deact’] or the entry ‘cust’ for customizing table, check whether the buffering couldn't be switched on.
• b).The Entry in ‘Identical’ = Superfluous identical executions
  Column shows the identical overhead as a percentage. The Identical means that not only the statement, but also the values are identical. So overhead expresses that from 2 identical executions one is necessary & the other is superfluous and could be saved.
• c).The Entry in ‘MinTime/R’ larger than 10.000 = Slow processing of statement
  The Index supported read from the database should need around 1.000 micro seconds or even less per record.Value of 10.000 micro seconds or even more is a good indication that there is a problem with the execution of that statement. So such statements should be analyzed in detail using the database explain, which is explained in the last section.
• d).The Entry in ‘Records’ equal zero = No record found
  It's problem is usually completely ignored "no record found" should be examined. Lets first, check whether the table should actually contain the record &  whether the customizing &  set up of the system is not correct. So sometimes "No record found" is expected &  used to determine program logic or to check whether keys are still available, etc. So these cases only a few calls should be necessary & identical executions should absolutely not appear.
• e).The High entries in ‘Executions’ or ‘Records’ = Really necessary?
  The High numbers should be checked. This especially in the case of records, a high number here can mean that too many records are read.

To change statements, errors are fortunately much rarer. It however, if they occur then they are often more serious :

• a).Entry in ‘BfTy’ = Why is a buffered table changed?
  Changing statements are executed on a buffered statement, it is questionable whether it's table is really suitable for buffering. So in this case of buffered tables, i.e., entries "ful", "gen" or "sgl’’, it might be better to switch off the buffering. So in this case of buffer-able tables, the deactivation seems to be correct.
• b).Entry in ‘Identical’ = Identical changes must be avoided
  The Identical executions of changing statements should definitely be avoided.
• c).Entry in ‘MinTime/R’ larger than 20.000 = Changes can take longer
  The Same argument as above just the limit is higher for changing statements.
• d).Entry in ‘Records’ equal zero = A change with no effect
  There are changes that should also have an effect on the database, so this is usually a real error which should be checked. So ABAP modify statement is realized on the database as an update followed by an insert if the record was not found and this case one statement out of the group should have an effect.
• e).High entries in ‘Executions’ and ‘Records’ = Really necessary?
  The Same problems as discussed above, but in this case even more serious.

=>This section we explained detailed checkon the statements of the SQL Statemnt Summary. So checks are slightly different for selecting & changing statements. So they address questions such as why a statement doesn't use the table buffer, why statements are executed identically, whether the processing is slow, why a statement was executed but no record was selected or changed, & whether a statement is executed too often or selects too many records.

5.Understanding the Database Explain -

"Database explain" should show the SQL statement as it goes to the database & the execution plan on the database. it's view has a different layout for the different database platforms supported by SAP & it can become quite complicated if the statement is complicated.

This section you show us an example the "Explain" for a rather simple index supported table access, which is one of the most common table accesses :

1. Database start with step1, So Index unique scan DD02L~0, where the three fields of the where condition are used to find a record on the index DD02L~0 [‘~0’ denotes always the primary key].
2. So in step2, table access by index rowed DD02L, the rowid is taken from the index to access the record in the table directly.

The databases display the execution plan in a graphical layout, where a double click on the table gives additional information, as shown on the right side. The date of the last statistic update & the number of records in the table are displayed. All indexes are listed with their fields & the number of distinct values for each field and with this information it is possible to calculate the selectivity of an index.

This example we should understand the principle of the "Explain" so that we can also understand more complicated execution plans. So database platforms don't use graphical layouts & are a bit harder to read, but still show all the relevant information.

=>This last section we showed an example of a database explain, which is the only way to find out whether a statement use an index & if so, which index. Specially in the case of a join, it is the proper index support that determines whether a statement needs fractions of seconds or even minutes to be finished.