Because the window scheme used in the modified LRU algorithm is no longer used, the hidden instance parameter _small_table_threshold became deprecated. However, in Oracle Database 11g, it is being used again, but for a different purpose. Starting with this version, the _small_table_threshold parameter is the threshold for a server process to start issuing direct reads. Direct reads can increase performance because blocks read from disk are not placed into the buffer cache, but instead are processed in the server process's PGA memory. However, it is more selfish read and can actually slow performance, because no other server process will benefit from the IO activity. My tests have shown that Oracle Database 11g does not always respect changes to this parameter.

Let's walk through an abstracted scenario. I'm leaving out a lot of detail, but this story will work fine for our purposes. Suppose you are a server process who must query a row, which resides in a specific block. Based on the SQL statement and the data dictionary, you know the file and block number. You're all about speed, so you're hoping the block already resides in the buffer cache. To check this, you need to get the buffer's buffer cache memory address, which resides in its buffer header.

To find the buffer header, you must access the CBC structure. You hash on the file and block number, which points you to a hash bucket. Based on this hash bucket, you look up the associated CBC latch and contend for it. After a few spins, you are able to acquire the latch, so you begin your sequential CBC search. The first buffer header is not the buffer you're interested in, and unfortunately, there is no second buffer header in this cache chain, so you know the buffer does not currently reside in the buffer cache.