Re: job_priority for Oracle scheduler jobs

Hi Fairlie,

We have one customer using job priority in prod with all jobs run under resource manager groups (to limit session concurrency for different job classes).

This was working well on 11gR2 - jobs assigned different priorities ran in the correct order based on priority within the resource group - has been problematic since upgrading to 19.15 (still yet to get to a more current RU). Basically the job priority is ignored if the jobs are run under a resource consumer group. I am not sure if this is the case for jobs run outside of a resource consumer group.

Overall there seem to be some fundamental changes to the way the DBMS scheduler works under the covers in 19c compared to 11gR2. Not unexpected, and probably for the better, but seeing some of the issues around the scheduler that have come out of this customer since upgrading I would definitely be erring on the side of caution and test the daylights out of any changes around job priority and scheduling. Particularly if you have a high number of jobs going through the scheduler.

Regards,

Mark
On 26 May 2023 at 8:41 AM +1000, Fairlie Rego <fairlie.rego_at_gmail.com>, wrote:
> Thanks Mark for your reply
> I am aware of this
> However the question was more to do with
> if anyone is using this in production in anger
> Apologies for not being clear on the previous email
>
> Cheers
> Fairlie
>
> Sent from my iPhone
>
> > On 26 May 2023, at 4:01 am, Powell, Mark <mark.powell2_at_dxc.com> wrote:
> >
> >
> > You use the set_attribute procedure to set the job_priority which defaults to 3 out of 1 to 5 with 1 being highest.
> >
> > DBMS_SCHEDULER (oracle.com)
> > PL/SQL Packages and Types Reference
> > The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that can be called from any PL/SQL program.
> > docs.oracle.com
> >
> >
> > Mark Powell
> > Database Administration
> > (313) 592-5148
> >
> >
> > From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Fairlie Rego <fairlie.rego_at_gmail.com>
> > Sent: Thursday, May 25, 2023 5:23 AM
> > To: ORACLE-L <oracle-l_at_freelists.org>
> > Subject: job_priority for Oracle scheduler jobs
> >
> > Hi all,
> >
> > I am currently working on a system which is a heavy user of Oracle scheduler jobs. During certain hours we have almost 20,000 jobs running in an hour
> > and some critical ones do not seem to get kicked off at the exact time they need to run.
> >
> > Is anyone using the job_priority feature and if so in what version and have they faced any issues with it.?
> >
> > "When several jobs within the same job class are scheduled to start at the same time, the job coordinator uses the job priority to decide which job to execute first."
> >
> > <image.png>
> >
> >
> > Cheers
> > Fairlie
> >
> >

--
http://www.freelists.org/webpage/oracle-l