Re: Poor Buffer Hit Ratio

I'm not a huge expert so I should clarify that right away but from what you describe here's the thing.

1.You have 4 instances correct, with 1 Gig of memory on the server. 2. The SGA for the largest instance you have takes up half that gig of memory at least.
3. You say this is double the other 3 combined, okay, let's just say all the other instances
take up 400M of memory (that's 80% of the largest when combined) okay? 4. Forget about the cache hits (db_block_buffer) for a sec here, Oracle SGA's are taking
up your entire memory space of 1 Gig already, there's no memory left for anything else and that
includes the processes required by the O/S to even run. This means that as a new process is required, let's say by one of your users or applications, it must swap out the entire SGA for one of your instances to disk, run that process, then probably swap more processes out to bring back the SGA to service your request. This is a HUGE, and I mean a HUGE performance hit. 5. My recommendation here if you don't buy more memory and I'd try this sooner rather than later, is to dramatically decrease your SGA sizes period. If someone disagrees with that here then that's what were all here for I figure and we can talk about it more.
6. I think yo may have hit upon your other cache hit problem there too, comples queries using most or all of the data so you may never improve that hit ratio anyway. I think someone here already mentioned fidning out what queries access what but I think this is secondary for your more immediate problem for now of no memory.
7. If you're not the Unix admin someone there should be and they should be able to tell what's going on with O/S swapping etc .... then you can confirm that this is happening as I describe to be sure.

Hope that helps some more, good luck,
Opus

<junderhi_at_my-deja.com> wrote in message news:8eq2o8$ris$1_at_nnrp1.deja.com...
> 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 Thu May 04 2000 - 00:00:00 CDT