Yuan Yao, An Oracle ACE's Blog

To give readers a taste of my book, Oracle Database Performance Tuning with AWR, ASH, and ADDM, I as sharing the section “Assessing Overall I/O Latency with Little’s Law” below.

Little’s Law is a fundamental relationship in queueing theory:

In this formula, L represents the average number of requests in the system, λ represents the average arrival rate, and Wrepresents the average time each request spends in the system.

This formula can be applied to Oracle I/O analysis. By combining the average number of sessions waiting for I/O with the I/O request rate, we can derive an average I/O response time. The AWR report provides both pieces of information. Figure 10-1 shows the Wait Classes by Total Wait Time section IO Profile section used to estimate I/O-related Average Active Sessions.

Figure 10-1. Wait Classes by Total Wait Time used to estimate I/O-related AAS

To apply Little’s Law, we map the three variables as follows:

• L: the average number of I/O requests in the system. In AWR, this can be estimated from I/O-related Average Active Sessions in the Wait Classes by Total Wait Time section. In this example, System I/O AAS is 1.9 and User I/O AAS is 0.2, giving a total I/O-related AAS of 2.1.
• λ: the arrival rate. In this context, this is the I/O request rate, or IOPS. The IO Profile section reports 266 I/O requests per second.
• W: the average time per request. This is the average I/O response time derived from Little’s Law.

Applying the formula:

The derived average I/O response time is 7.89 ms, which is relatively slow. The Host CPU section also reports 40.5% I/O wait, indicating that the host spent a substantial proportion of its time waiting for I/O requests to complete.