Plan | User | CONSUMER_GROUP | Level 1 | Level 2 | Utilization_Limit |
---|---|---|---|---|---|
Plan_D (Daytime) | APP1 | GROUP_A | 100 | 0 | 100 |
APP2 | |||||
APP3 | GROUP_B | 0 | 5 | 5 | |
APP4 | |||||
Plan_N (Nighttime) | APP1 | GROUP_A | 80 | 0 | 80 |
APP2 | |||||
APP3 | GROUP_B | 0 | 20 | 20 | |
APP4 |
过程记录:
=================================================
[1. Create Pending Area]
=================================================
SQL> exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
PL/SQL procedure successfully completed
SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
PL/SQL procedure successfully completed
=================================================
[2. Create A Resource Planing]
=================================================
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN(
3 plan => 'PLAN_D',
4 comment => 'Plan in daytime',
5 sub_plan => FALSE); --默认值即为 FALSE
6 END;
7 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN(
3 plan => 'PLAN_N',
4 comment => 'Plan in nighttime',
5 sub_plan => FALSE); --默认值即为 FALSE
6 END;
7 /
PL/SQL procedure successfully completed
=================================================
[3. Create Consumer Groups]
=================================================
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
3 consumer_group => 'GROUP_A',
4 comment => 'Group for APP1 and APP2',
5 mgmt_mth => 'ROUND-ROBIN'); --该用户组内各会话的CPU资源采取轮询的分配方法
6 END;
7 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
3 consumer_group => 'GROUP_B',
4 comment => 'Group for APP3 and APP4',
5 mgmt_mth => 'ROUND-ROBIN'); --该用户组内各会话的CPU资源采取轮询的分配方法
6 END;
7 /
PL/SQL procedure successfully completed
=================================================
[4. Map Sessions To Consumer Groups]
=================================================
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
3 attribute => 'ORACLE_USER',
4 value => 'APP1', --将APP1用户会话映射到“GROUP_A”
5 consumer_group => 'GROUP_A');
6 END;
7 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
3 attribute => 'ORACLE_USER',
4 value => 'APP2', --将APP2用户会话映射到“GROUP_A”
5 consumer_group => 'GROUP_A');
6 END;
7 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
3 attribute => 'ORACLE_USER',
4 value => 'APP3', --将APP3用户会话映射到“GROUP_B”
5 consumer_group => 'GROUP_B');
6 END;
7 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
3 attribute => 'ORACLE_USER',
4 value => 'APP4', --将APP4用户会话映射到“GROUP_B”
5 consumer_group => 'GROUP_B');
6 END;
7 /
PL/SQL procedure successfully completed
=================================================
[5. Create Resource Plan Directives]
-- 白天的时候,GROUP_A中的用户将得到最大保证,如果GROUP_A耗尽所有CPU资源,则GROUP_B无法获得CPU资源
-- 白天的时候,只有GROUP_A不占用CPU资源时,才会将CPU资源分配给GROUP_B,且最多分配5%的CPU给GROUP_B
-- 夜晚的时候,GROUP_A中的用户最多使用80%的CPU,将剩余20%的CPU留给GROUP_B中的用户
=================================================
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 plan => 'PLAN_D', --将该Plan Directive 关联到 PLAN "Plan_D"
4 group_or_subplan => 'GROUP_A', --可以指定用户组,也可以指定Sub Plan,和上一个参数构成了 Plan Diretive 的唯一属性
5 comment => 'Directives for GROUP_A in daytime',
6 max_utilization_limit => 100, --CPU使用的最大上限
7 mgmt_p1 => 100, --CPU使用最小限制(Level 1)
8 mgmt_p2 => 0); --CPU使用最小限制(Level 2)
9 END;
10 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 plan => 'PLAN_D', --将该Plan Directive 关联到 PLAN "Plan_D"
4 group_or_subplan => 'GROUP_B', --可以指定用户组,也可以指定Sub Plan,和上一个参数构成了 Plan Diretive 的唯一属性
5 comment => 'Directives for GROUP_B in daytime',
6 max_utilization_limit => 5, --CPU使用的最大上限
7 mgmt_p1 => 0, --CPU使用最小限制(Level 1)
8 mgmt_p2 => 5); --CPU使用最小限制(Level 2)
9 END;
10 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 plan => 'PLAN_N', --将该Plan Directive 关联到 PLAN "Plan_N"
4 group_or_subplan => 'GROUP_A', --可以指定用户组,也可以指定Sub Plan,和上一个参数构成了 Plan Diretive 的唯一属性
5 comment => 'Directives for GROUP_A in nighttime',
6 max_utilization_limit => 80, --CPU使用的最大上限
7 mgmt_p1 => 80, --CPU使用最小限制(Level 1)
8 mgmt_p2 => 0); --CPU使用最小限制(Level 2)
9 END;
10 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 plan => 'PLAN_N', --将该Plan Directive 关联到 PLAN "Plan_N"
4 group_or_subplan => 'GROUP_B', --可以指定用户组,也可以指定Sub Plan,和上一个参数构成了 Plan Diretive 的唯一属性
5 comment => 'Directives for GROUP_B in nighttime',
6 max_utilization_limit => 20, --CPU使用的最大上限
7 mgmt_p1 => 0, --CPU使用最小限制(Level 1)
8 mgmt_p2 => 20); --CPU使用最小限制(Level 2)
9 END;
10 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 plan => 'PLAN_N', --将该Plan Directive 关联到 PLAN "Plan_N"
4 group_or_subplan => 'OTHER_GROUPS', --可以指定用户组,也可以指定Sub Plan,和上一个参数构成了 Plan Diretive 的唯一属性
5 comment => 'Directives for OTHER_GROUPS in nighttime',
6 max_utilization_limit => 0, --CPU使用的最大上限
7 mgmt_p1 => 0, --CPU使用最小限制(Level 1)
8 mgmt_p2 => 0); --CPU使用最小限制(Level 2)
9 END;
10 /
PL/SQL procedure successfully completed
=================================================
[6. Validate & Submit Resource Plan]
=================================================
SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
PL/SQL procedure successfully completed
SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
PL/SQL procedure successfully completed
=================================================
[7. View Resource Plan]
=================================================
-- 查看数据库中定义的资源计划
select plan, mgmt_method, num_plan_directives, comments
from dba_rsrc_plans
where plan like 'PLAN%';
-- 查看资源消费者组定义
select consumer_group, mgmt_method, comments
from dba_rsrc_consumer_groups
where consumer_group like 'GROUP%';
-- 查看用户与资源消费者组的映射关系
select username, initial_rsrc_consumer_group
from dba_users
where username like 'APP_'
order by username
-- 查看资源分配定义
select plan,
group_or_subplan,
type,
max_utilization_limit,
mgmt_p1,
mgmt_p2
from dba_rsrc_plan_directives
where plan like 'PLAN%';
-- 查看当前登录的用户对应的消费者组
select user, resource_consumer_group
from v$session
where type != 'BACKGROUND';
-- 查看当前激活的资源计划
select name, is_top_plan from v$rsrc_plan;
-- 指定当前的资源计划
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PLAN_D';;
System altered
-- 重置当前的资源计划
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
System altered