How to get execution time for an specific job in dba_jobs?

[ericfjchen]

There are many scheduled jobs in dba_jobs. Can we get thet execution time for each job in dba_jobs?

Thanks

Eric

[Sridhar Bhaskarla]

Hi Eric,

How are you running those jobs?. If they are run through cron, you will see the start time and end time logged in there.

Or you can modify the script to add a logic something like this

SCRIPTNAME=$0
START=$SECONDS

END=$SECONDS
(( DIFF = $END - $START ))
echo "$(DATE): $SCRIPTNAME TOOK $DIFF Seconds " >> /tmp/scripts.log


-Sri

[Indira Aramandla]

Hi Eric,

The TOTAL_TIME column in DBA_JOBS represents the total cumulative time in seconds spent by the system on a job. To determine the execution time of a single execution, you need to view this column after the first execution of the job.

Eg:-

SQL> select job,failures,last_sec,next_sec,total_time,sysdate from dba_jobs;

JOB FAILURES LAST_SEC NEXT_SEC TOTAL_TIME SYSDATE
---------- ---------- -------- -------- ---------- --------
3 0 17:22:47 17:24:47 20 17:23:29

Here the TOTAL_TIME shows the "Total wallclock time spent by the system on this job, in seconds" - i.e. the accumulated time. To check how long it takes to run the job once, we need to check the TOTAL_TIME column after the first run of the job.
.

Indira A

[Yogeeraj_1]

hi eric,

you should look at NEXT_DATE and INTERVAL.

desc dba_jobs
Name Null? Type
------------------------------- -------- ----
JOB NOT NULL NUMBER
LOG_USER NOT NULL VARCHAR2(30)
PRIV_USER NOT NULL VARCHAR2(30)
SCHEMA_USER NOT NULL VARCHAR2(30)
LAST_DATE DATE
LAST_SEC VARCHAR2(8)
THIS_DATE DATE
THIS_SEC VARCHAR2(8)
NEXT_DATE NOT NULL DATE
NEXT_SEC VARCHAR2(8)
TOTAL_TIME NUMBER
BROKEN VARCHAR2(1)
INTERVAL NOT NULL VARCHAR2(200)
FAILURES NUMBER
WHAT VARCHAR2(4000)
NLS_ENV VARCHAR2(4000)
MISC_ENV RAW(32)
INSTANCE NUMBER
=========================================

The INTERVAL parameter could be:
'trunc(sysdate)+1+9/24'

which should be interpreted as:
take todays date, put it back to midnight, add one day (tomorrow at midnight)
and then add 9 hours.

hope this helps!

regards
Yogeeraj

[ROLAHER · Accepted solution]

Hi Eric,

The execution time for one execution are not in dba_jobs but in my opinion the best solution are that you create a metadate table to control your jobs, and at the begining and at the end of the procedure you must insrert the date of the jobs and all you want.

insert into my_control_table ('procedure_load','start',sysdate);

/**Execution job**/

insert into my_control_table ('procedure_load','end',sysdate);

Regards, Rolaher

[Brian Crabtree]

Eric,

You can also put something into the script like:

(At the start)
select sysdate into begintime from dual;

(At the end)
select sysdate into endtime from dual;
select round((endtime-begintime)*1440) into runtime from dual;

You could then insert it into a table without a problem.

Thanks,

Brian