Breakdown of Third Commute

45 minutes to get to work =

40 minutes driving +

5 minutes waiting for traffic lights

This time, about 89% of the time is spent driving and 11% waiting for traffic lights. The traffic light miss ratio has now dropped to 11%, which is much better than the original 33%. Unfortunately, the commute now takes 50% longer and is a lot longer in distance. The SLMR metric is improving, but not quite hitting the mark of getting home or to work sooner.

This illustration helps to show one of the flaws in tuning by ratios. Improving these metrics may not always resolve the poor performance the user sees. Unfortunately, for quite some time, that was the common knowledge and what any Remote DBA that went to training, seminars or sat at the feet of the guru’s learned. The hit-or-miss results obtained by following the ratio philosophy have muddied the waters. Some people claimed success by doing this hit-and-miss ratio philosophy, and then wrote books and gave seminars on the practice, when others followed the same steps and saw no positive change.

Sometimes the problem was a buffer cache that was undersized, and the low Buffer Cache Hit Ratio (BCHR) really was accurately measuring the problem. By increasing the buffer cache, there would be less data blocks that needed to be read from disk, which would result in better performance. Other times, the problem was that the network was too slow and the delays the users were complaining about were not a database problem. Moreover, maybe a user had started running a query that was performing multiple full table-scans on the largest table. The possible causes of a slow database are many and unfortunately, the ratio methodology cannot identify a lot of them.

Another problem with the ratio method is mathematical. The following three ratios are all the same, but they have different values for the components of the formula.

10/100=0.1

100/1000=0.1

1000/10000=0.1

All three of these ratios are 10%. If a job offer came with a tax-to-salary rate of 10%, which monthly paycheck would be preferred? $10,000 per month sounds much better than $1,000 per month. In database terms, in the case of the BCHR, a high value ratio could mean that the database is very efficient and is almost never reading from disk. Or it could mean that the database is reading some things from the buffer cache far more often than it needs to. A small code adjustment could bring the hit ratio down but increase the performance due to the reduction in data that needs to be read.

Examining trends can be insightful in database administration. For example, observing that database space usage is growing at a rate of 4 GB each month, to say that 20 GB of free space will only last 5 months is a simple exercise. In tuning, trend analysis can help identify the existence of a problem. For example, a BCHR that suddenly drops from an average of 90% to 60% indicates there could potentially be a problem. While there is some discussion about the validity of certain ratios for trending database performance and identifying deviations from normal to help identify problems, this book will not be discussing ratios in any great detail.

Another issue with ratios is that these values take a while after startup to be reliable. This is due to the fact that when the database is first started, none of the data blocks are in the buffer cache until they are first accessed. This means that until a normal load has been placed on the database for a period of time, these ratios are not reliable.

The problem with ratios is that bad ratios are not the problem. Bad ratios are an incomplete and sometimes misleading measure of an underlying issue. This is the basic flaw of the ratio tuning methodology.

The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Interface Analysis

ISBN 0-9745993-7-9

Stephen Andert

http://www.rampant-books.com/book_2004_2_wait_tuning.htm