脚本内容
-- 特定时间段内数据库资源消耗
select * from (
select
s.snap_id,stat_name,
value/100 - lag(value) over (partition by stat_name,s.instance_number order by s.snap_id)/100 value,
to_char(begin_interval_time,'MMDD HH24MI"-"') || to_char(end_interval_time,'HH24MI') as SNAP_TIME,
round((to_date(to_char(end_interval_time,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') - to_date(to_char(begin_interval_time,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'))*86400) as seconds
from dba_hist_sysstat s , dba_hist_snapshot snap
where
snap.snap_id = s.snap_id
and s.instance_number = snap.instance_number
and begin_interval_time > sysdate - nvl('&num_of_dates',3)
and s.stat_name in ('DB time','CPU used by this session','user I/O wait time','concurrency wait time','application wait time','cluster wait time')
)
pivot
(sum(value) as "SUM"
for stat_name in ('DB time' as DBTIME,
'CPU used by this session' as DBCPU,
'user I/O wait time' as IOWAIT,
'concurrency wait time' as CONCWAIT ,
'application wait time' as LOCKWAIT,
'cluster wait time' as RACWAIT))
评论
贡献排行榜