I look at IO issues in term of requirements and capacity. When there is an IO issue, the requirements have exceeded the capacity. The only exception is when a locking or blocking type of issue occurs, such as with a free buffer waits event (discussed in the next section). When you see a database file write issue, except for locking/blocking reasons, you know the IO requirements have exceeded the IO subsystem's capacity. With modern IO subsystems, I never assume the requirements are due to the database I'm working on, or even another Oracle database. With complex IO management comes the increased chance a different system's files are residing on the same physical disk as my database's files. So I'm very careful in my IO subsystem assumptions.

IO issues can become very emotional. Vendors can get involved, and people start protecting their turf. To help move toward a solution, I frame the issues and solutions using OraPub's 3-circle analysis method. From an application perspective, I find the SQL generating the dirty buffers. You will find one or more update, insert, and/or delete operations. They must be there, or these wait events would never have reached the top of the list.

From an Oracle perspective, I start thinking of any instance parameter that may increase Oracle's IO writing efficiency. For example, I would investigate looking for a way to increase the database writer's batch write size. As I mentioned, there are version-specific ways to alter the database writer's batch size. Investigate the _db_block_write_batch and the _db_writer_max_writes parameters. Also considering increasing the instance parameter _db_writer_max_scan_pct (the default may be 40, for 40%) or _db_writer_max_scan_cnt, as they determine how many LRU buffer headers a server process will scan before it signals the database writer to start writing. Increasing these parameters provides more time for the write list to build up, and therefore results in more blocks written per database writer IO request. This increase in efficiency can allow more blocks to be written to disk per second. My tests have shown that by increasing db_writer_max_scan_pct from 5 to 95, database writer operating system write calls decreased by 9% and db file parallel write waits decreased by 3%, while transaction activity increased by 14% and block changes per second increased by 19%. That's amazing, because just by changing this instance parameter, IO activity decreased while workload performed increased. This is exactly the type of improvement you want to see when tuning Oracle!