In case of a full table scan, table data fetched and deposited to the LRU end of the buffer cache. And if the table is CACHED the retrived data blocks will placed in the MRU end. Fine then the question is
Suppose the DB_BLOCK_BUFFERS=10000 and the full table (say table X) scan fetched 9,500 data blocks. There there will be two cenerios
1. The whole 9,500 block from the table X replace the 9,500 blokcs in the in the buffer cache and remaining 500 data blocks from other objects (For a certin point of time). So the whole Buffer Cache is exhausted. If this is true what will happen if a table Y is fetched with more that 10,000 blocks?
2. Out of 10,000 blocks in the Buffer cache certin number of blocks are used for stroing the data from the table X and after a threshold value these blocks reused further. If this is true what is the threshold value? Which parameter specying this value?
(Note : Leaving for today...bye)
Technical Lead (Databases)
Thomson Reuters (Markets)