Learn how to use transaction st05 to trace a program/transaction or just transaction or you just to know what is this a process executing at database level
SQL trace(ST05) provides the developer the ability to analyse database select statements.
This is transaction st05 main screen
You cannot run two st05 at the same time. To be able to start st05 you should see "all traces are switched off"
We are going to expalain how to use st05 with a practical example. Imagine that you are having performance problems with ZM08 transaction or you just want to know what is this transaction executing at database level
HOW TO START/ACTIVATE ST05 TRANSACTION
To start/activate st05 trace we have to choose "select trace" that is the type of trace based on our perform requirements. Most of times we only use "SQL Trace" . Then we click "Activate Trace with Filter" button
This new window will appear
we can choose between three principal filters: "User Name", "Transaction Name" or "Program Name"
It would be possible to also filter by work process number or by table name to trace all the sentences executed against a single or group of tables.
Most of times a combination of user name and transaction name or program name is used.
Once we have choosed the required filters we confirming with the green tick (red circle in the screeshot). We will see then this screen where we have the date and time our trace first started (17.07.2015 09:40:06)
HOW TO STOP/DEACTIVATE ST05 TRANSACTION
Once transaction ZM08 has finished
we deactivate the trace
And then we display the trace
By default it will show you the last scheduled trace timing in "Trace Period". Anyways if we are doing several traces in a row we can choose the date/time to display
We can also change the "Trace Type" and "Restrictions". To continue we click the green tickon left top.
If the number of records on our trace is more thatn 5000 rows a new prompt window will show up asking if you want to continue : YES
We will have something like this with all the statements executed ordered by the time sequence
We can order the output by "Runtime" column to see the expensier statements of this transaction.
OTHER ST05 utilities
1) If you want to save the trace info / delete a saved trace or display it
to save the trace used for the example
2) Top menu options for each statement
In our test
The query plan
If we want to find out what was the execution plan when this query was executed somethimes its possible to find it in st04> performance> History > SQL Statement History and search the date and time of this executions
3) Different options to display the info
Summarize Trace by SQL Statement : Combines the times for all the executions of a SQL statement . This is usually one of the best options to start our analysis.
Display Identical Selects: Combines the times for all the executions of a SQL statement with the same values ( e.g. values in where clause)
Please provide any feedback or questions regarding the content under the current page comments. If you have an issue or is looking for help, try to post your question on SAP community with Microsoft SQL Server tag.
In order to leave comments, you must be logged on with your SAP account.