Re: Database Buffer Cache Hit Ratio

Sybrand Bakker wrote:
> 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

> >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'

Shouldn't this cause free buffer waits instead of buffer busy waits ?

amit Received on Fri Oct 07 2005 - 10:21:48 CDT