Yuan Yao, An Oracle ACE's Blog

In queueing theory, there is a fundamental theorem known as Little’s law, authored by John Little. As documented on Wikipedia, the law states that the long-term average number of customers (L) in a stationary system is equal to the long-term average effective arrival rate (λ) multiplied by the average time that a customer spends in the system:

L = λ × W

This law can be applied to estimate Oracle I/O efficiency using an AWR report below:

• L (Average number of I/O request in the system)
  This corresponds to the number of sessions waiting on I/O. We can derive this using the Avg Active Sessions (AAS) from the Wait Classes by Total Wait Time section.
  In this example:
  • System I/O AAS = 1.9
  • User I/O AAS = 0.2
  • Total I/O-related AAS = 2.1
• \lambda (Arrival rate)
  This is the I/O request rate (IOPS), we can get this from the IO profile section, it states 266 I/O requests per second.
• W (Average time per request)
  This represents the average latency per I/O request. Applying Little’s Law:
  W = L / λ = 2100 ms / 266 ≈ 7.89 ms

This calculated latency of 7.89 ms provides a high-level sanity check. This is consistent with the latency figures reported in the IO stats section in the AWR report.