Whenever most Oracle DBAs hear that physical I/Os are costly, they immediately train their thoughts toward the physical disks and I/O subsystem. Yes, the storage layer is the slowest component, but that is only half of the story. The other half is about the stuff that goes on inside Oracle when blocks are being read into the SGA.
There are numerous operations that have to take place. For brevity’s sake, among them, the foreground process must first scan the free buffer list. If a free buffer is not found when the maximum scan limit is reached, the foreground process posts the DBWR process to make free buffers. Then the foreground process has to retry for the free buffer. Once it finds a free buffer, it unlinks it from the free lists chain and relinks the buffer in at the top of the LRU (Least Recently Used) or the midpoint insertion of the LRU (depending on the version). Then the pointers for the buffer header must be adjusted accordingly. There are at least two sets of pointers, and each change requires a latch get. The header structure of the block must also be initialized and updated. The bits in the block header must also be set and changed during the allocation of the buffer, the read of the block into the buffer, and the completion of a read in order to prevent other processes from using the block while it is influx.
Therefore the best way to combat the db file sequential read and db file scattered read waits is to reduce the demand for both the logical and physical I/Os. This can be best achieved through application and SQL tuning. Now that you have been informed how expensive physical I/Os are, you should also know that logical I/Os are not that cheap either.
Leave a Reply