recycle pool

[beginner]

one book i read says that candidate for buffer_pool_recycle should be large table perform frequent full table scans.

but i think the other way,
i think it should be large table that perform infrequent full table scan.

the reason is if we need perform frequent full table scan,
the table actually is hot, so we should not put it in recycle pool

who's right , the book or me?

[sambavan]

It depends on the table size. By normally doing a full table scan, you would be flushing out the buffer cache. That is the reason why it is put in the recycle buffer. But, the LRU machanism, would decide whether it would be flushed or not. When the first full table scan occours, the buffer would do a 20% scan into memory and would place it in the middle. When the second scan request for the same table comes, it would be pushed a level above or so. And This is how the buffer cache LRU functions. (Came to know through some conversations) This is not what the books say. The conversation point might be wrong too So, coming to the point If you were to split your buffer cache, then oracle would set a limit implicitly on the recycle buffer full table scans, preventing a frequent flushing.

Sam