创建并激活一个简单的 Resource Manager 计划

命令

-- 1) Define groups, plan, directives and mapping inside a pending area (PDB or non-CDB):
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('OLTP_GRP',  'Online transactions');
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('REPORT_GRP', 'Reporting / batch');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN('DAYTIME_PLAN', 'Business hours plan');

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DAYTIME_PLAN', group_or_subplan => 'OLTP_GRP',
    comment => 'OLTP first', mgmt_p1 => 70);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DAYTIME_PLAN', group_or_subplan => 'REPORT_GRP',
    comment => 'Reports limited', mgmt_p1 => 20,
    parallel_degree_limit_p1 => 4,
    switch_group => 'CANCEL_SQL', switch_time => 1800, switch_for_call => TRUE);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DAYTIME_PLAN', group_or_subplan => 'OTHER_GROUPS',
    comment => 'Everyone else (mandatory)', mgmt_p1 => 10);

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value          => 'REPORT_USER',
    consumer_group => 'REPORT_GRP');

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/
-- Switch privilege required for the mapping to take effect:
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('REPORT_USER', 'REPORT_GRP', FALSE);

-- 2) Activate (FORCE: prevents maintenance windows from switching the plan):
ALTER SYSTEM SET resource_manager_plan = 'FORCE:DAYTIME_PLAN' SCOPE = BOTH;

-- 3) Monitor:
SELECT sid, username, resource_consumer_group FROM v$session WHERE username IS NOT NULL;
SELECT name, active_sessions, execution_waiters, consumed_cpu_time, cpu_wait_time
FROM   v$rsrc_consumer_group;

-- 4) Roll back: deactivate the plan, then delete it
-- ALTER SYSTEM SET resource_manager_plan = '' SCOPE = BOTH;
-- BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
--       DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE('DAYTIME_PLAN');
--       DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA; END;
-- /

作用

决定 CPU 争用时哪类负载优先：为 OLTP 组分配 70% CPU、报表组分配 20%，将报表组的并行度限制为 4，并取消超过 30 分钟的调用（CANCEL_SQL）。用户自动映射到消费者组。

适用场景

用于白天报表拖慢 OLTP 的混合负载、限制不受控的并行查询，以及自动取消失控查询。

注意

Resource Manager 需要 Enterprise Edition（在 SE2 上非常有限）；无需额外许可。mgmt_p1 百分比只在 CPU 争用时生效，同一级别的总和不能超过 100%。CANCEL_SQL 会以 ORA-00040 终止正在运行的语句；错误的映射可能会终止关键工作，因此请仔细测试阈值。计划激活期间，CPU 限流表现为 'resmgr:cpu quantum' 等待事件。如需在 CDB 级别于 PDB 之间分配资源，请使用 CREATE_CDB_PLAN。