Skip to Main Content
  • Questions

  • oracle 11g place limits on memory to session/user

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jeremy.

Asked: November 27, 2018 - 2:12 pm UTC

Last updated: December 03, 2018 - 3:34 am UTC

Version: 11g enterprise version

Viewed 10K+ times! This question is

You Asked

Hello !

Here is my question : Is it possible to allow a minmum amount of memory to a specific session or a user on Oracle 11g? If it is, could anyone, please, explain it to me ? :)

My fundamental need is as follow :
I have two applications that are running queries on a same database engine. One of the applications has as a constraint to never be slowed in its querying. The second one has job to do that could require a large amount of memory, CPU and IO. And in a near futur the second application will reach the point that the first application will slow and consequently crash.

So :
I am looking for a solution to allow a minimum amount of resources to the first program.

What I know :

It seems possible to do so with the CPU and IO ( https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN027 )
It seems possible to do the trick with Oracle 12c ( https://www.linkedin.com/pulse/oracle-12c-release-2-new-feature-controlling-memory-gavin-soorma )
There is a lot of complex things in Oracle memory usage ( https://docs.oracle.com/cd/E25178_01/server.1111/e16638/memory.htm ) but I do not think there is what I looking for in it (or I did not understand it well enough).

Thanks a lot for your daily work.

Kind regards,
PINEAU Jérémy

and Connor said...

OK, a number of things to look at here

1) By default, session memory is controlled system wide by 'pga_aggregate_target'. Whilst this sets a target for the whole machine, it also sets a cap per session (around 5% of the total). Exceeding this (for example doing a large sort) will have the offending session start to use temporary storage rather then RAM.

2) There are profiles (allocated to user) - you can place caps on the CPU consumed on a per session or per single call basis. If you exceed it, you get errors for the offending session

3) There is resource manager. You can carve up the machines resources into what we call consumer groups, so that certain users/sessions/etc only get (say) 10% of the total machine CPU resources if the machine is loaded. Also you can set limits on the usage of other resources using the same API. An example of that here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=shared-undo-quota-can-we-dedicate-specific-amount-for-a-single-user

From your requirements, I suspect Resource Manager is what you want to be looking at.

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN027

Rating

  (4 ratings)

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

Comments

First of all : thank you

Jeremy PI, November 28, 2018 - 9:17 am UTC

Thank you for your answer but the documentation said (for the 11g version) that manageable resources are :
CPU
Degree of Parallelism Limit
Parallel Target Percentage
Parallel Queue Timeout
Active Session Pool with Queuing
Automatic Consumer Group Switching
Canceling SQL and Terminating Sessions
Execution Time Limit
Undo Pool
Idle Time Limit

There is nothing like memory management in it. Am I missing the point ?

In my head there is two possible answers but I really do not see how to apply them :
1. Make that a session have to use only a max limited RAM amount
2. Make that a session keep its single usage an amount of RAM

But maybe I am all wrong in my mindset. I am open to all suggestions (even if it is to read the documentation about the resource manager better if you assure me that the answer is in it). Maybe its a correlation between memory management (PGA/SGA) and the resource manager that I am missing but if that is the case then I really missed it.

Could you please enlight me?

Thanks for all your work !
Connor McDonald
November 29, 2018 - 12:36 am UTC

My point 1 was that pga_aggregate_target already controls that for sessions. They don't get more than 5% of the total for sql execution.

They could exceed that if they did things like create giant plsql arrays etc, but for SQL, they wont be allowed to consume more than that - they'll dump out to temporary storage.

If you want more granular than that, then you'd need to be on 12.2 and someone has already pointed out.

To Jeremy

J. Laurindo Chiappa, November 28, 2018 - 2:32 pm UTC

Hi : I will be waitiing anxiously for Connor´s answer, too, but (to the best of my knowledge) the capacity of PGA control/limit by session was added to RM only in 12cR2, see https://oracle-base.com/articles/12c/resource-manager-per-process-pga-limits-12cr2 ....
If this is confirmed, probably you will neeed to do the same as me, in previous occasions, ie : have a job running each 5 minutes or so consulting v$stat views and killing the session consuming too much memory for PGA....

Regards,

Chiappa

Thanks ;)

Jeremy PI, November 29, 2018 - 12:44 pm UTC

Thank you for your help and sorry for bothering you this much ;)
Connor McDonald
November 30, 2018 - 6:14 am UTC

No bother at all, we're glad to help

Need help

Morris, November 30, 2018 - 6:48 am UTC

Hi connor and thank you.
Am on 12.1 . My application do not exceed more than 10 sessions . PGA is set max up to 50gb.
How could let users consume more than the 5%?
Do you have an example how to setup such need?
I follow the link but it's on undo. Wht I need is letting any session consume what ever needed for their queries to complete using memory and CPU as I know in advance that the 50gb are enough for the 10 sessions . By an example I can setup and test it. Do you have a link or a set of scripts regarding the PGA and CPU?
Connor McDonald
December 03, 2018 - 3:34 am UTC

There are some internal (start with underscore) parameters that can be set to allow a single process to have a larger slice of the overall amount.

Log a call with Oracle Support, explain your requirement and they will give you advice on the parameters to set.

(I'm recommending that course of action, because then if you ever use Support for something else, they might say "Oh...you have underscore parameters set". In this way, they'll be aware of that)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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