Re: Database Buffer Cache Hit Ratio

On 6 Oct 2005 12:18:45 -0700, "pcambraf" <pedro.cambra_at_gmail.com> wrote:

>My question was, exactly if its possible that dirty buffers stand in
>databuffer cache waiting to be written by the lonely and sad DBWR
>proccess and this could cause that really needed blocks cannot be
>pulled to memory causing a low ratio.

Such blocks will cause 'buffer busy waits' The problem is better described in more generic terms. The buffer cache has been designed, assuming your database queries have 'locality'. Ie: a small subset of the data is queried by all processes over and over again.
If you however have a few large fact tables, and you have seemingly random queries on your fact tables, meaning there is no way to predict which data is going to be queried (and maybe modified), then that will result in a high buffer turnaround, and a corresponding low hit ratio. BCHR can also be sufficiently lowered by writing inefficient queries. In an efficient query almost all of the data is filtered out, and never gets to the end user. It is in the buffer cache though!

--
Sybrand Bakker, Senior Oracle DBA