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.
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
Thank you for your comment. I enjoy doing the blog and hope it is helpful to others.
Thank you Bobby – almost 2 years ago and you’re still helping me out =]
Thanks for your comment. Glad to hear that someone found it useful.
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
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
That is strange. Thanks Bobby for checking. I should probably start investigating this as a bug.
Thanks again
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.
It is a bug…. NOTE:2182080.1 – AWR Tablespace And Datafiles Av Read (ms) Is Incorrect After Upgrade to 12c
I think you need ABS function because some periods return negative values and you cannot get negative io_wait.
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
and use a.BLOCK_SIZE not block size from v$parameters
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
Bug 25416731 causes DBA_HIST_FILESTATXS to return 0 rows in Oracle 12.2, 18, and 19.