WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sys_time_model ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char (BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, ' hh24:mi') Elapsed, round((e_value - nvl(b_value, 0)) / (1000000 * 60), 2) per_M_dbtime
from sysstat
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
order by 1 desc;
查询最近7天的负载，放到excel中，插入曲线图，就可以看到负载的增长情况了，像下面也可以对比不同时间段的性能情况。
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sys_time_model ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char (BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, ' hh24:mi') Elapsed, round((e_value - nvl(b_value, 0)) / (1000000 * 60), 2) per_M_dbtime
from sysstat
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
order by 1 desc;
查询最近7天的负载，放到excel中，插入曲线图，就可以看到负载的增长情况了，像下面也可以对比不同时间段的性能情况。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26390465/viewspace-1764805/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
由于数据类型不一致导致的sql查询慢
请登录后发表评论
登录
全部评论
