For example, Database has 3 tables which is the same size but buffer can hold one table at one time. However high ratio could be inflated by a very frequent executed SQL which get data from buffer. To improve the hit ratio, you need to analyze SQL cache to tune expensiveSQLs and/or increase data buffer. It is generally recommended that buffer quality/hit ratio should be greater than 95%. WHAT BASIS CONSULTANTS NEED TO KNOW– PERFORMANCE ANALYSIS This is related to concurrent accessing on the same database table. This wait happens when a session wants to access a database block in memory but it cannot because the buffer is busy. Physical reads: Total number of disk access.īuffer Busy wait : Total number of buffer busy wait. Reads displays the sum of number of disk access and memory access since start of database server. Quality = number of memory reads/reads x 100%. Quality indicates how often would a requested data is found in memory without disk access. Goal of data buffer is to reduce physical I/O as much as possible.įield Size displays configured memory size. Accessing data in memory is much faster than accessing data in the disk. It is a memory area to store database data/blocks so future access on the same data can be fulfilled from memory. ST04 database performance overview screen: Similarly there is another factor which is in-memory sort ratio this should have higher values for the better system performance. 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.īut this must be as close as possible to 1 for a healthy system or better system performance. DD(data dictionary) cache quality should be more than 99%, similarly the SQL area get ratio. It should be less than 0.1% of total sorts. Busy time & CPU time ratio 60:40 ratio.Read/User Call 30means expensive SQL statements].Data buffer cache size & quality must be above 95%.It provides all kind of historical data and statistics so database performance can be evaluated and improvement opportunity from application point view or system point view can be identified. ST04 is used to monitor database performance.
0 kommentar(er)
