Re: How to get execution time for an specific job in dba_jobs? (40 Views)
Reply
Regular Advisor
ericfjchen
Posts: 221
Registered: ‎06-15-2004
Message 1 of 6 (40 Views)
Accepted Solution

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

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

Thanks

Eric
Please use plain text.
Honored Contributor
Sridhar Bhaskarla
Posts: 6,350
Registered: ‎08-15-2001
Message 2 of 6 (40 Views)

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

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
You may be disappointed if you fail, but you are doomed if you don't try
Please use plain text.
Honored Contributor
Indira Aramandla
Posts: 1,313
Registered: ‎06-21-2001
Message 3 of 6 (40 Views)

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

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
Never give up, Keep Trying
Please use plain text.
Honored Contributor
Yogeeraj_1
Posts: 4,613
Registered: ‎11-25-2001
Message 4 of 6 (40 Views)

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Please use plain text.
Occasional Visitor
ROLAHER
Posts: 4
Registered: ‎09-26-2004
Message 5 of 6 (40 Views)

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

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
Please use plain text.
Honored Contributor
Brian Crabtree
Posts: 509
Registered: ‎07-21-2000
Message 6 of 6 (40 Views)

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

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
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation