Wednesday, February 18, 2009

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

Summary
Do you know which segments in your database driven application 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.INST_ID,
                 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 ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS RANK, SEQ_PIO_R.*
  FROM (  SELECT ST.INST_ID,
                 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 ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS RANK, SEQ_PIO_W.*
  FROM (  SELECT ST.INST_ID,
                 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 ROWNUM <= 10;

No comments:

Post a Comment