实例
用户组 资源名 计划 DAYTIME 计划 NIGHTTIME 说明
A A
优先权级别 2 2 首先保证上一级的 CPU 使用,有剩余流入下一级
CPU 使用百分比 60 30 某一级别(上一级剩余)下所允许使用 CPU 的最大百分比
并发会话数限制 5 无限制 用户组同时执行任务数
并行度限制 2 2 并行度限制
执行时间控制(秒) 30 无限制 预估执行时间超出此设置的话,报错(ORA-07455)
空闲时间限制(秒) 300 无限制 会话的最大空闲时间
阻塞等待时间(秒) 10 10 当阻塞时,所持有资源释放的时间
插销数据的限制(KB) 200 无限制 用户组的插销数据限制
B B
优先权级别 2 2 同上
CPU 使用百分比 40 70 同上
并发会话数限制 5 无限制 同上
并行度限制 2 2 同上
执行时间控制(秒) 10 无限制 同上
空闲时间限制(秒) 300 无限制 同上
阻塞等待时间(秒) 10 10 同上
插销数据的限制(KB) 200 无限制 同上
C C
优先权级别 3 3 同上
CPU 使用百分比 100 100 同上
并发会话数限制 2 无限制 同上
并行度限制 1 1 同上
执行时间控制(秒) 10 无限制 同上
空闲时间限制(秒) 300 无限制 同上
空闲阻塞时间(秒) 5 5 同上
插销数据的限制(KB) 5 无限制 同上
用户 X、Y、Z;用户组 A 加入 X 和 Y 用户,用户组 B 加入 Y 和 Z 用户,用户组 C 加入 Z 用户。
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan(plan => 'daytime',comment => 'daytime plan');--
dbms_resource_manager.create_plan(plan => 'nighttime',comment => 'nighttime plan');
dbms_resource_manager.create_consumer_group(consumer_group => 'A',comment => 'group a');
dbms_resource_manager.create_consumer_group(consumer_group => 'B',comment => 'group b');
dbms_resource_manager.create_consumer_group(consumer_group => 'C',comment => 'group c');
dbms_resource_manager.create_plan_directive(plan => 'daytime',group_or_subplan => 'sys_group',
comment => 'sys group of daytime plan',cpu_p1 => 100,cpu_p2 => 0,cpu_p3 => 0,cpu_p4 => 0);
dbms_resource_manager.create_plan_directive(plan => 'daytime',group_or_subplan => 'A',
comment => 'a group of daytime plan',cpu_p1 => 0,cpu_p2 => 60,cpu_p3 => 0,cpu_p4 => 0,
active_sess_pool_p1 => 5,parallel_degree_limit_p1 => 2, max_est_exec_time => 30,
max_idle_time => 300, max_idle_blocker_time => 10, undo_pool => 200000);
dbms_resource_manager.create_plan_directive(plan => 'daytime',group_or_subplan => 'B',
comment => 'b group of daytime plan',cpu_p1 => 0,cpu_p2 => 40,cpu_p3 => 0,cpu_p4 => 0,
active_sess_pool_p1 => 5,parallel_degree_limit_p1 => 2, max_est_exec_time => 10,
max_idle_time => 300, max_idle_blocker_time => 10, undo_pool => 200000);
dbms_resource_manager.create_plan_directive(plan => 'daytime',group_or_subplan => 'C',
comment => 'c group of daytime plan',cpu_p1 => 0,cpu_p2 => 0,cpu_p3 => 100,cpu_p4 => 0,
active_sess_pool_p1 => 2,parallel_degree_limit_p1 => 1, max_est_exec_time => 10,
max_idle_time => 300, max_idle_blocker_time => 5, undo_pool => 20000);
dbms_resource_manager.create_plan_directive(plan => 'daytime',group_or_subplan => 'other_groups',
comment => 'other group of daytime plan',cpu_p1 => 0,cpu_p2 => 0,cpu_p3 => 0,cpu_p4 =>100,
parallel_degree_limit_p1 => 1,max_est_exec_time => 10,max_idle_time => 300,
max_idle_blocker_time => 5, undo_pool => 200000);
dbms_resource_manager.create_plan_directive(plan => 'nighttime',group_or_subplan => 'sys_group',
comment => 'sys group of nighttime plan',cpu_p1 => 100,cpu_p2 => 0,cpu_p3 => 0,cpu_p4 => 0);
dbms_resource_manager.create_plan_directive(plan => 'nighttime',group_or_subplan => 'A',
comment => 'a group of nighttime plan',cpu_p1 => 0,cpu_p2 => 30,cpu_p3 => 0,cpu_p4 => 0,
parallel_degree_limit_p1 => 2, max_idle_blocker_time => 10);
第 40 页
dbms_resource_manager.create_plan_directive(plan => 'nighttime',group_or_subplan => 'B',
comment => 'b group of nighttime plan',cpu_p1 => 0,cpu_p2 => 70,cpu_p3 => 0,cpu_p4 => 0,
parallel_degree_limit_p1 => 2, max_idle_blocker_time => 10);
dbms_resource_manager.create_plan_directive(plan => 'nighttime',group_or_subplan => 'C',
comment => 'c group of nighttime plan',cpu_p1 => 0,cpu_p2 => 0,cpu_p3 => 100,cpu_p4 => 0,
parallel_degree_limit_p1 => 1, max_idle_blocker_time => 5);
dbms_resource_manager.create_plan_directive(plan => 'nighttime',group_or_subplan => 'other_groups',
comment => 'other group of nighttime plan',cpu_p1 => 0,cpu_p2 => 0,cpu_p3 => 0,cpu_p4 => 100,
parallel_degree_limit_p1 => 1, max_idle_blocker_time => 5);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
/
创建未决区域(必须),接着创建资源计划,再创建资源用户组,最后创建资源分配方法
注:资源分配时必须设定 other_groups 组
col plan format a10
col group_or_subplan format a12
select plan,group_or_subplan,cpu_p1,cpu_p2,cpu_p3,cpu_p4,parallel_degree_limit_p1 parallel_limit,
active_sess_pool_p1 active_sess, max_est_exec_time max_time,undo_pool,max_idle_time,max_idle_blocker_time idle_blocker
from resource_plan_directive$ where plan in ('DAYTIME','NIGHTTIME') order by plan;
PLAN GROUP_OR_SUB CPU_P1 CPU_P2 CPU_P3 CPU_P4 PARALLEL_LIMIT ACTIVE_SESS MAX_TIME UNDO_POOL MAX_IDLE_TIME IDLE_BLOCKER
DAYTIME OTHER_GROUPS 0 0 0 100 1 4294967295 10 20000 300 5
DAYTIME C 0 0 100 0 1 2 10 10000 300 5
DAYTIME B 0 40 0 0 2 5 10 200000 300 10
DAYTIME A 0 60 0 0 2 5 30 200000 300 10
DAYTIME SYS_GROUP 100 0 0 0 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295
NIGHTTIME OTHER_GROUPS 0 0 0 100 1 4294967295 4294967295 4294967295 4294967295 5
NIGHTTIME C 0 0 100 0 1 4294967295 4294967295 4294967295 4294967295 5
NIGHTTIME B 0 70 0 0 2 4294967295 4294967295 4294967295 4294967295 10
NIGHTTIME A 0 30 0 0 2 4294967295 4294967295 4294967295 4294967295 10
NIGHTTIME SYS_GROUP 100 0 0 0 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295
列出新建计划详细内容
create user x identified by x;
create user y identified by y;
create user z identified by z;
grant connect,resource to x,y,z;
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager_privs.grant_switch_consumer_group('X','A',false);
dbms_resource_manager_privs.grant_switch_consumer_group('X','B',false);
dbms_resource_manager_privs.grant_switch_consumer_group('Y','B',false);
dbms_resource_manager_privs.grant_switch_consumer_group('Y','C',false);
dbms_resource_manager_privs.grant_switch_consumer_group('Z','C',false);
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'X', 'A');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'X', 'B');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'Y', 'B');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'Y', 'C');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'Z', 'C');
dbms_resource_manager.set_initial_consumer_group('X','A');
dbms_resource_manager.set_initial_consumer_group('Y','B');
dbms_resource_manager.set_initial_consumer_group('Z','C');
dbms_resource_manager.submit_pending_area();
end;
/
先授予切换用户组,接着把用户加入用户组最后设定默认用户组
show parameter plan
NAME TYPE VALUE
resource_manager_plan string
资源计划参数,默认值为为 INTERNAL_PLAN
col group_or_subplan format a12
select group_or_subplan,cpu_p1,cpu_p2,cpu_p3,cpu_p4,parallel_degree_limit_p1 parallel_limit,
active_sess_pool_p1 active_sess,queueing_p1,undo_pool,max_idle_time,max_idle_blocker_time idle_blocker
第 41 页
from resource_plan_directive$ where plan='INTERNAL_PLAN';
GROUP_OR_SUB CPU_P1 CPU_P2 CPU_P3 CPU_P4 PARALLEL_LIMIT ACTIVE_SESS QUEUEING_P1 UNDO_POOL MAX_IDLE_TIME IDLE_BLOCKER
OTHER_GROUPS 0 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295
alter system set resource_manager_plan='DAYTIME';
更改资源计划
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞2
添加新评论0 条评论