Does Your Storage Respond Fast Enough?

The response time of the storage subsystem is critical to overall Oracle database performance. Assessing storage responsiveness can be challenging for DBAs who lack direct access to the Operating System and cannot utilize traditional tools like iostat or sar.

Oracle provides a critical metric for this purpose: Average Synchronous Single-Block Read Latency. This metric, found in DBA_HIST_SYSMETRIC_SUMMARY, is a high-fidelity indicator of storage performance because synchronous single-block reads are latency-sensitive and typically bypass both the Oracle Buffer Cache and the OS filesystem cache. By measuring this “ground truth” of physical I/O, we can gauge the underlying storage subsystem’s true responsiveness.

Monitoring System-Wide Latency Trends

The following query tracks the instance-level response time alongside throughput and IOPS over the last 24 hours:

			
SELECT 
    h.snap_id, 
    TO_CHAR(s.begin_interval_time, 'HH24:MI:SS') AS snap_date,
    ROUND(MAX(CASE WHEN h.metric_name = 'Average Synchronous Single-Block Read Latency' 
                   THEN h.average END), 4) AS read_latency_ms,
    ROUND(MAX(CASE WHEN h.metric_name = 'I/O Megabytes per Second' 
                   THEN h.average END), 2) AS io_mbps,
    ROUND(MAX(CASE WHEN h.metric_name = 'I/O Requests per Second' 
                   THEN h.average END), 2) AS io_iops
FROM 
    dba_hist_sysmetric_summary h
JOIN 
    dba_hist_snapshot s 
    ON  h.snap_id = s.snap_id 
    AND h.dbid = s.dbid 
    AND h.instance_number = s.instance_number
WHERE 
    -- h.dbid = 2788912098 
    -- AND h.instance_number = 1
    -- AND s.snap_id between 50665 and 50688 
    -- 删除
    s.end_interval_time >= SYSDATE - 1
    AND h.metric_name IN (
        'Average Synchronous Single-Block Read Latency',
        'I/O Megabytes per Second',
        'I/O Requests per Second'
    )
GROUP BY 
    h.snap_id, 
    s.begin_interval_time
ORDER BY 
    h.snap_id;

		

Example output:

   SNAP_ID SNAP_DATE       READ_LATENCY_MS    IO_MBPS    IO_IOPS
__________ ____________ __________________ __________ __________
     50665 09:00:57                 1.4446     873.96    8363.52
     50666 10:00:34                 1.3953     895.07    7777.48
     50667 11:00:12                 2.5686     878.07    7103.34
     50668 12:00:25                  2.417     745.44     6983.6
     50669 13:00:14                 2.8073     761.12    6676.59
     50670 14:00:06                 2.3283      841.9    7749.51
     50671 15:00:09                  2.434     825.37    6894.98
     50672 16:00:02                 1.7174     922.75    9693.96
     50673 17:00:08                 2.0037     955.15    9056.83
     50674 18:00:00                 2.9695     875.13    6332.38
     50675 19:00:05                 2.4133     752.87    5696.98
     50676 20:00:30                 2.3414     828.19    7301.65
     50677 21:00:04                 2.2076     719.17    7180.67
     50678 22:00:14                 2.3239     689.28    7858.48
     50679 23:00:29                  2.401     736.87    6939.26
     50680 00:00:11                 2.5469     827.37    6952.54
     50681 01:00:25                 2.4921     851.02    7000.01
     50682 02:00:10                 1.8884      883.2    7890.56
     50683 03:00:17                 1.8381     872.64    8318.98
     50684 04:00:42                 1.7673        879    8203.89
     50685 05:00:26                 1.7349     981.54     7843.6
     50686 06:00:09                 1.9556     889.64    7235.46
     50687 07:00:53                   1.78     904.03    7685.64
     50688 08:00:34                  1.836     903.19    7800.01

24 rows selected.

The result indicate a relatively stable storage subsystem under sustained load.

Identifying Latency by File Type

While this system-level metric indicates overall responsiveness, it does not reveal where the latency occurs. For that, we use DBA_HIST_IOSTAT_FILETYPE to break down synchronous single-block read latency by file type, distinguishing between Data Files, Control Files, Temp Files, Log Files, and other categories.

The small_sync_read_latency column in DBA_HIST_IOSTAT_FILETYPE aligns closely with the Average Synchronous Single-Block Read Latency metric in DBA_HIST_SYSMETRIC_SUMMARY, as both provide a independent measurement of physical single-block read latency.

The following query calculates the total number of synchronous single-block read requests and their average latency per file type over the last 24 hours:

			
SELECT
    io.instance_number,
    io.filetype_name,
    MAX(io.small_sync_read_reqs) - MIN(io.small_sync_read_reqs) AS sync_reqs,
    ROUND(
        CASE 
            WHEN (MAX(io.small_sync_read_reqs) - MIN(io.small_sync_read_reqs)) > 0 
            THEN (MAX(io.small_sync_read_latency) - MIN(io.small_sync_read_latency)) / (MAX(io.small_sync_read_reqs) - MIN(io.small_sync_read_reqs)) 
            ELSE 0 
        END, 
    2) AS sync_read_ms
FROM
    dba_hist_iostat_filetype io
JOIN
    dba_hist_snapshot sn
    ON  io.snap_id = sn.snap_id
    AND io.dbid = sn.dbid
    AND io.instance_number = sn.instance_number
WHERE
    sn.end_interval_time >= SYSDATE - 1
    -- sn.dbid = 2788912098 
    -- AND sn.instance_number = 1
    -- AND sn.snap_id between 50665 and 50688   
    -- 删除
GROUP BY
    io.instance_number,
    io.filetype_name
ORDER BY
    io.instance_number,
    sync_reqs DESC;

		

Example output:

   INSTANCE_NUMBER FILETYPE_NAME                        SYNC_REQS    SYNC_READ_MS
__________________ _______________________________ ______________ _______________
                 1 Data File                          12262040675            0.83
                 1 Control File                          24139818            4.44
                 1 Temp File                               499355             9.6
                 1 Log File                                 14409            8.93
                 1 Archive Log                                520            7.72
                 1 Flashback Log                                0               0
                 1 Data Pump Dump File                          0               0
                 1 Data File Copy                               0               0
                 1 Archive Log Backup                           0               0
                 1 Data File Incremental Backup                 0               0
                 1 External Table                               0               0
                 1 Other                                        0               0
                 1 Data File Backup                             0               0

13 rows selected.

In the example results, the overwhelming majority of synchronous reads occur against Data Files, with a measured latency of 0.83 milliseconds. Control Files, Temp Files, and Log Files show higher latency, but their lower request volume means they have minimal impact on overall system performance.

Yuan Yao, An Oracle ACE's Blog