Expensive SQL

[Former Member]

hi, all,

our early watch alert yields objects that have the highest load due to expensive SQLs. some objects are standard tables others are custom tables. is there a way to check what specific program and/or SQL statements contribute to the objects' high load.

thanks very much,

rico

[tathagata_sengupta]

Hi Rico,

SAP has two tools ST05 (performance analysis) and SE30 (runtime analysis) to analyze and improve performance.

You can execute transaction ST05, Activate the trace, run the specific program or job and then deactivate the trace and analyze the results.

The results will give you a detailed list of the SQL statements which are executed and also give you details as to how much time it took etc.

You can try out this option.

Regards,

Tathagata

[Former Member]

hi, Tathagata,

thanks for your response. i'm currently trying out ST05 & SE30, and seeing SQL statements for the program i indicated.

regards,

rico

[Former Member]

Hi,

after using ST05 make sure that you create secondary indicies for your custom tables. Make sure that they supporting the SQL read. But be aware that every new index will take a small amount of time during the insert of a transaction but most of the time this is nothing compared to the saving you can get from reading a table with index support. Which DB system are you using? As I have worked with DB2, MS SQL, Oracle and MaxDB I can see that every database has its own problems and solutions for performance issues. For example SAP has OSS notes for the different DB system which explains what hints you can use. Use Notes Serach and enter SQL Hint and you find notes for you DB.

I hope this will get you started. I have done performance tuning for many years and it is a very interesting area.

Stefan

[Former Member]

hi, stefan,

i appreciate your help. i'll try to check SAP for ORACLE specific notes.

thanks,

rico