Meaning of "Disk Reads" Values in DBA_HIST_SQLSTAT

This post relates to my previous writing on mining the AWR. I noticed that it’s very easy to misinterpret the DISK_READS_TOTAL and DISK_READS_DELTA columns in DBA_HIST_SQLSTAT. Let’s see what the documentation says:

DISK_READS_TOTAL - Cumulative number of disk reads for this child cursor

DISK_READS_DELTA - Delta number of disk reads for this child cursor

You might think it’s clear enough and that’s exactly what I thought too. The number of disk reads is the number of IO requests to the storage. But is it really true?

I started suspecting something was not right after using my own awr_sqlid_perf_trend.sql script (see more details on this script here. I noticed the DISK_READS_DELTA values were too close to BUFFER_GETS_DELTA values for queries that use full table scans, which are normally executed using multi-block IO requests to the storage. I was expecting disk reads to be at least two times lower than the buffer gets, but it was something closer to 90% in a few cases. So was I looking at the number of IO requests or the number of blocks read from disks? The best way to find it out was a test case.

The following testing was done in an 11.2.0.3 database:

I created a new AWR snapshot and enabled tracing for my session. I made sure the db_file_multiblock_read_count parameter was set to a high value and then executed a SQL that was forced to use a full table scan (FTS) to read the data from disks. Another AWR snapshot was taken after that.

It was a single execution and look at the numbers! 1073 disk reads and 1092 buffer gets. Could it be the DISK_READS_DELTA is actually the number of blocks read from disks? I need to check the raw trace file to find out.

I found the following lines in the trace file. I’ve highlighted all lines that report waits on physical IO. Notice the first query (sqlid=’96g93hntrzjtr’) is a recursive SQL (dep=1) for the query I executed (sqlid=’036c3dmx2u3x9’, and it was executed during the PARSE phase “PARSE #7904600” for my query. There were few other recursive statements, but they didn’t do any disk IOs (you’ll have to trust me here). It’s good to know the lines are written to the trace file after the corresponding event completes, this is why the recursive statements of the parse phase are reported before the line describing the whole parse operation.

The next task was to count the “blocks” for db file sequential reads and “block cnt” for direct path reads. The recursive SQL (96g93hntrzjtr) read 2 data blocks from disks and the main SQL (036c3dmx2u3x9) read 1071 data blocks from disks. The total number is 1073! Hey, this is exactly what DISK_READS_DELTA (DISK_READS_1EXEC in the script outputs above) reported - so it’s the number of data blocks, and not the number of IO requests!

The investigation resulted in two obvious conclusions:

DISK_READS_TOTAL and DISK_READS_DELTA in DBA_HIST_SQLSTAT report the number of blocks read from disks.

The query statistics in DBA_HIST_SQLSTAT also include the data from execution of the recursive statements.

P.S. Later I found another column - DBA_HIST_SQLSTAT.PHYSICAL_READ_REQUESTS_DELTA - that was introduced in 11.2 along with a large number of additional columns. PHYSICAL_READ_REQUESTS_DELTA and PHYSICAL_READ_REQUESTS_TOTAL represent the number of IO requests that were executed. You can compare the numbers by counting the highlighted rows above to the value I found in DBA_HIST_SQLSTAT below.