课程第13次（2018-11-06星期二）

botang · 发表于 2018-11-6 20:12:00

select * from dba_rsrc_plans p
where p.plan like 'PLAN%';
select * from dba_rsrc_plan_directives pd
where pd.plan='PLAN1';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'PLAN2', comment => 'PLAN2',cpu_mth => 'RATIO' );
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'SYS_GROUP',
comment => 'SYS_GROUP',
mgmt_p1 => 40
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'GROUP1',
comment => 'GROUP1',
mgmt_p1 => 30
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'GROUP2',
comment => 'GROUP2',
mgmt_p1 => 20
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN2',
group_or_subplan => 'OTHER_GROUPS',
comment => 'OTHER_GROUPS',
mgmt_p1 => 10
);
dbms_resource_manager.submit_pending_area();
end;

复制代码

simple plan：

begin
dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
consumer_group1 => 'GROUP1',
group1_cpu => 80,
consumer_group2 => 'GROUP2',
group2_cpu => 20 );
end;
select * from dba_rsrc_plan_directives pd
where pd.plan='PLAN3';
select * from dba_rsrc_plans p
where p.plan like 'PLAN%';

复制代码

关于子计划:

parallel度控制实验：

select s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.USERNAME='HR' and s.TERMINAL='pts/4';
select * from dba_rsrc_plan_directives pd
where pd.plan='PLAN1';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_parallel_degree_limit_p1 => -1
);
dbms_resource_manager.submit_pending_area();
END;

复制代码

select * from dba_rsrc_plan_directives pd
where pd.plan='PLAN1';
select s.TERMINAL , s.USERNAME ,s.STATUS , s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.terminal in ('pts/4','pts/6','pts/7');
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_undo_pool => -1
);
dbms_resource_manager.submit_pending_area();
END;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
new_switch_estimate => true
);
dbms_resource_manager.submit_pending_area();
END;

复制代码

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP2',
new_max_utilization_limit => 40
);
dbms_resource_manager.submit_pending_area();
END;

复制代码

jarlean · 发表于 2018-11-8 18:05:01

补充个脚本，给用户默认组
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'HR',consumer_group => 'GROUP1',grant_option => false);
END;

BEGIN
dbms_resource_manager.set_initial_consumer_group(user => 'HR',consumer_group => 'GROUP1');
END;

botang · 发表于 2018-11-8 19:05:10

jarlean 发表于 2018-11-8 18:05
补充个脚本，给用户默认组
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_na ...