Re: cache vs keep buffer pool

In article <UsLa9.16617$g9.51713_at_newsfeeds.bigpond.com>, you said (and I quote):
>
> Based on the 9.2 doco set, CACHE is still a supported clause with no 'do not
> use' notes associated with it.

OK, I'll see if I can find where I read it. It's actually more important than any1 here might think.

>
> True again. I'm just saying that memory that is allocated but never use is
> about as wasteful as you can get. But an oversized Default pool is also
> wasteful and has a detrimental effect on performance.

Sure. But I have yet to see one system where buffer cache (split or DEFAULT only) is under-used. In all cases so far, it's exactly the opposite. With one exception: a SAP system. But we all know those are weird! ;)

> >
>
> All pools effectively behave the same but the issue here is this. The KEEP
> pool is meant to contain frequently accessed objects that ideally once
> loaded into memory, don't have to be re-read from disk.

Sure. But "meant to" doesn't mean "must".

> And what do we age out, things that
> are commonly accessed, by definition. When they get re-loaded, what do they
> age out, things that are commonly accessed. This is an ugly pattern.

I don't think so. What we age out or reload in KEEP is "commonly accessed" by definition, sure.
But we age out the "less commonly accessed". And every once in a while we reload something that now has become "more commonly accessed" and therefore should be cached as of now. Ie, we cope with the natural load pattern of an application.

No app is static in its demands. They all change during the daily processing. If I don't want to waste memory by over-allocating it to KEEP, then it is perfectly fine to have it age objects as they become less used and new ones that become more used get loaded. Perfectly alright and the very same reason why caches exist. The big diff with KEEP is really that it tends to get over-protective about its contents. Ie, it won't aggressively free up all buffers containing an object. Only the ones less used. That's fine by me.

> So what
> I'm suggesting is that aging out of objects is "worse" in this pool because
> by definition we are aging out stuff we *want* to put right back in again.

As I pointed out above, not necessarily. You'd put into KEEP objects that are by nature "look-up". And some large ones that have lots of clustered reads. And their indexes. During the day and as the load of the app changes, you'll see different object's blocks in KEEP. But the blocks there will be the ones currently being the most "looked-up". They don't all get loaded when the db starts, only as required. I can live with that.

> So the problem here is much magnified than in the default pool where
> generally, we age out stuff we *don't* want anymore. Add to that the
> possible fewer latches issues and we have a real problem with performance.

Agreed, the latches we have to be careful. But I believe they come more into relevance if we have writes going into a cached block. For read performance, they are not as relevant. It of course goes without saying that KEEP is *not* the place for volatile objects! The occasional refresh is fine, but not more than that.

>
> OK not hugely. Very Very important ;)

OK, OK! :D

>
> If the pool is too big and is greater than the sum of all KEEP objects, what
> ages out cached objects? Nothing, because nothing else can go into the KEEP
> pool !! So, if we have a table cached in the KEEP pool that is used once a
> week, that table effectively wastes storage all week because nothing else
> can age it out (unless the pool is too small, back to the problem above)

Sure. But I have yet to see any1 size KEEP too large. What I've seen is they start at around 1000 buffers. And then grow from there based on what people gather from running those awfully nice Steve Adams scripts that show which blocks of which objects are in which pool and the selects from v$buffer_pool (or whatever it's called in latest version). Largest so far was 10000. Plenty and still never anything else than full.

>
> Point taken ;) But it would be interesting to know how many DBAs use the
> CACHE clause vs the KEEP pool...

Well, how many 7.3.4 dbs out there? ;)

>
> I agree. But the self tuning stuff is still young and will improve as
> releases progress. I think our jobs are safe for a tad longer yet.
>

Richard, NT has been "self-tuning" since it first came out in 1991 or so. I had a huge discussion way back then in Canopus-Compuserve, precisely about this. My contention was that it was wrong and M$ would soon change it. Since then, in every subsequent release M$ has made more and more tuning parameters available for serious tuners to fiddle with. And it's not like their R&D doesn't like to hide things. There is a reason.

"Patterns" are a vague concept that might apply to Java designers, but has no place whatsoever in self-tuning. Unfortunately, that's all modern developers apply to this problem. It's involves a lot more than just that.

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