Re: Cache Hit Ratio from system views

On Aug 22, 4:57 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Aug 21, 2:18 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> > Bob Jones wrote:
> > > High BCHR is always better than low - provided everything else being equal.
>
> > Nonsense. Sorry but this is total mythological nonsense. A high BCHR may
> > be an indicator of nothing more than you write really lousy code.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> Correct, but he has consistently stated that "all other things
> equal...". In other words, let's assume that you have read every book
> by Jonathan Lewis, Tom Kyte, all the Oracle documentation (I know that
> is a sore spot for some people), ensured your hardware is properly
> configured, fill in whatever else you want...
>
> If your BCHR is 50% on one system and 99% on another (once again,
> identically coded, hardware, etc.)...
>
> is that not an indication that your DBA *may* have forgotten a couple
> of zeroes for db_cache_size parameter?
>
> I am truly curious, as this should be a yes or no answer. If the
> answer is anything other than no, then it does have *some* value.

The qualification is meaningless. If everything else is equal, then the two hit ratios will by definition be identical too. If you've got two identical machines, with their OSes configured identically, with the same instance configuration, the same physical database configuration, and both performing exactly the same set of SQL statements performed by the same number of users in the same way and same order, then inevitably the BCHR is going to have to end up essentially the same between the two of them.

So, to have a 50% ratio on one and 99% on another, SOMETHING must be different and not 'otherwise equal' between the two machines.

The only meaningful question is, therefore, WHAT is different, and - more relevant to your query- does the ratio help you find out what is different?

Is it different because one database is doing a massive full table scan and the other one isn't? Is it because the buffer cache is differently-sized between the two databases? Is it that the undo tablespace is differently sized between the two databases? Is it that one database is being used to do lots of flashback queries and the other one isn't? Is it indeed that your buffer caches are unequallysized ? Indeed: has Connor McDonald logged onto the one machine and not onto the other? Any or all of those things could be skewing the ratio.

The specific answer to your specific question is therefore 'No', because it isn't an indication of a mis-sized buffer cache, but an indication that something, somewhere might or might not be right or wrong. More than that it doesn't tell you.

The proposition can be simplified even further: suppose on ONE database the BCHR is always and consistently 70% (meaning nothing in and of itself) and then one day becomes 97%. That is indeed an indication of a problem -or, at least, that something or some aspect of database behaviour has changed, perhaps for the better, perhaps not. If I was reading 10,000 blocks and finding 7000 of them in memory, that would account for the 70% ratio. If I suddenly have to issue a query that reads 1,000,000 blocks of which 970,000 are in memory, that would account for the 97% ratio... but the 97% ratio means I have to do 30,000 physical reads and the 70% ratio only means I have to read 3,000 blocks... so you can't even tell whether 97% is "better" than than 70%.

Does that mean the BCHR is a useful indicator? Depends on your definition of 'useful', I suppose. But if I ran into a building and screamed "There is or isn't a fire! Evacuate the building or stay where you are!!", I wouldn't expect others to find that particularly useful! Received on Tue Aug 21 2007 - 20:54:28 CDT