Re: Buffer Hit Ratio

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u4rqurm04.fsf_at_verizon.net>...
> On 26 Aug 2001, jones1ai14_at_yahoo.com wrote:
>
> > Since our users access so much different historical data so
> > often, might it be reasonable to see the buffer hit ratio so
> > low?
>
> Yes. You answered your own your question. Each user going after
> different large data sets.

The buffer hit ratio dropping to 5% could be a sign of full table scan being used to retrieve data. Also the use of the PQO would make use of full scans and could drive the hit ratio down.

Also from Oracle note Doc ID: Note:33883.1 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".

I have compared the formulas adjusting for direct reads with the standard formula which appeared in this thread and I have only noticed a 1 or 2 percentage point change for systems not using PQO. I have not been able to test to see how the use of PQO, which uses direct reads, affects the ratio. If PQO is in use on your system you may want to perform the deduction and recalculate your hit ratio to see if it looks any different.

• Mark D Powell --