Re: the ways of buffer hit ratios are mysterious

In article <Eon74.13$9C2.536_at_newreader.ukcore.bt.net>,   "Kevin A Lewis" <Kevin_A_Lewis_at_Hotmail.com> wrote:
> We have similar problems managing a large SAP R/3 system on oracle.
>
> What you need to do is look at the statistics relating to the worst
> offending sql that is to be regularly found in the Library Cache and
analyse
> that with a view to seeing if any extra indexing is required. Only
add one
> when sure this will improve things rather than worsen them.
>
> As you knock off the worst offenders the general performance of the
Buffer
> Cache may well improve.
>
> However Mark made a good point; we are not here to improve the
statistics -
> only to improve the performance. The stats are an 'attempt' to
quantify the
> performance.
>
> regards
>
> --
> Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich
United
> Kingdom)
> <Kevin_A_Lewis_at_Hotmail.com>
>
> The views expressed herein by the author of this document
> are not necessarily those of BOCM PAULS Ltd.
>

Thanks for the kind word Kevin and here is something else to be considered when looking at all Oracle statistics, but the buffer pool in specific, namely, that the statistics you base the number on are subject to being wrong!

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>

--
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. Received on Mon Dec 20 1999 - 12:45:40 CST