Key to understanding free buffer waits is the difference between a push-to-disk issue and a pull-from-LRU-chain issue. When we see the wait event db file parallel write rise to the top of our reports, the problem is too much time being spent writing, or pushing, dirty buffers to disk. (The buffers are not actually moved to disk, but copied, which results in the block and buffer matching creating a free buffer.) A server process posts a free buffer waits event when it can't find a free buffer quick enough. One reason for this is the database writer has not pulled enough dirty buffers from the LRU chain and made them free again. So it's not a push-to-disk issue, but instead a pull-from-LRU-chain issue. While the difference may seem exaggerated, it has a huge impact on our solution approach.

If we focus on the pull issue, then we will do whatever it takes to ensure the database writer does not get too far behind in pulling dirty buffers from the buffer cache. In other words, our goal is to get the database writers to do more work because the issue is not writing to disk. If the issue were writing to disk, the top wait event would be db file parallel write, not free buffer waits.

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.