Figure 5-9. This is the instance statistics (v$sysstat based) snippet from the Oracle Database 10g-based Statspack report shown in Figure 5-8. During the Statspack report interval, v$sysstat reports Oracle processes consumed 33,020 seconds of CPU, parsing consumed 1,877 seconds, and recursive SQL consumed 6,426 seconds.

When confronted with a wait event report like the one shown in Figure 5-10, most Oracle DBAs will scream there is a blatant IO bottleneck. They will also be surprised when the IO subsystem team laughs in their face and tells them to go away. So let's look a little closer. The top wait event, db file scattered read, clearly indicates Oracle processes are waiting for multiple block read requests to complete. Clearly, a process needed Oracle blocks, which at the time they were requested did not reside in the buffer cache. As a result, the Oracle process needed to make a read call to the operating system. However, the operating system was able to provide the blocks to Oracle in less than a single millisecond. And this didn't occur just once or twice. Based on the report shown in Figure 5-10 (which I did not alter), this was the average situation for around 50,000 multiblock IO calls!

Figure 5-10. This classic OSM time interval wait event report shows the top wait event db file scattered read having an average wait time of 0.6 ms. Does this represent an IO bottleneck? Not unless you think a multiblock read request taking 0.6 ms is a problem.