Manager (the Resource Manager) enables us to optimize resource allocation among the many concurrent database sessions.Although we can manage resources via profiles a better option is to use the resource manager, by creating resource plans which specify how much of our resources should go to the various consumer groups, we can prioritize users and jobs. The resource manager can kill long running jobs, switch jobs to higher priority all automatically, the DBA can also manually switch users between resource groups.
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.
Inefficient scheduling : The operating system deschedules database servers while they hold latches, which is inefficient.
Inappropriate allocation of resources : The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.
Inability to manage database-specific resources, such as parallel execution servers and active sessions
How Does the Resource Manager Address These Problems?
The Resource Manager helps to overcome these problems by allowing the database more control over how hardware resources are allocated. The Resource Manager enables us to classify sessions into groups based on session attributes, and to then allocate resources to those groups in a way that optimizes hardware utilization for our application environment.
- 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) :
1.) Resource Consumer Group - A resource consumer group is a collection of users with similar requirements for resource consumption. Users can be assigned to more than one resource consumer group, but each user's active session can only be assigned to one resource consumer group at a time. For example, I can construct separate consumer groups for OLTP users, one for decision support users and one for users who typically create ad hoc queries.
2.) Resource plan - A resource plan can also have multiple subplans that specify a finer grain of resource allocation detail. For example, I can create two subplans for my OLTP users, one for high-volume users and one for lower-volume users. Note that a subplan can have more than one parent plan, but Oracle will not permit a subplan to "loop back" upon itself.
3.) Resource allocation method - dictates the specific method we choose to use to allocate resources like CPU.
4.) Resource Plan Directive - Resource plan directives allocate resources among the resource consumer groups in the resource plan. Essentially, directives connect resource consumer groups or subplans to their resource plans. There is at least one resource plan directive for each entry in the resource plan.
Resource plan directives use resource allocation methods to determine how the DRM will allocate resources to a resource consumer group or resource plan. Several allocation methods are available:
CPU : As its name implies, this method controls how much CPU utilization will be permitted to user sessions for different resource consumer groups within a specific resource plan. CPU utilization is parceled out in declining levels from 1 to 8, and percentages are used to assign how much CPU should be granted to each consumer group at each level. User sessions for a consumer group with the highest level will receive resources before sessions assigned to a lower level are allowed to receive resources, so this method provides a mechanism to insure the highest-priority sessions receive at least some CPU resources.
Automatic Consumer Group Switching : In some circumstances, I would like to have a user session that has begun to consume too many resources get switched automatically to a lower priority (for example, an ad hoc query user starts a "not in our lifetime" query, or a user runs a large report during peak OLTP processing hours). Using this method, the DRM can be directed to automatically switch such a user session to a different resource consumer group if the session is actively consuming resources beyond a certain amount of time. Even more impressive, this method can estimate an approximate time for the completion of the query and switch the session to a different group before the query is executed.
Active Session Pool Queueing : In some cases, I would like to be able to limit the activity for some groups of users based upon their maximum number of active sessions. This method detects when a specified threshold of user sessions has been reached for a consumer group and queues sessions until a "slot" is available. This method can also detect if a queued task has exceeded a specified time threshold, automatically terminate the queued task and return an error.
Maximum Estimated Execution Time : The DRM can also be directed to terminate an operation that has exceeded a specified maximum execution time. DRM will terminate the session and return a trappable error.
Maximum Degree of Parallelism : The DRM can limit the maximum degree of parallel processing for any operation within the plan.
Undo Pool Quota : Finally, the DRM can be directed to monitor the amount of undo (i.e. rollback) space being used by a resource consumer group. When undo utilization reaches the specified threshold, the DRM will terminate the current DML generating the redo log entries; no other members of that consumer group will be allowed to continue DML operations until sufficient redo space is available.
For more detail and examples click the below scripts
http://www.datadisk.co.uk/html_docs/oracle/resource_manager.htm
http://www.rittmanmead.com/2010/01/oracle-database-resource-manager-and-obiee/
http://oradbpedia.com/wiki/DBMS_RESOURCE_MANAGER_-_Overview
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dbrm001.htm
Enjoy :-)
No comments:
Post a Comment