Re: Cache a table

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3f9660e8$0$9554$afc38c87_at_news.optusnet.com.au>...  

> Disagree. The issue is not whether it would tend to want to stay in the
> buffer cache or not, but whether it is at risk of being dislodged by a
> rogue, huge, tablescan.

Howard, "Dislodged" is another of those Myths you hate so much!

In Oracle9i, full-table scans changed to make FTS rows go directly into the PGA for the user, completely bypassing the block cache.

Remember, back in Oracle7 there was a special recycle area at the LRU end of the cache for FTS, (which could NEVER page-out MRU blocks).

This was changed in 9i to put FTS blocks directly into the PGA because Oracle knew that no other task would use them.

In Oracle's OTN site we see the "Oracle Expert" article (your beloved Mr. Niemeic) state "a full table scan is put at the cold end of the LRU (Least Recently Used) list."

I verified this with a simple experiment. Start a fresh instance, read a small table, then do an FTS gainist a huge tables and run a query against v$bh. You will NOTsee the FTS rows in the data buffer!

It seems that this "dislodging" may be one of those urban myths! Received on Wed Oct 22 2003 - 18:47:21 CDT