Re: Memory Resident Tables.

In a private e-mail,
  Amir <amir7860_at_my-deja.com> wrote:
> Thanx a lot for ur input. I have 2 more question regarding cached
> tables.

> 1. Is there any passibility of aging out automatically such
> tables those are made cached by using alter table command?
> ( I want this table cached thru out the life of this Oracle instance).
>
> 2. Is there any data dictionary table or view which can describe
> me the status of cached tables?
>
> Regards, Amir

(Just in case I get any of this wrong, I will post this reply.)

1. Yes, my understanding is that they could age out. Simplifying a bit. The Database Buffer Cache (implemented on Unix as a piece of shared memory) is an order list of buffers containing database blocks read from the database files. The list is ordered using a least-recently- used algorithm. When a database block is need by a session Oracle first checks to see if it is in the cache. If it is not it is read from disk into the cache. To make room in a full cache, blocks from the LEAST- recently-used end are removed from the list (i.e. aged-out).

When a database block is read from disk via an index the block is cached at the MOST-recently-used end. If a database block is read via a full table scan (i.e. not by an index) then the block is cached at the LEAST-recently-used end.

ALTER TABLE CACHE means that a full table scan of the table will be treated as if the blocks had been read via an index.

The CACHE table option is intended for small lookup tables.

So if you CACHE an infrequently used table and then access via indexes sufficient data from other tables to overflow your buffer cache then the "cached" table will be "aged-out".

2. I do not know of any such view.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 17 1999 - 19:49:13 CDT