Querrys with highest execution time difference | SQL execution time difference Oracle







SELECT TO_CHAR(ss.BEGIN_INTERVAL_TIME,'DD.MM.YYYY HH24') AS hour, stat.sql_id ,trunc(sum(elapsed_time_delta/1000/1000))  as ELAPSED_TIME_DELTA_SEC,
        ( (max(elapsed_time_delta))/(min(elapsed_time_delta)) )  as diff
/*--PLAN_HASH_VALUE,BEGIN_INTERVAL_TIME,DISK_READS_DELTA,ROWS_PROCESSED_DELTA,STAT.SQL_ID*/
FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
  WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID
    AND SS.DBID = STAT.DBID
    AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
    AND STAT.SNAP_ID = SS.SNAP_ID
    AND BEGIN_INTERVAL_TIME > SYSDATE -5
    and elapsed_time_delta > 0 
   --and stat.sql_id ='4zqk56n8cqcff'
    GROUP BY TO_CHAR(ss.BEGIN_INTERVAL_TIME,'DD.MM.YYYY HH24'),stat.sql_id
    having  ( (max(elapsed_time_delta))/(min(elapsed_time_delta)) ) > 10 
        ORDER BY DIFF desc ;


 SELECT TO_CHAR(ss.BEGIN_INTERVAL_TIME,'DD.MM.YYYY HH24') AS hour, stat.sql_id, trunc(sum(elapsed_time_delta/1000/1000))  as ELAPSED_TIME_DELTA_SEC
/*--PLAN_HASH_VALUE,BEGIN_INTERVAL_TIME,DISK_READS_DELTA,ROWS_PROCESSED_DELTA,STAT.SQL_ID*/
FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
  WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID
    AND SS.DBID = STAT.DBID
    AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
    AND STAT.SNAP_ID = SS.SNAP_ID
    AND BEGIN_INTERVAL_TIME > SYSDATE -5
    and elapsed_time_delta > 0 
  and stat.sql_id ='58qnzrps7h4dr'
    GROUP BY TO_CHAR(ss.BEGIN_INTERVAL_TIME,'DD.MM.YYYY HH24'),stat.sql_id
  --  having  ( (max(elapsed_time_delta))/(min(elapsed_time_delta)) ) > 10 

        ORDER BY 2,3 desc ;    



Komentarze