Re: How does a table scan effect the cache?

Chuck Hamilton <chuck_hamilton_at_yahoo.com> wrote:
>When a full scan is done on a table, it's my understanding that the
>blocks read go to the least recently used end of the LRU list. Does
>that mean that every time the scan reads a group of blocks
>(db_file_multiblock_read_count to be exact), that the same blocks at
>the end of the LRU list keep getting replaced on each read? So
>assuming my dbfmbrc = 16, and no other access is happening against the
>table, and the table is large enough to not be cached at the top of
>the list, I would never have more than 16 blocks of that table in the
>cache? Is my understanding on this correct?

Yes, this is my understanding.

See the "Oracle 8i Concepts" manual Chapter 7 -

" The LRU Algorithm and Full Table Scans

When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache. "

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/