About V$FILESTAT, its SP/AWR history and SREADTIM/MREADTIM system statistics

The databases I look after in my day job always showed a pretty low MREADTIM value, just a little above SREADTIM. Recently, the disks in our SAN were changed and we now have a huge caching module installed and now I consistently see MREADTIM values significant below SREADTIM. It seems that with MREADTIM ‹ SREADTIM Oracle reverts to calculating these values as it does when only NOWORKLOAD statististics are available. When doing this, Oracle ends up with an MREADTIM › SREADTIM.

I still don’t understand the rationale for this behaviour, since my idea of system and object statistics is that they should represent the reality and if MREADTIM is lower than SREADTIM, well, then that’s the way it is. I am fine with a higher rate of Full Table Scans and Fast Full Index Scans if this is the faster access path. Maybe the fear is that there would be so many multiblock reads that the cache that is responsible for the low MREADTIM value would be less effective than the current ratio of SREADTIM/MREADTIM indicates, but in my opinion that could be controlled by regathering system statistics on a regular basis.

To tackle the problem I first wanted to get a good idea about the SREADTIM and MREADTIM values in our databases. When gathering system statistics for short intervals you end up with a value that only represents a partial load (in this databases and all other databases running on the same SAN). When you use a large gathering interval you end up with an average value that might neither represent your day-time transactional load, nor your night-time batch load.

So, I remembered Christian Antognini‘s approach to repetitively gather system statistics through a job and graph the values over time as explained here in his book “Troubleshooting Oracle Performance”. Since I was mainly interested in SREADTIM and MREADTIM I wondered if the same could be done with the readily available Statspack/AWR snapshots of V$FILESTAT.

There are values for PHYsical reads in this view, values for SINGLEBLK reads and if we subtract PHYRDS-SINGLEBLKRDS we end up with… well, NONSINGLEBLKRDS.

My assumption is that NONSINGLEBLKRDS = MULTIBLOCKRDS, but to be on the safe side and not risk to be summoned by the Oak Table Elders, I named the calculated columns NONSINGLEBLK…

I confirmed that direct path reads for multiple blocks are not counted against SINGLEBLCKRDS. I don’t see much relevance for single block direct path reads and I couldn’t think of a test case that triggers single block direct path reads to confirm that these are in fact counted against SINGLEBLCKRDS, so for now I simply declare my assumption to be true or at least pragmatic. 😉

So, in Mumbai (for the next release – it’s not yet in the current version it now is in the current version) I have added views that show the data from V$FILESTAT and V$TEMPSTAT for each data/temp file and that have the additional columns  NONSINGLEBLKRDS, NONSINGLEBLKRDTIM, SINGLEBLKRDTIM_AVG_MS, NONSINGLEBLKRDTIM_AVG_MS. Further, there are two views in Mumbai that show the aggregated values of all data/temp files.

V$FILESTAT view in Mumbai with additional calculated colums (from an idle test DB)

V$FILESTAT and V$TEMPSTAT show data since instance startup, so obviously, we are back at the problem of averages.

The new SP/AWR reports for aggregated data from STATS$FILESTAT and STATS$TEMPSTAT (respectively DBA_HIST_FILESTATXS and DBA_HIST_TEMPSTATXS) make much more sense.

Here are the graphs you can get from these reports:

Average read times per read operation (y-axis) over wall clock time (x-axis)
Normalized sum of read times per second (y-axis) over time (x-axis)
Normalized number of read operations per second (y-axis) over time (y-axis)

The first graph can be used to get a better idea about reasonable values for SREADTIM/MREADTIM system statistics.