ST04 – A Magic Transaction for SAP performance tuning – I


***************** Index of Pages on the topic…. ****************************
1. SAP Tuning – Gather Symptoms.
2. ST04 – A Magic Transaction for SAP performance tuning – I
3. ST04 – A Magic Transaction for SAP performance tuning – II
4. ST02 – SAP Tuning – Another Magic transaction – I (Part 4)
5. ST02 – SAP Tuning – Important SAP Parameters – II
6. ST03n – The meter gauge for SAP tuning
7. A handy reference for SAP Memory
******************************************************************************

I already have expressed that performance of SAP system is depended on many things. Hardware, OS, storage box, storage agent, clustering agent, database, customized (Z) programs and overall the usage intelligence of the users. But as a basis tuning expert you might always fill the need of a mirror which will reflect at least the image how your system is being utilized and behaving. I found SAP transaction code ST04 alone is going to help you out on the same. It basically shows detail face of the database behave and usage, both history as well as online.

Question may arise saying that why DB? But DB performance will show you all e.g. excess I/O will also point to OS issue as well as improper load balancing as well as the use of a query or the query itself.

So what are the areas we must look into the ST04? I am writing here the use of the same where ORACLE is used as the database of SAP.

In the first screen only it shows you a lot of statistics and indications, but my interest is on looking at the data buffer cache size and quality. Quality must be above 97%, and then apparently there is no major problem. If less than 95 % then it is problematic. But even a improperly tuned recursive query some time false take your quality value higher than 97%. So there is always a deeper investigation is required.

My second favorite look is the sort sections. It should be less than 0.1% of total sorts.
Another Important area is to look in the shared pool statistics. DD (data dictionary) cache quality should be more than 99%, similarly the SQL area get ratio, but if the database is oracle 10.2.0.2 it may show you some abnormally low figure. This is due to Oracle bug 5452234 and if you need to rectify then need to install the patch set 10.2.0.4.

There is also another interesting data, which is instance performance. There are some interesting statistics which ensures you the parsing status in the system. They are Soft parse ratio max value is 1 which is not possible because at least once the SQL is hard parsed and then soft parsed in its next executions. But this must be as close as possible to 1 for a healthy system. Similarly there is another fact which is in-memory sort ratio; this is for a healthy system should have higher values. In fact the less the disk sorts the better.

There are a lot of magic in the detail screen, which attracts me in this transaction code. One of the most interesting options which attract me more is the…This shows an excerpt of the shared cache. Just run the report keeping everything blank. Normally this gives you a list of SQLs hitting database sorted descending values of disk reads. Yes highly important. The more use of disk, the more slow the operation. But a very large database system with huge growth rate and lot of customized program can show you some startling figure here. Not all those programs and SQL statements in the top 10 rows…As a next step from the same list I will just sort the list in descending order based on the column which shows buffer gets/row. And will note those programs and SQL statements. A combination of these two will mainly point out which programs/SQLs are main causes slowing down the performances.

ST04 transaction has another beautiful magic. Click on any of such identified SQL statements, it provides you the plan of execution and the line of the program from where such SQL …

Today upto this much is enough…….I am in favour providing some screenshot in this article to give a better illustration. Let see whether that is possible…..I am running this blogsite free..so limited space..

***************** Index of Pages on the topic…. ****************************
1. SAP Tuning – Gather Symptoms.
2. ST04 – A Magic Transaction for SAP performance tuning – I
3. ST04 – A Magic Transaction for SAP performance tuning – II
4. ST02 – SAP Tuning – Another Magic transaction – I (Part 4)
5. ST02 – SAP Tuning – Important SAP Parameters – II
6. ST03n – The meter gauge for SAP tuning
7. A handy reference for SAP Memory
******************************************************************************

19 thoughts on “ST04 – A Magic Transaction for SAP performance tuning – I”

  1. Ramprasad said:

    Hi,
    This information is very useful for my daily checks of SAP production systems.

  2. Hi,
    i am working on monitoring project. it is very helpful

    regards
    Ramesh

  3. this is very useful to me. thank you very much. hope that you can add the more content.which is helps to our career.

  4. Awesome, thanks for sharing

  5. Excellent information, presented in an easy-to-read format. Thank you for your contributions!

  6. Thanks for all the reviewer and reader….
    Some more thing to come out here…
    Like the way I use to benchmark /compare the performance of a program taken for tuning. And some small bugs I find in in the transaction itself.
    I wish if I can share an excel or power point presentation….My own way to investigate performance of a SAP system……

  7. As a capacity planner (new to SAP) I need to look at usage, history, behaviour for capacity profiling would ST03n and ST04 be the best commands.

    Our SAP application is hosted on IBM Sys P.
    DB2 on IBM Sys Z. Sysplexed.
    I need to look after all SAP instance / variants across Prod, Verification, Dev, Tech plexes and DR.

    • Hi Ricardo,

      Capacity Planning is a buzzword and already there are so many standards available…I saw basically two trends in it, One trend do not want to look in depth (they don’t, they find it better to search buzzwords from net and copy and paste – normally these aliens are termed as Project Managers/Managers/ IT Audits etc ) and the others who only thinks time taken to process one transaction and no of users in the system (business/functional consultants).

      As of SAP, there is a quick sizer but I still don’t feel how does it goes for a customi(Z)ed transaction/report.

      Whats your way of doing it?

  8. Francie Larocco said:

    I’ve read several good stuff here. Definitely worth bookmarking for revisiting. I wonder how much effort you put to make such a excellent informative web site.

  9. alievitutty said:

    Just skimmed the topic. Amazing job

  10. ur steps r really gud,but for sort section i dnt understand the sentence “It should be less than 0.1% of total sorts.”can u pls explain?
    Thanks…..

    • More sort means in efficient query hitting the database, and you are fetching a huge no of records but actually using a few out of it…..This is actually showing the result of high buffer/record……

  11. hi ur post is good but i m not getting values as per ur documentation for st04 will it create any plbm?pls let me know..

  12. Raju Gogulamatham said:

    Your inputs, I am using in my current assignment. Good to keep visiting for your insights!

    Regards,
    Raju Gogulamatham

  13. chandrachur said:

    What to do in the below condition:

    1. Data buffer cache is less than 95%
    2. DD cache is less than 99%
    3. Sort is high :.1%

    thnx…..

    Chandrachur

    • Hi Chandrachur,

      Its not very clear what D U mean in 1 & 2. Guessing that these are hit ratios and to be very serious these are meaningless. Because any malformed SQL in loop will shoot up thos values to 100 %…..and writing something in ABAP is always possible…But this doesn’t solve the issue….isn’t it? Similarly the sort.

      Actually these are all tip of iceberg…I would have rather preferred delving nose further. Will check first from ST03n, identifying the transaction and reports taking more than 40% of the time and tjen back to st04 isolating the program and the SQLS being thrown by the program. Then finally can take nore apt decision.

      If you go in more detail with actual data then it can be pin pointed.

      Regards

  14. Hi,
    Excellent information.This is very useful information to me.
    Sap Training in Chennai

  15. krishnachaitanya said:

    hi,
    excellent information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s