oracle 数据库 最近两小时 数据文件 io 统计信息
select e.tsname,
substr(e.filename, 1, 52) filename,
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(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.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_INTERVAL_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_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.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_INTERVAL_TIME < E.END_INTERVAL_TIME) wps,
e.wait_count - nvl(b.wait_count, 0) waits,
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))) * 10) atpwt
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 = (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.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
union all
select e.tsname,
substr(e.filename, 1, 52) filename,
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(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.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_INTERVAL_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_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.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_INTERVAL_TIME < E.END_INTERVAL_TIME) 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))) * 10) atpwt
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 = (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.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;
结果输出为：
————————————————————————————————————————
select e.tsname,
substr(e.filename, 1, 52) filename,
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(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.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_INTERVAL_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_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.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_INTERVAL_TIME < E.END_INTERVAL_TIME) wps,
e.wait_count - nvl(b.wait_count, 0) waits,
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))) * 10) atpwt
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 = (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.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
union all
select e.tsname,
substr(e.filename, 1, 52) filename,
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(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.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_INTERVAL_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_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.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_INTERVAL_TIME < E.END_INTERVAL_TIME) 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))) * 10) atpwt
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 = (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.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;
TSNAME
------------------------------
FILENAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
READS RPS ATPR BPR WRITES WPS WAITS ATPWT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SYSAUX
/oracle/oradata/lixora/sysaux01.dbf
274 .038354953 13.2481752 1.90145985 1033 .144600973 0 0
SYSTEM
/oracle/oradata/lixora/system01.dbf
629 .088048415 1.3990461 1.02702703 147 .020577292 0 0
TEMP
/oracle/oradata/lixora/temp01.dbf
11 .001539797 0 3.63636364 11 .001539797 0
UNDOTBS2
/oracle/oradata/lixora/undotbs2.dbf
0 0 307 .042974345 0 0