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:
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.
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?
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.
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(); 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.
Tracing reveals the exact wait events and the number of blocks involved in each physical I/O.
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
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.
This investigation confirms two important facts about how Oracle tracks SQL statistics in AWR:
DISK_READS_TOTAL and DISK_READS_DELTA report the number of blocks read, not the number of physical I/O requests.DBA_HIST_SQLSTAT include data from recursive statements (like those used for space management or metadata lookups).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.
Ready to optimize your Oracle Database for the future?