| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Index compression vs. table compression
Jonathan Lewis wrote:
> Note in line:
>
>
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:41d57a50$0$5112$afc38c87_at_news.optusnet.com.au...
>
>>> Not if they're being used for doing lookups, I hope. >> >>Why? >>A small table is always likely to be read via a FTS using CBO. Even for a >>single key lookup... >>
See my post to Tom this evening. "Typically" and "likely" and "small" are the issues here, I think. You (and Tom) post one example showing an index being used; I post another showing a FTS being used. Which is the more *likely* scenario?
[snip]
>>>>And therefore a further tool is needed: a mechanism which will >>>>distinguish between nasty, huge FTSes of bulky tables, and small, OK, >>>>FTSes of useful lookup tables. >>> >>> >>> But a 'genuinely small' table - which means less than 20 >>> blocks or 2% of the number of buffers in the cache, >>> whichever is larger - is not automatically loaded to the >>> discard end of the LRU list, anyway >> >>Forget the "genuinely small". Deal with the actual issue being discussed >>here. How do you distinguish between benign and bad FTSes? >>FTS. And no, that's not a myth.
>
> Bad tablescans are ones that should not be happening,
> and benign tablescans are ones that should be happening,
> but probably will not be happening very often, because if
> they happen frequently they are bad tablescans. If you suggest
> that a 'small table lookup' should be a tablescan than you
> are propagating a myth that needs to be corrected.
I am suggesting that I would not particularly want to scatter indexes about liberally on small tables, and that accordingly FTSs might be the *only* way such tables would be read. It is also true that a single-row select from a 448-row table I consider to be small was read via a FTS this evening in my post to Tom, and not via an index. So yes, I am suggesting that *on balance* small tables are *likely* to be read via a
> I hadn't actually realised that this was the actual issue being
> discussed here, though, I was under the impression that the
> issue was a discussion on the mechanics of the buffer pools.
No. It wasn't. Which perhaps explains why my description of the *precise* mechanics of their operation was not particularly precise. It didn't need to be with respect to the actual issue under discussion, in my opinion. I take all sorts of outrageous liberties with "the truth" on day 1 of the DBA Fundamentals I course, too. I hope I can be forgiven for doing so: we always get there in the end.
>>Whatever. Does this change the conclusions to be drawn from anything I've >>written? If not, say so. If yes, explain why. >>>
>
> "Whatever" - that's a comment that doesn't go too well with another
> comment of yours, viz:
> "and I wish you'd get your terminology correct: it's a LEAST
> recently used list, not an MRU one".
If one can't even name things correctly, we might as all well give up. That is rather a different matter than explaining in (accurate) detail fairly esoteric internals matters which happen not, at that precise moment, actually to be relevant.
> I think one of your conclusions was this:
>>> And that is what the *CACHE* clause >>>does: if you specify it as an attribute of a small lookup table, its >>>blocks will indeed be read into the hot half of the LRU list, *even >>>though they were read by a FTS*.
But that, I think, makes no difference to the point I was making. CACHE/NOCACHE is, at least on one level, a mechanism to differentiate FTS's. The precise details of how it does so I leave to the day when the nub of the issue has actually been grasped by the original poster.
> I think another of your conclusions was this:
>
>>>All of which boils down to: CACHE/NOCACHE and KEEP/RECYCLE are trying to >>>do precisely the same thing.
>
> I think you are the primary complainant about 'sloppy descriptions'.
> This is a very sloppy comment. One of the features of the KEEP
> pool is that it can do a better job for SOME of the stuff that CACHE
> does.
Oh, come on. Don't cut me off at merely a full stop. Some context, please: "And that is precisely what the KEEP versus the RECYCLE pools set out to do, too.
Only, instead of trying to draw a vague distinction between halves of a single LRU list, the separate buffer pools create entirely separate buffer cache areas so that no matter how cold a small table might happen to get, it still can't be dislodged from its keep cache by an inadvertent large FTS directed to a RECYCLE cache... something that the CACHE/NOCACHE mechanism simply cannot ever guarantee.
All of which boils down to: CACHE/NOCACHE and KEEP/RECYCLE are trying to do precisely the same thing."
It is, I would have thought, abundantly obvious from *all* of the above that I am aware that to merely state CACHE/NOCACHE=KEEP/RECYCLE would be utterly ridiculous, never mind "sloppy". And that is why I didn't state that at all, if you only care to quote a modicum of the context in which that sentence appears. But if you'd prefer me to have added "only KEEP/RECYCLE does it better" to the sentence under scrutiny, then consider it done.
Regards
HJR
Received on Sat Jan 22 2005 - 02:35:00 CST
 |
 |