Re: cached table questions

There is also a bug in at least one of the 8.0 versions (4 or 5, can't remember which) which makes it very unsafe to CACHE a table which isn't in the KEEP pool.

A CACHED table no longer limits itself in any way to what used to be the CACHE_SIZE_THRESHOLD (10% of db_block_buffers). A very large table specified as CACHE but not KEEP in 8.0.x would blast everything out of the entire buffer pool.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Steve Adams wrote in message <38a47bd6.420873403_at_news.eagles.bbs.net.au>...
>Hello again Eugene,
>
>Sorry for the delay on this. I wanted to check my facts first.
>
>At 8.0, assigning a segment to the KEEP buffer pool is not adequate to
>ensure that it will be kept, if it going to be read via a full scan. The
>normal replacement policy that is used for the DEFAULT buffer pool
>applies, and multiblock reads reuse the same set of buffers at the LRU
>end of the cache. If you check X$KCBWDS.REPL_RID (the replacement policy
>id) you will see that it is 2 for all working sets, regardless of which
>buffer pool they belong to. Therefore, the CACHE keyword is still needed
>at 8.0 to keep tables that will be accessed via full scans. This is,
>however, no longer true at 8.1, and that is what I needed to check.
>
>Regards,
>Steve Adams
>http://www.ixora.com.au/
>http://www.oreilly.com/catalog/orinternals/
>http://www.christianity.net.au/
>
Received on Sat Feb 12 2000 - 06:53:16 CST