Wednesday, February 18, 2009

Which SQL are doing a lot of disk I/O

SELECT *
  FROM (  SELECT INST_ID,
                 SUBSTR (SQL_TEXT, 1, 500) SQL,
                 ELAPSED_TIME,
                 CPU_TIME,
                 DISK_READS,
                 EXECUTIONS,
                 DISK_READS / EXECUTIONS "Reads/Exec",
                 HASH_VALUE,
                 ADDRESS
            FROM GV$SQLAREA
           WHERE (INST_ID, HASH_VALUE, ADDRESS) IN
                    (SELECT DISTINCT INST_ID, HASH_VALUE, ADDRESS
                       FROM GV$SQL_PLAN
                      WHERE DISTRIBUTION IS NOT NULL)
                 AND DISK_READS > 100
                 AND EXECUTIONS > 0
        ORDER BY ELAPSED_TIME DESC)
 WHERE ROWNUM <= 30;

No comments:

Post a Comment