Skip to Main Content
  • Questions

  • new 11g metric - I/O Megabytes per second

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Chris.

Asked: March 02, 2010 - 11:58 pm UTC

Last updated: March 03, 2010 - 8:21 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Tom,

Thanks again for all your contributions to the Oracle community. This site is truly invaluable.


I have a question about a new metric I stumbled upon named "I/O Megabytes per Second". You can find this in the metric_name column of dba_hist_sysmetric_summary, and in the docs here:
http://download.oracle.com/docs/html/E16282_01/oracle_database_help/oracle_database_instance_throughput_iombs_ps.html

In any case, I ran a query like this on a dev machine:

col metric_name format a34
col snap_id format 99999
col begin_time format a20
col end_time format a20

select metric_name, maxval, snap_id, begin_time, end_time
from dba_hist_sysmetric_summary
where metric_name = 'I/O Megabytes per Second'
 and maxval = (select max(maxval)
   from dba_hist_sysmetric_summary
   where metric_name = 'I/O Megabytes per Second')
union all
select metric_name, maxval, snap_id, begin_time, end_time
from dba_hist_sysmetric_summary
where metric_name = 'Physical Read Total Bytes Per Sec'
 and maxval = (select max(maxval)
   from dba_hist_sysmetric_summary
   where metric_name = 'Physical Read Total Bytes Per Sec')
union all
select metric_name, maxval, snap_id, begin_time, end_time
from dba_hist_sysmetric_summary
where metric_name = 'Physical Write Total Bytes Per Sec'
 and maxval = (select max(maxval)
   from dba_hist_sysmetric_summary
  where metric_name = 'Physical Write Total Bytes Per Sec')
/



METRIC_NAME          MAXVAL SNAP_ID BEGIN_TIME    END_TIME
---------------------------------- ---------- ------- -------------------- --------------------
I/O Megabytes per Second    13.9833333  4846 01-mar-2010 18:00:24 01-mar-2010 19:00:24
Physical Read Total Bytes Per Sec   9632563.2  4846 01-mar-2010 18:00:24 01-mar-2010 19:00:24
Physical Write Total Bytes Per Sec 20144785.1  4846 01-mar-2010 18:00:24 01-mar-2010 19:00:24



Now, on this particular machine, it just so happened that all three statistics had their maximum values during the same interval. However, I then noticed something peculiar. If we convert say the "Physical Write Total Bytes Per Sec" into MB/s we get 20144785.1 / 1024 / 1024 = 19.21 MB/s. Well, not even considering the read portion, this value is well above the reported "I/O Megabytes per Second" of 13.98. Since they are both from the same snap interval, I don't see how this is possible.



I tried this on another machine and got the following:

METRIC_NAME          MAXVAL SNAP_ID BEGIN_TIME    END_TIME
---------------------------------- ---------- ------- -------------------- --------------------
I/O Megabytes per Second    .849575212  6379 24-feb-2010 21:59:37 24-feb-2010 22:59:38
Physical Read Total Bytes Per Sec  1886849.26  6499 01-mar-2010 22:00:40 01-mar-2010 23:00:40
Physical Write Total Bytes Per Sec 974796.467  6452 27-feb-2010 23:00:39 28-feb-2010 00:00:39




If I query for "I/O Megabytes per Second" in the interval that had the highest "Physical Read Total Bytes Per Sec", I get the folowing:

select maxval from dba_hist_sysmetric_summary where metric_name = 'I/O Megabytes per Second' and snap_id = 6499
/

    MAXVAL
----------
.033333333





Well, if during that same interval I did 1886849.26 / 1024 / 1024 = 1.8 MB/s reading, then I/O Megabytes per Second (which represents both reading and writing) cannot be below this value--and it is-- .8495 from above.

I was wondering if you see anything obvious that I've missed?

As a side note, there is a related metric "I/O Requests per Second" that does seem to add up closely when comparing "Physical Read Total IO Requests Per Sec" and "Physical Write Total IO Requests Per Sec".

It just seem the Megabytes per Second stat is a little off.

Thoughts?

Regards,
Chris




and Tom said...

All of these are sampled - they are digital observations - snapshots, sampled "as of a point in time". And they are not sample atomically - we don't get all metrics at the same precise exact point in time. So, they will be close (you should see a correlation between the rise and fall of READS+WRITES and IO rates - but it'll never be exact the same).

They look closer than close enough from here - remember that they are sampled. Just like ASH isn't nearly as precise as a TKPROF would be - an ASH report would be close to a TKPROF but nowhere near the "fidelity". These numbers are better than good enough - but they won't be exactly the same - because they are all sampled at slightly different points in time - by different modules - perhaps even on slightly different frequencies.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

feedback

Chris, March 03, 2010 - 4:12 pm UTC

Tom,

Thanks for taking the time to respond. I agree with your analysis that the snapshots are not atomic, and therefore the results will never align exactly, but the numbers should be close. I did more extensive testing today, and I can say that for the "I/O Requests per Second" metric, this is definitely the case. That being said, the "I/O Megabytes per Second" is a different story altogether. The numbers I posted yesterday were from a mostly idle system (so the MB/s were pretty low). Today I maxed the IO out and re-ran the comparison. Here's what it looks like:

set numwidth 5

select * from (
with data1 as (
 select snap_id, maxval io
 from dba_hist_sysmetric_summary
 where metric_name = 'I/O Requests per Second'
),
data2 as (
 select snap_id, maxval r
 from dba_hist_sysmetric_summary
 where metric_name = 'Physical Read Total IO Requests Per Sec'
),
data3 as (
 select snap_id, maxval w
 from dba_hist_sysmetric_summary
 where metric_name = 'Physical Write Total IO Requests Per Sec'
)
select a.snap_id, io, r+w, 100* abs(io - (r+w)) / (io + (r+w)) pct_diff
from data1 a, data2 b, data3 c
where a.snap_id = b.snap_id
 and b.snap_id = c.snap_id
order by snap_id desc
)
where rownum = 1
/

SNAP_ID    IO R+W PCT_DIFF
------- ----- ----- --------
   6539 74.69 86.96    7.591


Indeed these numbers (74.69 and 86.96) are close enough for me. Now onto the MB/s:

select * from (
with data1 as (
 select snap_id, maxval io
 from dba_hist_sysmetric_summary
 where metric_name = 'I/O Megabytes per Second'
),
data2 as (
 select snap_id, maxval / 1024 / 1024 r
 from dba_hist_sysmetric_summary
 where metric_name = 'Physical Read Total Bytes Per Sec'
),
data3 as (
 select snap_id, maxval / 1024 / 1024 w
 from dba_hist_sysmetric_summary
 where metric_name = 'Physical Write Total Bytes Per Sec'
)
select a.snap_id, io, r+w, 100* abs(io - (r+w)) / (io + (r+w)) pct_diff
from data1 a, data2 b, data3 c
where a.snap_id = b.snap_id
 and b.snap_id = c.snap_id
order by a.snap_id desc
)
where rownum = 1
/



SNAP_ID    IO R+W PCT_DIFF
------- ----- ----- --------
   6539 3.366 15.91    65.08



Now, this is not even close. A 5x difference in the reported numbers is just not right. For some metrics, a large discrepancy may not be that big of a deal. But if someone were to use this for I/O sizing / analysis, there is a large difference (both in money and hardware) in sizing a SAN that can support say 200MB/s versus one that do 1000MB/s.

Is it possible this metric is not getting pegged properly?


Actually, I just ran the last query again after the top of the hour, and it looks like the metric "reset" (i.e. it is waaaay low now):
SNAP_ID    IO R+W PCT_DIFF
------- ----- ----- --------
   6540 .0333 14.52    99.54



SYS@testbox> !date
Wed Mar  3 16:05:47 CST 2010


select distinct begin_time, end_time from dba_hist_sysmetric_summary where snap_id = 6540;

BEGIN_TIME      END_TIME
-------------------- --------------------
03-mar-2010 14:59:41 03-mar-2010 16:00:41




Regards,
Chris

what type of IO is it?

Kuldeep, October 30, 2014 - 4:01 pm UTC

about "I/O Requests per Second by I/O Function"
1) are these OS block IO's or Oracle BLOCK IO's
2) In both cases(OS block or Oracle Blocks) these should match (may be close to "I/O Megabytes per Second by I/O Function"

Can you please give example on calulation as how they are calculated from v$sysmetric_summary performance view?

Regards
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.