Re: Poor Buffer Hit Ratio

I'm with Sybrand on this one .... there isn't near enough memory for what you describe and you must be experiencing some serious swapping by the O/S. You have one instance where the block buffer size is half a gig and you still run 3 other instances? I run 3-4 instances on a similar setup but each SGA is at most 100M of my 1G memory. Don't forget the O/S eh. There's only so much of the database that can reside in memory anyway and it's not ike you can fit 30G of one database entirely in memory unless you got cash to burn for memory. If your users are using the same subset of tables day in day out then the hit ratio will be high. If they tend to hit tables all over the 30G database then there's probably no point in trying to tune it.

The way you describe it I'm kind've amazed it works at all too. I think a double of the memory capacity should actually be quadrupled if you want to size the SGA's as you describe IMO and that's just to get somewhere near the 33% max for Oracle usage for the O/S. I don't think it's a huge deal to push it 50% depending on what else is running and you should consider locking the SGA in memory if at all possible. This is unlikley in the scenario you described but might be with more memory.

opsu

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:957371094.23214.0.pluto.d4ee154e_at_news.demon.nl...
>
> <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