Re: cached table questions

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 Thu Feb 10 2000 - 09:55:26 CST