[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
/

--//做一个记录,也许以后工作有用.