Create a Database Resource Manager plan with directives

用户资源管理涉及到的数据包主要有两个：DBMS_RESOURCE_MANAGER和DBMS_RESOURCE_MANAGER_PRIVS。
其中包DBMS_RESOURCE_MANAGER主要是用于建立资源计划，建立资源用户组，建立资源分配方法等用户资源相关的管理；而DBMS_RESOURCE_MANAGER_PRIVS的主要用途是进行用户资源管理的权限控制。
 
资源计划包含多个资源组，每个组包含一系列的资源规划指令。
每个资源组需要关联到用户。
资源计划需要设置系统参数生效。
 
相关视图：
SELECT * FROM DBA_RSRC_PLANS;           --资源计划的基本信息
SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES; --资源计划的指令信息
SELECT * FROM DBA_RSRC_CONSUMER_GROUPS; --资源计划组的说明
SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;  --分配了资源组的用户
select * from V$RSRC_PLAN;              --当前启用了什么资源计划
 
资源计划指令的一些常用属性：
 
ACTIVE_SESS_POOL_P1
这个参数控制的是资源用户组内的用户同时可以运行的最大的活动SESSION的数量。这里值得强调的是ACTIVE_SESS_POOL_P1并不限制那些非活动的SESSION，仅仅对那些活动的SESSION有限制，因为一般说来只有那些活动的SESSION才会消耗系统的资源。
如果设置了该参数，超过这个数量的活动会话会出现以下等待事件。
等待事件：resmgr: become active
 
实验过程中，把该参数设置为1，第一个会话可以执行，第二个会话等待该等待事件，并且只有一个系统进程消耗CPU，但是需要第二个会话也需要执行完，第一个才能执行完，相当于把并行的会话变成串行执行。
 
CPU_P1
设置了CPU资源，当CPU使用率达到100%时，会话会出现以下等待事件
等待事件：resmgr:cpu quantum
CPU资源的管理，并不是将某个oracle进程的CPU使用率限制在一定的百分比以内，某些资料表示，仅仅当CPU的使用率为100%的时候，资源计划才可以发挥功效，来限制各个资源用户组的CPU分配，并出现以上等待事件
 
MAX_EST_EXEC_TIME
这个参数控制一个事务最大的执行时间。如果一个事务很复杂，执行时间很长，那么他就不会被系统执行。
执行时间过长的SQL会报类似以下错误：
ORA-07455: estimated execution time (74 secs), exceeds limit (10 secs)
 
 
分配给一个用户具有resource administrator的权限
begin
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME=> 'scott',
PRIVILEGE_NAME=>'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION=>FALSE);
end;
/

-- 创建用户，并分配权限，这些用户在之后使用资源管理
create user oltp   identified by oracle default tablespace USERS;
create user batch  identified by oracle default tablespace USERS;
create user adhoc  identified by oracle default tablespace USERS;
grant connect to oltp;
grant connect to batch;
grant connect to adhoc;
grant select on ldy.CPFM to oltp;
grant select on ldy.CPFM to batch;
grant select on ldy.CPFM to adhoc;
 
begin
-- 创建一个未决区，在做所有变更资源管理操作前，都需要创建一个未决区
 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- 创建一个资源计划
 DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN=>'SINGLE_LEVEL_PLAN',COMMENT=>'RESOURCE PLAN/METHOD FOR Single level');
-- 创建定制资源组
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'OLTP_Group',
  comment => 'Resource consumer group/method for online users sessions');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'BATCH_Group',
  comment => 'Resource consumer group/method for users sessions who run batch jobs');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'ADHOC_Group',
  comment => 'Resource consumer group/method for users sessions who execute Ad-Hoc Queries');
-- 创建资源计划指令，除了CPU的限制外，设置了OLTP组只能执行10秒以内的执行，BATCH组只能支持1个活动会话，需要注意的是一定还要设置一下OTHER_GROUPS，该资源组自动在资源计划中存在，必须设置。
 dbms_resource_manager.create_plan_directive(
  plan => 'SINGLE_LEVEL_PLAN',
  group_or_subplan => 'OLTP_Group',
  comment => 'Online day users sessions at level 1',
  cpu_p1 => 80,
  max_est_exec_time => 10,
  parallel_degree_limit_p1 => 0);
 dbms_resource_manager.create_plan_directive(
  plan => 'SINGLE_LEVEL_PLAN',
  group_or_subplan => 'BATCH_Group',
  comment => 'batch day users sessions at level 1',
  cpu_p1 => 10,
  active_sess_pool_p1 => 1,
  parallel_degree_limit_p1 => 10);
 dbms_resource_manager.create_plan_directive(
  plan => 'SINGLE_LEVEL_PLAN',
  group_or_subplan => 'ADHOC_Group',
  comment => 'ADHOC day users sessions at level 1',
  cpu_p1 => 10,
  parallel_degree_limit_p1 => 5);
 dbms_resource_manager.create_plan_directive(
  plan => 'SINGLE_LEVEL_PLAN',
  group_or_subplan => 'OTHER_GROUPS',
  comment => 'OTHER_GROUPS day users sessions at level 1',
  cpu_p1 => 0,
  parallel_degree_limit_p1 => 0);
-- 校验未决区
 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- 提交未决区生效创建好的资源计划
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
-- 需要将之前创建好的资源计划关联到用户，需要再次创建一下未决区
 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- 分配权限
 dbms_resource_manager_privs.grant_switch_consumer_group(
  grantee_name => 'ADHOC',
  consumer_group => 'ADHOC_Group',
  grant_option => FALSE);
 dbms_resource_manager_privs.grant_switch_consumer_group(
  grantee_name => 'OLTP',
  consumer_group => 'OLTP_Group',
  grant_option => FALSE);
 dbms_resource_manager_privs.grant_switch_consumer_group(
  grantee_name => 'BATCH',
  consumer_group => 'BATCH_Group',
  grant_option => FALSE);
-- 设置用户初始化资源组
 dbms_resource_manager.set_initial_consumer_group(
  user => 'ADHOC',
  consumer_group => 'ADHOC_Group');
 dbms_resource_manager.set_initial_consumer_group(
  user => 'OLTP',
  consumer_group => 'OLTP_Group');
 dbms_resource_manager.set_initial_consumer_group(
  user => 'BATCH',
  consumer_group => 'BATCH_Group');
-- 校验未决区，提交未决区
 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;
/
 

-- 如果需要删除资源计划，可以使用如下存储过程
begin
dbms_resource_manager.create_pending_area(); 
dbms_resource_manager.DELETE_PLAN_CASCADE(plan => 'SINGLE_LEVEL_PLAN');
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
/
 
激活resource manager需要设置数据库参数生效：
alter system set resource_limit=true scope=both;
alter system set resource_manager_plan = SINGLE_LEVEL_PLAN scope=both;
 
 
一个小例子：
create user sh identified by oracle default tablespace data1 ;
create user oltp_user identified by oracle default tablespace data1 ;
 
grant connect,resource to sh;
grant connect,resource to oltp_user;
 
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (
 grantee_name => 'SH',
 privilege_name => 'ADMINISTER_RESOURCE_MANAGER',
 admin_option => FALSE);
END;
/
 
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'OLTP',
 COMMENT => 'Resource consumer for oltp');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'DSS',
 COMMENT => 'Resource consumer for dss');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
 

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'WEEKDAYS',
   COMMENT => 'Resource plan/method for ocm');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'WEEKDAYS', GROUP_OR_SUBPLAN => 'OLTP',
   COMMENT => 'oltp method',
   active_sess_pool_p1 => 20,
   queueing_p1 => 60,
   switch_group =>'DSS',
   switch_time => 5,
   switch_estimate => true,
   max_est_exec_time => 20,
   undo_pool => 20,
   CPU_P1 => 50,
   max_idle_blocker_time => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'WEEKDAYS', GROUP_OR_SUBPLAN => 'DSS',
   COMMENT => 'oltp method',
   active_sess_pool_p1 => 5,
   queueing_p1 => 120,
   CPU_P1 => 30,
   parallel_degree_limit_p1 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'WEEKDAYS', GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
   COMMENT => 'all other users sessions at level 3',
   CPU_P1 => 20);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
 
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.oracle_user,
'OLTP_USER','OLTP'
);
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.oracle_user,
'SH','DSS'
);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
 
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group('OLTP_USER', 'OLTP', false);
END;
/
 
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group('SH', 'DSS', false);
END;
/
 
BEGIN
dbms_resource_manager.switch_plan( plan_name => 'WEEKDAYS', sid => 'PROD' );
END;
/
或
alter system set resource_manager_plan='WEEKDAYS';