oracle job average duration
SELECT j.OWNER,
j.JOB_NAME,
j.REPEAT_INTERVAL,
avg(extract(DAY FROM d.RUN_DURATION) * 24 * 60 * 60
+ extract(HOUR FROM d.RUN_DURATION) * 60 * 60
+ extract(MINUTE FROM d.RUN_DURATION) * 60
+ extract(SECOND FROM d.RUN_DURATION)) AS AVERAGE_D
FROM DBA_SCHEDULER_JOBS j
LEFT JOIN DBA_SCHEDULER_JOB_RUN_DETAILS d
ON d.OWNER = j.OWNER AND d.JOB_NAME = j.JOB_NAME
WHERE j.ENABLED = 'TRUE'
AND j.OWNER NOT IN ('SYS')
GROUP BY j.OWNER,
j.JOB_NAME,
j.REPEAT_INTERVAL
ORDER BY OWNER, JOB_NAME;