Here’s a curiosity. Or a banal observation, depending on your perspective.
There are, broadly speaking, two different causes of buffer busy waits. They are nicely described in the documentation here.
-
Waiting for a block to be read into the buffer cache by a different session.
-
Waiting for a block in memory to become available in some way (maybe it’s being modified by another session, for example)
The second of these is likely to be observed in an OLTP system, the first of them is more likely in a reporting system with multiple nonparallel full table/partition scans.
The curiosity is that the cure for these two causes are exactly the opposite of each other. To reduce the number of buffer busy waits you either have to increase the number of rows per block (to reduce the number of blocks needed to fulfill a full table scan) or reduce the number of rows per block (to reduce the chance of multiple sessions requiring rows that are in the same block).
So what to make of a consultant who sees a high number of buffer busy waits on a read-only reporting system, and advises that you increase pctfree or partition the table (sic) to reduce the number of rows per block?
Incidentally, I have a headache. Possibly it is caused by spending so much of the day frowning.
