Figure 8-17. Shown is a condensed version of OraPub's OSM script topdml.sql, which helps identify redo-generating SQL. Additional columns that can be useful are centered around locating where the SQL resides or its source, such as the action and service columns.

The operating system should have a painfully raging IO subsystem bottleneck. If not, then there must be IO blockage somewhere between the log writer background process and the physical disks. For example, if a vendor-specific tool indicates the IO devices are performing well within tolerance, yet you can clearly demonstrate the jaw-droppingly slow IO system call response times (for example, the output from tracing the log writer background process), you will need to work closely with the IO team to figure out where in the IO stack the bottleneck is occurring.

There are many pieces of hardware and software between the actual physical disk and the log writer background process, and any one of them could be the bottleneck. Performance specialists will tell you they have seen everything from broken Ethernet cables and connectors to nearly all IO being routed to a single controller or a host-based adapter (HBA) card.