Re: calculate hit ratio using v$buffer_pool_statistics

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
>

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/