Skip to Main Content
  • Questions

  • DBMS_SCHEDULER jobs - How Job_Priority attribute works?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Achal.

Asked: July 08, 2016 - 10:28 am UTC

Last updated: July 21, 2016 - 2:28 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

Request your help to understand working of Job_Priority attribute that can be set for jobs created via DBMS_SCHEDULER.

From my tests it seems that the attribute behavior is somehow linked to the number of jobs scheduled. However, looks like i am missing something.

[Scenario]

We are on Oracle DB 11.2.0.4. Sequence of steps which we follow are:

1. Create n jobs that will call a stored procedure.
2. Create these jobs with default job class and priority. Don't provide any schedule for the created jobs.
3. Enable these jobs.
4. Wait for 10 - 15 seconds.
5. Create n more jobs, similar to the jobs created in step 1. Set job_priority = 1 for these jobs using dbms_scheduler.set_attribute.
6. Enable these jobs.

Now when n is ~200 or less, Jobs with job_priority = 1 start executing as soon as scheduler has a free slot( job_queue_processes is set to 12). That is, scheduler immediately gives preference to jobs with priority 1.

Whereas, if n is high, say 1000, execution of jobs with job_priority = 1 is not started immediately. Scheduler keeps on executing the jobs with priority 3 and stars picking up priority 1 jobs somewhere in between.

Referred following documentation links:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#CIHHBGGI
https://docs.oracle.com/cd/E18283_01/server.112/e17120/scheduse007.htm#CIAFEFFA

The first link says that job_priority will impact when class and start_time is same. However, in this case job with priority = 1 should be executed after completion of jobs with priority = 3. Not sure if i am missing any thing.

Noticed a similar issue on Oracle community as well:

https://community.oracle.com/thread/489921?start=0&tstart=0

Thanks,
Achal

A quick dirty test code:

CREATE OR REPLACE PROCEDURE proc_wait
IS
BEGIN
     dbms_lock.sleep( 120 );
END;
/
CREATE SEQUENCE TEST_SEQ;

/*
Pre-Requisite:
1. Create proc_wait and Test_SEQ

Steps to run:
1. Run the code as it is to create 1200 jobs with default priority = 3.
2. comment 'lvnPriority      INTEGER;' and uncomment lvnPriority      INTEGER :=1;
3. Run again to create jobs with priority = 1.
*/
DECLARE

       lvnNumberOfJobs  INTEGER := 1200;
       lvnPriority      INTEGER;
       --lvnPriority      INTEGER :=1;
       lvsJobName       VARCHAR2(200);
       
BEGIN

       FOR I IN 1..lvnNumberOfJobs
       LOOP
         lvsJobName := 'TestJob'||TEST_SEQ.NEXTVAL||'_'||NVL(lvnPriority,3);
     
         DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME               => lvsJobName,
      JOB_TYPE               => 'STORED_PROCEDURE',
      JOB_ACTION               => 'proc_wait',
      NUMBER_OF_ARGUMENTS             => 0,
      COMMENTS               => 'Priority Test',
      ENABLED                => FALSE,
      AUTO_DROP               => TRUE );
      
        IF (lvnPriority IS NOT NULL) THEN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME                                        => lvsJobName,
  ATTRIBUTE                                   => 'job_priority',
  VALUE                                       => lvnPriority );
         END IF;
        
  DBMS_SCHEDULER.ENABLE(
  NAME                                     => lvsJobName);
         DBMS_OUTPUT.PUT_LINE('Created Job '|| lvsJobName);
       END LOOP;
END;

and Chris said...

Thanks for providing a well researched question with a complete test case!

But there's one tiny piece of information in the links which answers your question:

"If two jobs in the same class are scheduled to start at the same time, the one with the higher priority takes precedence"

See the parameter descriptions in create_job:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000363

So the priority only matters when two (or more) jobs have the same start time!

You're waiting a few seconds before scheduling the priority 1 jobs. So these must have a start date later than the priority 3 jobs. So the priority 3 jobs should start first.

If you schedule all the jobs to start at the same time in the near future, then you should see all the priority 1 jobs complete first:

CREATE OR REPLACE PROCEDURE proc_wait
IS
BEGIN
     dbms_lock.sleep( 20 );
END;
/

alter system set job_queue_processes = 40;

DECLARE

 lvnNumberOfJobs  INTEGER := 50;
 job_prefix     varchar2(20) := 'FIXED_START';
 lvsJobName       VARCHAR2(200);
 c_start_date  constant timestamp := current_timestamp + interval '1' minute;
  
  procedure create_job (pri int) as
  begin
    DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME               => lvsJobName,
      JOB_TYPE               => 'STORED_PROCEDURE',
      JOB_ACTION             => 'proc_wait',
      NUMBER_OF_ARGUMENTS    => 0,
      COMMENTS               => 'Priority Test',
      ENABLED                => FALSE,
      AUTO_DROP              => TRUE,
      start_date => c_start_date );
      
  DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME                                        => lvsJobName,
  ATTRIBUTE                                   => 'job_priority',
  VALUE                                       => pri );
        
  DBMS_SCHEDULER.ENABLE(
  NAME                                     => lvsJobName);
         DBMS_OUTPUT.PUT_LINE('Created Job '|| lvsJobName);
  end;
  
BEGIN

   FOR I IN 1..lvnNumberOfJobs
   LOOP
     lvsJobName := job_prefix||TEST_SEQ.NEXTVAL||'_'||3;
     create_job(3);
     
   END LOOP;
   
   FOR I IN 1..lvnNumberOfJobs
   LOOP
     lvsJobName := job_prefix||TEST_SEQ.NEXTVAL||'_'||1;
     create_job(1);
     
   END LOOP;
   
END;
/

alter session set nls_timestamp_tz_format = 'dd-Mon hh24:mi:ss';

select job_name, log_date, req_start_date, actual_start_date 
from user_scheduler_job_run_details
where  job_name like 'FIXED_START%'
order  by actual_start_date;

JOB_NAME           LOG_DATE         REQ_START_DATE   ACTUAL_START_DATE  
FIXED_START2539_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2540_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2541_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2542_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2543_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2544_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2545_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2546_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2547_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2548_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2549_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2550_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2551_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2552_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2553_1  11-Jul 09:45:11  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2554_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:51    
FIXED_START2555_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2556_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2557_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2558_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2559_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2560_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2561_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2562_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2563_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2564_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2565_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2566_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2567_1  11-Jul 09:45:12  11-Jul 10:44:51  11-Jul 10:44:52    
FIXED_START2568_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2569_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2570_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2571_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2572_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2573_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2574_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2575_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2576_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2577_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2578_1  11-Jul 09:45:13  11-Jul 10:44:51  11-Jul 10:44:53    
FIXED_START2579_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2580_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2581_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2582_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2583_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2584_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2586_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2585_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2587_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2588_1  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2489_3  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2490_3  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2491_3  11-Jul 09:45:31  11-Jul 10:44:51  11-Jul 10:45:11    
FIXED_START2493_3  11-Jul 09:45:33  11-Jul 10:44:51  11-Jul 10:45:13    
FIXED_START2492_3  11-Jul 09:45:33  11-Jul 10:44:51  11-Jul 10:45:13    
FIXED_START2494_3  11-Jul 09:45:33  11-Jul 10:44:51  11-Jul 10:45:13    
FIXED_START2495_3  11-Jul 09:45:33  11-Jul 10:44:51  11-Jul 10:45:13    
FIXED_START2496_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2497_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2498_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2499_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2500_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2501_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2502_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2503_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2504_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2505_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2506_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2507_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2508_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2509_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2510_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2511_3  11-Jul 09:45:34  11-Jul 10:44:51  11-Jul 10:45:14    
FIXED_START2512_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2513_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2514_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2515_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2516_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2517_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2518_3  11-Jul 09:45:35  11-Jul 10:44:51  11-Jul 10:45:15    
FIXED_START2520_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2519_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2521_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2522_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2523_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2524_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2525_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2526_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2527_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2528_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2529_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2531_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2530_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2532_3  11-Jul 09:45:53  11-Jul 10:44:51  11-Jul 10:45:33    
FIXED_START2533_3  11-Jul 09:45:54  11-Jul 10:44:51  11-Jul 10:45:34    
FIXED_START2534_3  11-Jul 09:45:54  11-Jul 10:44:51  11-Jul 10:45:34    
FIXED_START2535_3  11-Jul 09:45:54  11-Jul 10:44:51  11-Jul 10:45:34    
FIXED_START2536_3  11-Jul 09:45:54  11-Jul 10:44:51  11-Jul 10:45:34    
FIXED_START2537_3  11-Jul 09:45:54  11-Jul 10:44:51  11-Jul 10:45:34    
FIXED_START2538_3  11-Jul 09:45:54  11-Jul 10:44:51  11-Jul 10:45:34

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Achal Bansal, July 11, 2016 - 10:18 am UTC

Thanks so much for your help Chris !

I also noticed the information about the Start time. However, when i submit less # of jobs, say 200, the priority is immediately honored even when P1 jobs are submitted well after P3 jobs.

Moreover, if we create large # of jobs, say 1000, then the priority is honored but not immediately. I have detailed one such scenario below:

1. Create 1280 p3 jobs. No start_time, default class and priority.
2. Enable these jobs.
3. Create 1280 more jobs with job_priority = 1.
4. Jobs with priority 3 keep on executing till 92 of them are left.
5. After this, p1 jobs start executing.
6. After completion of all P1 jobs, remaining 92 p3 jobs execute.

This should not be the case if start time is the only factor. Why did scheduler scheduled p1 jobs before 92 P3 jobs?

We ensured that all the P1 jobs were created after the p3 jobs. Did we encounter any bug here?
Chris Saxon
July 11, 2016 - 12:35 pm UTC

The way your test case is constructed, you're going to have jobs that miss their start time. So after your first 1,000 jobs start, you'll have 280 P3 jobs and all your P1 jobs waiting.

(Side note - even if job_queue_processes = 1000, you may not get all 1,000 jobs starting at once! The processes parameter also limits how many sessions you can have. If this value is lower than 1,000 you'll find less than a thousand concurrent jobs).

So at this point you have 1,560 jobs waiting to start. So the priority may come into play here.

I'm not sure why you're getting 92 leftover P3 jobs. Is this value consistent across runs?

If this is causing a problem for you contact support. Or find a way to have less than 1,000 jobs queued at a given time ;)


Behavior of Scheduler when jobs miss Start time

Achal Bansal, July 13, 2016 - 8:56 am UTC

Thanks again for your reply Chris.

Can you please share additional information on following:

The way your test case is constructed, you're going to have jobs that miss their start time.
 So at this point you have 1,560 jobs waiting to start. So the priority may come into play here.


As job_queue_processes is set to 18, We do have lots of jobs which miss their start_time. In this case how scheduler pickup the jobs? My assumption was that it will still pickup the jobs in ascending order of their original start_time. Seems i am missing something.

Also, as we don't specify a start time or schedule, my assumption is that the time when job is enabled is picked up as start time.

Value 92 is not consistent it changes with the number of jobs we submit.
Chris Saxon
July 21, 2016 - 2:28 pm UTC

OK, I've asked around internally. Jobs should still begin in the order of their start times, even if they miss this for some reason.

Priority should only come into play when jobs have the exact same start time.

If this is causing you problems, contact support.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library