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