views:

437

answers:

1

I am new to the Oracle 10g Resource Manager and am looking for guidance on how to put together a plan to meet my goals. I will test the plan, but I'm hoping for some guidance so I don't have to try hundreds of different plan configurations. I have the following goals:

  1. Don't let non-sys sessions significantly slow down sys sessions.
  2. Don't let any OLTP users sessions significantly slow down any other OLTP users sessions.
  3. Don't let batch processing slow down OLTP.

Given these goals my first thought is to create the following consumer groups/plan directives:

Consumer Group       Level 1     Level 2    Level 3
SYS                  100%           0%        0%
OLTP1                  0%          10%        0%
OLTP2                  0%          10%        0%
OLTP3                  0%          10%        0%
OLTP4                  0%          10%        0%
OLTP5                  0%          10%        0%
OLTP6                  0%          10%        0%
OLTP7                  0%          10%        0%
OLTP8                  0%          10%        0%
OLTP9                  0%          10%        0%
OLTP10                 0%          10%        0%
BATCH                  0%           0%      100%

Using this method each OLTP user could be put in a different OLTP group (assuming 10 users). The documentation isn't very clear on this, but it sounds like if an OLTP user in group OLTP1 needs more than it's 10% share that it will get it as long as every other OLTP group is getting 10% if it needs it. Is my understanding accurate? Will this work or is there a better way?

+1  A: 

Hi Leigh, I would simplify this a little, make one group for OLTP, if they have the same requirements. Only make a new group when that new group has different requirements than the others in terms of priority. Also make sure that when an OLTP user has started a long running heavy duty process, that this session is switched to the batch group, or not started at all. Resource manager only kicks in when cpu consumption is at 100%. From that point on it will start dividing resources to make sure that each group gets what it should get based on your directives. Other things to think of are max parallel degree, session pool and (from 11g and up) undo usage and io limits.

best regards, Ronald http://ronr.blogspot.com

ik_zelf
Thanks for the response. I haven't got around to trying it yet. The reason I created multiple OLTP groups is so that no one OLTP application will trample the other OLTP applications. If they were all in the same group a runaway application could potentially use all the processing time for that group leaving nothing for the other applications. By segregating them when consumption reaches 100% the runaway OLTP application will be throttled to 10% and leave plenty of processing time for the other OLTP apps. Does this sound reasonable?
Leigh Riffel
It sounds reasonable but also a bit heavy. You could make such a runaway process switch to the batch group or maybe even terminate it when it reaches a profile limit for cpu usage.
ik_zelf
+1 The automatic action switching looks like it can only switch when an execution time is reached, an i/o mb limit is reached, or an i/o requests limit is reached. What I would like is to transfer a session to the batch group if it is impeding other OLTP sessions, but I can't tell that from these switch criteria.
Leigh Riffel
Leigh,you can also use the estimate option. This first checks if the action can be completed in time and when the databases decides it won't fit in the allowed time, the switch is executed before the action takes place. There can be more reasons for a session to interfere with an other session, like locking. Give it a try and make it simple.
ik_zelf
+1 Interesting, I'll take a look at that.
Leigh Riffel