Starting with 10g and the introduction of DMBS_SCHEDULER, Oracle database began tracking and storing the execution duration of a job. By default this information is kept around for 30 days. You can of course change the logging level and the retention period for the job runs – read the docs!
The job runs can be viewed via the dba_scheduler_job_run_details SYS view. Oracle SQL Developer shows this data in the job details panel of the schema browser even. So, answering the question, ‘How long does this job normally take to run?’ should be relatively easy to answer.
It is, if you can think in terms of INTERVAL. You see, Oracle doesn’t store the time a job takes to run in seconds, it uses the INTERVAL datatype.
DESC dba_scheduler_job_run_details Name NULL TYPE ----------------- ---- ---------------------------- LOG_ID NUMBER LOG_DATE TIMESTAMP(6) WITH TIME ZONE OWNER VARCHAR2(30) JOB_NAME VARCHAR2(65) JOB_SUBNAME VARCHAR2(65) STATUS VARCHAR2(30) ERROR# NUMBER REQ_START_DATE TIMESTAMP(6) WITH TIME ZONE ACTUAL_START_DATE TIMESTAMP(6) WITH TIME ZONE RUN_DURATION INTERVAL DAY(3) TO SECOND(0) INSTANCE_ID NUMBER SESSION_ID VARCHAR2(30) SLAVE_PID VARCHAR2(30) CPU_USED INTERVAL DAY(3) TO SECOND(2) CREDENTIAL_OWNER VARCHAR2(65) CREDENTIAL_NAME VARCHAR2(65) DESTINATION_OWNER VARCHAR2(128) DESTINATION VARCHAR2(128) ADDITIONAL_INFO VARCHAR2(4000)
So what is an INTERVAL?
Technically it’s INTERVAL DAY TO SECOND. From the docs –
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]
Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
So if I’m looking at an entry in dba_scheduler_job_run_details, and it shows ‘0:0:1.0’, we can read that as ‘Zero days, zero hours, and 1.0 seconds.’ As you can imagine, asking for an average using the avg() function doesn’t turn out so well –
SELECT avg(run_duration) FROM dba_scheduler_job_run_details; ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause: *Action: Error at Line: 3 COLUMN: 11
How do I get a number out of an INTERVAL?
Oracle provides a function called EXTRACT(). It allows you to pull DAYS, HOURS, or SECONDS from an INTERVAL. BONUS FEATURE: It also works for TIMESTAMPS! However, there is no direct conversion function that would take an interval and return the total number of seconds or minutes or hours, or a fraction thereof. So we have to do some MATH. I know, I know – that hurts. But, we can always cheat and ‘borrow’ from the great Tom Kyte 🙂
Using Tom’s math – basically taking the number of days and hours in a day and multiplying out to get seconds and then adding to the seconds field gives me total number of seconds. In my case I’m OK with seconds b/c most of my jobs don’t take very long to run. You could adjust the math and get an avg number of hours or days instead. I’m going with seconds.
So here is my borrowed query from Tom:
SELECT job_name, job_name, avg(EXTRACT( DAY FROM run_duration )*24*60*60 + EXTRACT( HOUR FROM run_duration )*60*60 + EXTRACT( MINUTE FROM run_duration )*60 + EXTRACT( SECOND FROM run_duration )) FROM dba_scheduler_job_run_details GROUP BY job_name, job_name HAVING avg(EXTRACT( DAY FROM run_duration )*24*60*60 + EXTRACT( HOUR FROM run_duration )*60*60 + EXTRACT( MINUTE FROM run_duration )*60 + EXTRACT( SECOND FROM run_duration )) > 0 ORDER BY 3 DESC
Why did you put JOB_NAME in there twice?
Because I wanted a chart report!
I like pretty pictures – it’s why my career path looks so bright (this is a joke about managers, they love charts.)
So clicking into my …_MAINTAIN_STATS job I can see that it ran in an avg of 5.25 seconds for a total of 4 times in the past 30 days. Averages can be misleading though. Four runs from 0 to 10 seconds is quite the range. You may want to build some logic in your report to restrict any average that doesn’t have at least a statistically significant number of data points.
Thanks to my Twitter friends for giving me something to chew on (and blog about!) this morning 🙂