Skip to Main Content
  • Questions
  • Gaining low level and in depth understanding on switching through data base resource manager


Question and Answer

Connor McDonald

Thanks for the question, Rajneesh.

Asked: March 18, 2020 - 10:17 am UTC

Answered by: Connor McDonald - Last updated: March 27, 2020 - 4:18 am UTC

Category: Database Administration - Version: 19 c

Viewed 100+ times

You Asked

I read that one way to switch the load plan is through scheduler. I guess scheduler will switch to a plan by taking switching decision based on time. It will not take switching decision based on need and availability of resources among various consumer user groups?

Let's say 90% of resource is guaranteed to processing user group (a group of OLTP users) and 10% resource to reporting user group, however processing user group is able to utilize only 80%. In this situation a new session/call or existing session/call from reporting user group will be automatically switched to processing user group?

or sessions/calls will not switch , however resources will be allocated to processing user group?

How DBRM knows (or how to implement that if processing user group needs only 80% at the moment (whilst guarantee is set to 90%) then remaining 10% should be allocated to other needy reporting user group?

However in next moment again needs change and now processing user group needs more then resource consumption will be automatically switched to 90% to processing user group and 10% to reporting user group?

If it happens and in automatically fashion then this switching may cause additional overhead? I mean DBRM is busy most of the time in switching and actual task is not being performed?

and we said...

Plenty of good resources on this

But to summarise your questions.

Res Mgr is not about stopping *unused* resources. If you say that group X gets 80% of the machine, and group Y gets 20%, then group X can definitely have 100% of the machine *as long* as group Y is not asking for those resources. As load from group Y ramps up, only then will calls from group X be throttled back.

So we are not "switching" as such (unless someone explictly issues a switch call for a session). We are controlling what resources are consumed as sessions make their calls to the database for CPU, IO etc For example, if you need (say) 10ms of CPU *and* the box is busy *and* other sessions get priority, then your session may actually get those 10ms of CPU interspersed with sleep calls (where you use no resources) in order to spread that 10ms out over 50ms....thus in effect, you got 20% of the resources.

You can measure the throttling by looking at the events related to resource management

SQL> select name from v$event_name
  2  where name like '%res%mgr%';

resmgr: redo throttle
resmgr:cpu quantum
resmgr:large I/O queued
resmgr: I/O rate limit
resmgr:internal state change
resmgr:sessions to exit
resmgr:become active
resmgr:pq queued
resmgr:plan change
resmgr:internal state cleanup

and you rated our response

  (1 rating)

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


Thanks for your help

March 26, 2020 - 4:37 am UTC

Reviewer: A reader

Thank you, it helps.
Connor McDonald


March 27, 2020 - 4:18 am UTC

glad we could help

More to Explore


Get all the information about database performance in the Database Performance guide.