how to know if there are expensive sql statements
running?what will you do then to improve the response time?
Answer Posted / yash
• Detection of Expensive SQL Statements Using the
Transaction Profile and the Statistical Records:
o Using the ST03N transaction profile, you can
identify transactions that cause a significant part of
total response time, as well as transactions with high
database request times. Use of the Transaction Profile:
 Sort the Average DB time (ms) column in descending
order. Transactions with high average DB request times
might be caused by expensive SQL statements.
 Sort the Total Database Time column in ascending
order. Then Total this column. Transactions causing more
than 5% of this total database time are worth a closer look
with respect to tuning measures. .
 Sort the Total Response Time column in ascending
order. Then Total this column. Transactions causing more
than 5% of this total response time are worth a closer look
with respect to tuning measures.
o Use Statistical Records (STAD):
 On the transaction STAD entry screen, enter the
following restrictions:
• Choose a time frame (remember that statistical
records are only available for a certain time).
• Select Task Type D.
• Enter a relevant value for DB request time, for
example, 1000 ms.
 Analyze all available data for long-running dialog
steps by double-clicking individual entries in the result
list.
o Using the Database Monitor:
 On the entry screen of the database monitor you
should check the following values: .
• The quality of the database data buffer should be
higher than 95%. During .warming up. of the SAP system the
quality may be lower, but should still be above 90% (except
during a very early phase).
• Check the number of user calls compared to
recursive calls. There should be less recursive calls than
user calls.
• Check the Reads/User Call. If this value exceeds 30
(blocks read per user call) then this is a strong indicator
for expensive SQL statements.
 Use the Database Process Monitor:
• Enter transaction ST04 and then choose Detailed
Analysis Menu → Oracle Session. .
• Identify the long-running task by finding the
corresponding workprocess ID. Compare the first value in
the Client PID column with the process ID of a work process
in SM50/SM66. Alternately, you can use the link to the work
process information by choosing Process after having marked
one line in the Oracle process overview. .
• Check the current action on the database by
clicking the statement in the SQL statement column. In the
resulting popup window, you will see the following options:
o 1. Dictionary information on the accessed object
o 2. The display of the execution plan for the SQL
statement (also called Explain)
o 3. A reference to the call point in the ABAP
program (if possible) .
• Analyze the Explain
Is This Answer Correct ? | 8 Yes | 0 No |
Post New Answer View All Answers
Where will you change the parameters in three profiles?
How can you find a list of objects that have been repaired in the system?
How will determine whether your sap server is unicode or ascii ?
How to identify a particular T_Code has been modified or not? If yes, how to identify who modified particular T_Code in current system?
Is it possible to install sap patches when other users are online?
What is the different type of users in sap?
How to lock or unlock a transaction code using sm01?
User wants lock ASAP then what will u do?
I'm getting the following runtime error in IDES "DYNPRO_MSG_IN_HELP". GUI is 720 final release and patch level:0
What settings we have to do in EEWC while using slandalone SAP-CRM system.
What are the activities you done in PI?
Can anyone help me in educating me in installing SAP 4.6C over windows server 2000. Please make it a step by step process, my career depends upon the success and failure of installation.
Can you tell me about the landscape migration to cloud ?
How client refresh is different than client copy?
how can we set a/r a/p netting for inter company code transactions? is it possible to do? if yes, give the solution?