Meaning of "Disk Reads" Values in DBA_HIST_SQLSTAT

This post builds on my previous work regarding Mining the AWR. While analyzing performance, I realized how easily the DISK_READS_TOTAL and DISK_READS_DELTA columns in DBA_HIST_SQLSTAT can be misinterpreted.

Let’s look at the official documentation:

• DISK_READS_TOTAL: Cumulative number of disk reads for this child cursor.
• DISK_READS_DELTA: Delta number of disk reads for this child cursor.

It seems straightforward: a "disk read" should be an I/O request to storage, right? As it turns out, the reality is a bit more nuanced.

The Suspicion: IO Requests vs. Blocks

I first suspected something was off while using my awr_sqlid_perf_trend.sql script. I noticed that for queries performing Full Table Scans (FTS), the DISK_READS_DELTA values were nearly identical to BUFFER_GETS_DELTA.

In a typical FTS, Oracle uses multi-block I/O requests. If DISK_READS represented I/O requests, I expected the number to be significantly lower than the buffer gets (at least by half). Instead, it was closer to 90%. This led to a critical question: Is Oracle reporting the number of I/O requests or the total number of blocks read?

The Test Case: Verifying the Metrics

To settle this, I ran a test on an Oracle 11.2.0.3 database. I created an AWR snapshot, enabled tracing, and forced a Full Table Scan with a high db_file_multiblock_read_count.

1. Setting Up the Environment

SQL> alter session set tracefile_identifier='TEST1'; SQL> -- Set high multiblock read count to encourage large IOs SQL> alter system set db_file_multiblock_read_count=128; SQL> exec dbms_workload_repository.create_snapshot(); SQL> -- Enable 10046 trace level 12 for deep IO visibility SQL> alter system set events '10046 trace name context forever, level 12'; SQL> select /*+ full(a) */ count(I_DATA) from tpcc.item; SQL> exec dbms_workload_repository.create_snapshot(); 

2. Analyzing the AWR Trend

After running the query (SQLID: 036c3dmx2u3x9), I checked the performance trend:

SQL> @awr_sqlid_perf_trend.sql 036c3dmx2u3x9 20 0.001 INST TIME                BUFFER_GETS_1EXEC DISK_READS_1EXEC EXECUTIONS ---- ------------------- ----------------- ---------------- ---------- 1    24.10.2013 03:53:06 1092.000          1073.000         1 

For a single execution, we see 1073 disk reads and 1092 buffer gets. To confirm if 1073 refers to blocks, we need to dive into the raw trace file.

Deep Dive into the Trace File

Tracing reveals the exact wait events and the number of blocks involved in each physical I/O.

Recursive SQL and Physical IO

In the trace, I found recursive SQL (SQLID: 96g93hntrzjtr) performed during the parse phase of my main query:

WAIT #25733316: nam='db file sequential read' ela= 885 file#=1 block#=64857 blocks=1... WAIT #25733316: nam='db file sequential read' ela= 996 file#=1 block#=58629 blocks=1... 

Total recursive blocks: 2

Main SQL and Multi-block IO

The main query performed several direct path read operations:

WAIT #7904600: nam='db file sequential read' ela= 1960 ... blocks=1... WAIT #7904600: nam='direct path read' ela= 1203 ... block cnt=5... WAIT #7904600: nam='direct path read' ela= 1531 ... block cnt=8... WAIT #7904600: nam='direct path read' ela= 1047 ... block cnt=15... ... WAIT #7904600: nam='direct path read' ela= 9481 ... block cnt=126... 

Total main query blocks: 1071

When you add the 2 recursive blocks to the 1071 main query blocks, you get exactly 1073.

Conclusion: What Have We Learned?

This investigation confirms two important facts about how Oracle tracks SQL statistics in AWR:

1. Disk Reads = Blocks: DISK_READS_TOTAL and DISK_READS_DELTA report the number of blocks read, not the number of physical I/O requests.
2. Recursive Inclusion: Statistics in DBA_HIST_SQLSTAT include data from recursive statements (like those used for space management or metadata lookups).

A Note on 11.2 and Newer

If you actually need the number of physical I/O requests, Oracle 11.2 introduced new columns: PHYSICAL_READ_REQUESTS_DELTA and PHYSICAL_READ_REQUESTS_TOTAL.

SQL> select DISK_READS_DELTA, PHYSICAL_READ_REQUESTS_DELTA       from dba_hist_sqlstat where sql_id='036c3dmx2u3x9';  DISK_READS_DELTA  PHYSICAL_READ_REQUESTS_DELTA ----------------  ---------------------------- 1073              20 

This clearly shows that while 1073 blocks were read, it only took 20 actual I/O requests to retrieve them.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?