Tuesday, August 27, 2013

Which SQL are doing a lot of disk I/O

Which SQL are doing a lot of disk I/O
                                                

Which SQL are doing a lot of disk I/O
SELECT * FROM 
   (SELECT  SUBSTR(sql_text,1,500) SQL,  
            ELAPSED_TIME, CPU_TIME, 
            disk_reads, executions,  
            disk_reads/executions "Reads/Exec", 
            hash_value,address   
     FROM V$SQLAREA   
     WHERE  ( hash_value, address ) IN (  
         SELECT DISTINCT HASH_VALUE, address 
           FROM v$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