Re: buffer hit cache ratio

> Also, if caching (and cache ratios) are so irrelevant, then please
> educate me:

I don't think you'll ever hear anyone say that *caching* of blocks is irrelevant. Caching is important for performance reasons pure and simple. If the cache was irrelevant, you could simply set DB_CACHE_SIZE=0 and be done with it. Caching helps database performance, otherwise it wouldn't be there.

What is largely irrelevant is some magic number that you derive to determine how well the overall system is performing, i.e. the BCHR. Let me give you an example. I recently wrote a white paper and my "efficiency" of typing was 98%. That means that 98% of the characters that I typed did not have to be modified in any way. Was my performance in writing that white paper good? Yes or no? The real answer is that you can't tell. This number in no way reflects how much effort it took to do the research for that white paper. It in no way reflects how much effort it took to organize the topic presented in the paper in a clear, concise, meaningful way. It in no way reflects how often I was interrupted in writing the white paper before it was complete. In short, the typing efficiency ratio that I showed above has other influencing factors and outside considerations that the metric does not take into account or adjust for.

The same analogy can be extended to the BCHR. So your BCHR is 98%. Does this mean that the requests for blocks was efficient on the database? One can't really tell just from this number. Logical IO requests may be inefficient and causing large amounts of CPU to be consumed. In this case, the very high BCHR is misleading. If the BCHR is low for a certain time period, it could be that one malformed query caused the low BCHR, but all other statements in the database are performing quite nicely. Does this mean that you need to raise the Buffer Cache size? Probably not for one malformed SQL statement. Rather, the correct action is most likely to make the SQL statement well-formed and not touch the buffer cache at all! What people are trying to say is that the BCHR has other influencing factors and outside considerations that the metric does not take into account or adjust for.

> Why does Oracle have a KEEP pool for 100% caching of objects?

For caching of (small?) objects that will be reused often so that they don't get aged out of the cache by the aging algorithm for the DEFAULT buffer cache. Please don't confuse the well-formed concept of a cache with a malformed metric to tune its size. And that's what we're talking about, using the BCHR as a poor method to help determine the correct size of the Buffer Cache.

> Why does oracle use the optimizer_index_caching parameter (a measure if index BHR) to determine whether to invoke an index?

This isn't quite exactly the same thing. Using a parameter to define the percentage chance that an index block will be in the cache is a different thing than using a cache hit ratio to determine (or tune) the correct size of the Buffer Cache. I'll agree that this parameter is not the most perfect way of adjusting the CBO's behavior. In fact, I'd rather let the CBO determine if the blocks it needs are actually in the Buffer Cache, and then determine an execution plan based on this information than to give it a percentage chance that the index blocks it needs will be found in the buffer cache.

> Why did Oracle create the v$db_cache_advice, PGA advice and shared pool advice tools?

The V$PGA_TARGET_ADVICE view does include the ESTD_PGA_CACHE_HIT_PERCENTAGE column. But to me, the more important columns are the ESTD_EXTRA_BYTES_RW column and the ESTD_OVERALLOC_COUNT column. I'd also use other methods to tune the PGA_AGGREGATE_TARGET parameter, none of which rely on any ratio. That is the "workarea executions%" stats in V$SYSSTAT and the information from V$PGASTAT.

The V$SHARED_POOL_ADVICE does include the nice ESTD_LC_TIME_SAVED column which I do, on occasion, find useful in tuning the Shared Pool. But more importantly, I use the V$SGASTAT view.

I can see where one could argue that the V$DB_CACHE_ADVICE view is akin to the BCHR. But it has a different take than the BCHR. This view does include the ESTD_PHYSICAL_READ_FACTOR and ESTD_PHYSICAL_READS columns which shows the affects of raising or lowering the DB_CACHE_SIZE parameter given the current workload. However, this view does have some problems similar to the BCHR. What I find most useful out of this view is to show that many times, increasing (or decreasing) the DB_CACHE_SIZE parameter will have very little affect on the BCHR. Many times that I've queried this view, I've seen where my estimated physical reads will decrease very little for a large increase in the cache size.

Cheers and have a good holiday season!
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"