Re: Index compression vs. table compression

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41d3480c$0$1084$afc38c87_at_news.optusnet.com.au...
> Rick Denoire wrote:
> [snip]
>
>> That reminds me the "cache" option for tables, I also never understood
>> how that could go well. Nowadays, Oracle discourages its use.
>
> No they don't. They say that it is unnecessary and less effective than the
> multiple buffer pools feature you seem not quite to grasp properly. CACHE
> was an attempt to keep large full table scans at the cold end of the LRU
> list, and thus prevent the warm half from being flushed out. Precisely
> what the RECYCLE pool does. Only the RECYCLE pool does the job more
> effectively and more certainly, because it's not trying to 'partition' a
> single LRU list, but has one all to itself.
>

Hi Howard

Oh dear, I see little has changed since I last had a look here ...

For someone who demands so much "precision" from others, it's been a while since I last read such *imprecise* contributions. Others have pointed out some of your errors and inconsistencies but if I can just go back to this little piece and clarify things a little for the benefit of Rick and others you've confused.

CACHE was an attempt to keep FTS at the *hot end* of the LRU list, not the cold end, although as Jonathan has pointed out, Oracle no longer puts blocks at the "end". So the above is incorrect. The idea of "caching" something in memory is to "keep" something in memory and this is the purpose of the "KEEP" pool.

So CACHE and KEEP kinda compliment each other (not CACHE and RECYCLE as incorrectly stated above) although blocks that are "CACHE"D have a rather hard time of staying cached due to the way Oracle's touch count mechanism discriminates against them. However blocks that are placed in the KEEP pool can remain cached with some assurance, *if* the size of the KEEP pool is larger than the sum of the size of all KEEP objects. The danger with the KEEP pool though is that you "keep" and allocate resources to an object that could be better utilised elsewhere.

So yes Rick, you are correct and Howard was somewhat imprecise (dead wrong) in what he said above and in his earlier post to you regarding FTS wasting memory and pushing out blocks.

And Rick, just to clarify another error of Howard's, the purpose of the RECYCLE pool is not necessarily to deal with FTSs. Large FTS are already dealt with rather efficiently in the DEFAULT pool by placing blocks on the least (cold) recently used end of the LRU list to be quickly reused. As such, they cause minimal disruption. Also remember a table that's read via FTSs could also be read via an index by another process, assessing blocks that could benefit from caching. However, if you have very large, *randomly* accessed tables (via index access), where the likelihood of two separate processes requiring the same block is extremely minimal, then here we have a scenario where a block has been inserted in the middle of the LRU list and will "waste" a memory buffer or two while it slowly migrates and floats itself to the end of the LRU list to be finally reused. Multiply such blocks out by an (un)healthy sum, and we may prematurely push out other more useful blocks before their touch counts can increment sufficiently.

For the RECYCLE pool therefore, the best candidates are large, randomly access tables where blocks are unlikely to be reused by other processes, *not* simply FTS tables as is often stated by Howard and others.

Cheers

Richard Received on Fri Dec 31 2004 - 19:08:03 CST