This article explains the following query :
How to analyze expensive SQL statement using SM50/SM66 ?
Go to transaction SM66 and have a look at the global workprocess overview.
i) Have a look at Action column and identify any long running Sequential read/ direct read /insertion /updation etc
ii) If so, please note the respective report that is being run and the table that is being used for the same. From the report we can make whether it is SAP standard report or customized report. If it is customized, we can take help of ABAPer to see if the report can be finetuned in cases of bad programming. Table details can be used to figure out the size of the table and whether statistics are upto date for the table or not.
Please note : "Updatestats job should run daily so that system will have recent statistics about all the tables. These statistics will be useful for Cost based optimizer to identify optimized execution plan for an SQL statement"
iii) Also, please note the user who is running that process. So that, later user can be approached and a trace(ST05) can be kept for his activity to understand his transaction in detail which would help for finetuning.
iv) SM50 transaction can be used to view the detailed display of the process and the SQL statement that is being executed
For example, Please refer below screenshot of SM66 :
In the below screenshot,
i) Highlighted user is running ZFI_TDS* report on RBKP table in dialog mode and sequential read action is happening on the same.
ii) user DDIC is running RBDMONI_* report (in background mode) on table BDCP2 and direct read is happening on the same. It has already consumed 783 seconds of CPU time.
To analyze point i) scenario in the above case, please note the server name on which this process is running. Go to that server through SM51 and have a look at SM50 transaction.
Identify the respective PID of the process which you have suspected as expensive SQL from the overview and double click on that process which opens up detailed display as below:
How to analyze expensive SQL statement using SM50/SM66 ?
Go to transaction SM66 and have a look at the global workprocess overview.
i) Have a look at Action column and identify any long running Sequential read/ direct read /insertion /updation etc
ii) If so, please note the respective report that is being run and the table that is being used for the same. From the report we can make whether it is SAP standard report or customized report. If it is customized, we can take help of ABAPer to see if the report can be finetuned in cases of bad programming. Table details can be used to figure out the size of the table and whether statistics are upto date for the table or not.
Please note : "Updatestats job should run daily so that system will have recent statistics about all the tables. These statistics will be useful for Cost based optimizer to identify optimized execution plan for an SQL statement"
iii) Also, please note the user who is running that process. So that, later user can be approached and a trace(ST05) can be kept for his activity to understand his transaction in detail which would help for finetuning.
iv) SM50 transaction can be used to view the detailed display of the process and the SQL statement that is being executed
For example, Please refer below screenshot of SM66 :
In the below screenshot,
i) Highlighted user is running ZFI_TDS* report on RBKP table in dialog mode and sequential read action is happening on the same.
ii) user DDIC is running RBDMONI_* report (in background mode) on table BDCP2 and direct read is happening on the same. It has already consumed 783 seconds of CPU time.
To analyze point i) scenario in the above case, please note the server name on which this process is running. Go to that server through SM51 and have a look at SM50 transaction.
Identify the respective PID of the process which you have suspected as expensive SQL from the overview and double click on that process which opens up detailed display as below:
No comments:
Post a Comment