Resource Plan

※ consumer group 간 resource mapping

   

▣ Resource Plan 생성

※ Activate this plan : 현재 Plan 활성화

   

▶ show sql

BEGIN

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.create_plan( 'MYPLAN', '');

dbms_resource_manager.create_plan_directive(

    plan => 'MYPLAN',

    group_or_subplan => 'DEFAULT_CONSUMER_GROUP',

    comment => '',

    cpu_p1 => 5, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

    parallel_degree_limit_p1 => NULL,

    active_sess_pool_p1 => NULL,

    queueing_p1 => NULL,

    switch_group => '',

    switch_time => NULL,

    switch_estimate => false,

    max_est_exec_time => NULL,

    undo_pool => NULL,

    max_idle_time => NULL,

    max_idle_blocker_time => NULL,

    switch_time_in_call => NULL

);

dbms_resource_manager.create_plan_directive(

    plan => 'MYPLAN',

    group_or_subplan => 'DW',

    comment => '',

    cpu_p1 => 1, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

    parallel_degree_limit_p1 => NULL,

    active_sess_pool_p1 => NULL,

    queueing_p1 => NULL,

    switch_group => '',

    switch_time => NULL,

    switch_estimate => false,

    max_est_exec_time => NULL,

    undo_pool => NULL,

    max_idle_time => NULL,

    max_idle_blocker_time => NULL,

    switch_time_in_call => NULL

);

dbms_resource_manager.create_plan_directive(

    plan => 'MYPLAN',

    group_or_subplan => 'OLTP',

    comment => '',

    cpu_p1 => 90, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

    parallel_degree_limit_p1 => NULL,

    active_sess_pool_p1 => NULL,

    queueing_p1 => NULL,

    switch_group => '',

    switch_time => NULL,

    switch_estimate => false,

    max_est_exec_time => NULL,

    undo_pool => NULL,

    max_idle_time => NULL,

    max_idle_blocker_time => NULL,

    switch_time_in_call => NULL

);

dbms_resource_manager.create_plan_directive(

    plan => 'MYPLAN',

    group_or_subplan => 'OTHER_GROUPS',

    comment => '',

    cpu_p1 => 4, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,

    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,

    parallel_degree_limit_p1 => NULL,

    active_sess_pool_p1 => NULL,

    queueing_p1 => NULL,

    switch_group => '',

    switch_time => NULL,

    switch_estimate => false,

    max_est_exec_time => NULL,

    undo_pool => NULL,

    max_idle_time => NULL,

    max_idle_blocker_time => NULL,

    switch_time_in_call => NULL

);

dbms_resource_manager.submit_pending_area();

dbms_resource_manager.switch_plan( plan_name => 'MYPLAN', sid => 'orcl' );

END;