Oracle 表空间的I/O统计

apollo

查询过去2小时的表空间IO统计

select e.tsname tsname, 

       sum(e.phyrds - nvl(b.phyrds, 0)) reads, 

       sum(e.phyrds - 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(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_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 = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time > sysdate-1/24 and rownum<2) 

           AND B.STARTUP_TIME = E.STARTUP_TIME 

           AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME) rps, 

       decode(sum(e.phyrds - nvl(b.phyrds, 0)), 

              0, 

              0, 

              10 * (sum(e.readtim - nvl(b.readtim, 0)) / 

              sum(e.phyrds - nvl(b.phyrds, 0)))) atpr, 

       decode(sum(e.phyrds - nvl(b.phyrds, 0)), 

              0, 

              0, 

              sum(e.phyblkrd - nvl(b.phyblkrd, 0)) / 

              sum(e.phyrds - nvl(b.phyrds, 0))) bpr, 

       sum(e.phywrts - nvl(b.phywrts, 0)) writes, 

       sum(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) * 60 + 

               EXTRACT(SECOND FROM 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<2) 

           AND E.SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time > sysdate-1/24 and rownum<2) 

           AND B.STARTUP_TIME = E.STARTUP_TIME 

           AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME) wps, 

       sum(e.wait_count - nvl(b.wait_count, 0)) waits, 

       decode(sum(e.wait_count - nvl(b.wait_count, 0)), 

              0, 

              0, 

              10 * (sum(e.time - nvl(b.time, 0)) / 

              sum(e.wait_count - nvl(b.wait_count, 0)))) atpwt, 

       sum(e.phyrds - nvl(b.phyrds, 0)) + 

       sum(e.phywrts - nvl(b.phywrts, 0)) ios 

  from dba_hist_filestatxs e, dba_hist_filestatxs b 

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 = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time > sysdate-1/24 and rownum<2) 

   and b.dbid = e.dbid 

   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 

group by e.tsname 

union all

select e.tsname tsname, 

       sum(e.phyrds - nvl(b.phyrds, 0)) reads, 

       sum(e.phyrds - 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(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_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 = ( select snap_id from DBA_HIST_SNAPSHOT where end_interval_time > sysdate-1/24 and rownum<2) 

           AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME) rps, 

       decode(sum(e.phyrds - nvl(b.phyrds, 0)), 

              0, 

              0, 

              (sum(e.readtim - nvl(b.readtim, 0)) / 

              sum(e.phyrds - nvl(b.phyrds, 0))) * 10) atpr, 

       decode(sum(e.phyrds - nvl(b.phyrds, 0)), 

              0, 

              to_number(NULL), 

              sum(e.phyblkrd - nvl(b.phyblkrd, 0)) / 

              sum(e.phyrds - nvl(b.phyrds, 0))) bpr, 

       sum(e.phywrts - nvl(b.phywrts, 0)) writes, 

       sum(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) * 60 + 

               EXTRACT(SECOND FROM 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<2) 

           AND E.SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time > sysdate-1/24 and rownum<2) 

           AND B.STARTUP_TIME = E.STARTUP_TIME 

           AND B.END_INTERVAL_TIME < E.END_INTERVAL_TIME) wps, 

       sum(e.wait_count - nvl(b.wait_count, 0)) waits, 

       decode(sum(e.wait_count - nvl(b.wait_count, 0)), 

              0, 

              0, 

              (sum(e.time - nvl(b.time, 0)) / 

              sum(e.wait_count - nvl(b.wait_count, 0))) * 10) atpwt, 

       sum(e.phyrds - nvl(b.phyrds, 0)) + 

       sum(e.phywrts - nvl(b.phywrts, 0)) ios 

  from dba_hist_tempstatxs e, dba_hist_tempstatxs b 

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 = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time > sysdate-1/24 and rownum<2) 

   and b.dbid = e.dbid 

   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 

group by e.tsname 

order by ios desc, tsname;