6 Replies Latest reply: Mar 27, 2014 8:12 AM by GregV RSS

    Question on scheduler

    995371

      Hi Experts,

       

      I have a question on a scheduling jobs.

       

      which one will take high priority while running the jobs through either DBMS_SCHEDULER with priority 3 or DBMS_JOB?

       

      and is there any way to find the priority of a job which is ran by DBMS_JOB?

       

      Thanks in advance!!!

        • 1. Re: Question on scheduler
          GregV

          Hi,

           

           

          Which priority are you talking about?

          As per the documentation, DBMS_JOB and Scheduler use the same job coordinator to start job slaves, so there's no "priority" as such.

          Anyway, consider using only DBMS_SCHEDULER. It is much more flexible and has more capabilities than DBMS_JOB.

          • 2. Re: Question on scheduler
            Karthick_Arp

            GregV wrote:

             

            Anyway, consider using only DBMS_SCHEDULER. It is much more flexible and has more capabilities than DBMS_JOB.

             

            True, but I like DBMS_JOB for one reason. They are transactional. The job can be submitted as part of a transaction. Which I miss in DBMS_SCHEDULER.

             

            Example

             

            SQL> select * from temp;

             

            no rows selected

             

            SQL> BEGIN
              2          DBMS_SCHEDULER.CREATE_JOB
              3          (
              4             job_name             => 'KAR_TEST',
              5             job_type             => 'PLSQL_BLOCK',
              6             job_action           => 'BEGIN INSERT INTO TEMP(NO) VALUES (1); END;',
              7             number_of_arguments  => 0,
              8             start_date           => sysdate,
              9             repeat_interval      => NULL,
            10             end_date             => NULL,
            11             job_class            => 'DEFAULT_JOB_CLASS',
            12             enabled              => TRUE,
            13             auto_drop            => TRUE,
            14             comments             => NULL
            15          );
            16  END;
            17  /

             

            PL/SQL procedure successfully completed.

             

            SQL> select * from temp;

             

                    NO
            ----------
                     1

             

            SQL> DECLARE
              2          l_job_no integer;
              3  BEGIN
              4          DBMS_JOB.SUBMIT
              5          (
              6             job       =>  l_job_no,
              7             what      =>  'BEGIN INSERT INTO TEMP(NO) VALUES (2); END;',
              8             next_date =>  sysdate,
              9             interval  =>  'NULL',
            10             no_parse  =>  FALSE,
            11             instance  =>  0,
            12             force     =>  FALSE
            13          );
            14  END;
            15  /

             

            PL/SQL procedure successfully completed.

             

            SQL> select * from temp;

             

                    NO
            ----------
                     1

             

            SQL> commit;

             

            Commit complete.

             

            SQL> select * from temp;

             

                    NO
            ----------
                     1
                     2

             

            SQL>

            • 3. Re: Question on scheduler
              Nimish Garg

              There is no priority between DBMS_JOB and DBMS_SCHEDULER as far as I know.

              • 4. Re: Question on scheduler
                995371

                I could find some thing about job_priority which we can set while submitting a job while submitting through dbms_scheduler.

                 

                because we will be submitting many jobs,but we are excepting the jobs should pick by the priority.

                BEGIN
                 
                DBMS_SCHEDULER.create_job (
                    job_name        =>
                'test_priority_job',
                    job_type        => 'PLSQL_BLOCK',
                   
                job_action      => 'BEGIN DBMS_LOCK.sleep(10); END;',
                    start_date     
                => SYSTIMESTAMP,
                    repeat_interval => 'freq=minutely;',
                   
                end_date        => SYSTIMESTAMP + 1/48,
                    enabled         =>
                FALSE,
                    comments        => 'Job used to test priorities.');

                 

                 
                DBMS_SCHEDULER.set_attribute (
                    name      =>
                'test_priority_job',
                    attribute => 'job_priority',
                    value    
                => 1);   

                 

                  DBMS_SCHEDULER.enable (name
                => 'test_priority_job');
                END;
                /

                • 5. Re: Question on scheduler
                  GregV

                  True, but I don't think this is really an issue. You can create the job as disabled and run it once you want to commit.

                  • 6. Re: Question on scheduler
                    GregV

                    The job_priority parameter pertains to the job class. That is, if some Scheduler jobs belongs to the same class, you can set a priority between these jobs with this parameter. It has nothing to do with DBMS_JOB.

                    So assign the same job class to the jobs you need to establish a priority for and set the priority like you did in your example.