Oracle Database Resource Manager

Oracle资源管理器（Oracle Database Resource Manager，以下简称DBRM）管理数据库的资源分配。

DBRM解决哪些问题

操作系统决策数据库资源分配的缺陷

? 由操作系统决策数据库资源分配时，可能会遇到以下问题：

• 过度的资源开销
  • 当服务器进程数量很大时，在Oracle数据库服务器进程之间进行操作系统上下文切换会导致过多开销。
• 低效的调度
  • 操作系统使用寄存器调度数据库服务，这样做效率很低
• 资源分配不当
  • 操作系统平均分配资源给所有的活动进程，不能判断任务的优先级
• 不能管理特定的数据库资源
  • 管理并行和活动会话

DBRM解决的问题

• 限制同一组内成员并行度

• 为不同的用户或应用分配不同的CPU时间

• 监控资源

• 限制每个会话的PGA内存量

• 限制会话的空闲时间

• 根据负载需求，允许数据库使用不同的资源计划

资源管理组成元素

通过DBMS_RESOURCE_MANAGER 包创建和管理以下元素

Resource consumer group（资源使用者组）

资源使用者组（使用者组）是一组用户会话，这些会话根据其处理需求进行分组。任何活动的资源计划中最多只能有28个资源使用者组。

系统默认资源消费组，他们不能修改或删除，如下：

• SYS_GROUP：为用户帐户SYS或SYSTEM创建的会话的初始资源使用者组
• OTHER_GROUPS：给未分配给资源消费组成员的会话的资源消费组
• ORA$AUTOTASK：
• _ORACLE_BACKGROUND_GROUP_：

查看资源使用者组

select name from v$rsrc_consumer_group where con_id = 1;

Resource Plan Directives（资源计划指令）

? 资源管理器根据属于当前活动资源计划的一组资源计划指令将资源分配给使用者组。指令可以通过多种方式限制使用者组的资源分配。例如，它可以控制使用者组获得的CPU占总CPU的百分比，并且可以限制使用者组中活动会话的总数。

Resource plan（资源计划）

资源计划是指令的容器，这些指令指定了如何将资源分配给资源使用者组。

资源管理器管理权限

需要授权 ADMINISTER_RESOURCE_MANAGER 系统权限

权限管理

通过DBMS_RESOURCE_MANAGER_PRIVS包的过程给用户或角色进行授权和回收 ADMINISTER_RESOURCE_MANAGER 系统权限

示例

授权资源管理器的管理权限给HR用户

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
   GRANTEE_NAME   => ‘HR‘,
   PRIVILEGE_NAME => ‘ADMINISTER_RESOURCE_MANAGER‘,
   ADMIN_OPTION   => FALSE);
END;
/

将会话分配给资源使用者组

会话的两种属性类型：

• login attributes
• run-time attributes

创建使用者组映射规则

通过DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING过程将会话属性/值对映射到使用者组

PROCEDURE SET_CONSUMER_GROUP_MAPPING
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ATTRIBUTE			VARCHAR2		IN
 VALUE				VARCHAR2		IN
 CONSUMER_GROUP 		VARCHAR2		IN     DEFAULT

ATTRIBUTE参数枚举值：

示例

通过以下PL / SQL块使用户SCOTT每次登录时都映射到DEV_GROUP使用者组：

-- 1. 创建 pending area
exec dbms_resource_manager.create_pending_area;

-- 2. 创建映射规则
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
     (DBMS_RESOURCE_MANAGER.ORACLE_USER, ‘SCOTT‘, ‘DEV_GROUP‘);
END;
/

-- 3. validate pending area
exec dbms_resource_manager.validate_pending_area;

-- 4. 提交 pending area
exec dbms_resource_manager.submit_pending_area;

通配符

SET_CONSUMER_GROUP_MAPPING过程支持通配符

• % ：表示多个字符
• _ : 表示单个字符
• \ ：表示转移字符

通配符只支持以下属性：

• CLIENT_OS_USER
• CLIENT_PROGRAM
• CLIENT_MACHINE
• MODULE_NAME
• MODULE_NAME_ACTION
• SERVICE_MODULE
• SERVICE_MODULE_ACTION

修改或删除使用者组映射规则

修改映射规则

运行SET_CONSUMER_GROUP_MAPPING过程将属性/值对指定给一个新使用者组

删除映射规则

运行SET_CONSUMER_GROUP_MAPPING过程将属性/值对指定给NULL使用者组

创建映射规则优先级

使用SET_CONSUMER_GROUP_MAPPING_PRI过程将每个属性的优先级设置为从1（最重要）到12（最不重要）的唯一整数。

以下示例说明了这种优先级设置：

BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
    EXPLICIT => 1,
    SERVICE_MODULE_ACTION => 2,
    SERVICE_MODULE => 3,
    MODULE_NAME_ACTION => 4,
    MODULE_NAME => 5,
    SERVICE_NAME => 6,
    ORACLE_USER => 7,
    CLIENT_PROGRAM => 8,
    CLIENT_OS_USER => 9,
    CLIENT_MACHINE => 10,
    CLIENT_ID => 11);
END;
/

SET_CONSUMER_GROUP_MAPPING_PRI要求包含伪属性EXPLICIT作为参数，且必须设置为1。

示例

为了说明映射规则优先级是如何工作的，设置以下映射规则

-- 1. 创建 pending area
exec dbms_resource_manager.create_pending_area;

-- 2. 创建映射规则
-- rule 1
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
     (DBMS_RESOURCE_MANAGER.ORACLE_USER, ‘SCOTT‘, ‘DEV_GROUP‘);
END;
/

-- 2.2 rule 2
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
     (DBMS_RESOURCE_MANAGER.MODULE_NAME, ‘EOD_REPORTS‘, ‘LOW_PRIORITY‘);
END;
/

-- 优先级
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
    EXPLICIT => 1,
    SERVICE_MODULE_ACTION => 2,
    SERVICE_MODULE => 3,
    MODULE_NAME_ACTION => 4,
    MODULE_NAME => 5,
    SERVICE_NAME => 6,
    ORACLE_USER => 7,
    CLIENT_PROGRAM => 8,
    CLIENT_OS_USER => 9,
    CLIENT_MACHINE => 10,
    CLIENT_ID => 11);
END;
/

-- 3. validate pending area
exec dbms_resource_manager.validate_pending_area;

-- 4. 提交 pending area
exec dbms_resource_manager.submit_pending_area;

? 在SCOTT的连接会话中使用DBMS_APPLICATION_INFO.SET_MODULE过程设置module name为‘EOD_REPORTS‘，此刻，就会将该会话分配到‘LOW_PRIORITY‘使用者组。因为module name的映射规则比用户名的映射规则优先级高。

通过DBA_RSRC_MAPPING_PRIORITY 视图查看会话映射规则的顺序

select * from DBA_RSRC_MAPPING_PRIORITY;

手动切换使用者组

提供2个过程实现切换使用者组：

• DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS
  • 调整单个连接会话的优先级
• DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER
  • 用户的所有会话

切换指定会话到使用者组

SWITCH_CONSUMER_GROUP_FOR_SESS过程使指定的会话立即移入指定的资源使用者组。实际上，此过程可以提高或降低会话的优先级。

PROCEDURE SWITCH_CONSUMER_GROUP_FOR_SESS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID			NUMBER			IN
 SESSION_SERIAL 		NUMBER			IN
 CONSUMER_GROUP 		VARCHAR2		IN

例子

将会话（17，12345）切换到HIGH_PRIORITY使用者组

BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (‘17‘, ‘12345‘, ‘HIGH_PRIORITY‘);
END;
/

切换用户的所有会话

SWITCH_CONSUMER_GROUP_FOR_USER过程更改与指定用户名有关的所有会话的资源使用者组。

PROCEDURE SWITCH_CONSUMER_GROUP_FOR_USER
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 USER				VARCHAR2		IN
 CONSUMER_GROUP 		VARCHAR2		IN

例子

将属于用户HR的所有会话切换到LOW_GROUP使用者组

BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (‘HR‘, ‘LOW_GROUP‘); 
END;
/

授权用户或应用程序切换使用者组

? 赋予DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP过程的执行权限，可以让用户或程序切换当前的使用者组。

PROCEDURE SWITCH_CURRENT_CONSUMER_GROUP
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NEW_CONSUMER_GROUP		VARCHAR2		IN
 OLD_CONSUMER_GROUP		VARCHAR2		OUT
 INITIAL_GROUP_ON_ERROR 	BOOLEAN 		IN

过程的参数含义

例子

SET serveroutput on
DECLARE
    old_group varchar2(30);
BEGIN
  DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP(‘BATCH_GROUP‘, old_group, FALSE);
  DBMS_OUTPUT.PUT_LINE(‘OLD GROUP = ‘ || old_group);
END;
/

自动切换使用者组

以下是这种自动会话切换类型涉及的资源计划指令属性。

• SWITCH_GROUP
• SWITCH_TIME
• SWITCH_ESTIMATE
• SWITCH_IO_MEGABYTES
• SWITCH_IO_REQS
• SWITCH_FOR_CALL
• SWITCH_IO_LOGICAL
• SWITCH_ELAPSED_TIME

授予和撤销切换特权

DBMS_RESOURCE_MANAGER_PRIVS过程可以授权或回收 用户，角色，PUBLIC 的切换权限。

OTHER_GROUPS拥有授予PUBLIC的切换权限

授权切换权限

向用户SCOTT授予切换到使用者组OLTP的特权，并且它可以给其它用户授予切换特权

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   GRANTEE_NAME   => ‘SCOTT‘,
   CONSUMER_GROUP => ‘OLTP‘,
   GRANT_OPTION   =>  TRUE);
END;
/

回收切换特权

回收用户SCOTT切换到使用者组OLTP的特权

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
   REVOKEE_NAME   => ‘SCOTT‘, 
   CONSUMER_GROUP => ‘OLTP‘);
END;
/

资源管理器管理的资源类型

• CPU
• Exadata I/O
• Parallel Execution Servers
• Program Global Area (PGA)
• Runaway Queries
• Active Session Pool with Queuing
• Undo Pool
• Idle Time Limit

CPU

资源管理器分配尚未使用的CPU资源给使用者组，也可以限制特定使用者组CPU资源

管理属性

? 使用管理属性MGMT_Pn（其中n是1到8之间的整数）来指定多个级别的CPU资源分配。例如，使用MGMT_P1指令属性指定级别1的CPU资源分配，并使用MGMT_P2指令属性指定级别2的资源分配。

使用限制（Utilization Limit）

? 使用UTILIZATION_LIMIT属性为资源使用者组的CPU利用率指定上限。

Program Global Area (PGA)

? 通过配置DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE过程的session_pga_limit参数值限制使用者组中每个连接会话的PGA资源。

资源计划

创建简单的资源计划

? 使用DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN过程创建资源计划

示例

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(simple_plan     => ‘SIMPLE_PLAN1‘
                                          ,consumer_group1 => ‘MYGROUP1‘
                                          ,group1_percent  => 80
                                          ,consumer_group2 => ‘MYGROUP2‘
                                          ,group2_percent  => 20);
END;
/

创建复杂的资源计划

流程步骤

• 创建暂存区域（Create a pending area）
  • 使用CREATE_PENDING_AREA过程完成
• 创建、修改或删除使用者组（Create, modify, or delete consumer groups）
  • 使用CREATE_CONSUMER_GROUP过程完成
• 将会话映射到使用者组（Map sessions to consumer groups）
  • 使用SET_CONSUMER_GROUP_MAPPING过程完成
• 创建资源计划（Create the resource plan）
  • 使用CREATE_PLAN完成
• 创建资源计划指令（Create resource plan directives）
  • 使用CREATE_PLAN_DIRECTIVE完成
• 验证暂存区域（Validate the pending area）
  • 使用VALIDATE_PENDING_AREA完成
• 提交暂存区域（Submit the pending area）
  • 使用SUBMIT_PENDING_AREA完成
• 清理暂存区域
  • 使用CLEAR_PENDING_AREA完成

详细过程

Pending area

Pending area：暂挂区域是暂存区域，可以在其中创建新的资源计划，更新现有计划或删除计划，而不会影响当前正在运行的应用程序。

创建暂存区域

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/

创建资源使用者组

• 过程的参数

PROCEDURE CREATE_CONSUMER_GROUP
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CONSUMER_GROUP 		VARCHAR2		IN
 COMMENT			VARCHAR2		IN     DEFAULT
 CPU_MTH			VARCHAR2		IN     DEFAULT
 MGMT_MTH			VARCHAR2		IN     DEFAULT
 CATEGORY			VARCHAR2		IN     DEFAULT

• 参数说明

• 示例

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
   CONSUMER_GROUP => ‘OLTP‘,
   COMMENT        => ‘OLTP applications‘);
END;
/

-- 相当于
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
   CONSUMER_GROUP => ‘OLTP‘,
   COMMENT        => ‘OLTP applications‘,
   MGMT_MTH       => ‘ROUND-ROBIN‘);
END;
/

映射会话到使用者组

• 过程

PROCEDURE SET_CONSUMER_GROUP_MAPPING
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ATTRIBUTE			VARCHAR2		IN
 VALUE				VARCHAR2		IN
 CONSUMER_GROUP 		VARCHAR2		IN     DEFAULT

• 参数说明

• 示例
  • 将用户OE的会话映射到OLTP使用者组

BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(  
    ATTRIBUTE      => DBMS_RESOURCE_MANAGER.ORACLE_USER, 
    VALUE          => ‘OE‘, 
    CONSUMER_GROUP => ‘OLTP‘);
END;
/

创建资源计划

• 过程定义

PROCEDURE CREATE_PLAN
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN				VARCHAR2		IN
 COMMENT			VARCHAR2		IN     DEFAULT
 CPU_MTH			VARCHAR2		IN     DEFAULT
 ACTIVE_SESS_POOL_MTH		VARCHAR2		IN     DEFAULT
 PARALLEL_DEGREE_LIMIT_MTH	VARCHAR2		IN     DEFAULT
 QUEUEING_MTH			VARCHAR2		IN     DEFAULT
 MGMT_MTH			VARCHAR2		IN     DEFAULT
 SUB_PLAN			BOOLEAN 		IN     DEFAULT
 MAX_IOPS			NUMBER			IN     DEFAULT
 MAX_MBPS			NUMBER			IN     DEFAULT

• 参数说明

• 示例

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN    => ‘DAYTIME‘,
   COMMENT => ‘More resources for OLTP applications‘);
END;
/

创建资源计划指令

• 过程定义

PROCEDURE CREATE_PLAN_DIRECTIVE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN				VARCHAR2		IN
 GROUP_OR_SUBPLAN		VARCHAR2		IN
 COMMENT			VARCHAR2		IN     DEFAULT
 CPU_P1 			NUMBER			IN     DEFAULT
 CPU_P2 			NUMBER			IN     DEFAULT
 CPU_P3 			NUMBER			IN     DEFAULT
 CPU_P4 			NUMBER			IN     DEFAULT
 CPU_P5 			NUMBER			IN     DEFAULT
 CPU_P6 			NUMBER			IN     DEFAULT
 CPU_P7 			NUMBER			IN     DEFAULT
 CPU_P8 			NUMBER			IN     DEFAULT
 ACTIVE_SESS_POOL_P1		NUMBER			IN     DEFAULT
 QUEUEING_P1			NUMBER			IN     DEFAULT
 PARALLEL_DEGREE_LIMIT_P1	NUMBER			IN     DEFAULT
 SWITCH_GROUP			VARCHAR2		IN     DEFAULT
 SWITCH_TIME			NUMBER			IN     DEFAULT
 SWITCH_ESTIMATE		BOOLEAN 		IN     DEFAULT
 MAX_EST_EXEC_TIME		NUMBER			IN     DEFAULT
 UNDO_POOL			NUMBER			IN     DEFAULT
 MAX_IDLE_TIME			NUMBER			IN     DEFAULT
 MAX_IDLE_BLOCKER_TIME		NUMBER			IN     DEFAULT
 SWITCH_TIME_IN_CALL		NUMBER			IN     DEFAULT
 MGMT_P1			NUMBER			IN     DEFAULT
 MGMT_P2			NUMBER			IN     DEFAULT
 MGMT_P3			NUMBER			IN     DEFAULT
 MGMT_P4			NUMBER			IN     DEFAULT
 MGMT_P5			NUMBER			IN     DEFAULT
 MGMT_P6			NUMBER			IN     DEFAULT
 MGMT_P7			NUMBER			IN     DEFAULT
 MGMT_P8			NUMBER			IN     DEFAULT
 SWITCH_IO_MEGABYTES		NUMBER			IN     DEFAULT
 SWITCH_IO_REQS 		NUMBER			IN     DEFAULT
 SWITCH_FOR_CALL		BOOLEAN 		IN     DEFAULT
 MAX_UTILIZATION_LIMIT		NUMBER			IN     DEFAULT
 PARALLEL_TARGET_PERCENTAGE	NUMBER			IN     DEFAULT
 PARALLEL_QUEUE_TIMEOUT 	NUMBER			IN     DEFAULT
 PARALLEL_SERVER_LIMIT		NUMBER			IN     DEFAULT
 UTILIZATION_LIMIT		NUMBER			IN     DEFAULT
 SWITCH_IO_LOGICAL		NUMBER			IN     DEFAULT
 SWITCH_ELAPSED_TIME		NUMBER			IN     DEFAULT
 SHARES 			NUMBER			IN     DEFAULT
 PARALLEL_STMT_CRITICAL 	VARCHAR2		IN     DEFAULT
 SESSION_PGA_LIMIT		NUMBER			IN     DEFAULT
 PQ_TIMEOUT_ACTION		VARCHAR2		IN     DEFAULT

• 参数说明

• 示例1

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN             => ‘DAYTIME‘,
   GROUP_OR_SUBPLAN => ‘OLTP‘,
   COMMENT          => ‘OLTP group‘,
   MGMT_P1          => 75);
END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => ‘DAYTIME‘, 
   GROUP_OR_SUBPLAN         => ‘REPORTING‘,
   COMMENT                  => ‘Reporting group‘,
   MGMT_P1                  => 15,
   PARALLEL_DEGREE_LIMIT_P1 => 8,
   ACTIVE_SESS_POOL_P1      => 4,
   SESSION_PGA_LIMIT        => 20);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                     => ‘DAYTIME‘, 
   GROUP_OR_SUBPLAN         => ‘OTHER_GROUPS‘,
   COMMENT                  => ‘This one is required‘,
   MGMT_P1                  => 10);
END;
/

• 示例2

使用RATIO方法分配CPU，该方法使用比率而不是百分比。假设应用程序提供了三种服务级别：Gold, Silver 和 Bronze。这样，创建三个名为GOLD_CG，SILVER_CG和BRONZE_CG的使用者组，并创建以下资源计划：

GOLD_CG，SILVER_CG，BRONZE_CG和OTHER_GROUPS消费组的CPU分配比例分别为10：5：2：1

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN
   (PLAN             => ‘SERVICE_LEVEL_PLAN‘,
    MGMT_MTH         => ‘RATIO‘, 
    COMMENT          => ‘Plan that supports three service levels‘);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
   (PLAN             => ‘SERVICE_LEVEL_PLAN‘,
    GROUP_OR_SUBPLAN => ‘GOLD_CG‘, 
    COMMENT          => ‘Gold service level customers‘,
    MGMT_P1          => 10);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
   (PLAN             => ‘SERVICE_LEVEL_PLAN‘,
    GROUP_OR_SUBPLAN => ‘SILVER_CG‘, 
    COMMENT          => ‘Silver service level customers‘,  
    MGMT_P1          => 5);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
   (PLAN             => ‘SERVICE_LEVEL_PLAN‘,
    GROUP_OR_SUBPLAN => ‘BRONZE_CG‘, 
    COMMENT          => ‘Bronze service level customers‘,
    MGMT_P1          => 2);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
    (PLAN            => ‘SERVICE_LEVEL_PLAN‘, 
    GROUP_OR_SUBPLAN => ‘OTHER_GROUPS‘,
    COMMENT          => ‘Lowest priority sessions‘,
    MGMT_P1          => 1);
END;
/

验证暂存区域

• 遵守的规则及检查项

  • 计划不能包含循环，例如子计划不能引用上层计划
  • 计划指令引用的所有计划和资源使用者组必须存在
  • 所有计划都必须具有指向计划或资源使用者组的计划指令
  • 任何给定级别的所有百分比之和不得超过100
  • 无法删除被活动实例使用的当前*计划
  • 以下参数只能出现在引用资源使用者组的计划指令中，而不能出现在其他资源计划中：
    • ACTIVE_SESS_POOL_P1
    • MAX_EST_EXEC_TIME
    • MAX_IDLE_BLOCKER_TIME
    • MAX_IDLE_TIME
    • PARALLEL_DEGREE_LIMIT_P1
    • QUEUEING_P1
    • SESSION_PGA_LIMIT
    • SWITCH_ESTIMATE
    • SWITCH_FOR_CALL
    • SWITCH_GROUP
    • SWITCH_IO_MEGABYTES
    • SWITCH_IO_REQS
    • SWITCH_TIME
    • UNDO_POOL
    • UTILIZATION_LIMIT
  • 任何活动计划中最多只能有28个资源使用者组，且一个计划最多可以有28个子计划
  • 资源计划和资源使用者组不能使用相同的名称
  • 任何有效计划中的都必须有针对OTHER_GROUPS的计划指令。这样可以确保为不属于当前活动计划中所包含的任何使用者组的会话分配资源（由OTHER_GROUPS指令指定）。

• 示例

BEGIN
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/

提交暂存区域

? 提交过程包含验证，可以不需要单独调用VALIDATE_PENDING_AREA过程。在执行验证之前，不会提交任何更改。

BEGIN
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

清理暂存区域

BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

启用资源管理器并切换计划

启用资源管理器

初始化参数方式

配置初始化参数RESOURCE_MANAGER_PLAN启用资源管理器。默认未启用资源管理器

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘mydb_plan‘;

-- 
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘FORCE:mydb_plan‘;

Oracle Scheduler Windows方式

-- 创建维护窗口
BEGIN
  DBMS_SCHEDULER.CREATE_WINDOW(
    window_name     => ‘EARLY_MORNING_WINDOW‘,
    duration        =>  NUMTODSINTERVAL(1, ‘hour‘),
    resource_plan   => ‘DEFAULT_MAINTENANCE_PLAN‘,
    repeat_interval => ‘FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0‘);
  DBMS_SCHEDULER.ADD_GROUP_MEMBER(
    group_name  => ‘MAINTENANCE_WINDOW_GROUP‘,
    member      => ‘EARLY_MORNING_WINDOW‘);
END;
/

-- 激活
BEGIN
  dbms_auto_task_admin.enable(
    client_name => ‘RESOURCE_PLAN‘,
    operation   => NULL,
    window_name => ‘EARLY_MORNING_WINDOW‘);
END;
/

查看

SELECT client_name, status, consumer_group, window_group
  FROM dba_autotask_client;

select window_name, resource_plan from dba_scheduler_windows where resource_plan is not null;

关闭资源管理器

1. 配置初始化参数

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘‘;

2. 关闭资源管理器相关的维护窗口

-- 检查
select * from DBA_SCHEDULER_WINDOWS;
-- 执行过程将resource_plan属性值设为空, execute dbms_scheduler.set_attribute(‘<window name>‘,‘RESOURCE_PLAN‘,‘‘); 
??execute dbms_scheduler.set_attribute(‘WEEKNIGHT_WINDOW‘,‘RESOURCE_PLAN‘,‘‘);
??execute dbms_scheduler.set_attribute(‘WEEKEND_WINDOW‘,‘RESOURCE_PLAN‘,‘‘);?


select ‘execute dbms_scheduler.set_attribute(‘‘‘||WINDOW_NAME||‘‘‘,‘‘RESOURCE_PLAN‘‘,‘‘‘‘);‘ cmd_sql from dba_scheduler_windows;

Oracle数据库资源管理器案例

多级资源计划案例

结构图

配置SQL

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => ‘bugdb_plan‘, 
   COMMENT => ‘Resource plan/method for bug users sessions‘);
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => ‘maildb_plan‘, 
   COMMENT => ‘Resource plan/method for mail users sessions‘);
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => ‘mydb_plan‘, 
   COMMENT => ‘Resource plan/method for bug and mail users sessions‘);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘Online_group‘, 
   COMMENT => ‘Resource consumer group/method for online bug users sessions‘);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘Batch_group‘, 
   COMMENT => ‘Resource consumer group/method for batch job bug users sessions‘);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘Bug_Maint_group‘,
   COMMENT => ‘Resource consumer group/method for users sessions for bug db maint‘);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘Users_group‘, 
   COMMENT => ‘Resource consumer group/method for mail users sessions‘);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘Postman_group‘,
   COMMENT => ‘Resource consumer group/method for mail postman‘);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘Mail_Maint_group‘, 
   COMMENT => ‘Resource consumer group/method for users sessions for mail db maint‘);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘bugdb_plan‘,
   GROUP_OR_SUBPLAN => ‘Online_group‘,
   COMMENT => ‘online bug users sessions at level 1‘, MGMT_P1 => 80, MGMT_P2=> 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘bugdb_plan‘, 
   GROUP_OR_SUBPLAN => ‘Batch_group‘, 
   COMMENT => ‘batch bug users sessions at level 1‘, MGMT_P1 => 20, MGMT_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘bugdb_plan‘, 
   GROUP_OR_SUBPLAN => ‘Bug_Maint_group‘,
   COMMENT => ‘bug maintenance users sessions at level 2‘, MGMT_P1 => 0, MGMT_P2 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘bugdb_plan‘, 
   GROUP_OR_SUBPLAN => ‘OTHER_GROUPS‘, 
   COMMENT => ‘all other users sessions at level 3‘, MGMT_P1 => 0, MGMT_P2 => 0,
   MGMT_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘maildb_plan‘, 
   GROUP_OR_SUBPLAN => ‘Postman_group‘,
   COMMENT => ‘mail postman at level 1‘, MGMT_P1 => 40, MGMT_P2 => 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘maildb_plan‘,
   GROUP_OR_SUBPLAN => ‘Users_group‘,
   COMMENT => ‘mail users sessions at level 2‘, MGMT_P1 => 0, MGMT_P2 => 80);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘maildb_plan‘,
   GROUP_OR_SUBPLAN => ‘Mail_Maint_group‘,
   COMMENT => ‘mail maintenance users sessions at level 2‘, MGMT_P1 => 0, MGMT_P2 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘maildb_plan‘,
   GROUP_OR_SUBPLAN => ‘OTHER_GROUPS‘, 
   COMMENT => ‘all other users sessions at level 3‘, MGMT_P1 => 0, MGMT_P2 => 0,
   MGMT_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘mydb_plan‘, 
   GROUP_OR_SUBPLAN => ‘maildb_plan‘, 
   COMMENT=> ‘all mail users sessions at level 1‘, MGMT_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘mydb_plan‘, 
   GROUP_OR_SUBPLAN => ‘bugdb_plan‘, 
   COMMENT => ‘all bug users sessions at level 1‘, MGMT_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

说明

In this plan schema, CPU resources are allocated as follows:

• Under mydb_plan, 30% of CPU is allocated to the maildb_plan subplan, and 70% is allocated to the bugdb_plan subplan. Both subplans are at level 1. Because mydb_plan itself has no levels below level 1, any resource allocations that are unused by either subplan at level 1 can be used by its sibling subplan. Thus, if maildb_plan uses only 20% of CPU, then 80% of CPU is available to bugdb_plan.
• maildb_plan and bugdb_plan define allocations at levels 1, 2, and 3. The levels in these subplans are independent of levels in their parent plan, mydb_plan. That is, all plans and subplans in a plan schema have their own level 1, level 2, level 3, and so on.
• Of the 30% of CPU allocated to maildb_plan, 40% of that amount (effectively 12% of total CPU) is allocated to Postman_group at level 1. Because Postman_group has no siblings at level 1, there is an implied 60% remaining at level 1. This 60% is then shared by Users_group and Mail_Maint_group at level 2, at 80% and 20%, respectively. In addition to this 60%, Users_group and Mail_Maint_group can also use any of the 40% not used by Postman_group at level 1.
• CPU resources not used by either Users_group or Mail_Maint_group at level 2 are allocated to OTHER_GROUPS, because in multilevel plans, unused resources are reallocated to consumer groups or subplans at the next lower level, not to siblings at the same level. Thus, if Users_group uses only 70% instead of 80%, the remaining 10% cannot be used by Mail_Maint_group. That 10% is available only to OTHER_GROUPS at level 3.
• The 70% of CPU allocated to the bugdb_plan subplan is allocated to its consumer groups in a similar fashion. If either Online_group or Batch_group does not use its full allocation, the remainder may be used by Bug_Maint_group. If Bug_Maint_group does not use all of that allocation, the remainder goes to OTHER_GROUPS.

维护使用者组、资源计划和计划指令

更新使用者组

调用过程UPDATE_CONSUMER_GROUP更新使用者组

PROCEDURE UPDATE_CONSUMER_GROUP
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CONSUMER_GROUP 		VARCHAR2		IN
 NEW_COMMENT			VARCHAR2		IN     DEFAULT
 NEW_CPU_MTH			VARCHAR2		IN     DEFAULT
 NEW_MGMT_MTH			VARCHAR2		IN     DEFAULT
 NEW_CATEGORY			VARCHAR2		IN     DEFAULT

• 创建暂存区域

• 执行UPDATE_CONSUMER_GROUP过程

  •   BEGIN
        DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP (
         CONSUMER_GROUP => ‘OLTP‘,
         NEW_COMMENT    => ‘OLTP applications‘,
         NEW_MGMT_MTH   => ‘ROUND-ROBIN‘);
      END;
      /
    

• 提交暂存区域

删除使用者组

调用过程DELETE_CONSUMER_GROUP 删除使用者组

PROCEDURE DELETE_CONSUMER_GROUP
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CONSUMER_GROUP 		VARCHAR2		IN

• 创建暂存区域

• 执行DELETE_CONSUMER_GROUP过程

  •   BEGIN
        DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (CONSUMER_GROUP => ‘OLTP‘);
      END;
      /
    

• 提交暂存区域

更新资源计划

PROCEDURE UPDATE_PLAN
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN				VARCHAR2		IN
 NEW_COMMENT			VARCHAR2		IN     DEFAULT
 NEW_CPU_MTH			VARCHAR2		IN     DEFAULT
 NEW_ACTIVE_SESS_POOL_MTH	VARCHAR2		IN     DEFAULT
 NEW_PARALLEL_DEGREE_LIMIT_MTH	VARCHAR2		IN     DEFAULT
 NEW_QUEUEING_MTH		VARCHAR2		IN     DEFAULT
 NEW_MGMT_MTH			VARCHAR2		IN     DEFAULT
 NEW_SUB_PLAN			BOOLEAN 		IN     DEFAULT
 NEW_MAX_IOPS			NUMBER			IN     DEFAULT
 NEW_MAX_MBPS			NUMBER			IN     DEFAULT

• 创建暂存区域

• 执行UPDATE_PLAN过程

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN (
   PLAN => ‘DAYTIME‘,
   NEW_COMMENT => ‘50% more resources for OLTP applications‘);
END;
/

• 提交暂存区域

删除资源计划

• 创建暂存区域

• 执行DELETE_PLAN过程

BEGIN
  DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => ‘great_bread‘);
END;
/

• 提交暂存区域

更新计划指令

PROCEDURE UPDATE_PLAN_DIRECTIVE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN				VARCHAR2		IN
 GROUP_OR_SUBPLAN		VARCHAR2		IN
 NEW_COMMENT			VARCHAR2		IN     DEFAULT
 NEW_CPU_P1			NUMBER			IN     DEFAULT
 NEW_CPU_P2			NUMBER			IN     DEFAULT
 NEW_CPU_P3			NUMBER			IN     DEFAULT
 NEW_CPU_P4			NUMBER			IN     DEFAULT
 NEW_CPU_P5			NUMBER			IN     DEFAULT
 NEW_CPU_P6			NUMBER			IN     DEFAULT
 NEW_CPU_P7			NUMBER			IN     DEFAULT
 NEW_CPU_P8			NUMBER			IN     DEFAULT
 NEW_ACTIVE_SESS_POOL_P1	NUMBER			IN     DEFAULT
 NEW_QUEUEING_P1		NUMBER			IN     DEFAULT
 NEW_PARALLEL_DEGREE_LIMIT_P1	NUMBER			IN     DEFAULT
 NEW_SWITCH_GROUP		VARCHAR2		IN     DEFAULT
 NEW_SWITCH_TIME		NUMBER			IN     DEFAULT
 NEW_SWITCH_ESTIMATE		BOOLEAN 		IN     DEFAULT
 NEW_MAX_EST_EXEC_TIME		NUMBER			IN     DEFAULT
 NEW_UNDO_POOL			NUMBER			IN     DEFAULT
 NEW_MAX_IDLE_TIME		NUMBER			IN     DEFAULT
 NEW_MAX_IDLE_BLOCKER_TIME	NUMBER			IN     DEFAULT
 NEW_SWITCH_TIME_IN_CALL	NUMBER			IN     DEFAULT
 NEW_MGMT_P1			NUMBER			IN     DEFAULT
 NEW_MGMT_P2			NUMBER			IN     DEFAULT
 NEW_MGMT_P3			NUMBER			IN     DEFAULT
 NEW_MGMT_P4			NUMBER			IN     DEFAULT
 NEW_MGMT_P5			NUMBER			IN     DEFAULT
 NEW_MGMT_P6			NUMBER			IN     DEFAULT
 NEW_MGMT_P7			NUMBER			IN     DEFAULT
 NEW_MGMT_P8			NUMBER			IN     DEFAULT
 NEW_SWITCH_IO_MEGABYTES	NUMBER			IN     DEFAULT
 NEW_SWITCH_IO_REQS		NUMBER			IN     DEFAULT
 NEW_SWITCH_FOR_CALL		BOOLEAN 		IN     DEFAULT
 NEW_MAX_UTILIZATION_LIMIT	NUMBER			IN     DEFAULT
 NEW_PARALLEL_TARGET_PERCENTAGE NUMBER			IN     DEFAULT
 NEW_PARALLEL_QUEUE_TIMEOUT	NUMBER			IN     DEFAULT
 NEW_PARALLEL_SERVER_LIMIT	NUMBER			IN     DEFAULT
 NEW_UTILIZATION_LIMIT		NUMBER			IN     DEFAULT
 NEW_SWITCH_IO_LOGICAL		NUMBER			IN     DEFAULT
 NEW_SWITCH_ELAPSED_TIME	NUMBER			IN     DEFAULT
 NEW_SHARES			NUMBER			IN     DEFAULT
 NEW_PARALLEL_STMT_CRITICAL	VARCHAR2		IN     DEFAULT
 NEW_SESSION_PGA_LIMIT		NUMBER			IN     DEFAULT
 NEW_PQ_TIMEOUT_ACTION		VARCHAR2		IN     DEFAULT

• 创建暂存区域

• 执行UPDATE_PLAN_DIRECTIVE过程

BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
         PLAN             => ‘SIMPLE_PLAN1‘,
         GROUP_OR_SUBPLAN => ‘MYGROUP1‘,
         NEW_COMMENT      => ‘Higher priority‘
        );
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

• 提交暂存区域

删除计划指令

PROCEDURE DELETE_PLAN_DIRECTIVE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN				VARCHAR2		IN
 GROUP_OR_SUBPLAN		VARCHAR2		IN

• 创建暂存区域

• 执行DELETE_PLAN_DIRECTIVE过程

BEGIN
  DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE(PLAN => ‘great_bread‘);
END;
/

• 提交暂存区域

查看资源管理器配置和状态

查看使用者组

使用视图DBA_RSRC_CONSUMER_GROUP_PRIVS 显示授予用户或角色的使用者组。

col grantee for a12
col granted_group for a32
col grant_option for a16
col initial_group for a16
SELECT GRANTEE, GRANTED_GROUP, GRANT_OPTION, INITIAL_GROUP FROM dba_rsrc_consumer_group_privs;

查看资源计划信息

使用DBA_RSRC_PLANS视图显示数据库中定义的所有资源计划。

SELECT plan,status,comments FROM dba_rsrc_plans;

查看会话的当前使用者组

col username for a16
col RESOURCE_CONSUMER_GROUP for a32
SELECT sid,serial#,username,resource_consumer_group FROM v$session;

查看当前活动的计划

col name for a24
col is_top_plan for a16
SELECT name, is_top_plan FROM v$rsrc_plan;

监视资源管理器

下面的视图监视资源管理器的配置

SELECT name, is_top_plan FROM v$rsrc_plan;

-- 监视消耗的资源，包括CPU，I/O 和并行
SELECT name, active_sessions, queue_length,
  consumed_cpu_time, cpu_waits, cpu_wait_time
  FROM v$rsrc_consumer_group;
  
-- 监视连接会话的状态
SELECT se.sid sess_id, co.name consumer_group, 
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id
   AND co.name <> ‘_ORACLE_BACKGROUND_GROUP_‘;

-- 
col window_name for a32
SELECT sequence# seq, name plan_name,
to_char(start_time, ‘YYYY-MM-DD HH24:MM‘) start_time,
to_char(end_time, ‘YYYY-MM-DD HH24:MM‘) end_time, window_name
FROM v$rsrc_plan_history;

-- 
SELECT sequence# seq, name, cpu_wait_time, cpu_waits,
consumed_cpu_time FROM v$rsrc_cons_group_history;

-- 跟踪以毫秒为单位的CPU指标，会话数或过去一分钟的利用率
SELECT sequence#, consumer_group_name, avg_active_parallel_stmts, avg_queued_parallel_stmts, 
avg_active_parallel_servers, avg_queued_parallel_servers, parallel_servers_limit
FROM v$rsrcmgrmetric;

资源管理器数据字典视图

Oracle-Database Resource Manager(DBRM)