シンプルな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グループにCPUの70%、レポートグループに20%を割り当て、レポートグループの並列度を4に制限し、30分を超える呼出しを取り消す(CANCEL_SQL)。ユーザーは自動的にコンシューマグループにマッピングされる。

使用する場面

日中のレポートがOLTPを遅くする混在ワークロード、制御されないパラレルクエリの上限設定、暴走したクエリの自動取り消しに。

注意

Resource ManagerにはEnterprise Editionが必要(SE2では非常に限定的)。追加ライセンス不要。mgmt_p1の割合はCPU競合時のみ適用され、1つのレベルで合計100%を超えられない。CANCEL_SQLは実行中の文をORA-00040で強制終了する。誤ったマッピングは重要な処理を終了させる可能性があるため、しきい値は慎重にテストする。プランが有効な間、CPUの抑制は'resmgr:cpu quantum'待機イベントとして現れる。CDBレベルでのPDB間の配分にはCREATE_CDB_PLANを使用する。