Overall I/O Query

I hacked together a query today that shows the overall I/O performance that a database is experiencing.

The output looks like this:

End snapshot time   number of IOs ave IO time (ms) ave IO size (bytes)
------------------- ------------- ---------------- -------------------
2015-06-15 15:00:59        359254               20              711636
2015-06-15 16:00:59        805884               16              793033
2015-06-15 17:00:13        516576               13              472478
2015-06-15 18:00:27        471098                6              123565
2015-06-15 19:00:41        201820                9              294858
2015-06-15 20:00:55        117887                5              158778
2015-06-15 21:00:09         85629                1               79129
2015-06-15 22:00:23        226617                2               10744
2015-06-15 23:00:40        399745               10              185236
2015-06-16 00:00:54       1522650                0               43099
2015-06-16 01:00:08       2142484                0               19729
2015-06-16 02:00:21        931349                0                9270

I’ve combined reads and writes and focused on three metrics – number of IOs, average IO time in milliseconds, and average IO size in bytes.  I think it is a helpful way to compare the way two systems perform.  Here is another, better, system’s output:

End snapshot time   number of IOs ave IO time (ms) ave IO size (bytes)
------------------- ------------- ---------------- -------------------
2015-06-15 15:00:25        331931                1              223025
2015-06-15 16:00:40        657571                2               36152
2015-06-15 17:00:56       1066818                1               24599
2015-06-15 18:00:11        107364                1              125390
2015-06-15 19:00:26         38565                1               11023
2015-06-15 20:00:41         42204                2              100026
2015-06-15 21:00:56         42084                1               64439
2015-06-15 22:00:15       3247633                3              334956
2015-06-15 23:00:32       3267219                0               49896
2015-06-16 00:00:50       4723396                0               32004
2015-06-16 01:00:06       2367526                1               18472
2015-06-16 02:00:21       1988211                0                8818

Here is the query:

select 
to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') "End snapshot time",
sum(after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS) "number of IOs",
trunc(10*sum(after.READTIM+after.WRITETIM-before.WRITETIM-before.READTIM)/
sum(1+after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS)) "ave IO time (ms)",
trunc((select value from v$parameter where name='db_block_size')*
sum(after.PHYBLKRD+after.PHYBLKWRT-before.PHYBLKRD-before.PHYBLKWRT)/
sum(1+after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS)) "ave IO size (bytes)"
from DBA_HIST_FILESTATXS before, DBA_HIST_FILESTATXS after,DBA_HIST_SNAPSHOT sn
where 
after.file#=before.file# and
after.snap_id=before.snap_id+1 and
before.instance_number=after.instance_number and
after.snap_id=sn.snap_id and
after.instance_number=sn.instance_number
group by to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')
order by to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS');

I hope this is helpful.

– Bobby

P.S. Here is an updated version of the query using suggestions from the comments below:

select 
to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') "End snapshot time",
sum(after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS) "number of IOs",
trunc(10*sum(after.READTIM+after.WRITETIM-before.WRITETIM-before.READTIM)/
sum(1+after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS)) "ave IO time (ms)",
trunc(sum(after.block_size * (after.PHYBLKRD+after.PHYBLKWRT-before.PHYBLKRD-before.PHYBLKWRT))/
sum(1+after.PHYRDS+after.PHYWRTS-before.PHYWRTS-before.PHYRDS)) "ave IO size (bytes)"
from DBA_HIST_FILESTATXS before, DBA_HIST_FILESTATXS after,DBA_HIST_SNAPSHOT sn
where 
after.file#=before.file# and
after.snap_id=before.snap_id+1 and
before.instance_number=after.instance_number and
after.snap_id=sn.snap_id and
after.instance_number=sn.instance_number
group by to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')
HAVING 
SUM(after.PHYRDS + after.PHYWRTS - before.PHYWRTS - before.PHYRDS) >= 0 AND 
SUM(after.PHYBLKRD + after.PHYBLKWRT - before.PHYBLKRD - before.PHYBLKWRT) >= 0
order by to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS');

PPS. Bug 25416731 makes this script return 0 rows for 12.2, 18, and 19 version databases.

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

14 Responses to Overall I/O Query

  1. Thank you Bobby, good scripts and your blog is also very useful for other DBAs like me..I am your frequent reader…waiting for new stuffs and experience notes

  2. Leo Ciccone says:

    Thank you Bobby – almost 2 years ago and you’re still helping me out =]

  3. Karthik says:

    Thanks Bobby. Your blog helped me.

    One question, I have one business requirement to get the total physical reads in MB per AWR snap. I can get this values from AWR but I am trying to get the values from DBA_HIST_FILESTATXS(To automate) and I get very different values between AWR and DBA_HIST_FILESTATXS.

    Looks like I am doing something fundamentally wrong. Any thoughts where I am going wrong bobby?

    For example:
    Data from DBA_HIST_FILESTATXS
    ============================
    sum(PHYBLKRD) in Snap2: 11003716519
    sum(PHYBLKRD) in Snap1: 11000112159

    Actual physical block reads in Snap2(Delta)= 11003716519-11000112159= 3604360

    Data from AWR:
    ============
    Physical read (blocks) per second = 46,066.4
    Since AWR Snapshot Interval is 60Minutes = 46,066.4 * 3600 = 165839040

    • Bobby says:

      I did a quick check and the two came out pretty close. This is on a 12.1.0.2 Linux 64 bit test database that is not very active.

      AWR report had this:

      Physical read (blocks): 0.6 (per second)

      It was 03-Jan-18 10:00:42 to 31-Jan-18 12:00:11 which is about 2426400 seconds.

      2426400 * .6 = 1455840 blocks read

      Looking at DBA_HIST_FILESTATXS for the first snap we had SUM(PHYBLKRD) = 20009.
      For the second 1210331.

      1210331 – 20009 = 1190322 blocks read

      1455840 for awr and 1190322 for FILESTATXS seem pretty close since the AWR per second measure is probably rounded to one digit after the decimal point.

      Bobby

      • Karthik says:

        That is strange. Thanks Bobby for checking. I should probably start investigating this as a bug.

        Thanks again

        • Bobby says:

          I don’t know if it is a bug. I think that the AWR probably gets its information from a different view than DBA_HIST_FILESTATXS. I think it is one of the database statistics. There may be situations where these differ. It was interesting to me that your two values divided pretty evenly. One was about 46 times the other. But, I didn’t see that kind of difference in my quick test. There are several different views that display I/O information and sometimes it is hard to reconcile them because they don’t represent exactly the same things. You might look at the File IO Stats part of the AWR. It might come from DBA_HIST_FILESTATXS. Would be interesting to see if they line up.

          • Z_Man says:

            It is a bug…. NOTE:2182080.1 – AWR Tablespace And Datafiles Av Read (ms) Is Incorrect After Upgrade to 12c

  4. Damir Vadas says:

    I think you need ABS function because some periods return negative values and you cannot get negative io_wait.

    • Damir Vadas says:

      This happened if in AWR you have DB upgrade process, so numbers are not one by another bigger.
      It is fixed to avoid those records in a way to add
      HAVING SUM (a.PHYRDS + a.PHYWRTS – b.PHYWRTS-b.PHYRDS)>=0 AND SUM(a.PHYBLKRD+a.PHYBLKWRT – b.PHYBLKRD-b.PHYBLKWRT)>=0

      Hope this helps

  5. Damir Vadas says:

    and use a.BLOCK_SIZE not block size from v$parameters

    • Bobby says:

      Thanks for your comments. I updated the query and put the modified version in the P.S. at the end of the post. One strange thing though. I could not get my old query or the new one to work on 12.2, 18, or 19. It just returns zero rows.

      Bobby

Leave a Reply to Damir Vadas Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.