Re: reads vs writes

I find counts or IOPS useful mainly if each I/O request is exactly the same size, such as the 4096 bytes used by IBM mainframes forever and ever, amen.  But Oracle RDBMS uses a variety of I/O request sizes, ranging from 512 bytes to 1 MByte and beyond, so counting I/Os or measuring IOPS is of limited use with Oracle database workloads because 1000 sequential reads of 512M each arguably cannot be compared to 1000 random-access single-block reads of 8K each.  Thus, IOPS and counts of I/Os are not very useful for any purpose, in general.

Throughput is a very useful metric for measuring and understanding I/O performance because it is a rate based on a constant unit of measure, usually MB/s.  When comparing a value of 50 MB/s to a value of 100 MB/s, it is safe to say that the latter value is greater.

Latency, the amount of time it takes for an I/O request to be fulfilled, is also dependent on the size of the I/O request, and thus difficult to obtain or compare without that qualification. If someone says that they are achieving I/O latency of 1 ms, then that is a suspect statement, because we don't know the I/O request size used, nor whether it was a read, write, or combination of reads and writes.  On the other hand, if they say they achieved I/O latency of 1 ms with 8 KB reads, then we have a useful value for comparison with other 8 KB reads.

Oracle attempts to provide more information in the views related to V$SYSMETRIC (i.e. including V$SYSMETRIC_HISTORY and DBA_HIST_SYSMETRIC_SUMMARY).  I would hope that, for the counts and latency metric, Oracle makes some attempt to "normalize" I/O request size and not just throw all data from all I/O requests into the same bucket, but I haven't seen any information one way or the other, so I tend to take Oracle's SYSMETRIC information for IOPS and latency with a bit of suspicion.

However, Oracle also presents some information on I/O throughput in these views, such as METRIC_NAME of...

• Physical Read Bytes Per Sec
• Physical Read Total Bytes Per Sec
• Physical Write Bytes Per Sec
• Physical Write Total Bytes Per Sec