Here’s a question that appeared in my email a few days ago:
Based on the formula: “sreadtim = ioseektim + db_block_size/iotrfrspeed”, sreadtim should always bigger than ioseektim.
But I just did a query on my system, find it otherwise, get confused:
SQL> SELECT * FROM SYS.AUX_STATS$;< SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- -------------------- SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 10-08-2014 10:45 SYSSTATS_INFO DSTOP 10-10-2014 10:42 SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 680.062427 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 4.716 SYSSTATS_MAIN MREADTIM 2.055 SYSSTATS_MAIN CPUSPEED 1077 SYSSTATS_MAIN MBRC 4 SYSSTATS_MAIN MAXTHR 956634112 SYSSTATS_MAIN SLAVETHR 252928
How do we explain this ?
This question highlights two points – one important, the other only slightly less so.
The really important point is one of interpretation. Broadly speaking we could reasonably say that the (typical) time required to perform a single block read is made up of the (typical) seek time plus the transfer time which, using the names of the statistics above, would indeed give us the relationship: sreadtim = ioseektim + db_block_size/iotfrspeed; but we have to remember that we are thinking of a simplified model of the world. The values that we capture for sreadtim include the time it takes for a request to get from Oracle to the O/S, through the various network software and hardware layers and back again; the formula ignores those components completely and, moreover, doesn’t allow for the fact that some “reads” could actually come from one of several caches between Oracle and the disc without any physical disc access actually taking place. Similarly we should be aware that the time for an actual I/O seek would vary dramatically with the current position of the read head, the radial position of the target block, the speed and current direction of movement of the read head, and the rotational distance to the target block. The formula is not attempting to express a physical law, it is simply expressing an approximation that we might use in a first line estimate of performance.
In fact we can see in the figures above that multi-block reads (typically of 4 blocks) were faster than single block reads on this hardware for the duration of the sampling period – and that clearly doesn’t fit with the simple view embedded in our formula of how disc drives work. (It’s a fairly typical effect of SANs, of course, that large read requests make the SAN software start doing predictive read-ahead, so that the next read request from Oracle may find that the SAN has already loaded the required data into its cache.)
There is, however, the second point that these figures highlight – but you have to be in the know to spot the detail: whatever the complexities introduced by SAN caching, we’re not comparing the right numbers. The ioseektim and iotfrspeed shown here are the default values used by Oracle. It looks as if the user has called dbms_stats.gather_system_stats() with a 48 hour workload (dstart = 8th Oct, dstop = 10th Oct) but hasn’t yet executed the procedure using the ‘noworkload’ option. Perhaps the ioseektim and iotfrspeed figures from a noworkload call would look a little more reasonable when compared with the 4.716 milliseconds of the gathered sreadtim.
There may still be a large gap between the model and the reality, but until the two sets of figures we’re using come from the same place we shouldn’t be comparing them.