Understanding SQL Trace

By Aveek Ghose, IBM India

Performing an SQL Trace:

1. Access transaction ST05. From the initial screen, click the check box for SQL trace, under the trace requests, click Trace on.

2. Run the program containing the SQL query that needs to be analyzed in the SE38 editor.

3. Come back to ST05 screen, under Trace requests, click Trace off.      

4. Click List trace to view the trace of the current statement.

Screen shot of the initial ST05 screen           

After clicking list trace

Basic List Trace

The basic list trace shows the sequence of database operations that are taking place while a query is processed, it includes prepare, fetch, open, reopen, execute. In addition the basic list trace also gives the duration execution for each of the operations with color legend.            

Summarizing the basic trace list (menu path: Goto à Summary)

A basic trace list can be summarized to see the summation of execution times of similar database operations. Use menu path: GotoàSummary. The above screen shot depicts the summed up execution time. The summary option lists the summed up execution time of all the queries that the SQL trace has captured at its runtime, but the identification of a particular query can be done using the PID – Process ID which is displayed as the first column of the above screen shot. For example, 896 is the process ID for one of the SQL statements. 

Summary of a particular trace   

To get the total execution time, specifically for a particular SQL Statement, select all the PID corresponding to it and click summarize in the application toolbar. This leads to a compressed data of SQL trace as above. 

Color legends indicating the processes in a trace 1

Click here to continue...