how to know if there are expensive sql statements
running?what will you do then to improve the response time?
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / lalit rana
You can check your Early watch report which gives the list
of expensive SQL statement.
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / venkatesan k
ST03 ---> Detailed Analysis--> Transaction profile-->
Standard--> Selectb task type as DIA---> Sort the Totao DB
time in Ascending order
ST04--> detailed Analysis--> Resourse Consuption--> Oracle
Seeion Monitor--> Identify the long running task by
comparing that with WP Id.
Double click on the task --> resulting DIA Box--> Explain
SQL statement-->Analyse
Is This Answer Correct ? | 0 Yes | 1 No |
Can SAP patches be installed when other users are online?
When you use DB02 Transaction, You see something called as Swaps, What are they, and how the increase in swaps does affect your SAP system Performance
What is SAPCPE.EXE??
I need to change the Company address parameters of a user. The path is SU01 -> <Username> -> Change - > Address Tab - > Display Icon in Company Address section. In the new screen, i see a preview section icon; on clicking which, I can change the parameters such as "Sending Country"; "Street/PO Box number" and number of lines in the resulting pop up. Here though i change the parameter "Street/PO Box number" field to 'S' ( Street has priority over PO Box), its changing again to 'P' (PO Box has priority over street) only. In the help section it is written that if we set the user parameter (tcode SU3) LND1 to the desired country we can control the user specific Sending Country with the desired value else it takes the Sending Country of the Company defined by default. But no such parameter has been given for Street/PO Box number so that we could change its value atleast there. Someone suggest me how to change the Street/PO Box number of the Address format for a user to the desired value.
what is meant by AT and ATX ?
What is the procedure to apply Patches?
How many types of work processes are there in sap ?
Explain what is sap ides?
Why is it that Only in SAP-BI / SAP-BW Systems do you have the Profile parameter rdisp/max_wprun_time to be set to a value greater than 600 secs.
how to assign a object to 100 roles at a time.
2 Answers Patni, Reliance, Reliance Industries,
what are the current support stack levels at market place?
What to do if If Buffer Hit ratio is less than 95%