How to know average time Oracle waited for a physical block to be read from disk and placed into the buffer cache (SGA) ?

Oracle does not directly record "time taken to move blocks from disk to SGA" for every read. However, we can estimate it using I/O wait events and read time statistics.

1. Average Single-Block Read Time (Disk → Buffer Cache)

SELECT event,

total_waits,

time_waited_micro/1000000 AS time_waited_sec,

ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms

FROM v$system_event

WHERE event = 'db file sequential read';

This event represents:

• Reading a data block from disk into the buffer cache (SGA).
• Commonly seen during index lookups and single-row access.

---

2. Average Multi-Block Read Time

SELECT event,

total_waits,

time_waited_micro/1000000 AS time_waited_sec,

ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms

FROM v$system_event

WHERE event = 'db file scattered read';

For full table scans, Oracle reads multiple blocks from disk into the buffer cache.

---

3. Direct Read Time (Bypassing Buffer Cache)

SELECT event,

total_waits,

ROUND(time_waited_micro/total_waits/1000,2) avg_wait_ms

FROM v$system_event

WHERE event LIKE 'direct path read%';

Used by:

• Parallel queries
• Large scans
• Temp operations

---

4. Physical Read Statistics

SELECT

physical_reads,

physical_read_bytes/1024/1024 AS read_mb,

physical_read_total_io_requests,

ROUND(physical_read_total_bytes/

physical_read_total_io_requests/1024,2) avg_kb_per_read

FROM v$sysstat

WHERE name LIKE 'physical read%';

---

5. Best Query: Average Physical Read Latency

SELECT

ROUND(SUM(read_time) /

NULLIF(SUM(phyblkrd),0), 2) AS avg_ms_per_block

FROM v$filestat;

or in newer versions:

SELECT

ROUND(

(SUM(singleblkrdtim) / NULLIF(SUM(singleblkrds),0)),

2

) AS avg_single_block_read_ms

FROM v$iostat_file;

---

6. Real-Time Storage Latency by Datafile

SELECT

file_no,

filetype_name,

small_read_reqs,

ROUND(small_read_servicetime/

NULLIF(small_read_reqs,0),2) avg_read_ms

FROM v$iostat_file

ORDER BY avg_read_ms DESC;

---

For AWR (historical)

To see how much time the database spent waiting for disk reads during a period:

SELECT event_name,

total_waits,

ROUND(time_waited_micro/1000000,2) seconds_waited

FROM dba_hist_system_event

WHERE event_name IN (

'db file sequential read',

'db file scattered read',

'direct path read'

);

Practical Interpretation

Avg Read Time	Interpretation
< 1 ms	Excellent (All Flash/Exadata)
1–5 ms	Good
5–10 ms	Acceptable
10–20 ms	Slow
> 20 ms	Investigate storage

For a DBA, the most commonly used metric to determine disk-to-SGA read latency is:

SELECT event,

ROUND(time_waited_micro/total_waits/1000,2) avg_read_ms

FROM v$system_event

WHERE event='db file sequential read';

To include the snapshot date and time from AWR, join DBA_HIST_SYSTEM_EVENT with DBA_HIST_SNAPSHOT:

SELECT

s.begin_interval_time,

s.end_interval_time,

e.event_name,

e.total_waits,

ROUND(e.time_waited_micro / 1000000, 2) AS seconds_waited,

ROUND(e.time_waited_micro / NULLIF(e.total_waits,0) / 1000, 2) AS avg_wait_ms

FROM dba_hist_system_event e

JOIN dba_hist_snapshot s

ON e.snap_id = s.snap_id

AND e.dbid = s.dbid

AND e.instance_number = s.instance_number

WHERE e.event_name IN (

'db file sequential read',

'db file scattered read',

'direct path read'

)

ORDER BY s.begin_interval_time;

This gives the average time Oracle waited for a physical block to be read from disk and placed into the buffer cache (SGA).