Re: Poor Buffer Hit Ratio

To clarify...

  Our R50 supports 4 instances, true. But, the large instance I describe is almost double the size of the other 3 combined. I'll take in mind your comments, but would also like to know if you have additional input provided the extra info I have provided. We see similar problems in the hit ratio even for our smaller clients. I imagine this is due to the rather high complexity of the queries and that they do indeed hit nearly all of the tables available (not at once, heh).

In article <sh0v174fa4n68_at_corp.supernews.com>,   "opus" <opus_at_nospam.ca> wrote:
> 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
> >
> >
> >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 03 2000 - 00:00:00 CDT