I don't think there is a lot you can do to prevent this behaviour. Oracle excludes the blocks that are already in the cache and reads the remaining blocks accordingly by multiblock or single block reads. I think Jonathan Lewis describes the details in Oracle Core (where he also states that the blocks read by an FTS go not to the MRU end but to the mid point of the list). I could imagine (but don't remember) that the single blocks also go to the mid point of the list - but again: I don't think there is much to do to prevent this situation. So if you need FTS you have to use Oracle's implementation.
There are a few different patterns of behaviour dependent on the size of the table (relative to the size of the buffer cache), but the key question is probably "will the reads increment the touch count" - because if they don't the blocks will fall off the LRU list fairly promptly, if they do then the blocks could (after a couple of tablescans) end up being promoted into the hot half of the cache.
I've done a couple of quick tests (requiring a little care in setup) that suggested the touch count was not incremented so had no effect on whether the blocks would get preferential treatment when they reached the end of the LRU.
I am a little puzzled by your expression: "cached on LRU or MRU list" - it's not two different lists; but people do talk about "the MRU end of the LRU list".
Sorry for the imprecise terminology.
The more general question might have been:
Will blocks read by 'db file sequential read' instead of 'db file scattered read'
during FULL SCAN operations ((LARGE) TABLE FULL SCAN and INDEX FAST FULL SCAN)
be handled differently in matters of caching (LRU insertion point and touchcount setting) and therefore more likely flood/flush the buffer cache ?
As far as I understood your answers this should not be the case.
Thank you for participating,
Aah . Another forums bug. The "correct" answer is shown as "correct" when I view the thread as https://community.oracle.com/thread/2612840 However, when I view the thread from the communications tab, it doesn't show which post is marked as correct !
I read 927351's followup to your "correct" answer as a fresh question raised as a correct answer "hasn't been received yet !"
I wanted to say that irrespective of whether a 'db file sequential read' is part of a Full Table Scan or any other type of lookup (e.g. an Indexed Read), it is treated in the same manner in the buffer cache.
Hemant K Chitale
As I did understand the discussion so far newly read blocks will be cached in a different/special way (LRU position, touch count and other flags) to avoid buffer cache clean out when submitted during FULL TABLE SCANS, scattered reads as well as sequential reads.
I still consider the performance degradation of (scattered reads enabled) FULL SCAN operations with bigger caches as a serious misbehavior.
It makes little sense (to me) to split multi block reads into even more physical read operations just to save some bandwith resources or whatever.
I have been surprised to see that INDEX FAST FULL SCAN read blocks will NOT be handled in the same way as TABLE FULL SCAN operations to avoid buffer cache clean outs, which I was presuming probably because of its scattered read usage, though the Oracle documentation clearly describes the caching exception explicitly and only for Full Table Scans. Beside setting “flag = 524288″ (only_sequential_access) they seem rather to be handled as simple INDEX FULL SCAN operations.
Additionally I was surprised about a good performance of INDEX FULL SCAN operations during my tests.
I guess these might benefit considerably from a filesystem (prefetch) cache and a "well organized" index as I got very
low average wait times for db file sequential reads in this case.
A1: It's got to be done that way - the in-memory copy of the block might have been updated since it was last read and contain data that is needed but not available in the on-disk copy. I have seen some clues, though, that there is work being done on a mechanism that uses maximum reads then throws away the unwanted blocks.
A2 The last time I checked the index fast full scan was handled in exactly the same was as the tablescan. Since indexes are generally much small than the tables they index it is easy to be fooled into thinking that something completely different has happened because the index just happens to belong to the next size down from the table. You may also see the activity looking like a FULL SCAN because tests which create a new large index will tend to have the leaf blocks in order in the data segment, so the physical difference between a "fast full scan" and a "full scan with readahead (prefetch)" is marginal.
A3 There is an index prefetch option available - Oracle can do "db file scattered read" and "db file parallel read" while doing an index full scan.