Using Oracle's wait interface, it is extremely simple to detect an IO bottleneck. If you want to know how long it takes the IO subsystem to respond to a single or multiblock read request, or how long it takes the IO subsystem to complete a multiblock database writer or log writer request, you need not go any further than a simple wait event report. Of course, you can also use standard operating system reports like iostat and sar. You can even get a bit tricky and use operating system process tracing to remove Oracle from the equation. But I'm getting ahead of myself!

My general IO subsystem performance rule of thumb is read requests must complete in 10 ms or less and write requests must complete in 5 ms or less. Only the most arrogant IO vendors would argue their 20 ms response-time IO subsystem is performing acceptably. When I look at device busyness via iostat or sar, unless the device is at least 5% busy, I don't bother to check the response time. Low-activity devices, when they are active, can result in some crazy-looking response times. Since the low-activity devices are not doing any real work for the system, we can just ignore them.

We can creatively use Oracle's instrumentation to monitor IO subsystem response time. Since Oracle times (instruments) each and every IO request, we simply query from the wait event views to get a very accurate performance view. Regardless of how the IO devices are performing, how the IO subsystem is configured, or how the network-attached storage (NAS) or storage area network (SAN) is performing, we can easily tell how long an Oracle IO request is taking.