Re: Cache a table

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 23 Oct 2003 19:21:29 +1000
Message-ID: <3f979d65$0$24515$afc38c87@news.optusnet.com.au>

Geomancer wrote:

> "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!

I'm not enitrely sure what you're getting at, but none of this makes much sense. They invented the recycle buffer precisely so that full table scans can find a home in the Buffer Cache which wouldn't 'dislodge' other buffers of more long-lasting use.

The idea that Oracle refuses to cache blocks read via FTS is just silly: how could Oracle "know no other task would use them"? We might just as well abolish the buffer cache entirely and have done with it, if that were really true.

There *are* direct reads which by-pass the buffer cache, but a full table scan against a regular table wouldn't be one of them.

If you could be more precise about your sources, or what you are suggesting, I'll happily discuss it, but generally FTSes go via the buffer cache.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Thu Oct 23 2003 - 04:21:29 CDT