Re: Poor Buffer Hit Ratio

OS paging is transparent to applications - so paging or not will not of itself affect application-based metrics (e.g. determining the buffer cache hit ration by looking at v$sysstat - the usual way).

Of course, OS paging is very serious and should be avoided.

The original post seems to point to an application design issue, not a memory tuning issue at all. Why else could you explain almost no improvement in the buffer cache hit ratio despite tripling the number of db block buffers?

Poor reuse of data blocks will always lead to a low buffer cache hit ratio (by definition).

Thus it could be a DSS type system, or one performing many full table scans for instance.

Your advice about setting db_block_lru_statistics is slightly inaccurate.

If you want to model the improvement in buffer cache hit ratio, you change

DB_BLOCK_LRU_EXTENDED_STATISTICS. (not DB_BLOCK_LRU_STATISTICS)

x$kcbrbh is for db_block_extended_statistics analyses.

x£kcbcbh is for db_block_lru_statistics analyses.

David P.

Oracle Certified DBA.

Glasgow, Scotland.

Sybrand Bakker wrote:
>
> <junderhi_at_my-deja.com> schreef in berichtnieuws
> 8eph7l$6tt$1_at_nnrp1.deja.com...
> > We are running Oracle 8.0.4 on an 8-way R50 with 1GB of physical memory.
 The
> > machine is running 4 instances with the largest approximately 30GB in
 total
> > size. This largest instance is using db_block_size of 4096 and has 120000
> > db_block_buffers. The instance previously had only 40000
 db_block_buffers,
> > but after noticing our block buffer hit ratio of approx 70%, I tripled it.
> > Unfortunately, we have not obvered a performance increase as the hit ratio
 is
> > still 70%. We are considering doubling the physical memory, but I'm not
> > convinced that this low hit ratio will be alleviated with a generic
 increase
> > in physical memory. Any suggestions?
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> Frankly, you should be amazed it still works at all. Running 4 instances on
> one single server with only 1 G of memory is a proof of either
> - insufficient funding by management
> - someone has gone insane
> (Sorry for such blunt words).
> Oracle recommends using not more than one third of memory for SGA, you
> should be way beyond that and observe heavy pagefaulting on the server.
> Buy that memory, or better still buy that second server (they're not that
> expensive!!!!).
> To determine an increase in hit ratio other than to stick a thumb in the
> air,
> set db_block_lru_statistics (init.ora parameter) to the amount you want to
> add (in number of buffers), bounce the database and observe the results in
> the x$kcbrbh table.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
  Received on Wed May 03 2000 - 00:00:00 CDT