You are working on an online transaction processing (OLTP) system. You detected free buffer waits events for your database instance running in a machine that has multiple CPUs. You increased the database buffer cache size as the first step. After a few hours of work on the database, further investigation shows that the same event is being recorded.
What would be your next step to avoid this event in future?
A.
Decrease the value of the DBWR_IO_SLAVES parameter.
B.
Set the USE_INDIRECT_DATA_BUFFERS parameter to TRUE.
C.
Increase the value of the DB_WRITER_PROCESSES parameter.
D.
Increase the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
Answer:C
This event occurs mainly when a server process is trying to read a new buffer into the buffer cache but too many buffers are either pinned or dirty and thus unavailable for reuse. The session posts to DBWR then waits for DBWR to create free buffers by writing out dirty buffers to disk.
DBWR may not be keeping up with writing dirty buffers in the following situations:
– The I/O system is slow.
– There are resources it is waiting for, such as latches.
* The buffer cache is so small that DBWR spends most of it’s time cleaning out buffers for server processes.
[*]The buffer cache is so big that one DBWR process is not enough to free enough buffers in the cache to satisfy requests.