Sql Execution Time From AWR Reports

SELECT
  SQL_ID,
  PLAN_HASH_VALUE,
  PARSING_SCHEMA_NAME,
  ELAPSED_TIME_DELTA,
  BEGIN_INTERVAL_TIME,
  AVG_ELAPSED_TIME_DELTA,
  MAX_ELAPSED_TIME_DELTA,
  CNT
  /*IN SECONDS */
   FROM (
SELECT SQL_ID,PLAN_HASH_VALUE,PARSING_SCHEMA_NAME,Trunc(ELAPSED_TIME_DELTA/1000000) AS ELAPSED_TIME_DELTA,BEGIN_INTERVAL_TIME ,
trunc((Trunc(avg(ELAPSED_TIME_DELTA) OVER (PARTITION BY sql_id)))/1000000)  AS avg_ELAPSED_TIME_DELTA ,
trunc((Trunc(max(ELAPSED_TIME_DELTA) OVER (PARTITION BY sql_id)))/1000000)  AS MAX_ELAPSED_TIME_DELTA,
trunc(count(ELAPSED_TIME_DELTA) OVER (PARTITION BY sql_id))  AS CNT
FROM
(
SELECT STAT.SQL_ID,SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA, STAT.SNAP_ID, SS.END_INTERVAL_TIME, ss.BEGIN_INTERVAL_TIME
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
)
               ) v 

               WHERE AVG_ELAPSED_TIME_DELTA > 1
               AND ELAPSED_TIME_DELTA =  MAX_ELAPSED_TIME_DELTA
               AND CNT > 100
               AND MAX_ELAPSED_TIME_DELTA > (AVG_ELAPSED_TIME_DELTA * 50)
               AND ELAPSED_TIME_DELTA > 600
               AND BEGIN_INTERVAL_TIME > SYSDATE - 14
                ORDER BY 1,5



Regards
Kamil Piórek 
kamil.piorekk@gmail.com