My name is Mark Tiger, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g).
Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly. I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.
In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them. I will try to present the information in an easy to understand way. My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA. The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle. However they are easily adaptable for versions of UNIX/AIX and windows etc.
11g R2 – About Oracle Database Resource Manager
Oracle Database Resource Manager enables you to manage multiple workloads within a database that are contending for system and database resources.
When database resource allocation decisions are left to the operating system, you may encounter the following problems with workload management.
· 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 de-schedules database servers while they hold latches, which is inefficient.
· Inappropriate allocation of resources: The operating system distributes resources equally among all active processes, and cannot prioritize one task over another.
· Inability to manage Database Specific resources, such as parallel execution servers and and active sessions.
The resource manager helps to overcome these problems, by allowing the Oracle Database more control over how hardware resources are allocated. In an environment with multiple concurrent user sessions that run jobs with different priorities, all sessions should not be treated equally. The Resource Manager enables you to classify sessions into groups based on the session attributes. Resources can then be allocated to those groups in order to optimize the hardware utilization for a specific application environment.
You can do this with the resource manager:
· Guarantee selected sessions a minimum amount of CPU regardless of the load on the system and the number of users
· Distribute available CPU, by allocating percentages of CPU time to different applications or users. 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 operations performed by members of a group.
· Manage the order of parallel statements in the parallel statement queue. Parallel statements from a critical application, can be enqueued before or ahead of parallel statements from a low priority group of users.
· Limit the number of parallel servers that a group of users can use. This ensures that all the available parallel servers are not allocated to only one 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. Additional sessions beyond the maximum are queued for execution. You can also specify a timeout period, after which queued jobs will terminate. By limiting the total number of active sessions competing for resources, the active sessions that are processing are able to enjoy a much faster throughput.
· Managing runaway sessions or calls:
o Place an absolute limit on the percentage of CPU that a group can consume.
o Detect when a session or call consumes more than a specified amount of CPU or I/O. Then the session can be automatically terminated or switched to a user group that is allocated a small amount of CPU. Doing this will mitigate the impact of the runaway session or call.
· Prevent the execution of operations, that the Optimizer estimates will run for longer time, than a pre-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.
· Allow the Oracle Database to use different sets of resource plans, based on the changing workload requirements. You can dynamically change the resource plan from a daytime plan to a night time plan, without needing to restart the instance. You can also schedule a resource plan change with Oracle Scheduler.
Elements of the Resource Manager
Resource Consumer Group
A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, and not to individual sessions.
A Container for directives that specify how resources are allocated to resource consumer groups. You specify how the Database allocates resources by activating a specific resource plan.
Resource Plan Directive
Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.
The DBMS_RESOURCE_MANAGER package is used to create and maintain these elements. The elements are stored in tables in the data dictionary. For example V$RESOURCE, V$RESOURCE_LIMIT
Resource Consumer Groups
A resource consumer group is a collection of user sessions that are grouped together based on their processing needs. When a session is created it is automatically mapped to a consumer group based on mapping rules that you set up. As an administrator(DBA), you can manually switch a session to a different consumer group. An application can call a package procedure, that will switch its session to a particular consumer group.
Resource Manager allocates resources such as CPU usage to consumer groups only. When a session becomes a member of a consumer group, its resource allocation is determined by the resource allocation for the consumer group.
There are special consumer groups in the data dictionary that can’t be modified or deleted:
· SYS_GROUP: This is the initial consumer group for all sessions created by SYS or SYSTEM. This initial consumer group can be overwritten by “session to consumer group” mapping rules.
· OTHER_GROUPS: This consumer group contains all sessions that have not been assigned to a consumer group. Every resource plan must contain a directive to OTHER_GROUPS.
Resource Plan Directives
The Resource Manager allocates resources to consumer groups according to the set of resource plan objectives; that belong to the currently active resource plan. There is a parent-child relationship between a resource plan, and its resource plan directives. Each directive references one consumer group, and no two directives for the currently active resource plan, can reference the same consumer group.
A directive has several ways in which it can limit resource allocation for a consumer group. It can for example control how much CPU the consumer group gets as a percentage of the total CPU. It can limit the total number of sessions that can be active in a consumer group.
In addition to the pre-defined resource plans for the Oracle Database, you can create any number of resource plans. Only one resource plan is active at a time. When a resource plan is active, each of its child resource plan directives controls resource allocation for a different consumer group. Each plan must include a directive that allocates resources to the consumer group OTHER_GROUPS. OTHER_GROUPS applies to all sessions that belong to a consumer group that is not part of the currently active plan.
“Resource Plan” or “Plan” denotes one element of the resource manager. It could also refer to a “Resource Plan Schema”, which includes the resource plan, its resource plan directives, and the consumer groups that the directives reference. For example the “DAYTIME” resource plan could mean either the “DAYTIME” resource plan element, or the “DAYTIME” Resource Plan Schema. It is easier to speak of the DAYTIME plan for example than the DAYTIME Resource Plan Schema. So you could say for example; the DAYTIME plan, favours interactive applications over batch applications.
Here’s how a simple resource plan could work:
Resource Plan “DAYTIME”
· Directive 1 (75% of CPU)
o Consumer group “OLTP”
· Directive 2 (15% of CPU)
o Consumer group “Reporting”
· Directive 3 (10% of CPU)
o Consumer group “OTHER_GROUPS”
The currently active resource plan does not enforce allocations until CPU usage is at 100%. If the CPU usage is below 100%, then the database is not CPU bound, and therefore there is no need to enforce allocations, in order to ensure that all sessions get their designated resource allocation. When the allocation is enforced, unused resource allocations by one consumer group, Resource Manager permits the other consumer groups to use the unused resources.
The Oracle Database provides a procedure to enable you to quickly create a simple resource plan; CREATE_SIMPLE_PLAN
Instead of referencing a consumer group, a resource plan directive can reference another resource plan. In this case the plan is referred to as a subplan. The subplan itself would have directives that allocate resources to consumer groups and other subplans.
The resource allocation schema then works like this:
The top resource plan which is the currently active resource plan; divides resources up among consumer groups and subplans. Each subplan allocates its portion of the total resource allocation among its resource groups and subplans. You can create hierarchical plans with unlimited subplans.
A resource subplan is created in the same way that a resource plan is created. To create a plan that is only to be used as a subplan, you must use the SUB_PLAN argument in the package procedure: DBMS_RESOURCE_MANAGER.CREATE_PLAN.
In any top level plan, you can reference a subplan only once. A subplan is not required to have a directive to OTHER_GROUPS and cannot be set as a resource plan.
Example of plan with subplans:
· SALES_TEAM plan (60% CPU)
o WHOLESALE group (50% CPU)
o RETAIL group (50% CPU)
· MARKET group (20% CPU)
· DEVELOP_TEAM plan (20% CPU)
o BREAD group (50% CPU)
o MUFFIN group (50% CPU)
· 20% of CPU resources to the consumer group MARKET
· 60% of CPU resources to the subplan SALES_TEAM, which in turn divides its share equally between the WHOLESALE and RETAIL consumer groups.
· 20% of CPU resources to the subplan DEVELOP_TEAM, which in turns divides its resources equally between the BREAD and MUFFIN consumer groups.
It is possible for a subplan or consumer group to have multiple parents. For example: The MARKET group could also be included in the SALES_TEAM subplan.
A plan cannot contain any loops, for example:
The SALES_TEAM subplan, cannot have a directive that references the GREAT_BREAD plan.
Resource Manager Administration Privileges
You must have the system privilege ADMINISTER_RESOURCE_MANAGER to be able to administer the resource manager. This privilege is granted with the ADMIN option to administrators, through the DBA role. If you have the ADMINISTER_RESOURCE_MANAGER privileges, than you can execute all of the procedures in the DBMS_RESOURCE_MANAGER package.
As an administrator with the ADMIN option, you may decide to delegate duties, and grant the privilege to other users or roles. Use the DBMS_RESOURCE_MANAGER_PRIVS to do so.
Grants the ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
Revokes the ADMINISTER_RESOURCE_MANAGER system privilege from a user or role.
The following PL/SQL block grants the administrative privilege to HR, but does not grant HR the ADMIN option. HR can execute all the procedures in the DBMS_RESOURCE_MANAGER package. HR can’t use the GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privileges to others.
GRANTEE_NAME => ‘HR’,
PRIVILEGE_NAME => ‘ADMINISTER_RESOURCE_MANAGER’,
ADMIN_OPTION => ‘FALSE’
You can revoke this privilege by using the REVOKE_SYSTEM_PRIVILEGE procedure. The ADMINISTER_RESOURCE_MANAGER system privilege can only be granted or revoked using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted or revoked using the SQL GRANT or REVOKE statements.
P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam. Watch this spot for details on the books, as they start becoming available.