Re: cached table questions

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/

On Thu, 10 Feb 2000 10:55:26 -0500, "Eugene Firyago" <efiryago_at_bisys.com> wrote:

>Hi Steve,
>
>Since v8.x the CACHE keyword is substituted by BUFFER_POOL keyword in
>STORAGE clause providing table caching functionality (see SQL Reference on
>STORAGE clause):
>
>CREATE|ALTER TABLE|INDEX|CLUSTER ... STORAGE( BUFFER_POOL
>KEEP|RECYCLE|DEFAULT )
>
>You just define ...BUFFER_POOL KEEP... (instead of CACHE) to put your table
>into KEEP pool. Of course, you need to adjust that buffer to fit the
>table(s) to be kept in. Also I believe the MRU algorithm is not needed to be
>used anymore since the keep buffer guarantees table blocks persistance in
>memory.
>
>Regards,
>Eugene.
>
>
>Steve Adams <steve.adams_at_ixora.com.au> wrote in message
>news:38a1b634.239207061_at_news.eagles.bbs.net.au...
>> 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 Sat Feb 12 2000 - 06:07:17 CST