Re: The Cache option

"Beldar" <beldar_from_remulac_at_yahoo.com> wrote in message news:9a7hn8$1ufg$1_at_newssvr05-en0.news.prodigy.com...
> The Cache option of the Create Table and Alter Table commands ensures that
> the first time a table is accessed, the entire table is loaded into the
 SGA
> and marked as 'most recently used'. According to Oracle doc, this data is
> still subject to LRU algorithms, yet stays in the SGA longer than normal.
> HOW? Isn't every block marked 'most recently used' when it's first
> accessed?
>
>

Normally, yes, but not if they were read in via a full table scan. Full table scans are placed at the LRU end of the list, because otherwise the entire list would very quickly get flushed. But a small table, marked with the CACHE attribute, read by a full table scan is placed at the MRU end of the list.

The idea was that small, frequently used lookup tables would persist in the buffer cache for longer (though you might reasonably ask 'well if it's frequently used, what end of the LRU list does it end up at anyway????!')

And the whole discussion is moot anyway, since whilst that had some performance impact in Oracle 7, it was practically obsolete in Oracle 8, and is definitely obsolete in 8i. Well, you can still *use* the clause in 8i, but the arrival of multiple buffer pools and the like really mean that it is of no practical use.

Regards
HJR Received on Sun Apr 01 2001 - 15:39:34 CDT