topic Re: SQL Trace..? in Application Development and Automation Discussions

SQL Trace..?

Former Member — Wed, 06 Jun 2007 09:47:22 GMT

wt is SQL TRACE...?

explain in detail...?

Re: SQL Trace..?

Former Member — Wed, 06 Jun 2007 09:52:53 GMT

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

Re: SQL Trace..?

amit_khare — Wed, 06 Jun 2007 09:53:38 GMT

Check the links -

Regards,

Amit

Reward all helpful replies.

Re: SQL Trace..?

uwe_schieferstein — Wed, 06 Jun 2007 09:54:33 GMT

Hello Prashanth

The transaction for SQL trace is <b>ST05</b>.

I use this transaction most frequently if I need to from which DB tables data are read or in which DB tables data are written when executing a certain function (e.g. function module, BAPI, transaction).

For example, BAPIs usually use specific structures for external exposure at their interface which have almost nothing to do with the DB tables where data are finally stored. To find out the relevant DB tables you can use the SQL trace.

Reading a SQL trace is just a matter of experience. Next to the relevant DB tables you will see plenty of technical DB tables that are not relevant for your trace.

Regards

Uwe

Re: SQL Trace..?

Former Member — Wed, 06 Jun 2007 09:55:57 GMT

Well using SQL trace

like as follows:

Goto transaction ST05. Press TRACE ON.

Execute your transaction .

Come back to this screen and click TRACE OFF.

Then check the trace details.

You end up getting unnecessary details

Lets say you have to analyze your particular SQL query,

1. put a breakpoint at the select statement and execute the transaction , as it stops at your breakpoint , run SQL trace in seperate session.

2. Trace ON

3. Execute the select statement.

4. Trace OFF

5. Analyze.

In this way you can analyze your particular select query

Pls check links like:

http://help.sap.com/saphelp_nw04/helpdata/en/17/358df9c9fee2469105731e10756921/frameset.htm

http://help.sap.com/saphelp_bw30b/helpdata/en/d1/801f89454211d189710000e8322d00/frameset.htm

http://www.sapbrain.com/TOOLS/SQLTRACE/SQL_TRACE.html

________________

SQL Trace

SQL trace (ST05) provides the developer with the ability to analyze database select statements. Simply execute ST05 to turn on SQL trace, then execute the statement/program you want to analyze. Now turn off SQL trace using ST05

And click on list trace to view the details.

Authorization trace analysis

1. Open two sessions

2. Execute transaction ST01 in one of the sessions

3. Select the authorization checkbox, note the other traces

you can perform (SQL, RFC, Table Buffer etc)

4. Click the 'Trace On' button

5. Within your other session execute the transaction/report

you want to trace or get the user in question to do it

6. Return to the session where you turned the trace on and

click on 'Trace Off' otherwise it will continue to record all authorization checks

7. Click on the 'Analysis' button

8. Enter appropriate data into selection screen such as

Username, type of trace records (i.e. Authorization check)

9. Click on the Execute button.

10. Report displaying trace results will now be displayed