2/13/2016 3:47:09 PM
SAP ABAP Performance Tuning- Both ABAP and Functional consultant can learn quickly about SAP SQL Performance tuning concept. Transaction code ST04-DB Performance Monitor used to find root cause for SAP SQL statement which can be offline or online. This blog explains about find the expensive SQL without taking trace in SAP. Finding Expensive SQL is really challenging but here I am explaining very straight forward.
Issue : In production system user raised a incident/request that one transaction taking too much time to complete and even which is scheduled in background and it is taking about a day(20 hours to 24 hours).
Initial action taken: Requested to user to execute the report in online/dialog mode since it is an production environment and even we do not have authorization to run and we should not do any posting in production environment.
Analysis(below the steps covering only online):
Step 1) Go to Transaction Code SM50/SM51-Work process in SAP
You can use SM50 if you have single instance or else use SM51. Get the user ID and put filter(User ID and Report) in SM50.
Note : Refresh frequently about 2 to 5 minutes and see the columns "Current Action" and "Current Action Info". Here we could see the table is "COEP" screenshot is give below. Copy the Process ID of operating system. If there is no table access then surely it will be issue with ABAP code. You can continue the below steps if you found any table access otherwise get the trace using ST12 which will cover separate blog.
Step 2) Go to Transaction Code ST04-DB Performance Monitor
Expand Performance->Wait Event Analysis->Session Monitor
Go to the column Client process ID and filter with your copied Process ID
Step 3) Select the row and click ABAP Source button
Now system system will be pointing to the corresponding SQL
Step 4) Select the row and click Explain button
We could see the SQL execution plan which will show which index being used. "TABLE ACCESS FULL COEP", here we could see Estim. CPU-Costs, Estim. IO-Costs and the Estim. CPU-Costs is too high. Now we have deducted the exact expensive SQL. Now we need to analyse the solution which is give below.
a) Why any one of index not used?
Based on SQL where clause the cost-based optimizer (CBO) will decide which index is best means system will search both the indices either primary index or secondary index.
b) What are the fields in where clause from the table COEP?
Fields can be get it from the program with corresponding SQL query - kokrs, objnr, gjahr and bukrs
c) Why primary index is not used?
The primary index fields in COEP's are KOKRS, BELNR and BUZEI but in our where clause only first field is matching and remaining fields are not matching. Still primary index can be choose by CBO but the uniqueness on the field KOKRS values are very less(using the transaction code DB05-Analysis of a Table Acc. to Index) I have given the analysis with different fields.
Please find below the Distinct values
d) Why secondary indices are not used?
There are around 6 secondary indices are there even-though CBO not used those indices since the SQL where clause fields and index fields are with different combination.
e) Can we create an secondary index?
Yes but we can try to include additional fields into the existing SQL otherwise we need to consider what is the frequency of execution? how many users are affected? can we run this program as background?. if there is no option either we need to modify the existing SQL or to create secondary index on the table COEP based on where clause fields.
f) Can we add new fields into the where clause?
Yes, before changing the source code we can add new fields and see whether any index being used-using ST04->Diagnostics->EXPLAIN
Note : Without code change we can check whether adding new field in to query will be useful or not
Below the query is original query.
("MANDT"=:A0 AND "KOKRS"=:A1 AND "OBJNR"=:A2 AND "GJAHR"=:A3 AND
"BUKRS"=:A4) OR ("MANDT"=:A5 AND "KOKRS"=:A6 AND "OBJNR"=:A7 AND
"GJAHR"=:A8 AND "BUKRS"=:A9) OR ("MANDT"=:A10 AND "KOKRS"=:A11 AND
"OBJNR"=:A12 AND "GJAHR"=:A13 AND "BUKRS"=:A14) OR ("MANDT"=:A15 AND
"KOKRS"=:A16 AND "OBJNR"=:A17 AND "GJAHR"=:A18 AND "BUKRS"=:A19) OR
("MANDT"=:A20 AND "KOKRS"=:A21 AND "OBJNR"=:A22 AND "GJAHR"=:A23 AND
Add one more field "LEDNR" which is given below
("MANDT"=:A0 AND "LEDNR"=:A25 AND "KOKRS"=:A1 AND "OBJNR"=:A2 AND "GJAHR"=:A3 AND "BUKRS"=:A4) OR
("MANDT"=:A5 AND "KOKRS"=:A6 AND "OBJNR"=:A7 AND "GJAHR"=:A8 AND "BUKRS"=:A9) OR ("MANDT"=:A10
AND "KOKRS"=:A11 AND "OBJNR"=:A12 AND "GJAHR"=:A13 AND "BUKRS"=:A14) OR ("MANDT"=:A15 AND
"KOKRS"=:A16 AND "OBJNR"= :A17 AND "GJAHR"=:A18 AND "BUKRS"=:A19) OR ("MANDT"=:A20 AND "KOKRS"=
:A21 AND "OBJNR"=:A22 AND "GJAHR"=:A23 AND "BUKRS"=:A24)
Please find below the screenshot with different execution plan
Conclusion: Adding new filed giving best execution plan but we need to consider what values we are going to pass to the corresponding select query. To check the domain against the field "LEDNR" if any fixed values or value table. In our case once one value has been(00-Standard ledger) exist in the domain "LEDNR".
Question : If there is no values(fixed values and value table) then how to pass?
Discussed with user and functional consultant and see how many unique values exist for the field(using the transaction DB05). So changed the program with adding new field to corresponding SQL and ran the program and verified the execution plan using shared cursor cache(ST04). Declared a constant "CONSTANTS : lc_lednr type coep-lednr VALUE '00'." Just adding two lines of code the problem is resolved.
Note: Please the snapshot shows offline analysis