quarkman <quarkman_at_myrealbox.com> wrote in message news:<oprs4gpthszkogxn_at_haydn>...
> On 30 Jul 2003 01:52:37 -0700, Jeffrey Yee <jeffyee_at_hotmail.com> wrote:
>
> > Hi,
> >
> > From the manual, the hit ratio for each individual buffer pool would
> > be
> >
> > SELECT NAME, 1 - (physical_reads / (db_block_gets + consistent_gets)
> > FROM V$BUFFER_POOL_STATISTICS
> > ORDER BY NAME;
> >
> > With this formula, I would assume that the total of (db_block_gets +
> > consistent_gets) also covers the physical_reads. If not, would it be
> > more correct if the above statement is written as follow:
>
>
> The formula is trying to work out the ratio of in-memory reads to physical
> reads. A block get or a consistent get is a hit on the buffer cache, and of
> course involved a physical read some time in the past, but right now it's
> achieving a read without any physical I/O.
>
> >
> > SELECT NAME, 1 - (physical_reads / (db_block_gets + consistent_gets +
> > physical_reads)
> > FROM V$BUFFER_POOL_STATISTICS
> > ORDER BY NAME;
>
> Therefore, what you have here is incorrect. You're no longer comparing the
> ratio of physical reads to logical ones, but physical reads to all reads
> (logical+physical). Which might be an interesting statistic, but isn't
> telling you very much more than the original formula was.
>
> >
> > I'm a bit confuse with the definition of db_block_gets &
> > consistent_gets. Please do advice. Thank you.
>
> The best advice I think I can offer is: don't bother calculating the hit
> ratio at all. It's largely meaningless. It has a role to play in
> distinguishing between several plausible causes of wait events (for
> example, if you have free buffer waits, that can be caused by too small a
> buffer cache, or by a lazy DBWR. How do you tell which is the issue? Well,
> if your hit ratio is good, it's unlikely to be a small buffer cache, and
> therefore is probably a lazy DBWR). So as a subsidiary diagnostic, it's
> fine, and your first formula is adequate for that. But don't spend too much
> time worrying about getting a 'perfect' formula for the Hit Ratio so that
> you can use it as a primary tuning tool... because it's hopelessly
> unreliably in that respect.
>
> ~QM
>
>
>
> >
> > Best Regards,
> > Jeffrey Yee
> >
Thanks for the advice. Previously, I failed to see how increasing the
db writer can increase db performance.
Received on Wed Jul 30 2003 - 21:03:40 CDT
