SQL – Trace analysis is one of the effective tools available with SAP for performance analysis of different sql statements. It helps to analyse the % of access of different tables for a job. Also it helps us to identify deadlock of table access.
Procedure
1. Find out the job on which system it is running. ( T-code : SM66 ).
Goto T-code: SM51. (Overview of SAP Servers).Remote logon to particular INSTANCE (select instance & then cntrl+shift+F8).In SAP R/3 System screen enter the T-Code: ST05 (SQL TRACE).In Trace Requests Screen click on “Trace on for User”.You will get window “performance trace filter for writing Trace records”.
In this window USER NAME: username for that job. (If you want Usertrace).
OR
In Procee ID: mention the PID of that job (if you want PID Trace).
It will show you the
State of trace
SQL trace: switched on for USER.
Wait for Some time (i.e 5/10 minutes or depends on the filesize allocated for
That instance file in that instance).if it exceeds the size it will overwrite the original.
Click on the “Traceoff” for switch off the trace.
For Example SQL Trace Filename: /usr/sap///log/TRACE.
Then goto O/S level logon as sudoer as root.
# cd /usr/sap///log.
Then copy the trace file as per Convention.(JOB_USER_Servername_date{yyyymmdd}.trc)
For e.g. trace of RP for user ZRPLBATCH on server SUN013_01 on date 20030515 will be “RP_ZRPLBATCH_SUN013_01_20030515.trc”
Then comeback to SAP R/3 Tcode as: ST05 screen.
goto Menu Bar: Click on --Trace/List immediate.
You will get Filter trace list screen
In this screen enter Trace File Path: /usr/sap///log/sample001 (for example)
Username :
Object name :
Then Choose Enter
It will prompt you Number of Trace record Window ----- Choose “YES”.
You will get the Screen “Basic Trace List”.
In menu bar Goto---> Statement summary.
In this summary you will get information about objects which is accessing long time(Duration).
Like Object name and Duration
Analyze “Objct “ which is taking more time to complete the processing,
Find out the Name of the TABLES/SQL statements.
a. for finding out OPTIMIZING Statistics
Goto T-Code DB20 --- enter the Table name. then press “ Refresh Information”.
b. For finding out the SQL Querry is taking long time for complete the processing.
In the “ Basic Trace List “ select the particular SQL Querry.
In the Application bar click on “Explain ”.
Procedure
1. Find out the job on which system it is running. ( T-code : SM66 ).
Goto T-code: SM51. (Overview of SAP Servers).Remote logon to particular INSTANCE (select instance & then cntrl+shift+F8).In SAP R/3 System screen enter the T-Code: ST05 (SQL TRACE).In Trace Requests Screen click on “Trace on for User”.You will get window “performance trace filter for writing Trace records”.
In this window USER NAME: username for that job. (If you want Usertrace).
OR
In Procee ID: mention the PID of that job (if you want PID Trace).
It will show you the
State of trace
SQL trace: switched on for USER.
Wait for Some time (i.e 5/10 minutes or depends on the filesize allocated for
That instance file in that instance).if it exceeds the size it will overwrite the original.
Click on the “Traceoff” for switch off the trace.
For Example SQL Trace Filename: /usr/sap///log/TRACE.
Then goto O/S level logon as sudoer as root.
# cd /usr/sap///log.
Then copy the trace file as per Convention.(JOB_USER_Servername_date{yyyymmdd}.trc)
For e.g. trace of RP for user ZRPLBATCH on server SUN013_01 on date 20030515 will be “RP_ZRPLBATCH_SUN013_01_20030515.trc”
Then comeback to SAP R/3 Tcode as: ST05 screen.
goto Menu Bar: Click on --Trace/List immediate.
You will get Filter trace list screen
In this screen enter Trace File Path: /usr/sap///log/sample001 (for example)
Username :
Object name :
Then Choose Enter
It will prompt you Number of Trace record Window ----- Choose “YES”.
You will get the Screen “Basic Trace List”.
In menu bar Goto---> Statement summary.
In this summary you will get information about objects which is accessing long time(Duration).
Like Object name and Duration
Analyze “Objct “ which is taking more time to complete the processing,
Find out the Name of the TABLES/SQL statements.
a. for finding out OPTIMIZING Statistics
Goto T-Code DB20 --- enter the Table name. then press “ Refresh Information”.
b. For finding out the SQL Querry is taking long time for complete the processing.
In the “ Basic Trace List “ select the particular SQL Querry.
In the Application bar click on “Explain ”.
No comments:
Post a Comment