Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
1

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run non optimized queries.

share|improve this question

2 Answers

up vote 8 down vote accepted

There is a built-in utility called Resource Manager exactly for that purpose: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776

share|improve this answer
up vote 3 down vote

If you're looking for something simpler than the resource manager, try Profiles.

I haven't had much success with the resource manager. In my experience with it I've found that it's easier to give priority over resources to certain users than to limit users.

share|improve this answer
Isn't Profiles just a part of Resource Manager (per your link)? – Nick Chammas Nov 4 '11 at 19:11
No. Profiles is a much more basic way of limiting resource than the resource manager. This might be a better link. – Nikitajy Nov 4 '11 at 19:16
Very good.. thank you for this. – Karrax Nov 5 '11 at 17:35
Profiles are indeed simpler, but they work much differently than resource manager. Profiles use intrinsic limits with no notion of the condition of the database or whether the statement/session will have any significant impact on performance at all. For example, if a statement exceeds the CPU_PER_CALL, it returns an error even if the execution would have little impact on the system. – Leigh Riffel Jan 13 '12 at 15:35
Resource Manager on the other hand doesn't do anything if the statement could complete without impacting the overall system even if that means it uses 99.9% of the CPU. From the documentation: "When every group can secure as much CPU resources as it demands, the Database Resource Manager first seeks to maximize system throughput, not to enforce allocation percentages." – Leigh Riffel Jan 13 '12 at 15:35
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.