Talk:Improving Buffer Cache Hit Ratio

From Oracle FAQ
Jump to: navigation, search

I do not agree with the conclusion of the article.

Of course, the are ways to manipulate the figure. But, everybody starting from the programmer up-to the manager share one interest: a well running database. Fooling the figures is nobodies interest.

The Cache Buffer Hit Ratio is therefore a very valuable indicator for the shape of the database.

Is the value of this figure very low, and you know details about your "production" database, this knowledge may help to tune.

For example: have a massive data load, followed by massive data processing and in the meanwhile concurrent 24x7 access from the Internet. Some Hash-Joins on big tables can sweep big parts of cache. Sometimes a penalty for required very fast joins.

Increasing the cache's size, so that big tables fit and the internet is served (you know the amount of requests from the web, etc.), can help to size the buffer cache properly and improve the performance of the product.

Regards DBA of several 300+ GB 24x7 Databases


The main problem is that the BCHR is not that meaningful for DBAs. How many DBAs have increased their buffer caches just to realize that it makes no difference at all? Worst are those DBAs that increase their caches and not measure the increase/ decrease in performance. They just look at the higher BCHR and feel good about it while in fact they've achieved nothing.

There are better ways to tune now. The Oracle Wait Interface (OWI)provides exact details. No need to rely on fuzzy meaningless counters anymore...

[edit] Buffer Cache Hit Ratio

This example does not prove that the buffer cache hit ratio is meaningless. A hit ratio of 99 percent on a database that has performance problems can be very meaningfull. It means that disk reads are probably not the problem and you should look elsewhere. Instead of looking at the top ten SQL statements by disk reads, look at the top ten SQL statement by consistent gets. You'll probably see that query on the dual table which ran 10 million times in the example.

A high buffer cache hit ratio is very nice, but not the end of performance tuning by a long shot. Wait events and other statistics are just as important if not more so. But the buffer cache hit ratio is not something to ignore. A low one can still indicate a problem.

As for artificially changing the hit ratio with a procedure, any statistic can be changed with an appropriate procedure. The author needs to be a little more specific about what this is supposed to prove.