Yuan Yao, An Oracle ACE's Blog

As a DBA, having a clear understanding of your storage performance is essential to correctly judge overall database behavior. However, in many enterprises, DBAs do not have OS-level access, so they cannot use tools like iostat or sar. Fortunately, Oracle’s AWR Snapshot Raw Data retains historical I/O performance metrics that can help fill this gap.

One useful metric is ‘Average Synchronous Single-Block Read Latency’ from the view DBA_HIST_SYSMETRIC_HISTORY.

Why this metric?

Because it typically reflects physical reads that go directly to storage, often bypassing the OS cache. This makes it a more realistic indicator of actual storage performance at the I/O layer, rather than being affected by OS-level buffering.

The following query evaluates both the average latency and its variance (stability) during busy periods (AAS > 10) over the past 30 days:

-- Yuan Yao Nov 2025
SELECT AVG(h.value) AS average_latency,
       VARIANCE(h.value) AS variance_latency
FROM DBA_HIST_SYSMETRIC_HISTORY h
JOIN DBA_HIST_SNAPSHOT s ON h.snap_id = s.snap_id
JOIN DBA_HIST_SYSMETRIC_HISTORY b ON h.snap_id = b.snap_id
WHERE h.metric_name = 'Average Synchronous Single-Block Read Latency'
  AND s.begin_interval_time >= SYSDATE - 30
  AND b.metric_name = 'Average Active Sessions'
  AND b.value > 10
  AND h.value != 0;

The metric unit is milliseconds.

• Lower average latency means faster reads.
• Smaller variance indicates more stable storage performance.

Together, they help you understand how your storage behaves in real-world workload conditions, even without access to OS-level monitoring tools.

Example output from a physical server:

AVERAGE_LATENCY       VARIANCE_LATENCY
--------------------  --------------------------------------------
0.7546538627422566    0.9798021232402001050268728028903789285714

Example output from a virtual machine:

AVERAGE_LATENCY VARIANCE_LATENCY
--------------- ----------------
44.1579326       6088.10981

These results clearly show the difference between physical and virtualized environments — both in read latency and in stability.