行迁移检测分析脚本

select snap_id,
to_char(snap_time, ‘yyyy-mm-dd hh24:mi’) snap_time,
v1 “table fetch continued row”,
v2 “table fetch by rowid”,
decode(v2, 0, 0, trunc(v1 / v2 * 100, ’2′)) || ‘%’ “chain to rowid scan(%)”,
v3 “table scan rows gotten”,
decode(v3, 0, 0, trunc(v1 / v3 * 100, ’2′)) || ‘%’ “chain to all scan(%)”
from (select a.snap_id,
b.snap_time,
max(decode(a.stat_name,
‘table fetch continued row’,
trunc(a.value / b.snap_interval),
0)) v1,
max(decode(a.stat_name,
‘table fetch by rowid’,
trunc(a.value / b.snap_interval),
0)) v2,
max(decode(a.stat_name,
‘table scan rows gotten’,
trunc(a.value / b.snap_interval),
0)) v3
from (select snap_id,
stat_name,
decode(sign(value – lag(value, 1)
over(partition by stat_name order by
snap_id)),
-1,
value,
value – lag(value, 1)
over(partition by stat_name order by snap_id)) value
from dba_hist_sysstat
where stat_name in
(‘table fetch continued row’, ‘table fetch by rowid’,
‘table scan rows gotten’)) a,
(select snap_id,
snap_time,
trunc((snap_time – lag(snap_time)
over(order by snap_id)) * 24 * 60) snap_interval
from (select snap_id,
to_date(to_char(begin_interval_time,
‘yyyy-mm-dd hh24:mi:ss’),
‘yyyy-mm-dd hh24:mi:ss’) snap_time
from dba_hist_snapshot
where to_char(begin_interval_time, ‘yyyymmdd’) between
‘&start_yyyymmdd’ and ‘&end_yyyymmdd’)) b
where a.snap_id = b.snap_id
group by a.snap_id, b.snap_time)
order by snap_id;

Trackback

no comment untill now

Add your comment now

切换到手机版