Diary of a Confused DBA…..

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..