Oracle database recent two-hour data files and IO statistics

Select E.tsname, substr (E.filename, 1,) filename, E.PHYRDS-NVL (b.phyrds, 0) reads, (e.phy  
               RDS-NVL (b.phyrds, 0))/(SELECT EXTRACT (Day from E.end_interval_time-b.end_interval_time) * 86400 + EXTRACT (HOUR from E.end_interval_time-b.end_interval_time) * 3600 + EXTRACT (MI Nute from E.end_interval_time-b.end_interval_time) * + EXTRACT (SECOND from E.END_INTERVAL_TIME-B.E Nd_interval_time) from Dba_hist_snapshot B, dba_hist_snapshot E WHERE b.snap_id = (select snap_id F  
           Rom dba_hist_snapshot where end_interval_time >sysdate-3/24 and End_interval_time <sysdate-2/24 and ROWNUM&LT;2) and e.snap_id = (select snap_id from Dba_hist_snapshot where End_interval_time > Sysdate-1/24 and Rownum<2  
           ) and B.dbid = (select DBID from v$database) and e.dbid = (select DBID from V$database) and B.instance_numbER = 1 and E.instance_number = 1 and b.startup_time = E.startup_time and B.end_inte  
              Rval_time < E.end_interval_time) RPS, Decode (E.PHYRDS-NVL (b.phyrds, 0)), 0, To_number (NULL), ((E.READTIM-NVL (b.readtim, 0))/(E.PHYRDS-NVL (b.phyrds, 0)) * 10) ATPR, Decode (E.PHYRDS-NVL (b.phyrds, 0)), 0, To_number (NULL), (  E.PHYBLKRD-NVL (B.PHYBLKRD, 0))/(E.PHYRDS-NVL (b.phyrds, 0)) BPR, E.PHYWRTS-NVL (B.phywrts, 0) writes, (E.PHYWRTS-NVL (b.phywrts, 0))/(SELECT EXTRACT (Day from E.end_interval_time-b.end_inter Val_time) * 86400 + EXTRACT (HOUR from E.end_interval_time-b.end_interval_time) * 3600 + EXTRACT (MINUTE from E.end_interval_time-b.end_interval_time) * + EXTRACT (SECOND F ROM E.end_interval_time-B.end_interval_time) from Dba_hist_snapshot B, dba_hist_snapshot E WHERE b.snap_id = (select SNAP _id from Dba_hist_snapshot where End_interval_time >sysdate-3/24 and End_interval_time <sysdate-2/24 and rownum &LT;2) and e.snap_id = (select snap_id from Dba_hist_snapshot where End_interval_time > Sysdate-1/24 and R OWNUM&LT;2) and B.dbid = (select DBID from v$database) and e.dbid = (select DBID from V$database ) and B.instance_number = 1 and E.instance_number = 1 and b.startup_time = E.startu P_time and B.end_interval_time < E.end_interval_time) WPS, E.WAIT_COUNT-NVL (b.wait_count, 0) WA  Its, decode (E.WAIT_COUNT-NVL (b.wait_count, 0)), 0, 0, ((e.time -NVL (b.time, 0))/(E.WAIT_COUNT-NVL (b.wait_count, 0)) ATPWT from Dba_hist_filestatxs E, DB A_hist_filestatxs b whereb.snap_id = (select snap_id from Dba_hist_snapshot where End_interval_time >sysdate-3/24 and End_interval_time <sys DATE-2/24 and rownum<2) and e.snap_id = (select snap_id from Dba_hist_snapshot where End_interval_time > Sysdat  
   E-1/24 and rownum<2) and b.dbid = (select dbid from v$database) and e.dbid = (select dbid from V$database) and b.dbid = e.dbid and B.instance_number = 1 and E.instance_number = 1 and B.instance_number = E.instan Ce_number and b.tsname = e.tsname and b.file# = e.file# and b.creation_change# = e.creation_change# an D ((E.PHYRDS-NVL (b.phyrds, 0)) + (E.PHYWRTS-NVL (b.phywrts, 0)) > 0 Union ALL Select E.tsname, substr (E. FileName, 1, E.PHYRDS-NVL (b.phyrds, 0) reads, (E.PHYRDS-NVL (b.phyrds, 0))/( SELECT EXTRACT (Day from E.end_interval_time-b.end_interval_time) * 86400 + EXTRACT (HOU R from E.end_interval_timE-b.end_interval_time) * 3600 + EXTRACT (MINUTE from E.end_interval_time-b.end_interval_time) * 60 + EXTRACT (SECOND from E.end_interval_time-b.end_interval_time) from Dba_hist_snapshot B, dba_h Ist_snapshot E where b.snap_id = (select snap_id from Dba_hist_snapshot where End_interval_time &GT;SYSDATE-3/2 4 and End_interval_time <sysdate-2/24 and rownum<2) and e.snap_id = (select snap_id from Dba_hist_snaps  
           Hot where End_interval_time > Sysdate-1/24 and rownum<2) and b.dbid = (select DBID from V$database) and E.dbid = (select DBID from v$database) and B.instance_number = 1 and E.instance_nu Mber = 1 and b.startup_time = E.startup_time and B.end_interval_time < E.end_interval_time) R PS, Decode (E.PHYRDS-NVL (b.phyrds, 0)), 0, To_number (NULL), ((E . READTIM-NVL (B.readtim, 0))/(E.PHYRDS-NVL (b.phyrds, 0))) ATPR, Decode (E.PHYRDS-NVL (b.phyrds, 0)), 0, To_number (NULL), (E.PHYBLKRD-NVL (B.PHYBLKRD, 0))/(E.phyrds  
       -NVL (b.phyrds, 0)) BPR, E.PHYWRTS-NVL (b.phywrts, 0) writes, (E.PHYWRTS-NVL (b.phywrts, 0)) (SELECT EXTRACT (Day from E.end_interval_time-b.end_interval_time) * 86400 + EXTRACT (HOUR from E.end_interval_time-b.end_interval_time) * 3600 + EXTRACT (MINUTE from E.end_interval_time- B.end_interval_time) * + EXTRACT (SECOND from E.end_interval_time-b.end_interval_time) FR OM dba_hist_snapshot B, dba_hist_snapshot E where b.snap_id = (select snap_id from Dba_hist_snapshot where End_ Interval_time >sysdate-3/24 and End_interval_time <sysdate-2/24 and rownum<2) and e.snap_id = (Selec T snap_id from Dba_hist_SNAPSHOT where End_interval_time > Sysdate-1/24 and rownum<2) and b.dbid = (select DBID from V$database ) and E.dbid = (select DBID from v$database) and B.instance_number = 1 and E.instan Ce_number = 1 and b.startup_time = E.startup_time and B.end_interval_time < E.end_interval_ti  
              ME) WPS, E.WAIT_COUNT-NVL (b.wait_count, 0) waits, decode (E.WAIT_COUNT-NVL (b.wait_count, 0)),  0, To_number (NULL), ((E.TIME-NVL (b.time, 0))/(E.wait_count- NVL (b.wait_count, 0)) ATPWT from Dba_hist_tempstatxs E, Dba_hist_tempstatxs b where b.snap_id = (select SNA p_id from Dba_hist_snapshot where End_interval_time >sysdate-3/24 and End_interval_time <sysdate-2/24 and rownum& LT;2) and e.snap_id = (select snap_id from Dba_hist_snapshot where End_interval_time > Sysdate-1/24 and rownum< 2) and b.dbid = (SelecT dbid from v$database) and e.dbid = (select dbid from v$database) and b.dbid = E.dbid, and B.instance_numbe R = 1 and E.instance_number = 1 and B.instance_number = E.instance_number and b.tsname = E.tsname and b.file# = e.file# and b.creation_change# = e.creation_change# and (E.PHYRDS-NVL (b.phyrds, 0)) + (E.phywrts- NVL (b.phywrts, 0)) > 0 ORDER by tsname, filename;