Re: Buffer cache statistics (ratios) and CBO SQL optimization?

"JEDIDIAH" <jedi_at_nomad.mishnet> wrote in message news:3ff8313a$1_7_at_athenanews.com...

>
> Cache hit ratios are dependent on how dispersed your data is. It
> doesn't matter how small your cache is relative to your physical
> data as much as what your code is doing.
>
> Locality of reference(s) is the key.
>

I'd consider "working set" as the key. Related data may be adjacent to other data and not cached. Every application has a set of data that is constantly referenced although it may not be adjacent. That is where caches can make a huge difference. A cache only needs to be able to contain that working set or a significant portion of it.

In the early days of virtual memory and paging, real memory was essentially a cache for all the programs you could concurrently execute in a system. Only the currently executing and recurrent code would reside in memory all the time, with transient code being brought in when needed. The same principle applies to a data cache. In fact in some systems you would have two paging caches - one for code and one for data. But I digress.

The average speed of access to a given cached size versus the amount of data in the working set is defined by an equation that resolves to a hyperbole, if memory doesn't fail me (cripes, time flies: nearly 25 years since I last looked into this!).

That means it works well up to a certain proportion between cached data and working set size, then it goes down the gurgler fast in terms of efficiency. When dealing with memory allocation and paging, this degradation was called "thrashing". I don't think there is a similar term to define essentially the same result with data caches in the specific case of databases. But the effect is similar.

Bottom line: find the "working set" of your environment and combination of apps and size your cache accordingly. Within the limits of price, SLA and hardware capacity, of course.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam