Some things can never be said often enough. Buffer cache hit ratio is a worthless indicator of performance when Oracle is pounding the daylights out of a few cache buffer chains. I see Hemant Chitale has just blogged on this topic:
Buffer Cache Hit Ratio GOOD or BAD ?
About Logical Reads
All logical reads in Oracle start with a hash algorithm on the database block address. Since there is an unknown number of blocks in the database (dba), this cannot be a “perfect hash” so there are hash collisions. Oracle resolves this by “chaining” dbas with equal hash values. Chains hang off of “buckets” and each bucket has a latch. To walk a chain (looking for the exact dba your session needs), the latch is first aquired. These are db block gets and db block consistent gets depending on the type of block you are looking for (versioning). Applications that clone a lot of blocks can have a “piling up” affect on the buckets that govern these hot chains. Fix that problem at the application level before worrying about hit ratio and long before trying to deal with latch dynamics (e.g., spin count, increasing buckets, etc).
Hi.
In his OpenWorld presentation, Richard Niemiec actually commented on still using Hit Ratios, but only as one of several top level indicators. I almost gasped when he said it, but his approach kinda made sense.
Rather than saying a CHR of X is good and Y is bad, he argued that if the database normally runs with a CHR of X and now it is running at Y, something must have changed and that warrants some further investigation. So he uses them like an indicator of change, not as a performance measurement.
Of course, there are many other metrics one can use like this…
Cheers
Tim…
Re Tim Hall’s comment that many other metrics can be used to determine if performance is going through the floor:
I rely on users. If the users are unhappy (they do let us know) then something needs to be investigated.
Sure, there are many things you can do to measure performance. But if the users are happy, and everything is working, do you really want to chase down the ‘performance’ problem?
Most of us have far too much to do as it is without investigating a blip on the BCHR.
That’s my $0.02 on it anyway.
Jared
A change in BCHR is an indicator of a positive change or negative change?
If BCHR was 95 and went to 92 and the database performance is same, why to waste effort in investigating the change in BCHR?
Why not simply focus on wait event analysis?
Richard Neimic approch is a result of Complusive tuning disorder.