Re: OLTP should have priority over jobs

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Tue, 1 Mar 2016 06:51:19 -0700
Message-ID: <56D59E57.3020307_at_gmail.com>



I concur.

If the dbms_scheduler is used, batch jobs can be assigned to classes, which in turn be both prioritized and assigned to consumer groups. That allows jobs to be held back in case of load issues.

We used to discuss this in the Oracle University DBA Workshop courses.

   Pretty sure it's still in there, but I haven't taught the basic courses in over 18 months.

It'll also be in the Scheduler chapters of the Administrator's guide.

There's also a great, but dated, Packt Publishing book on the scheduler ... at least as tech reviewer I thought it as great.

/Hans

On 29/02/2016 10:25 PM, Mark W. Farnham wrote:
> What Hans wrote should work. Sometimes it is hard to come up with a satisfying matrix of consumers, loads, and priorities.
>
> IF you have a job scheduler for the batch jobs (such as the concurrent manager in E-business suite), another approach is simply to leave sufficient headroom available for OLTP jobs by not allowing too many jobs to run at the same time (including high parallel degrees). This may involve creating workshifts for when you know OLTP work is unlikely and so you can raise the limits.
>
> Please notice I did *not* deprecate the method Hans proposed. This is simply another method. The details of your situation (including whether you have a batch job scheduler at all) would affect which route may be better for you or if they are a tie.
>
> Regards,
>
> mwf
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hans Forbrich
> Sent: Monday, February 29, 2016 9:34 PM
> To: oracle-l_at_freelists.org
> Subject: Re: OLTP should have priority over jobs
>
> Basically:
>
> - Define one or more consumer groups
> - Assign a session to a consumer group
> - can be done by a variety of things (logged in user, IP address, client s/w ...)
> - Create a plan that fills your needs
> - Activate the plan.
>
> Oracle provides free resources on the net to help you. Search for "oracle obe resource manager". Two useful resources are https://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf
> and (not quite on the mark, but useful) https://www.youtube.com/watch?v=NlgxhV5cTRc
>
> HTH
> /Hans
>
> On 29/02/2016 1:07 PM, Woody McKay wrote:
>> Hi,
>>
>> Is there a best practice for having oltp application connections to
>> have priority over running jobs?
>>
>> Mgmt wants to ensure that the user application isn't slowed down by
>> jobs running in the background.
>>
>> I have thought about using different resource plans (rather than
>> default_maintenance_plan for all, but I'm not sure how to implement
>> that. If the solution is using different plans, how do you know what
>> jobs and scheduled jobs run as who? By the job owner?
>>
>> Win 2012R2, 32cpu, 512g ram, 26 SIDs, oracle 12.1.0.2.7
>> --
>> Sincerely - Woody
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 01 2016 - 14:51:19 CET

Original text of this message