Answers in-line
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Howard J. Rogers wrote in message <396f1859_at_news.iprimus.com.au>...
>
>> The CACHE option was to tell Oracle that
>> a table that exceeded the small table threshold
>> should nevertheless be treated as a small
>> table for LRU/MRU purposes, provided it
>> also fell below cache_size (default 10% of
>> db_block_buffers).
>>
>
>Yup. And the enormous performance benefits from telling Oracle this
>are....?
>
That when a specific table is larger than a
size you would normally wish to consider
small, Oracle will nevertheless load it into
the top end of the buffer so that it has to
fall through to the LRU end, rather than
cycling it through a small window and re-reading
it from disc all the time.
Continuous scanned access to a table
will, in general, NOT keep the whole table
in the buffer.
>>
>> If the system is Oracle 8, then one option
>> is to declare a KEEP buffer pool larger
>> than the size of the table, and allocate
>> the table to the KEEP pool. Once the table
>> is pulled into the pool by non-scanning
>> activity it will stay there.
>>
>
>And hence the CACHE clause is....
>
>useless?
>
Which is why Oracle has been telling us
since the year dot (I mean, since Oracle 8
came out) to use the KEEP buffer pool and
not to use the CACHE clause.
In fact if you use the CACHE clause with
Oracle 8 (.0.5 particularly) you can blast
the entire db_block_buffer with the contents
of a single large table. So you should definitely
NOT use the CACHE clause.
The entire buffer management system has
changed dramatically for 8.1, though, so
all bets are off at that point.
Received on Fri Jul 14 2000 - 00:00:00 CDT
