Oracle Top IO objects user wise

Top IO objects Top 20 IO OBJECTS by owner [code]

set linesize 200 pagesize 100
select * from (
select t.* from (
select owner,object_name,
round(100*(sum(decode( statistic_name, 'logical reads', VALUE,null)) /sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as l_reads,
round(100*(sum(decode( statistic_name, 'physical reads', VALUE,null))/sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_reads,
round(100*(sum(decode( statistic_name, 'physical reads direct', VALUE,null))/sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_reads_d,
round(100*(sum(decode( statistic_name, 'physical writes', VALUE,'physical writes direct', VALUE,null)) /sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_writes,
round(100*sum(value)/sum(sum(value)) over(),2) AS total
from gv$segment_statistics
where object_type in ('TABLE', 'INDEX')
--and owner='OWNER'
and owner not in ('SYS', 'SYSTEM')
and statistic_name in ('logical reads','physical reads', 'physical reads direct', 'physical writes', 'physical writes direct')
group by object_name,owner
order by sum(value) desc )t 
where rownum < 21)
/






set linesize 300 pagesize 300
col object_name 		for a25
col owner 				for a20
col statistic_name  	for a20

SELECT ROWNUM AS RANK, Seg_Lio.*
FROM ( SELECT 
       -- St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
		'LIO' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'logical reads'
		ORDER BY St.VALUE DESC) Seg_Lio
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_r.* 
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
		'PIO Reads' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'physical reads'
		ORDER BY St.VALUE DESC) Seq_Pio_r
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_w.*
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
        St.Obj#,
        St.Object_Type,
        St.Object_Name,
        St.VALUE,
        'PIO Writes' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'physical writes'
		ORDER BY St.VALUE DESC) Seq_Pio_w
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_w.*
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
        'PIO READS Direct' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'physical reads direct'
		ORDER BY St.VALUE DESC) Seq_Pio_w
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20
 UNION ALL
		SELECT ROWNUM AS RANK, Seq_Pio_w.*
		FROM ( SELECT 
		--St.Statistic_Name,
		St.Owner,
		St.Obj#,
		St.Object_Type,
		St.Object_Name,
		St.VALUE,
		'DB Block changes' AS Unit
		FROM gv$segment_Statistics St
		WHERE St.Statistic_Name = 'db block changes'
		ORDER BY St.VALUE DESC) Seq_Pio_w
		WHERE owner not in ('SYS', 'SYSTEM') 
		and object_type='TABLE' 
		and ROWNUM <= 20;
		




define statistic_name='physical writes'

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
/