In my experience, a ratio of 15 reads to every write is very much to be
expected (i.e. workload of 93-94% reads, 6-7% writes) for almost all
database workloads. I would be shocked if it were outside this range,
especially using V$SYSSTAT data averaged over 2 years.
It strikes me that there is not a great deal that can be understood just
from I/O counts. I/O is always measured at least three ways...
- counts (I/O requests per second or IOPS) <== this is what you're
seeking...
- throughput (MB/s)
- latency (s, ms, us, or ns)
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
For I/O latency, Oracle also presents some information using METRIC_NAME
of...
