Re: 2 Buffer Cache Questions

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1115160207.398912_at_yasure...
> RogBaker_at_gmail.com wrote:
>> 1. If you select * from a table, shouldn't that load the whole table
>> into the database buffer cache, provided it fits. I have a script that
>> I found that shows me what's in the cache ....
>
> Please post the script ... I have my doubts too.
> --

The point that's been missed is that if the table being selected is large (greater than 2% of buffer cache), a full table scan will load blocks directly into the cold or least recently used end of the buffer cache LRU list resulting in it's blocks being subsequently overwritten during the scan. Therefore, *no*, the whole table wouldn't be expected to be found in the buffer cache, even if it's size is less than the buffer cache.

Thankfully it's expected and desirable behaviour else FTS of large tables will have the undesirable effect of potentially forcing more useful blocks out of cache. This mechanism is somewhat version dependent.

Of course, if the FTS is performed in parallel, then direct reads into the PGA are performed, bypassing the buffer cache entirely.

Cheers

Richard Received on Wed May 04 2005 - 08:54:06 CDT