Monday, February 4, 2019

SQL with huge I/O disk

SELECT * FROM
   (SELECT   
            sql_fulltext 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