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
, filename
, from
, extract
Interval
oracle数据库io、统计oracle数据库大小、oracle数据库统计信息、oracle数据库统计函数、数据库io,以便于您获取更多的相关知识。
时间: 2024-08-03 22:37:30