oracle 資料庫 資料表空間最近2小時 io 統計資訊

oracle 資料庫 資料表空間最近2小時 io 統計資訊

——————————————————————————

——————for single instance ——————————

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;