Let's walk through another buffer cache scenario to highlight the free buffer wait situation and also round out the database writer discussion I started in the previous section. 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 hoping the block already resides in the buffer cache, but to confirm this and acquire its memory address, you must access the CBC structure. Referring to Figure 6-6 as an example, I hash on the file and block number, which points me to hash bucket CBC 01 (in Figure 6-9). Based on this hash bucket, you look up the associated latch (CS 800) and contend for it. After a few spins, you are able to acquire the latch, so you can begin your sequential CBC search. However, as Figure 6-9 shows, CBC 01 is empty and contains no buffer headers. Therefore, you know the block does not reside in the buffer cache. To get the block, you make a read call to the IO subsystem (for example, readv in Linux), and while waiting, you post a db file sequential read. Finally, you get the block, stop waiting, and start consuming CPU. You now have the block in your PGA memory.

You need to find a free buffer to cache the block, so you go to your LRU chain. But before you can scan your LRU chain, you must acquire the appropriate LRU chain latch. Your LRU chain is LRU 05 (in Figure 6-15), so you contend for latch CS 910. Finally, with both your LRU chain latch and the block in your PGA memory, you begin scanning LRU 05, looking for a free buffer. You first encounter buffer header BH 365 and examine the buffer header. The good news is the buffer is free, but the bad news is it's very popular, with a touch count of 12. So you promote the buffer to the MRU end of the list, and with great satisfaction, reduce its touch count to 0.

You move on to the next buffer header, BH 330. You examine the buffer header and discover it is a dirty buffer with a touch count of 1, which does not meet the default popularity threshold of 2. So, you move the buffer header to the LRU chain's write list. After you complete the move, you check the dirty list length to ensure it is less than _db_large_dirty_queue. The dirty list is only 6, which is less than the common default of 25, so you do not tell the database writer to write its write list.