3 Replies Latest reply: Apr 19, 2013 1:22 PM by 770691 RSS

    max_run_duration sniper job and heavy database load

    770691
      I’ve setup jobs very similar as described in by RnR in this post.
      Re: limit runtime of a JOB
      The only difference is that I replaced “dbms_lock.sleep(65);” with an heavyweight procedure that uses most resources on the database server and runs much longer than a min.


      So it looks something like this…

      begin
      dbms_scheduler.create_job
      ( 'first_job', job_action =>
      'insert into job_output values(systimestamp, ''first job begins'');
      heavy_weight_prc;
      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;
      /



      The problem seems that sinper_job doesn’t run when max duration is exceeded because of the heavy load on the database server. Sinper_job runs once heavy_weight_prc is finished.
      Please advise on how to make this work properly. How can I ensure sinper_job always runs no matter what the load on database is?

      Edited by: Phil on Apr 17, 2013 2:38 PM
      Running 11g release 2
        • 1. Re: max_run_duration sniper job and heavy database load
          spajdy
          I understand what sometime it is necessary don't allow run jobs over specified period of time. But this is usually used for some maintainence task.
          If you heavy_weight_prc do some bussines task and you know that it duration is longer than 60s why you want to kill it when running longer then 60s ?
          This don't make any sense to me. When you procedure do all work in one transakction then it starts and after 60s it is killed and all is rolled back. You only produce heavy load on DB without any positive bussines efect.
          • 2. Re: max_run_duration sniper job and heavy database load
            770691
            1 min is simply a test value. In production it normally runs 5min and I want to kill it to free resources if it runs 10mins because something is wrong.
            There is no buissness logic besides I want to kill all jobs that run over max_run_duration.

            Edited by: Phil on Apr 18, 2013 8:52 AM
            It seems the sniper job needs to run at highest priorty.
            Chapter 4 Managing Resources in book "Mastering Oracle Scheduler" by Ronald Rood and the dbms_resource_manager package seem relevant.
            • 3. Re: max_run_duration sniper job and heavy database load
              770691
              Problem sloved it wasn't high load it seems to be very poor default value.

              select value from v$parameter where name='job_queue_processes';
              value=1


              This statement fixed my problem

              alter system set job_queue_processes=30;