Oracle-Database Resource Manager(DBRM)

Oracle Database Resource Manager

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

DBRM解决哪些问题

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

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

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

DBRM解决的问题

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

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

  • 监控资源

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

  • 限制会话的空闲时间

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

资源管理组成元素

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

元素 描述
Resource consumer group（资源消费者组） 根据资源需求将会话分组，资源管理器（DBRM）将资源分配给资源消费组，而不是为单独会话分配资源
Resource plan（资源计划） 如何将资源分配给资源消费者组的一系列指令规则
Resource plan directive（资源计划指令） 将资源消费者组与特定计划相关联，并指定如何将资源分配给该资源消费者组。

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 系统权限

Procedure 描述
GRANT_SYSTEM_PRIVILEGE Grants the ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
REVOKE_SYSTEM_PRIVILEGE Revokes the ADMINISTER_RESOURCE_MANAGER system privilege from a user or role.

示例

授权资源管理器的管理权限给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
Parameter Description
ATTRIBUTE The session attribute type, specified as a package constant
VALUE The value of the attribute
CONSUMER_GROUP The consumer group to map to for this attribute/value pair

ATTRIBUTE参数枚举值：

Attribute Type Description
ORACLE_USER Login The Oracle Database user name
SERVICE_NAME Login The database service name used by the client to establish a connection
CLIENT_OS_USER Login The operating system user name of the client that is logging in
CLIENT_PROGRAM Login The name of the client program used to log in to the server
CLIENT_MACHINE Login The name of the computer from which the client is making the connection
CLIENT_ID Login The client identifier for the session
The client identifier session attribute is set by the DBMS_SESSION.SET_IDENTIFIER procedure.
MODULE_NAME Run-time The module name in the currently running application as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure or the equivalent OCI attribute setting
MODULE_NAME_ACTION Run-time A combination of the current module and the action being performed as set by either of the following procedures or their equivalent OCI attribute setting:
DBMS_APPLICATION_INFO.SET_MODULE
DBMS_APPLICATION_INFO.SET_ACTION
The attribute is specified as the module name followed by a period (.), followed by the action name (module_name.action_name).
SERVICE_MODULE Run-time A combination of service and module names in this form: service_name.module_name
SERVICE_MODULE_ACTION Run-time A combination of service name, module name, and action name, in this form: service_name.module_name.action_name
ORACLE_FUNCTION Run-time An RMAN or Data Pump operation. Valid values are DATALOAD, BACKUP, and COPY. There are predefined mappings for each of these values. If your session is performing any of these functions, it is automatically mapped to a predefined consumer group.
示例

通过以下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

过程的参数含义

Parameter Description
NEW_CONSUMER_GROUP 用户要切换到的消费者组。
OLD_CONSUMER_GROUP 返回用户切换到的使用者组的名称。
INITIAL_GROUP_ON_ERROR 控制切换错误的行为：
如果为TRUE，则在发生错误的情况下，用户将切换到初始使用者组。如果为FALSE，则会引发错误。
例子
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 的切换权限。

Procedure Description
GRANT_SWITCH_CONSUMER_GROUP Grants permission to a user, role, or PUBLIC to switch to a specified resource consumer group.
REVOKE_SWITCH_CONSUMER_GROUP Revokes permission for a user, role, or PUBLIC to switch to a specified resource consumer group.

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
  • 参数说明
参数名称 描述
CONSUMER_GROUP Name to assign to the consumer group.
COMMENT 注解
CPU_MTH 不推荐使用。请使用MGMT_MTH。
MGMT_MTH 用于在消费者组中的会话之间分配CPU的资源分配方法。默认使用轮询方式（ 'ROUND-ROBIN'）,它使用循环调度程序来确保公平执行会话。另一个方式是 'RUN-TO-COMPLETION' ，它指定长时间运行的会话排在其他会话之前。这样有助于长时间运行的会话（例如批处理）更快地完成。
  • 示例
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
  • 参数说明
参数名称 Description
ATTRIBUTE 会话属性类型
VALUE 属性值
CONSUMER_GROUP 资源消费者组的名称
  • 示例
    • 将用户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
  • 参数说明
参数名称 Description
PLAN 计划名称
COMMENT 注解
CPU_MTH 不推荐使用。请使用MGMT_MTH。
ACTIVE_SESS_POOL_MTH 活动会话池资源分配方法。ACTIVE_SESS_POOL_ABSOLUTE是默认的唯一方法。
PARALLEL_DEGREE_LIMIT_MTH 用于指定PARALLEL_DEGREE_LIMIT_ABSOLUTE上的限制的资源分配方法是默认且唯一方法。
QUEUEING_MTH 排队资源分配方法。控制从队列中删除非活动会话并将其添加到活动会话池。FIFO_TIMEOUT是默认且唯一可用的方法。
MGMT_MTH 用于指定每个消费者组或子计划获得多少CPU的资源分配方法。'EMPHASIS'（默认方法）适用于使用百分比指定CPU如何在消费者组之间分配的单级或多级计划。'RATIO' 适用于使用比率指定CPU分配方式的单级计划。
SUB_PLAN 如果为TRUE，则该计划不能用作顶级计划；它只能用作子计划。默认值为FALSE。
  • 示例
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
  • 参数说明
参数 描述
PLAN Name of the resource plan to which the directive belongs.
GROUP_OR_SUBPLAN Name of the consumer group or subplan to which to allocate resources.
COMMENT Any comment.
CPU_P1 Deprecated. Use MGMT_P1.
CPU_P2 Deprecated. Use MGMT_P2.
CPU_P3 Deprecated. Use MGMT_P3.
CPU_P4 Deprecated. Use MGMT_P4.
CPU_P5 Deprecated. Use MGMT_P5.
CPU_P6 Deprecated. Use MGMT_P6.
CPU_P7 Deprecated. Use MGMT_P7.
CPU_P8 Deprecated. Use MGMT_P8.
ACTIVE_SESS_POOL_P1 Specifies the maximum number of concurrently active sessions for a consumer group. Other sessions await execution in an inactive session queue. Default is UNLIMITED.
QUEUEING_P1 Specifies time (in seconds) after which a session in an inactive session queue (waiting for execution) times out and the call is aborted. Default is UNLIMITED.
PARALLEL_DEGREE_LIMIT_P1 Specifies a limit on the degree of parallelism for any operation. Default is UNLIMITED.
SWITCH_GROUP Specifies the consumer group to which a session is switched if switch criteria are met.If the group name is CANCEL_SQL, then the current call is canceled when switch criteria are met. If the group name is CANCEL_SQL, then the SWITCH_FOR_CALL parameter is always set to TRUE, overriding the user-specified setting.If the group name is KILL_SESSION, then the session is killed when switch criteria are met.If the group name is LOG_ONLY, then information about the session is recorded in real-time SQL monitoring, but no specific action is taken for the session.If NULL, then the session is not switched and no additional logging is performed. The default is NULL. An error is returned if this parameter is set to NULL and any other switch parameter is set to non-NULL.Note: The following consumer group names are reserved: CANCEL_SQL, KILL_SESSION, and LOG_ONLY. An error results if you attempt to create a consumer group with one of these names.
SWITCH_TIME Specifies the time (in CPU seconds) that a call can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
SWITCH_ESTIMATE If TRUE, the database estimates the execution time of each call, and if estimated execution time exceeds SWITCH_TIME, the session is switched to the SWITCH_GROUP before beginning the call. Default is FALSE.The execution time estimate is obtained from the optimizer. The accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics. In general, you should expect statistics to be no more accurate than ± 10 minutes.
MAX_EST_EXEC_TIME Specifies the maximum execution time (in CPU seconds) allowed for a call. If the optimizer estimates that a call will take longer than MAX_EST_EXEC_TIME, the call is not allowed to proceed and ORA-07455 is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is UNLIMITED.The accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics.
UNDO_POOL Sets a maximum in kilobytes (K) on the total amount of undo for uncommitted transactions that can be generated by a consumer group. Default is UNLIMITED.
MAX_IDLE_TIME Indicates the maximum session idle time, in seconds. Default is NULL, which implies unlimited.
MAX_IDLE_BLOCKER_TIME Indicates the maximum session idle time of a blocking session, in seconds. Default is NULL, which implies unlimited.
SWITCH_TIME_IN_CALL Deprecated. Use SWITCH_FOR_CALL.
MGMT_P1 For a plan with the MGMT_MTH parameter set to EMPHASIS, specifies the CPU percentage to allocate at the first level. For MGMT_MTH set to RATIO, specifies the weight of CPU usage. Default is NULL for all MGMT_Pn parameters.
MGMT_P2 For EMPHASIS, specifies CPU percentage to allocate at the second level. Not applicable for RATIO.
MGMT_P3 For EMPHASIS, specifies CPU percentage to allocate at the third level. Not applicable for RATIO.
MGMT_P4 For EMPHASIS, specifies CPU percentage to allocate at the fourth level. Not applicable for RATIO.
MGMT_P5 For EMPHASIS, specifies CPU percentage to allocate at the fifth level. Not applicable for RATIO.
MGMT_P6 For EMPHASIS, specifies CPU percentage to allocate at the sixth level. Not applicable for RATIO.
MGMT_P7 For EMPHASIS, specifies CPU percentage to allocate at the seventh level. Not applicable for RATIO.
MGMT_P8 For EMPHASIS, specifies CPU percentage to allocate at the eighth level. Not applicable for RATIO.
SWITCH_IO_MEGABYTES Specifies the number of megabytes of physical I/O that a session can transfer (read and write) before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
SWITCH_IO_REQS Specifies the number of physical I/O requests that a session can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
SWITCH_FOR_CALL If TRUE, a session that was automatically switched to another consumer group (according to SWITCH_TIME, SWITCH_IO_MEGABYTES, or SWITCH_IO_REQS) is returned to its original consumer group when the top level call completes. Default is NULL.
PARALLEL_QUEUE_TIMEOUT Specifies the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out.
PARALLEL_SERVER_LIMIT Specifies the maximum percentage of the parallel execution server pool that a particular consumer group can use. The number of parallel execution servers used by a particular consumer group is counted as the sum of the parallel execution servers used by all sessions in that consumer group.
UTILIZATION_LIMIT Specifies the maximum CPU utilization percentage permitted for the consumer group. This value overrides any level allocations for CPU (MGMT_P1 through MGMT_P8), and also imposes a limit on total CPU utilization when unused allocations are redistributed. You can specify this attribute and leave MGMT_P1 through MGMT_P8 NULL.
SWITCH_IO_LOGICAL Number of logical I/O requests that will trigger the action specified by SWITCH_GROUP. As with other switch directives, if SWITCH_FOR_CALL is TRUE, then the number of logical I/O requests is accumulated from the start of a call. Otherwise, the number of logical I/O requests is accumulated for the length of the session.
SWITCH_ELAPSED_TIME Elapsed time, in seconds, that will trigger the action specified by SWITCH_GROUP. As with other switch directives, if SWITCH_FOR_CALL is TRUE, then the elapsed time is accumulated from the start of a call. Otherwise, the elapsed time is accumulated for the length of the session.
SHARES Allocates resources among pluggable databases (PDBs) in a multitenant container database (CDB). Also allocates resources among consumer groups in a non-CDB or in a PDB.See "CDB Resource Plans".
PARALLEL_STMT_CRITICAL Specifies whether parallel statements from the consumer group are critical.When BYPASS_QUEUE is specified, parallel statements from the consumer group are critical. These statements bypass the parallel queue and are executed immediately.When FALSE or NULL (the default) is specified, parallel statements from the consumer group are not critical. These statements are added to the parallel queue when necessary.
SESSION_PGA_LIMIT Specifies the maximum amount of PGA memory, in megabytes, that can be allocated to each session in a particular consumer group. If a session exceeds the limit, then its process is terminated with an ORA-10260 error.
  • 示例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 = '';
  1. 关闭资源管理器相关的维护窗口
-- 检查
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数据库资源管理器案例

多级资源计划案例

结构图

Description of Figure 27-3 follows

配置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;

监视资源管理器

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

动态视图名称 说明
V$RSRC_PLAN 显示当前活动的资源计划及其子计划
V$RSRC_PLAN_HISTORY 显示何时在实例上启用或禁用资源计划
DBA_HIST_RSRC_PLAN 基于AWR快照存储数据
V$RSRC_CONSUMER_GROUP 监视消耗的资源，包括CPU，I/O 和并行
V$RSRC_CONS_GROUP_HISTORY
DBA_HIST_RSRC_CONSUMER_GROUP 基于AWR快照存储数据
V$RSRC_SESSION_INFO 监视连接会话的状态
V$RSRCMGRMETRIC 跟踪过去一分钟内以毫秒为单位的CPU指标，会话数或利用率
V$RSRCMGRMETRIC_HISTORY 跟踪过去60分钟内以毫秒为单位的CPU指标，会话数或利用率
DBA_HIST_RSRC_METRIC 基于AWR快照存储数据 
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;

资源管理器数据字典视图

View Description
DBA_RSRC_CONSUMER_GROUP_PRIVS 列出所有资源使用者组以及被授予它们的用户和角色
DBA_RSRC_CONSUMER_GROUPS 列出所有资源使用者组
DBA_RSRC_MANAGER_SYSTEM_PRIVS 列出了已被授予Resource Manager系统特权的所有用户和角色
DBA_RSRC_PLAN_DIRECTIVES 列出所有的资源计划指令
DBA_RSRC_PLANS 列出数据库中存在的所有资源计划
DBA_RSRC_GROUP_MAPPINGS 列出所有会话属性的所有各种映射键值对
DBA_RSRC_MAPPING_PRIORITY 列出每个属性的当前映射优先级
DBA_HIST_RSRC_PLAN 列出基于AWR快照的激活的资源计划的历史信息
DBA_HIST_RSRC_CONSUMER_GROUP 显示基于AWR快照的资源使用者组的历史统计信息
V$RSRC_CONS_GROUP_HISTORY 显示资源使用者组的累积统计信息
V$RSRC_CONSUMER_GROUP 显示当前活动的资源使用者组信息
V$RSRCMGRMETRIC 显示过去一分钟内每个消费者组消耗的资源的历史记录和累积的CPU等待时间
V$RSRCMGRMETRIC_HISTORY 以分钟为单位显示过去一小时每个消费者组的资源消耗历史记录和累积的CPU等待时间。如果启用了新的资源计划，则将清除历史记录
V$RSRC_PLAN 显示当前激活的资源计划
V$RSRC_PLAN_HISTORY 显示何时在实例上启用或禁用资源管理计划。了解随着时间的推移如何在消费者组之间共享资源
V$RSRC_SESSION_INFO 显示每个会话的资源管理器统计信息。显示会话如何受到资源管理器的影响
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/binliubiao/p/13329372.html