Skip to Main Content
  • Questions
  • Guarantee a limit for total amount of resources used by all the sessions of users in a particular user group


Question and Answer

Connor McDonald

Thanks for the question, Rajneesh.

Asked: March 18, 2020 - 4:54 am UTC

Answered by: Connor McDonald - Last updated: March 26, 2020 - 2:51 am UTC

Category: Database Administration - Version: 19 c

Viewed 100+ times

You Asked

Can we guarantee a limit for total amount of resources used by all the sessions of users in a particular user group so their total usage does not exceed 10% of CPU, 10% of IO, 10% of Memory?

Can we ensure that a user group follows different resource limit plan based on varying workloads? for example 90% of total CPU and 90% of total PGA memory to processing group and 10% of that to reporting group during peak business hours and 60% to processing group and 40% to reporting group during non-peak business hours?

If possible then how to implement? Is it through switching group option? If resources are available from other group (idle and not being used), then they are automatically assigned to needy group regardless of specified limit?


and we said...

Yes. You can switch sessions in and out of consumer groups. For example, I had a recent customer who wanted to ensure that ad hoc didn't swamp their system

So when a user submitted an adhoc query through the app we did:

dbms_session.switch_current_consumer_group('ADHOC', :x, true);

which put into a group which lower CPU resource, including a auto-switch CANCEL_SQL directive of 60 seconds to avoid runaway queries.

When the query finished and the results sent back, we did

dbms_session.switch_current_consumer_group('NORMAL', :x, true);

to flick them back into the normal usage model.

The other thing you could consider is the automatic switching faciltiies, where when a session exceeds some limit you automatically switch them (typically into a lower priority group) to reduce their impact on the system.

In terms of "If resources are available from other group (idle and not being used)", yes. Resource Manager typically wont artificially limit your resource consumption on a server. It is designed to properly distribute resources to those that need it most when the system is under load.

That is a common reason people often say "Resource Manager isn't working - my low priority sessions are using 100%". Low priority sessions will use 100% if there are no high priority sessions using the system.

and you rated our response

  (1 rating)


Thanks for your help

March 25, 2020 - 4:39 am UTC

Reviewer: A reader

Thanks for providing clarity
Connor McDonald


March 26, 2020 - 2:51 am UTC

glad we could help

More to Explore


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