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 /
Search This Blog
Total Pageviews
Monday, 17 October 2011
Oracle Top IO objects user wise
Top IO objects
Top 20 IO OBJECTS by owner
[code]
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)
No comments:
Post a Comment