간단한 Resource Manager plan 생성 및 활성화

명령어

-- 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 그룹에 CPU 70%, 보고서 그룹에 20%를 주고, 보고서 그룹의 병렬도를 4로 제한하며, 30분을 초과하는 호출을 취소한다(CANCEL_SQL). 사용자는 consumer group에 자동으로 매핑된다.

사용 시점

주간 보고서가 OLTP를 느리게 하는 혼합 워크로드, 통제되지 않는 병렬 쿼리 제한, 폭주 쿼리의 자동 취소용.

주의

Resource Manager는 Enterprise Edition이 필요하며(SE2에서는 매우 제한적) 추가 라이선스는 없다. mgmt_p1 비율은 CPU 경합 시에만 적용되며 한 레벨에서 합계가 100%를 넘을 수 없다. CANCEL_SQL은 실행 중인 문장을 ORA-00040으로 종료한다. 잘못된 매핑은 중요 작업을 종료시킬 수 있으므로 임계값을 신중히 테스트한다. plan이 활성화된 동안 CPU 제한은 'resmgr:cpu quantum' 대기 이벤트로 나타난다. CDB 수준에서 PDB 간 분배에는 CREATE_CDB_PLAN을 사용한다.