Re: Cache a table

Howard,

Could you elaborate 'direct read' more? I know reading LOB is a kind of 'direct read', anything else?

Thanks.
Jack

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3f979d65$0$24515$afc38c87_at_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 - 12:29:18 CDT