Using ST04 Transaction to Analyze Oracle Database

ST04 is a very good transaction to analyze database performance. To use it reliably, you need to reset the statistic using the reset button and check the new values after a few days' use to populate with new values and analyze it by using Since reset.

Time/User call should be less than 20ms. Note that the value can be much higher due to the inclusion of special idle events which limits the relevance of this data.

The ratio of Busy wait time to CPU time should be close to 60:40. This is an indication of a well-tuned system. If you see very high values (such as 80:20), system performance can be improved using 'wait event tuning'.  If the CPU time is significantly higher than 40%, check the CPU utilization on the database server.


Hit ratio (Quality) of the data buffer should be more than 94%. A low hit ratio might be due to small size of the data buffer.Chek SAP note 619188 for a deeper analysis.

Reads/User Calls should be less than 15. If it is too high, check for expensive SQL statements. Check SAP Note 766349.

Go to Detail Analysis Menu -> File System Requests and check the average read time (Avg(ms) for Blk Reads) for individual data files or in total (Total under the column). If the values is very high in comparison to 10 ms, check whether the problem can be solved by improving the data distribution or if there is any Disk I/O problem at hardware level.
You can analyse more on disk usage from ST06/OS07 (-> Detail Analysis Menu -> Disk). If the load on the database server's disks is more than 80%, you need to redistribute the data files.

DD-cache quality should be more than 80% pinratio should be more than 95%;  reloads/pin should be lower than 0.04User/recursive calls should be more than 2. If there is too much deviation in these indicators, you need to increase shared pool size.

Comments

Popular posts from this blog

OS/DB Migration - CMD. STR, TOC, EXT, R3load, DDLDBS.TPL and more

Fixing Inconsistent Table - Table activation fails due to inconsistency between DD and DB

301 Redirect Using SAP Web Dispatcher