Is it any way to find out an execution (elapse) time for the job?

Posted on 2007-10-17
Last Modified: 2013-12-19

Is it any way to find out an execution (elapse) time for the job?

ALL_JOBS.TOTAL_TIME seams indicating a cumulative processing time for a job rather then time per each execution.
Question by:GoodName
    LVL 73

    Assisted Solution

    you have to include such timing in your job and log it somewhere.  There is nothing in Oracle itself to do it.
    LVL 73

    Accepted Solution

    Here's a simple framework to do it.

    create table job_time(job_name varchar2(100), started date, duration_in_seconds number);

    then change your "what" in your dbms_job to something like this....

       v_start date := sysdate;
        call_your_job_procedure(param1, param2, etc....);
        insert into job_timer (job_name, started, duration_in_seconds)
               values ('your job name', v_start_date,(sysdate-v_start_date) * 86400);

    Author Comment

    Yes, I offered to my client to create a job_log_table (+proc) but they rejected to add extra table to the production invirontment.

    It is surprising that powerful Oracle does not have such simple statistic/historical information about jobs executions.

    Any way, thanks guys for your attention.
    LVL 73

    Expert Comment

    wow I get the answer AND the assist.  You must have really liked my answers.  



    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now