重庆思庄Oracle、Redhat认证学习论坛
标题: Oracle 表空间的I/O统计 [打印本页]
作者: apollo 时间: 2014-11-11 21:08
标题: Oracle 表空间的I/O统计
查询过去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;
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |