This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Mar 27, 2008 2:46 PM by RnR RSS

limit runtime of a JOB

25447 Newbie
Currently Being Moderated
create a job, job_scan
run the job_scan
works great.

Can I tell job_scan to STOP_JOB, FORCE=TRUE after so many minutes in an attribute? I could not find it..

I can write ANOTHER job to monitor what JOBS are running, and if it job_scan runs over XX minutes, then DBMS_SCHEDULER.stop_job('xxx',TRUE);

Thx
  • 1. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    You can use the max_run_duration attribute to setup a sniper job which will kill the job after its time is up. Here is sample code to do that.

    Hope this helps,
    Ravi.

    <pre>
    -- ======== run a sniper job after the first job exceeds its time limit

    -- create a table for output
    create table job_output (a timestamp with time zone, b varchar2(1000));

    -- add an event queue subscriber for this user's messages
    exec dbms_scheduler.add_event_queue_subscriber('myagent')

    -- create the first job and have it raise an event whenever it completes
    -- (succeeds, fails or stops)
    begin
    dbms_scheduler.create_job
    ( 'first_job', job_action =>
    'insert into job_output values(systimestamp, ''first job begins'');
    dbms_lock.sleep(65);
    insert into job_output values(systimestamp, ''first job ends'');',
    job_type => 'plsql_block',
    enabled => false ) ;
    dbms_scheduler.set_attribute
    ( 'first_job' , 'max_run_duration' , interval '60' second);
    end;
    /

    -- create a simple second job that runs after the first has
    -- exceeded its max_run_duration and kills it
    begin
    dbms_scheduler.create_job('sniper_job',
    job_type=>'plsql_block',
    job_action=>
    'insert into job_output values(systimestamp, ''second job runs'');
    dbms_scheduler.stop_job(''first_job'',true);',
    event_condition =>
    'tab.user_data.object_name = ''FIRST_JOB'' and
    tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
    queue_spec =>'sys.scheduler$_event_queue,myagent',
    enabled=>true);
    end;
    /

    -- enable the first job so it starts running
    exec dbms_scheduler.enable('first_job')

    -- ======================================
    </pre>
  • 2. Re: limit runtime of a JOB
    25447 Newbie
    Currently Being Moderated
    thank you!
  • 3. Re: limit runtime of a JOB
    592384 Newbie
    Currently Being Moderated
    Hi ,
    I noticed that in your example it appears that you have created a second job to kill the first job when its max_run_duration is exceeded.
    However, is it possible that I can use this second job (as the main generic job) to monitior several jobs that are created dynamically , so when the max_run_duration is exceeded it kills them.
    Instead of having to repeatly create a new sniper_job to associate with each job that has been timed out.
  • 4. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    Yes this is possible. The sniper job has to point to a program which has a metadata argument of type EVENT_MESSAGE . The program should extract the job name and owner from the event message and kill the job.

    If I have some time I'll post code for this.

    Hope this helps,
    Ravi.
  • 5. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    Here is code for a general purpose sniper job that snipes any job that has exceeded its max duration. Attached at the end is code for a simple test case that snipes two jobs.

    One limitation of this is that it only works for jobs in the same schema as the sniper job.

    Hope this helps,
    Ravi.
      -- create a table for output
      create table job_output (a timestamp with time zone, b varchar2(1000));

      -- add an event queue subscriber for this user's messages
      exec dbms_scheduler.add_event_queue_subscriber('myagent')

      -- create a sniper procedure
      create or replace procedure sniper_proc
        (message IN sys.scheduler$_event_info) as
      begin

        -- if this is not a JOB_OVER_MAX_DUR message, error out
        if message.event_type != 'JOB_OVER_MAX_DUR' then
          raise PROGRAM_ERROR;
        end if;

        -- stop the job
        dbms_scheduler.stop_job('"'||message.object_owner||'"."'||
          message.object_name ||'"');

        -- insert into job output
        insert into job_output values (systimestamp,
         'sniper job sniped '||'"'||message.object_owner||'"."'||
          message.object_name ||'"');
      end;
      /

      -- create a sniper program
      begin
        dbms_scheduler.create_program (
            program_name => 'sniper_prog',
            program_action=> 'sniper_proc',
            program_type => 'stored_procedure',
            number_of_arguments => 1,
            enabled => FALSE) ;

        dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1);
        dbms_scheduler.enable('sniper_prog');
      end;
      /

      -- create a general purpose sniper job to kill any job that has
      -- exceeded its max_run_duration
      begin
        dbms_scheduler.create_job('sniper_job',
          program_name=>'sniper_prog',
          event_condition =>
            'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
          queue_spec =>'sys.scheduler$_event_queue,myagent',
          enabled=>true);
      end;
      /

      -- create two jobs to test the sniper job
      begin
        dbms_scheduler.create_job
          ( 'first_job', job_action =>
              'insert into job_output values(systimestamp, ''first job begins'');
               commit; dbms_lock.sleep(75);
               insert into job_output values(systimestamp, ''first job ends'');',
            job_type => 'plsql_block',
            enabled => false ) ;
        dbms_scheduler.set_attribute
          ( 'first_job' , 'max_run_duration' , interval '60' second);

        dbms_scheduler.create_job
          ( 'second_job', job_action =>
              'insert into job_output values(systimestamp, ''second job begins'');
              commit; dbms_lock.sleep(75);
              insert into job_output values(systimestamp, ''second job ends'');',
            job_type => 'plsql_block',
          enabled => false ) ;
        dbms_scheduler.set_attribute
          ( 'second_job' , 'max_run_duration' , interval '60' second);

        dbms_scheduler.enable('first_job');
        dbms_lock.sleep(10);
        dbms_scheduler.enable('second_job');
      end;
      /
  • 6. Re: limit runtime of a JOB
    592384 Newbie
    Currently Being Moderated
    thank you ever so much for the information
  • 7. Re: limit runtime of a JOB
    592384 Newbie
    Currently Being Moderated
    Hi,

    I was wondering how can I also check if a job is completed with errors. I was wondering if I should set the job's attribute to job_completed + job_failed or should I use job_run_completed.

    IHow chould test the message.eventtype to be job completed with errors. Should i just check it to be a value of 'JOB_COMPLETED ' or 'JOB_FAILED'.
  • 8. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    If you wanted to fire another job when the first job had failed.

    You should set raise_events to dbms_scheduler.job_failed . dbms_scheduler.job_run_completed would also work but would also raise events when a job run succeeded or a job run was stopped.

    dbms_scheduler.job_completed is a different occurrence which occurs when a repeating job has passed its end_date or its max_runs or its max_failures and will no longer run. I don't think that applies to this case.

    You should check in the event condition for the second job that
    'tab.user_data.event_type = ''JOB_FAILED'''

    You can use code similar to that above and create a program that can extract the job name and error number from the event message and use that.

    There is a code example of doing this in the job e-mail notification package available from the Scheduler webpage

    http://www.oracle.com/technology/products/database/scheduler/index.html

    Hope this helps,
    Ravi.
  • 9. Re: limit runtime of a JOB
    592384 Newbie
    Currently Being Moderated
    Hi,
    I was wondering if it possible to create a monitor job that detects the following job states: started, running, failed and completed. So, when either of these states gets called a stored procedure gets called which will update and delete specific tables depending what state the job is in.
    So far, I have created a generic individual job for each state which has an even condition set to one of these states i.e.

    job1 event condition = started
    job2 event condition = running
    job3 event condition = failed.


    Note: - these jobs are linked to program1 which is used to call a stored procedure that has the scheduler queue message object as an input parameter. The stored procedure looks at the message.eventtype attribute to detect the job states, and from that it updates or delete values from its corresponding db.

    However, I wanted one job to look at all these states and I did try something like the following however it appears not to work:

    job1 event condition = event type in (started, running, failed)...
  • 10. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    It should certainly be possibly to do this with code similar to the code posted above using the approach that you have mentioned (a control program having a single metadata argument).

    Two things

    - for the control job you would have to have an event condition looking something like this

    event_condition =>
    'tab.user_data.event_type in (''JOB_STARTED'',''JOB_FAILED'') AND
    tab.user_data.object_name in (''JOB1'',''JOB2'',''JOB3'')'

    or you can have the event_condition be less specific (or even NULL) and do all your filtering in the stored procedure.

    - I can't see the difference between "job1 started" and "job1 running" . In fact the scheduler only has an event for job_started. The entire list of event_types is in dbmssch.sql.

    If you are still having trouble, you should post the not-working code (including the event condition).

    Hope this helps,
    Ravi.
  • 11. Re: limit runtime of a JOB
    592384 Newbie
    Currently Being Moderated
    Hi ,
    Sorry to hassle you again, what I have done is created four different background job which are fired when the job state has: started, completed, failed and timeout job in code such as the following. I was wondering if i could create on background job that snipes as below. And one background job that monitors the different progress changes of the current job status instead if having to create three extra background jobs :

    //timeout program and job that is used to signal to a table that the current job has tined out and then
    //snipe it
    DBMS_SCHEDULER.CREATE_PROGRAM (program_name =>
    psis_mig_constants.c_TIMEO_PROG_NAME,
    program_action=> 'psis_mig_job_manager.timeout_sniper_proc',
    program_type => 'stored_procedure',
    number_of_arguments => 1,
    enabled => FALSE) ;
    DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT (psis_mig_constants.c_TIMEO_PROG_NAME,'event_message',1);
    DBMS_SCHEDULER.ENABLE(psis_mig_constants.c_TIMEO_PROG_NAME);
    DBMS_SCHEDULER.CREATE_JOB(psis_mig_constants.c_TIMEO_JOB_NAME,
    program_name=>psis_mig_constants.c_TIMEO_PROG_NAME,
    event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
    queue_spec =>'sys.scheduler$_event_queue,' || v_current_subscriber,
    enabled=>true,
    auto_drop =>false);


    //these are the three monitor jobs that are used detects when a job has started and signals to some table //the current job status and do some underlying task for each different state that is fired..
    // I was wondering if it is possible for me to create a single job that follow the current job status instead
    //of createing three differ monitor jobs in the background?
    DBMS_SCHEDULER.CREATE_PROGRAM (program_name => psis_mig_constants.c_MONITOR_PROG_NAME,
    program_action=> 'psis_mig_job_manager.monitor_detector_proc',
    program_type => 'stored_procedure',
    number_of_arguments => 1,
    enabled => FALSE) ;
    DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT (psis_mig_constants.c_MONITOR_PROG_NAME,'event_message',1);
    DBMS_SCHEDULER.ENABLE(psis_mig_constants.c_MONITOR_PROG_NAME);

    //does specific task when the current job status is set to fail, it signals to a table that the current job starus has completed with errors (exception raised)
    DBMS_SCHEDULER.CREATE_JOB(psis_mig_constants.c_ERRORC_MONTIOR_JOB_NAME,
    program_name=>psis_mig_constants.c_MONITOR_PROG_NAME,
    event_condition => 'tab.user_data.event_type = ''JOB_FAILED''',
    queue_spec =>'sys.scheduler$_event_queue,' || v_current_subscriber,
    enabled=>true,
    auto_drop =>false);

    //onces the job status is set to stert it does specific task and signal the current status of the job
    DBMS_SCHEDULER.CREATE_JOB(psis_mig_constants.c_RUNNING_MONTIOR_JOB_NAME,
    program_name=>psis_mig_constants.c_MONITOR_PROG_NAME,
    event_condition => 'tab.user_data.event_type = ''JOB_STARTED''',
    queue_spec =>'sys.scheduler$_event_queue,' || v_current_subscriber,
    enabled=>true,
    auto_drop =>false);

    //detects when a job has been completed and does a specific task
    DBMS_SCHEDULER.CREATE_JOB(psis_mig_constants.c_COMPLETED_MONTIOR_JOB_NAME,
    program_name=>psis_mig_constants.c_MONITOR_PROG_NAME,
    event_condition => 'tab.user_data.event_type = ''JOB_SUCCEEDED''',
    queue_spec =>'sys.scheduler$_event_queue,' || v_current_subscriber,
    enabled=>true,
    auto_drop =>false);



    Here are he attributes that are set on the job that is submitted so to have the three monitor jobs detect its current status:
    DBMS_SCHEDULER.create_job(job_name => v_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => p_executionString,
    start_date => v_job_timestamp,
    repeat_interval => NULL,
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'Creating running jobs .');

    -- 9. then set the max numbers of run attribute to one.
    DBMS_SCHEDULER.set_attribute(name => v_job_name,
    attribute => psis_mig_constants.c_JOB_MAX_RUN_ATTRIBUTE,
    value => psis_mig_constants.c_JOB_MAX_NOS_OF_RUNS);

    -- 11. then set the raise event attribute for a job that has failed or started.
    DBMS_SCHEDULER.set_attribute(name => v_job_name,
    attribute => psis_mig_constants.c_JOB_RAISE_EVENT,
    value => DBMS_SCHEDULER.job_failed + DBMS_SCHEDULER.job_started + DBMS_SCHEDULER.job_succeeded
    );

    -- 10. then set the max duration attribute for sniping the job if it has a timeout value set to it
    DBMS_SCHEDULER.set_attribute(name => v_job_name,
    attribute => psis_mig_constants.c_JOB_OVER_MAX_DUR_ATTRIBUTE,
    value => numtodsinterval(v_timeout_value, 'SECOND'));

    DBMS_SCHEDULER.enable (name =>v_job_name);

    Q1 Is there a better way to do this, since i know when i submitt several hundreds of jobs to be monitored it takes some time for the event jobs to be fired. Even though the scheduler states the job has completed successfully.
    Q2 Also, sometimes some jobs that have been submitted for some reason the corresponding even based job hasn't been fired..Could there be anything wrong that I have done within my set up..
    Q3 When I submit running jobs, how can i ensure they are running done in parallel? Would i have to call the run job function than cal the enable function?
    Is there any parameters that i need to set to get the jobs running in parallel.
  • 12. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    // I was wondering if it is possible for me to create a single job that follow the current job status instead
    //of createing three differ monitor jobs in the background?

    Yes, just change the event_condition to something like

    'tab.user_data.event_type in (''JOB_STARTED'',''JOB_SUCCEEDED'',''JOB_FAILED'')'

    And then in the program read the event_type field to see what event type was received.

    One possible downside to this is that in 10g there is a bug that events received while a job is running will be ignored. So if the job is doing processing while another triggering event is received then the new event will be ignored (this is fixed in 11g).


    Q1 Is there a better way to do this, since i know when i submitt several hundreds of jobs to be monitored it takes some time for the event jobs to be fired. Even though the scheduler states the job has completed successfully.

    For large numbers of jobs you could of course have your own polling job which runs once every 5 minutes and checks the all_scheduler_job_run_details or all_scheduler_running_jobs views and does whatever needs to be done.

    Q2 Also, sometimes some jobs that have been submitted for some reason the corresponding even based job hasn't been fired..Could there be anything wrong that I have done within my set up..

    I can't see any possible reason that this might happen. You could output event message info to a log file or log table to see why this is happening.

    Q3 When I submit running jobs, how can i ensure they are running done in parallel? Would i have to call the run job function than cal the enable function?
    Is there any parameters that i need to set to get the jobs running in parallel.

    I am not sure what you are asking here. If several jobs are scheduled to run concurrently they will as long as there is not a excessive load. You should ensure that the values of

    select value from v$parameter where name='job_queue_processes';
    and
    select value from all_scheduler_global_attribute
    where ATTRIBUTE_NAME= 'MAX_JOB_SLAVE_PROCESSES';

    are both non-NULL and sufficiently high for your application. Using run_job should not be necessary, it is useful if you want to have the job run out of its normal schedule immediately.

    Hope this helps,
    Ravi.
  • 13. Re: limit runtime of a JOB
    592384 Newbie
    Currently Being Moderated
    Hi Ravi,
    thanks for your quick reply... I did try creating a single job with the event condition set to eventtype in ('JOB_STARTED','JOB_COMPLETED','JOB_FAILED') , however i found when a job got started the event tyype got detected but the program never recieved the fired event type being set to job_completed or job_failed which i found odd.
    However if I set the event condition to ('JOB_COMPLETED','JOB_FAILED') this appears to work which made me presumme , that if a job status has changed to either complete or failed it will recieve the correct event type value since they are mutal exclusive. But it can't deal with an event type being fired for when a job has started and then completed or when a job has started and failed.
  • 14. Re: limit runtime of a JOB
    RnR Pro
    Currently Being Moderated
    Hi,

    I think you may have run into the bug I was talking about.

    If a job is very short and fails then it generates a start event and a failed event in very quick succession.

    The bug may cause the second event to be ignored because the first is still being processed .

    So I guess the upshot is that if two events may occur very close together it is better to use different monitoring jobs (until 11g when this is fixed).

    Hope this helps,
    Ravi.
1 2 Previous Next