Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cached table questions

Re: cached table questions

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Tue, 08 Feb 2000 18:35:33 GMT
Message-ID: <38a05d0f.150850631@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 Tue Feb 08 2000 - 12:35:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US