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
Prześlij komentarz