Re: cached table questions

Hello Eugene,

Not quite so. The CACHE keyword must still be used for large objects that are intended to be read into a KEEP buffer pool via full scans. Otherwise, each multiblock read will reuse the same set of buffers, and the segment will be neither cached nor kept, except for the last couple of blocks.

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.net.au/

 

On Wed, 9 Feb 2000 10:12:49 -0500, "Eugene Firyago" <efiryago_at_bisys.com> wrote:

>Francois,
>
>The KEEP database buffer (a part of whole database buffer pool) is available
>since Oracle8.x. It has to be used along with RECYCLE database buffer to
>gain better database performance and memory utilization (see Oracle8.x
>Administrator's and Tuning Guides for directions). So the CACHE keyword is
>obsolete in 8.x environment. With Oracle7(?) the only CACHE keyword is
>available.
>
>Eugene.
>
>
>Steve Adams <steve.adams_at_ixora.com.au> wrote in message
>news:38a05d0f.150850631_at_news.eagles.bbs.net.au...
>> Hello Francois,
>>
>> Despite the fact that the keyword "CACHE" makes is sound like the table
>> will be kept in memory, that is not what it means. If you want that
>> functionality, you need to set up a KEEP buffer pool and assign the
>> table to that buffer pool.
>>
>> The CACHE keyword just alters the retention of blocks read for full
>> table scans. By default, the blocks from small tables are retained in
>> cache subject to normal LRU semantics. But blocks from large tables are
>> immediately reused. "Small" means less than or equal to 2% of
>> db_block_buffers (or 4 blocks for a cache of less than 200 blocks).
>> Whereas, if the CACHE keyword or hint has been used, then blocks from
>> scans of large tables are retained in cache (not immediately reused)
>> until the number of block read reaches the cache_size_threshold, which
>> is 10% of db_block_buffers by default. Not however that blocks thus
>> retained are not kept in cache indefinetly. They are subject to normal
>> LRU semantics. The only difference is that otherwise, they would have
>> been immediately reused.
>>
>> Regards,
>> Steve Adams
>> http://www.ixora.com.au/
>> http://www.oreilly.com/catalog/orinternals/
>> http://www.christianity.net.au/
>>
>>
>> On Mon, 7 Feb 2000 12:00:09 +0100, "Francois Junod"
>> <fjunod_at_swissquote.ch> wrote:
>>
>> >Hello,
>> >
>> >I cached a table, to get better performance, using the following commands
>:
>> >
>> >alter table emp cache;
>> >select * from emp;
>> >
>> >Questions :
>> >
>> >1. How can I see the cached tables and their size in memory ?
>> >2. Where are they cached, in the shared pool or included with the db
>blocks
>> >buffers ?
>> > Which parameter should I increase to pin tables in memory ?
>> >3. Is a cached table only used when I run a select statement or is it
>used
>> >for update statement as well ?
>> >4. What occurs with an insert statement on a cached table ? Is the new
>row
>> >stay in memory ?
>> >
>> >
>> >Any advises or experiences about cached table are welcome.
>> >
>> >Francois
>> >
>> >
>>
>
>
Received on Wed Feb 09 2000 - 12:52:42 CST