Re: Poor Buffer Hit Ratio

Thanks for the excellent info Mark. I have since kicked my db_block_buffers down to 40000 (our original setting) and performance appears unchanged. Unfortunately, I am not familiar with how to assess the two parameters you mentioned:

'How many free buffers
 do you have on average and what are your buffer pool latch hit ratios?'

Are these available through v$sysstat or might you have a snippet that you use to monitor these? Thanks in advance.

In article <8esdtp$f6c$1_at_nnrp1.deja.com>,   Mark D Powell <markp7832_at_my-deja.com> wrote:
> In my opinion you probably have more buffers than you effecively use:
> The buffer pool hit ratio is unimportant in itself. What counts is
 how
> well your application runs. If it is running well a hit ratio of 70%
 is
> great, and if it is running slow then a hit ratio of 90% is not good
> enough. How many free buffers do you have on average and what are
 your
> buffer pool latch hit ratios? These will give you a better indication
> of how effecively application is using the buffer pool. I believe
 that
> the OS and your Oracle shadow processes could make better use of most
> most of the memory your are allocating to the buffer pool. Here is an
> Oracle note that states that for ver 7.3.4 and 8 that the statistics
> used to calculate the buffer pool hit ratio are invalid to begin with.
>
> Doc ID: Note:33883.1
> Folder: Tuning, Optimization and Other Performance Issues
> Topic:
> Subject: STATISTIC: V7+8 DERIVED "cache hit ratio"
> Creation date: : 14-FEB-1996
> Last Revision Date: 02-APR-1999
> Product: Oracle Server - Enterprise Edition V7
>
> NB: This is a DERIVED statistic.
>
> Based On: <Statistic:Db_Block_Gets>
> <Statistic:Consistent_Gets>
> <Statistic:Physical_Reads>
>
> Definition:
> The hit ratio gives an indication of how often the
> various processes accessing the data buffers find the blocks
> in cache.
>
> Calculation:
> Cache Hit Ratio = 1 - Physical Reads
> ---------------------------------
> (db block gets + consistent gets)
>
> This is generally expressed as a percentage.
>
> Comments:
> - This should generally be >80%
> There is probably still scope for tuning if it is <90% *BUT*
> note that the hit ratio is not the best measure of performance.
>
> - The ratio can be artificially high in applications making
> poor use of an UNSELECTIVE index. Eg: constantly scanning
> a 'yes/no' index accesses the same blocks over and over
> for the wrong reason.
>
> - In Oracle 7.3.4 & Oracle8.0 a change was made to the
> "physical reads" statistic such that it also includes
> "direct reads" and hence the above formula is incorrect
> in these releases.
>
> - In Oracle8.1 "Direct reads" are recorded so the above
> formula can be ammended to deduct "Direct reads"
> from "physical reads".
>
> - In Oracle8 the query below can be used to determine the
> hit ratio for each buffer pool. In 7.3.4 there is no
> reliable method of computing the ratio.
>
> - Oracle8 allows multiple buffer pools which can be
> looked at individually using <View:V$BUFFER_POOL_STATISTICS>
> which is created by the CATPERF.SQL script.
>
> Note:
>
> Some documentation incorrectly reports hit ratio to be:
> Hit Ratio = Logical Reads / ( Logical Reads + Physical Reads )
>
> Related: <Statistic:Logical_Reads>
> <Parameter:DB_BLOCK_BUFFERS>
> <Parameter:BUFFER_POOL_KEEP>
> <View:V$BUFFER_POOL>
>
> > "opus" <opus_at_nospam.ca> wrote:
> > 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
> ...snip.....
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

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