DBAtlas · Database operations atlas

Create and activate a simple Resource Manager plan

OracleScheduler

Command

-- 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;
-- /

What it does

Decides which workload wins under CPU contention: gives the OLTP group 70% and the report group 20% of CPU, limits parallelism to 4 in the report group, and cancels calls exceeding 30 minutes (CANCEL_SQL). Users are mapped to consumer groups automatically.

When to use it

For mixed workloads where daytime reports slow down OLTP, to cap uncontrolled parallel queries, and to cancel runaway queries automatically.

Caution

Resource Manager requires Enterprise Edition (very limited on SE2); no extra license. mgmt_p1 percentages apply only under CPU contention and cannot exceed 100% in total at one level. CANCEL_SQL kills the running statement with ORA-00040; a wrong mapping can kill critical work, so test thresholds carefully. While a plan is active, CPU throttling shows up as the 'resmgr:cpu quantum' wait event. For sharing between PDBs at CDB level use CREATE_CDB_PLAN.

Open in DBAtlas →

Related