Oracle资源管理器简单来说,就是根据人员角色分配CPU资源,例如分配75%的CPU给开发人员,剩下的25%给批处理人员。步骤如下:
1.execute
dbms_resource_manager.create_pending_area();
划分出一块未决的区域
2.execute dbms_resource_manager.create_plan-
(Plan=>'Developers',-
Comment=>'Developers, in Development database');
创建资源计划名称
3.execute dbms_resource_manager.create_consumer_group-
(Consumer_group=>'Batch_developers',-
Comment=>'Batch developers');
execute dbms_resource_manager.create_consumer_group-
(Consumer_group=>'Online_developers',-
Comment=>'Online developers');
建立资源消费组,可将相应的人员划分到相应的组中。
4.execute dbms_resource_manager.create_plan_directive-
(plan=>'developers',-
Group_or_subplan=>'Batch_developers',-
Comment=>'Batch developers',-
Cpu_p1=>75,-
Cpu_p2=>0,-
Parallel_degree_limit_p1=>12);
execute dbms_resource_manager.create_plan_directive-
(plan=>'developers',-
Group_or_subplan=>'Online_developers',-
Comment=>'Online developers',-
Cpu_p1=>25,-
Cpu_p2=>0,-
Parallel_degree_limit_p1=>6);
建立资源规划指令
execute dbms_resource_manager.create_plan_directive-
(plan=>'Developers',-
Group_or_subplan=>'other_groups',-
comment=>'all other users session at level3',-
cpu_p1=>0,-
cpu_p2=>0,-
cpu_p3=>100);
这个很重要,一定要建立Other_groups.未建立组的人员。
5.execute dbms_resource_manager.validate_pending_area();
验证未决区域指令是否正确
execute DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
提交未决区域
execute
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP-
('Jenny','Online_developers',TRUE);
赋予Jenny转换到消费者组的权限
execute dbms_resource_manager.set_initial_consumer_group-
(user=>'Jenny',-
consumer_group=>'Online_developers')
将Jenny加入到资源消费者组中
6.ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=mydb_plan;
最后启用资源管理规划。