// ]]>

Friday, 7 February 2014

The SQL Trace (ST05)

 SQL Trace

Used to trace execution of SAP program/job to catch performance statistics related to SQL Statement Executions.

you can click "yes" to display all record or no just depend on your situation.
If your program is keeping executing a same set of SQL again and again, 1st 5,000 record might be enough. It takes time and system resource to display all records. In worst cases, ST05 can get timeout/memory error due to huge number of records collected.
Trace records would be depends on Trace period and program design.

2. Trace Results – The Extended Trace List




 I normally use "Summarized SQL Statement" first. If "Summarized SQL Statement" shows a lot of "Identical selection". I might go back to "Trace List" to review "Display Identical select statements".

 Trace List->summarize Trace by SQL Statement
 Trace List->Display Identical Selects



  • The ‘DDIC information’ provides some useful information about the table and has links to further table details and technical settings.
  • The ‘Explain’ shows how the statement was processed by the database, particularly which index was used. More information about ‘Explain’ can be found in the last section.

It would take you directly to the source code after you click " review source code" and help you to understand why the SQL has been executed so many times.
Ex. the SQL might be inside a ABAP loop.

Enqueue trace list
if you double click the statement

RFC trace list
this would help to identify which call is bottleneck of the performance so improvement plan can be worked on.
performance trace->active stack trace (after trace is completed) ->Call stack
Call hierarchy can help you understand the business logic/process related to an operation like SQL execution.
If one SQL in a program has production performance issue due to table access and the program is working fine is testing environment, you might want to check SQL execution plan difference between two boxes.
ST05 has a quick way for you to verify SQL execution plan in different boxes such as testing box and production box.
following is the SQL statement i need to verify-
SELECT "REGIO", "LAND1" FROM "KNA1" WHERE "MANDT"=:A0 AND "KUNNR"=:A1