Figure 9-10. Shown is an actual response-time curve based on a heavily read IO-loaded Linux Oracle Database 10g Release 1 system. The dotted line is the service time (CPU), and the solid line is the response time (CPU plus all non-idle wait time), with the difference between the two being queue time (non-idle wait time). The unit of work is physical Oracle blocks read, which is the instance statistic physical reads. The initial large jump in queue time occurred when IO read concurrency (wait event, read by other session) suddenly appeared and eventually become about one-third of all the non-idle wait time.

There is so much to be gleaned from this single graph. While the top wait event was db file scattered read, notice the queue time for each arrival (it's the difference between the response time and the service time lines). Before queuing really sets in, the queue time (not service time or response time) is around 0.01 ms! This means that while the requested blocks were not in Oracle's buffer cache, they were in some other cache. The operating system was able to provide Oracle a requested block, on average, in around 0.01 ms.

While these details are not shown, when queuing took a second dramatic increase at around an arrival rate of 7E6 pio/ms, it wasn't because the physical devices became busy. They averaged at around only 3% utilized. Queue time took this big increase because CPU utilization reached around 80%. Because IO requests were being satisfied primarily with CPU resources, from an Oracle performance perspective, IO response time was based on CPU utilization! Since we can see that Figure 9-10 does exhibit response-time curve characteristics, in this particular situation, we can use a CPU queuing theory model to anticipate Oracle IO read times.