How to capture segments with high db block changes?
Posted: June 17, 2014 Filed under: General | Tags: block, capture, changes, db, high, segments Leave a commentHow to capture segments with high db block changes?
select a.inst_id,
a.statistic_name,
a.owner,
a.object_name,
a.object_type,
a.value,
(a.value / b.sum_value) * 100 perc
from (select *
from (select inst_id,
owner,
object_name,
object_type,
value,
rank() over(partition by inst_id, statistic_name order by value desc) rnk,
statistic_name
from gv$segment_statistics
where value > 0)
where rnk < 11) a,
(select inst_id, statistic_name, sum(value) sum_value
from gv$segment_statistics
group by statistic_name, inst_id) b
where a.statistic_name = b.statistic_name
and a.inst_id = b.inst_id
and a.statistic_name = ‘db block changes’
order by a.statistic_name, a.value desc;