[20180227]show all segment level statistics.txt
https://orainternals.wordpress.com/2013/06/12/dude-where-is-my-redo/
REM Author : Riyaj Shamsudeen
prompt To show all segment level statistics in one screen
prompt
set lines 140 pages 100
col owner format A12
col object_name format A30
col statistic_name format A30
col object_type format A10
col value format 99999999999
col perc format 99.99
undef statistic_name
break on statistic_name
with segstats as (
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 and statistic_name like '%'||'&&statistic_name' ||'%'
) where rnk <31
) ,
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id)
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 segstats a , sumstats b
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
order by a.statistic_name, a.value desc
/
with segstats as (
select * from (
select inst_id, owner, object_name, object_type , statistic_name , value,
rank () over (partition by inst_id, statistic_name order by value desc ) rnk
from (
select inst_id, owner, object_name, object_type , statistic_name , sum(value) value
from gv$segment_statistics
where value >0 and statistic_name like '%'||'&&statistic_name' ||'%'
group by inst_id, owner, object_name, object_type, statistic_name
)
) where rnk <31
),
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id)
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 segstats a , sumstats b
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
order by a.statistic_name, a.inst_id, a.value desc
/
--//做一个记录,也许以后工作有用.
