Tuesday, August 27, 2013

Which segments have top Logical I/O & Physical I/O

Which segments have top Logical I/O & Physical I/O
                                                

Summary
Do you know which segments in your Oracle Database have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and helps to target tuning efforts on these segments:
SELECT ROWNUM AS Rank, Seg_Lio.* FROM 
         (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'logical reads'
         ORDER BY St.VALUE DESC) Seg_Lio
 WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_r.* FROM 
         (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Reads' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'physical reads'
         ORDER BY St.VALUE DESC) Seq_Pio_r
 WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_w.* FROM 
         (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Writes' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'physical writes'
         ORDER BY St.VALUE DESC) Seq_Pio_w
 WHERE ROWNUM <= 10;

No comments :

Post a Comment