How to use Sql Tracer

[Former Member]

Hi ,

How to use SQL Tracer..(ST05).

Thanks,

Subbu

[Former Member]

You should use ST05 when you have a low performing program. It can help you to find which SELECT statements are taking the most time and sometimes will offer a better way to access the data, suggesting other indexes, etc.

Go ST05 and

-click the activate trace button,then leave the transaction.

-Run your program either via tcode or directly from SE38,

-after completion of the program,

-go back to ST05 and click the Deacivate Trace button. Now click the Display Trace button,

-in the dialog, just click the green check.

-The next screen is the trace, where it lists all of the database tables that were accessed during the time of the trace, including system tables.

-The duration column will be important when looking for low performing SELECTs, the higher the number, the lower the performance,

-Put your cursor on a line where the OP is "OPEN" and click Explain, here is some good information about the select statement, how the data was accessed, and with which index.

Regards,

Tushar Mundlik

[Former Member]

You should use ST05 when you have a low performing program. It can help you to find which SELECT statements are taking the most time and sometimes will offer a better way to access the data, suggesting other indexes, etc.

Go ST05 and

-click the activate trace button,then leave the transaction.

-Run your program either via tcode or directly from SE38,

-after completion of the program,

-go back to ST05 and click the Deacivate Trace button. Now click the Display Trace button,

-in the dialog, just click the green check.

-The next screen is the trace, where it lists all of the database tables that were accessed during the time of the trace, including system tables.

-The duration column will be important when looking for low performing SELECTs, the higher the number, the lower the performance,

-Put your cursor on a line where the OP is "OPEN" and click Explain, here is some good information about the select statement, how the data was accessed, and with which index.

Regards,

Tushar Mundlik

[Former Member]

Hi,

ST05: SQL trace

1.create a small ABAP/4 program that contains only the select statement. Before proceeding, test it to ensure that it works.

2.Open that program in the editor so that it is ready and waiting to execute.

3.Open a new session using the menu path System->Create session.

4.Run transaction ST05 (enter /nst05-zero-five, not oh-five-in the Command field, or choose the menu path System->Utilities->SQL Trace). The Trace SQL Database Requests screen is displayed.

5.If the Trace SQL Status Information box reads Trace SQL is switched off, go to step 7.

6.At this point, the Trace SQL Status Information box contains Trace SQL switched on by, followed by the user id who turned on the trace and the date and time it was started. You must switch it off before you can proceed. If the trace was started within the past hour, it is possible that it is still being used. Contact the indicated user or try again later. If the trace was started hours or days ago, the user probably left it on by mistake and it can be safely turned off. To turn off the trace, press the Trace Off pushbutton. The message in the Trace SQL Status Information box should now read Trace SQL is switched off.

7.Press the Trace On pushbutton. The Trace SQL Database Requests dialog box is displayed. The DB-Trace for User field should contain your user ID. If your user ID is not in this field, enter it now.

8.Press the OK button. You are returned to the Trace SQL Database Requests screen and the status information reads Trace SQL switched on by, indicating that you turned on the trace.

9.Switch back to the window containing your editor session (the one with your program waiting to execute).

10.Press F8 to run your program. (Only press F8, do not do anything else, do not even press the Back button.)

11.When your program has run and the hourglass is no longer displayed, switch back to the trace window.

12.Press the Trace Off pushbutton. The status information reads Trace SQL is switched off.

13.Press the List Trace pushbutton. The Trace SQL Database Requests dialog box is displayed. The fields on this screen will already contain values.

14.Press the OK button. You might need to wait a little while, at most a couple of minutes. The Trace SQL: List Database Requests screen is displayed.

15.Type %sc in the Command field and press the Enter key. The Find dialog box is displayed.

16.Type the name of the table you are tracing in the Search For field. (This is the table named in the select statement in your ABAP/4 program.)

17.Press the Find button. A search results list should be displayed with your table name highlighted.

18.Click on the first highlighted table name. You are returned to the Trace SQL: List Database Requests screen. Your cursor is positioned on the first line containing your table name. To the right of it, in the Operation column, should be the word PREPARE, OPEN, or REOPEN.

19.Press the Explain SQL button on the Application toolbar. The Show Execution Plan for SQL Statement screen is displayed.

20.Scroll down to the execution plan. The index used will be displayed in blue.

Jogdand M B

[Former Member]

hi,

SQL tracer:

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm