Oracle Database Resource Management

• 1. Understanding Resource Manager Terminology
• 2. Understanding Resource Manager Allocation method
• 3. Understanding Default_plan
• 4. New Resource Plan
• 5. Create a consumer group
• 6. Understanding Resource Allocation methods
• 7. Assigning a consumer group
• 8. Activating the Resource plan
• 9. Understanding the Resource Manager view
• 10. Monitor Resource Manager
• Reference

1. Understanding Resource Manager Terminology

Resource Manager's 3 Basic structures: Resource consumer group, resource plan (and secondary Plan), Resource Plan Wizard.

Use Database Explorer to help manage how resources, such as CPU and restore space, are distributed between user sessions. Continue with the following steps to configure the resource manager:

• consumer groups define consumer groups that group user sessions according to resource processing requirements. The resource plan assigns the resource to the consumer group. Specify users and roles that can start a session in each consumer group, or switch their sessions to each consumer group.
• A consumer group mapping defines a consumer group mapping rule that maps a user session to a consumer group based on session attributes such as user name, service name, module name, and so on. To resolve mapping conflicts, apply the mapping rules in the order in which the rule precedence is mapped.
• A schedule defines a resource plan that contains directives that specify how resources are assigned to the consumer group. For example, for each schedule, you need to specify the percentage of CPU resources that are assigned to each consumer group. You can optionally specify additional limits, such as the maximum time that a session in a consumer group can be performed or kept idle, or the maximum amount of CPU or I/O resources that a session consumes before it automatically switches to a low-priority consumer group.
• sets the resource plan to view the current activity. Activates the resource plan.
• Performance Statistics monitor statistics for currently enabled resource plans. Monitor CPU and I/O usage by consumer group, and monitor the number of constraints that the resource manager performs for the CPU by the consumer group.

2. Understanding Resource Manager Allocation method

Resource Manager can allocate resources based on one or more of the following units of measure:

• CPU Usage
• Degree of parallelism
• Number of active sessions
• Undo Space
• CPU time limit
• Idle time limit

3. Understanding Default_plan

Resource Plan Resource_manager_plan parameters:

  sql> Show parameter resourcename TYPE VALUE----                       -----------------------------------------------------------------------------------------------Resource_limit                           Boolean falseresource_manager_cpu_allocation integer 2resource_manager_plan string--set Resource_manager_plan to default_plansql> alter system set Resource_manage R_plan = ' Default_plan '; System altered. Sql> Show parameter resourcename TYPE VALUE---------------                       ------------------------------------------------------------------------------------Resource_limit Boolean falseresource_manager_cpu_allocation integer 2resource_m Anager_plan string Default_plan  

If the value of the Resource_manager_plan parameter is not set, resource management is not performed in the instance.

4. New Resource Plan

You can use the Create_plan, Update_plan, and Delete_plan of the PL/SQL package "Dbms_resource_manager" to create, update, and delete resource plans.

--为资源管理器会话创建等待区(必须要创建)exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();--新建资源计划,名称DEVELOPERSexecute DBMS_RESOURCE_MANAGER.CREATE_PLAN -(Plan => ‘DEVELOPERS‘, -Comment => ‘Developers, in Development database‘);

5. Create a consumer group

Create a new two consumer group: Online_developers and Batch_developers

--新建使用者组 ONLINE_DEVELOPERSexec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP -(Consumer_Group => ‘ONLINE_DEVELOPERS‘, -Comment => ‘Online developers‘);--新建使用者组 BATCH_DEVELOPERSexec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP -(Consumer_Group => ‘BATCH_DEVELOPERS‘, -Comment => ‘Batch developers‘);

6. Understanding Resource Allocation methods

The

Creates two plan guides to allocate 75% of the CPU resources to the Online_developers group, and 25% of the CPU resources to the Batch_developers group.
In addition, limit the parallelism of the Online_developers group to 12 and limit the parallelism of the Batch_developers group to 6.

--ONLINE_DEVELOPERSexec dbms_resource_manager.create_plan_directive -(Plan => ‘DEVELOPERS‘, -Group_or_subplan => ‘ONLINE_DEVELOPERS‘, -Comment => ‘Online developers‘, -Cpu_p1 => 75, -Cpu_p2 => 0, -Parallel_degree_limit_p1 => 12);--BATCH_DEVELOPERSexec dbms_resource_manager.create_plan_directive -(Plan => ‘DEVELOPERS‘, -Group_or_subplan => ‘BATCH_DEVELOPERS‘, -Comment => ‘Batch developers‘, -Cpu_p1 => 25, -Cpu_p2 => 0, -Parallel_degree_limit_p1 => 6);--OTHER_GROUPS(计划中必须包含此组)exec dbms_resource_manager.create_plan_directive -(Plan => ‘DEVELOPERS‘, -Group_or_subplan => ‘OTHER_GROUPS‘, -Comment => ‘Everyone else‘, -Cpu_p1 => 0, -Cpu_p2 => 100, -Parallel_degree_limit_p1 => 6);--验证是否有效exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();--提交更改exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();--清除更改exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

7. Assigning a consumer group

The user is reasonably assigned to the consumer group,
Assign Jingyu to the Online_developers consumer group:

--赋予JINGYU切换到ONLINE_DEVELOPERS使用者组的权限exec dbms_resource_manager_privs.grant_switch_consumer_group -(grantee_name=>‘jingyu‘, -consumer_group=>‘ONLINE_DEVELOPERS‘, -grant_option=>true);--将JINGYU分配到Online_developers使用者组exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP -(‘JINGYU‘,‘ONLINE_DEVELOPERS‘);

Assign Alfred to the Batch_developers consumer group:

--赋予ALFRED切换到BATCH_DEVELOPERS使用者组的权限exec dbms_resource_manager_privs.grant_switch_consumer_group -(grantee_name=>‘alfred‘, -consumer_group=>‘BATCH_DEVELOPERS‘, -grant_option=>true);--赋予用户administer_resource_manager权限begin  dbms_resource_manager_privs.grant_system_privilege(  grantee_name=>‘alfred‘,  privilege_name=>‘administer_resource_manager‘,  admin_option=>true);end;/--将alfred分配到Batch_developers使用者组exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP -(‘alfred‘,‘BATCH_DEVELOPERS‘);

8. Activating the Resource plan

--激活资源计划 DEVELOPERSalter system set resource_manager_plan = ‘DEVELOPERS‘;

9. Understanding the Resource Manager view

DBA_RSRC_PLANS                  -> 资源计划和每个计划的状态DBA_RSRC_PLAN_DIRECTIVES        -> 资源计划指导DBA_RSRC_CONSUMER_GROUPS        -> 资源计划使用者组DBA_RSRC_CONSUMER_GROUP_PRIVS   -> 使用者组用户和角色分配DBA_RSRC_GROUP_MAPPINGS         -> 会话属性与使用者组之间的使用者组映射DBA_RSRC_MAPPING_PRIORITY       -> 资源映射优先级DBA_USERS                       -> INITIAL_RSRC_CONSUMER_GROUP列包含用户的初始化使用者DBA_RSRC_MANAGER_SYSTEM_PRIVS   -> 被授予DBMS_RESOURCE_MANAGER包上的权限的用户

View the status and characteristics of each plan:

set linesize 120col plan for a30col status for a30select plan, status, num_plan_directives, mandatory from dba_rsrc_plans;status列值为PENDING说明计划尚未经过验证并成功提交；mandatory列值为YES说明无法删除计划。

10. Monitor Resource Manager

The following 3 dynamic performance views show statistics for Resource Manager:

v$rsrc_consumer_group   -> 按使用者组列出的CPU使用率v$sysstat               -> 所有会话的CPU使用率v$sesstat               -> 按会话列出的CPU使用率

You can also query the details of CPU, I/O, wait time, and queued sessions through the Resource Manager statistics connection of EM.

Reference

• The OCP Certification Exam Guide (1z0-053) [M]. Tsinghua University Press, 2010.

Oracle Database Resource Management