0227show all segment level statistics

简介: [20180227]show all segment level statistics.txt https://orainternals.wordpress.com/2013/06/12/dude-where-is-my-redo/ REM Author : Ri...

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

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

目录
相关文章
有趣的 events_statements_current 表问题
有趣的 events_statements_current 表问题
154 0
|
关系型数据库 测试技术 Oracle
[20180102]statistics_level=BASIC.txt
[20180102]statistics_level=BASIC.txt --//一个测试环境不知道谁设置statistics_level=BASIC,导致重启出现错误,自己在测试环境模拟看看: SYS@book> create pfile='/tmp/@.
1250 0
|
关系型数据库 数据库管理 Oracle
|
Oracle 关系型数据库 SQL
【MOS】EVENT: DROP_SEGMENTS - cleanup of TEMPORARY segments (文档 ID 47400.1)
【MOS】EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (文档 ID 47400.1) ***Checked for relevance on 14-Jun-2012*** ...
1198 0