【Oracle】Database Resource Manager完全ガイド｜コンシューマーグループ・リソースプラン・DBMS_RESOURCE_MANAGER まで解説

-- 現在適用されているリソースプランを確認する
SHOW PARAMETER resource_manager_plan;

-- または V$PARAMETER から確認する
SELECT name, value FROM V$PARAMETER WHERE name = 'resource_manager_plan';
-- 値が空（NULL）= Resource Manager が無効

-- 登録されているコンシューマーグループの一覧
SELECT consumer_group, status, comments
FROM DBA_RSRC_CONSUMER_GROUPS
ORDER BY consumer_group;

-- 登録されているリソースプランの一覧
SELECT plan, status, comments
FROM DBA_RSRC_PLANS
ORDER BY plan;

-- プランディレクティブの詳細（どのグループにどのリソースを割り当てているか）
SELECT
    plan,
    group_or_subplan,
    cpu_p1,                  -- レベル1での CPU 配分（%）
    cpu_p2,                  -- レベル2での CPU 配分（%）
    parallel_degree_limit_p1,-- 並列度の上限
    active_sess_pool_p1,     -- 同時アクティブセッション数の上限
    max_est_exec_time,       -- 推定実行時間の上限（秒）
    undo_pool,               -- UNDO 使用量の上限（KB）
    switch_group             -- CPU 時間超過時の切り替え先グループ
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE plan = 'YOUR_PLAN_NAME'
ORDER BY group_or_subplan;

-- 保留エリアを作成する（作業開始前に必ず実行）
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/

-- コンシューマーグループを作成する
BEGIN
    -- OLTP 業務用グループ（高優先度）
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'OLTP_GROUP',
        comment        => 'OLTP業務セッション（高優先度）'
    );

    -- バッチ処理用グループ（低優先度）
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'BATCH_GROUP',
        comment        => 'バッチ処理セッション（低優先度）'
    );

    -- 分析クエリ用グループ（最低優先度・並列度制限あり）
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'REPORT_GROUP',
        comment        => '分析・レポート処理（最低優先度）'
    );
END;
/

-- リソースプランを作成する
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan    => 'MIXED_WORKLOAD_PLAN',
        comment => 'OLTP・バッチ・レポートの混在環境向けプラン'
    );
END;
/

-- プランディレクティブを設定する（各グループへのリソース配分）
BEGIN
    -- OLTP グループ: CPU 70%（最優先）
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan                    => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan        => 'OLTP_GROUP',
        comment                 => 'OLTP 業務: CPU 最優先',
        cpu_p1                  => 70,    -- レベル1で CPU の 70% を割り当て
        parallel_degree_limit_p1 => 4     -- 並列度の上限
    );

    -- BATCH グループ: CPU 20%
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan                    => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan        => 'BATCH_GROUP',
        comment                 => 'バッチ処理: CPU 20%',
        cpu_p1                  => 20,
        parallel_degree_limit_p1 => 2
    );

    -- REPORT グループ: CPU 10%・実行時間 600 秒で自動切り替え
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan                    => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan        => 'REPORT_GROUP',
        comment                 => 'レポート: CPU 10%・時間制限あり',
        cpu_p1                  => 10,
        parallel_degree_limit_p1 => 8,      -- 並列クエリのみ上限を高く
        switch_time             => 600,      -- 600 秒でグループ切り替え
        switch_group            => 'LOW_GROUP'  -- 切り替え先グループ
    );

    -- OTHER_GROUPS: どのグループにも属さないセッション（必須設定）
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan             => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan => 'OTHER_GROUPS',
        comment          => '未分類セッション',
        cpu_p1           => 0   -- 余剰 CPU を使用
    );
END;
/

-- 保留エリアを確定する（ここで初めて設定が登録される）
BEGIN
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

-- ユーザーにコンシューマーグループの切り替え権限を付与する
BEGIN
    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
        grantee_name   => 'BATCH_USER',
        consumer_group => 'BATCH_GROUP',
        grant_option   => FALSE
    );
END;
/

-- ユーザーのデフォルトコンシューマーグループを設定する（ログイン時のグループ）
BEGIN
    DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
        user           => 'BATCH_USER',
        consumer_group => 'BATCH_GROUP'
    );
END;
/

-- セッション中にグループを切り替える（ユーザー自身が実行）
BEGIN
    DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP(
        new_consumer_group  => 'REPORT_GROUP',
        old_consumer_group  => :old_group,
        initial_group_on_error => FALSE
    );
END;
/

-- ユーザー設定でデフォルトグループを確認する
SELECT username, resource_consumer_group
FROM DBA_USERS
WHERE username = 'BATCH_USER';

-- ALTER USER でデフォルトコンシューマーグループを変更する
ALTER USER BATCH_USER DEFAULT_CONSUMER_GROUP = BATCH_GROUP;

-- 作成したプランをアクティブにする
ALTER SYSTEM SET resource_manager_plan = 'MIXED_WORKLOAD_PLAN' SCOPE=BOTH;

-- 無効にする（Resource Manager を使わない状態に戻す）
ALTER SYSTEM SET resource_manager_plan = '' SCOPE=BOTH;

-- 特定の時間帯に自動的にプランを切り替える場合は DBMS_SCHEDULER と組み合わせる
-- （夜間バッチ用プランを 21:00-05:00 に自動適用するなど）

-- 現在のセッション別コンシューマーグループを確認する
SELECT
    s.sid,
    s.username,
    s.program,
    s.resource_consumer_group,  -- 現在のコンシューマーグループ
    s.status,
    s.event,
    s.seconds_in_wait
FROM V$SESSION s
WHERE s.type = 'USER'
ORDER BY s.resource_consumer_group, s.sid;

-- グループ別の CPU 使用量・待機時間を確認する（V$RSRC_CONSUMER_GROUP）
SELECT
    name,
    active_sessions,      -- アクティブセッション数
    execution_waiters,    -- CPU を待っているセッション数
    consumed_cpu_time,    -- 消費した CPU 時間（ミリ秒）
    cpu_waits,            -- CPU 待機回数
    cpu_wait_time,        -- CPU 待機時間合計（ミリ秒）
    yield_waits           -- 他グループへの CPU 解放回数
FROM V$RSRC_CONSUMER_GROUP
ORDER BY name;

-- AWR に蓄積されたコンシューマーグループ統計を確認する
SELECT
    s.begin_interval_time,
    r.consumer_group_name,
    r.cpu_consumed_time / 1000 AS cpu_sec,
    r.avg_running_sessions,
    r.avg_waiting_sessions
FROM DBA_HIST_RSRC_CONSUMER_GROUP r
JOIN DBA_HIST_SNAPSHOT s ON r.snap_id = s.snap_id
WHERE s.begin_interval_time >= SYSDATE - 1   -- 過去 24 時間
ORDER BY s.begin_interval_time, r.consumer_group_name;