What Problems Does the Resource Manager Address?
When database resource allocation decisions are left to the operating system, we may encounter the following problems:
Excessive overhead : Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
Inability to manage database-specific resources, such as parallel execution servers and active sessions
How Does the Resource Manager Address These Problems?
- Guarantee certain sessions a minimum amount of processing resources regardless of the load on the system and the number of users.
- Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage can be given to ROLAP (relational online analytical processing) applications than to batch jobs.
- Limit the degree of parallelism of any operation performed by members of a group of users.
- Create an active session pool. An active session pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. The active session pool limits the total number of sessions actively competing for resources, thereby enabling active sessions to make faster progress.
- Manage runaway sessions or calls by detecting when they consume more than a specified amount of CPU or I/O. Such sessions can be automatically terminated or switched into a different (lower priority) group.
- Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit.
- Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.
Example: A Simple Resource Plan
There are four elements to the Database Resource Manager (DRM) :
3.) Resource allocation method - dictates the specific method we choose to use to allocate resources like CPU.
For more detail and examples click the below scripts