####一、资源管理授权
SQL> begin
2 dbms_resource_manager_privs.grant_system_privilege(grantee_name =>'SCOTT',
3 privilege_name =>'administer_resource_manager',
4 admin_option =>true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_resource_manager_privs.grant_system_privilege(grantee_name =>'SH',
3 privilege_name =>'administer_resource_manager',
4 admin_option =>true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_resource_manager_privs.grant_system_privilege(grantee_name =>'HR',
3 privilege_name =>'administer_resource_manager',
4 admin_option =>true);
5 end;
6 /
PL/SQL procedure successfully completed.
二、创建、验证待处理区
SQL> begin
2 dbms_resource_manager.create_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_resource_manager.validate_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
三、创建资源组
3.1、创建
SQL> begin
2 dbms_resource_manager.create_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_resource_manager.create_consumer_group(
3 consumer_group => 'OLTP',
4 comment => 'OLTP Resource Manage');
5 dbms_resource_manager.create_consumer_group(
6 consumer_group => 'OLAP',
7 comment => 'Resource Manage');
8 dbms_resource_manager.create_consumer_group(
9 consumer_group => 'DSS',
10 comment => 'DSS Resource manage');
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_resource_manager.submit_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
3.2查询
SQL> select * from dba_rsrc_consumer_groups where CONSUMER_GROUP in ('OLAP','OLTP','DSS');
CONSUMER_GROUP_ID CONSUMER_GROUP CPU_METHOD MGMT_METHOD INTERN COMMENTS CATEGORY STATUS MANDAT
----------------- ------------------------------ -------------------- -------------------- ------ -------------------- -------------------- -------------------- ------
73242 OLAP ROUND-ROBIN ROUND-ROBIN NO Resource Manage OTHER NO
73243 DSS ROUND-ROBIN ROUND-ROBIN NO DSS Resource manage OTHER NO
73244 OLTP ROUND-ROBIN ROUND-ROBIN NO OLTP Resource Manage OTHER NO
四、用户加入资源组授权
为了用户能在在除默认资源组之间切换，需要授予该权限
4.1、创建
SQL> begin
2 dbms_resource_manager.create_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
begin
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'SCOTT',
consumer_group=> 'OLTP',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'SCOTT',
consumer_group=> 'OLAP',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'SCOTT',
consumer_group=> 'DSS',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'HR',
consumer_group=> 'OLTP',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'HR',
consumer_group=> 'OLAP',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'HR',
consumer_group=> 'DSS',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'SH',
consumer_group=> 'OLTP',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'SH',
consumer_group=> 'OLAP',
grant_option=> FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=> 'SH',
consumer_group=> 'DSS',
grant_option=> FALSE);
end;
/
SQL> begin
2 dbms_resource_manager.submit_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
4.2、验证
select * from dba_rsrc_consumer_group_privs;
GRANTEE GRANTED_GROUP GRANT_ INITIA
------------------------------ ------------------------------ ------ ------
HR DSS NO NO
HR OLTP NO NO
SH OLAP NO NO
SCOTT OLTP NO YES
SH DSS NO YES
SCOTT DSS NO NO
PUBLIC LOW_GROUP NO NO
HR OLAP NO YES
SCOTT OLAP NO NO
SYSTEM SYS_GROUP NO YES
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
SH OLTP NO NO
五、用户资源组映射
5.1、创建
SQL> begin
2 dbms_resource_manager.create_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
begin
dbms_resource_manager.set_initial_consumer_group(
user => 'SCOTT',
consumer_group => 'OLTP');
dbms_resource_manager.set_initial_consumer_group(
user => 'HR',
consumer_group => 'OLAP');
dbms_resource_manager.set_initial_consumer_group(
user => 'SH',
consumer_group => 'DSS');
end;
/
SQL> begin
2 dbms_resource_manager.submit_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
5.2、查询
SQL> select * from dba_rsrc_group_mappings where consumer_group in ('OLAP','OLTP','DSS');
ATTRIBUTE VALUE CONSUMER_GROUP STATUS
------------------------------ ------------------------------ ------------------------------ --------------------
ORACLE_USER SH DSS
ORACLE_USER HR OLAP
ORACLE_USER SCOTT OLTP
六、创建资源计划和计划指令
SQL> begin
2 dbms_resource_manager.create_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2
3 DBMS_RESOURCE_MANAGER.CREATE_PLAN (
4 plan => 'myplan',
5 comment => 'my plan for test');
6
7 dbms_resource_manager.create_plan_directive(
8 plan =>'myplan',
9 group_or_subplan =>'OLTP',
10 comment => 'OLTP for test',
11 max_utilization_limit => 30,
12 switch_time => 120,
13 switch_group =>'low_group',
14 max_idle_blocker_time =>300,
15 switch_for_call => true);
16 dbms_resource_manager.create_plan_directive(
17 plan =>'myplan',
18 group_or_subplan =>'OLAP',
19 comment => 'OLAP for test',
20 max_utilization_limit => 20,
21 switch_time => 120,
22 switch_group =>'low_group',
23 max_idle_blocker_time =>300,
24 switch_for_call => true);
25 dbms_resource_manager.create_plan_directive(
26 plan =>'myplan',
27 group_or_subplan =>'DSS',
28 comment => 'DSS for test',
29 max_utilization_limit => 10,
30 switch_time => 120,
31 switch_group =>'low_group',
32 max_idle_blocker_time =>300,
33 switch_for_call => true);
34 dbms_resource_manager.create_plan_directive(
35 plan =>'myplan',
36 group_or_subplan =>'low_group',
37 comment => 'low_group',
38 max_utilization_limit => 10);
39 dbms_resource_manager.create_plan_directive(
40 plan =>'myplan',
41 group_or_subplan =>'other_groups',
42 comment => 'other_groups',
43 max_utilization_limit => 10);
44 dbms_resource_manager.create_plan_directive(
45 plan =>'myplan',
46 group_or_subplan => 'sys_group',
47 comment => 'sys_group',
48 max_utilization_limit => 10);
49 end;
50 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_resource_manager.submit_pending_area;
3 end;
4 /
PL/SQL procedure successfully completed.
这步需要注意，放到和资源计划一起创建，用同一个待处理区。否则报错
ORA-29382: 暂挂区验证失败
ORA-29377: 使用者组 OTHER_GROUPS 不是最高级计划 MYPLAN 的组成部分
ORA-06512: 在 "SYS.DBMS_RMIN", line 437
ORA-06512: 在 "SYS.DBMS_RESOURCE_MANAGER", line 798
ORA-06512: 在 line 2
原因就是官方文档这句
SYS_GROUP
This is the initial consumer group for all sessions created by user accounts SYS or SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.
OTHER_GROUPS
This consumer group contains all sessions that have not been assigned to a consumer group. Every resource plan must contain a directive to OTHER_GROUPS.
七、激活资源计划指令
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
resource_limit boolean TRUE
resource_manage_goldengate boolean FALSE
resource_manager_cpu_allocation integer 2
resource_manager_plan string SCHEDULER[0x4D56]:DEFAULT_MAIN
TENANCE_PLAN
SQL> alter system set resource_manager_plan='myplan';
System altered.
八、查询资源管理相关视图
select * from dba_rsrc_consumer_groups;
select * from dba_rsrc_consumer_group_privs
select * from dba_rsrc_group_mappings；
select * from v$rsrc_plan;
select username,initial_rsrc_consumer_group;
select sid,serial#,username,resource_consumer_group from v$session;
select * from dba_rsrc_plan_directives;
select * from v$rsrc_cons_group_history ;
select * from v$rsrc_plan_history;
select * from v$rsrc_consumer_group;
select * from dba_rsrc_consumer_group_privs;
select * from dba_rsrc_group_mappings;
select * from dba_rsrc_plans;
select * from dba_rsrc_mapping_priority;
select * from dba_rsrc_manager_system_privs;
九、其它资源管理相关操作
9.1.1、创建待处理区域
begin
dbms_resource_manager.create_pending_area;
end;
/
9.1.2、验证待处理区域
begin
dbms_resource_manager.validate_pending_area;
end;
/
9.1.3、清除待处理区域
begin
dbms_resource_manager.clear_pending_area;
end;
/
9.1.4、提交待处理区域
begin
dbms_resource_manager.submit_pending_area;
end;
/
9.2.1、创建资源组
begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'OLTP',
comment => 'OLTP Resource Manage');
dbms_resource_manager.create_consumer_group(
consumer_group => 'OLAP',
comment => 'Resource Manage');
dbms_resource_manager.create_consumer_group(
consumer_group => 'DSS',
comment => 'DSS Resource manage');
end;
/
9.2.2、更新资源组
begin
dbms_resource_manager.update_cosumer_group('DSS','DSS Resource manage');
end;
/
9.2.3、删除资源组
begin
dbms_resource_manager.delete_cosumer_group('DSS');
end;
/
9.2.4、查询资源组
select consumer_group,cpu_method, status from dba_rsrc_consumer_groups order by 1;
9.3.1、注册用户至资源组
begin
dbms_resource_manager.set_initial_consumer_group(
user => 'SCOTT',
consumer_group => 'OLTP');
dbms_resource_manager.set_initial_consumer_group(
user => 'HR',
consumer_group => 'OLAP');
dbms_resource_manager.set_initial_consumer_group(
user => 'SH',
consumer_group => 'DSS');
end;
/
9.4.1、创建简单资源计划
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
simple_plan IN VARCHAR2 DEFAULT NULL,
consumer_group1 IN VARCHAR2 DEFAULT NULL,
group1_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group2 IN VARCHAR2 DEFAULT NULL,
group2_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group3 IN VARCHAR2 DEFAULT NULL,
group3_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group4 IN VARCHAR2 DEFAULT NULL,
group4_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group5 IN VARCHAR2 DEFAULT NULL,
group5_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group6 IN VARCHAR2 DEFAULT NULL,
group6_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group7 IN VARCHAR2 DEFAULT NULL,
group7_cpu IN NUMBER DEFAULT NULL, -- deprecated
consumer_group8 IN VARCHAR2 DEFAULT NULL,
group8_cpu IN NUMBER DEFAULT NULL, -- deprecated
group1_percent IN NUMBER DEFAULT NULL,
group2_percent IN NUMBER DEFAULT NULL,
group3_percent IN NUMBER DEFAULT NULL,
group4_percent IN NUMBER DEFAULT NULL,
group5_percent IN NUMBER DEFAULT NULL,
group6_percent IN NUMBER DEFAULT NULL,
group7_percent IN NUMBER DEFAULT NULL,
group8_percent IN NUMBER DEFAULT NULL);
9.4.2、复杂资源计划
DBMS_RESOURCE_MANAGER.CREATE_PLAN (
plan IN VARCHAR2,
comment IN VARCHAR2 DEFAULT NULL,
cpu_mth IN VARCHAR2 DEFAULT NULL, -- deprecated --CPU 资源的分配方法
active_sess_pool_mth IN VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE', --活动会话池资源分配方法。限制活动会话的数量。所有其他会话都处于非活动状态，并在队列中等待被激活。ACTIVE_SESS_POOL_ABSOLUTE是默认且唯一可用的方法。
parallel_degree_limit_mth IN VARCHAR2 DEFAULT 'PARALLEL_DEGREE_LIMIT_ABSOLUTE',-- 用于指定任何操作的并行度限制的资源分配方法。PARALLEL_DEGREE_LIMIT_ABSOLUTE是默认且唯一可用的方法。
queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',--排队资源分配方法。控制排队的非活动会话的执行顺序。FIFO_TIMEOUT是默认且唯一可用的方法
mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS',指定每个消费者组或子计划获得多少资源（例如，CPU或I/O）的资源分配方法EMPHASIS- 对于使用百分比来指定 I/O 资源如何在消费者组之间分配的多级计划RATIO- 对于使用比率来指定 I/O 资源分配方式的单级计划
sub_plan IN BOOLEAN DEFAULT FALSE,--如果TRUE, 表示该计划仅用作子计划。子计划不需要有OTHER_GROUPS指令。默认为FALSE。
max_iops IN NUMBER DEFAULT NULL,
max_mbps IN NUMBER DEFAULT NULL);
9.4.3、修改资源计划
DBMS_RESOURCE_MANAGER.UPDATE_PLAN (
plan IN VARCHAR2,
new_comment IN VARCHAR2 DEFAULT NULL,
new_cpu_mth IN VARCHAR2 DEFAULT NULL, -- deprecated
new_active_sess_pool_mth IN VARCHAR2 DEFAULT NULL,
new_parallel_degree_limit_mth IN VARCHAR2 DEFAULT NULL,
new_queueing_mth IN VARCHAR2 DEFAULT NULL,
new_mgmt_mth IN VARCHAR2 DEFAULT NULL,
new_sub_plan IN BOOLEAN DEFAULT FALSE,
new_max_iops IN NUMBER DEFAULT NULL,
new_max_mbps IN NUMBER DEFAULT NULL);
9.4.4、删除资源计划
DBMS_RESOURCE_MANAGER.DELETE_PLAN ( plan IN VARCHAR2);
9.5.1、创建资源计划指令
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
comment IN VARCHAR2 DEFAULT NULL,
cpu_p1 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p2 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p3 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p4 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p5 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p6 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p7 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p8 IN NUMBER DEFAULT NULL, -- deprecated
active_sess_pool_p1 IN NUMBER DEFAULT NULL,
queueing_p1 IN NUMBER DEFAULT NULL,
parallel_degree_limit_p1 IN NUMBER DEFAULT NULL,
switch_group IN VARCHAR2 DEFAULT NULL,
switch_time IN NUMBER DEFAULT NULL,
switch_estimate IN BOOLEAN DEFAULT FALSE,
max_est_exec_time IN NUMBER DEFAULT NULL,
undo_pool IN NUMBER DEFAULT NULL,
max_idle_time IN NUMBER DEFAULT NULL,
max_idle_blocker_time IN NUMBER DEFAULT NULL,
switch_time_in_call IN NUMBER DEFAULT NULL, -- deprecated
mgmt_p1 IN NUMBER DEFAULT NULL,
mgmt_p2 IN NUMBER DEFAULT NULL,
mgmt_p3 IN NUMBER DEFAULT NULL,
mgmt_p4 IN NUMBER DEFAULT NULL,
mgmt_p5 IN NUMBER DEFAULT NULL,
mgmt_p6 IN NUMBER DEFAULT NULL,
mgmt_p7 IN NUMBER DEFAULT NULL,
mgmt_p8 IN NUMBER DEFAULT NULL,
switch_io_megabytes IN NUMBER DEFAULT NULL,
switch_io_reqs IN NUMBER DEFAULT NULL,
switch_for_call IN BOOLEAN DEFAULT NULL,
max_utilization_limit IN NUMBER DEFAULT NULL, -- deprecated
parallel_target_percentage IN NUMBER DEFAULT NULL, -- deprecated
parallel_server_limit IN NUMBER DEFAULT NULL,
utilization_limit IN NUMBER DEFAULT NULL,
switch_io_logical IN NUMBER DEFAULT NULL,
switch_elapsed_time IN NUMBER DEFAULT NULL,
shares IN NUMBER DEFAULT NULL,
parallel_stmt_critical IN VARCHAR2 DEFAULT NULL,
session_pga_limit IN NUMBER DEFAULT NULL,
pq_timeout_action IN NUMBER DEFAULT NULL,
parallel_queue_timeout IN NUMBER DEFAULT NULL,);
5.2、修改资源计划指令
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
new_comment IN VARCHAR2 DEFAULT NULL,
new_cpu_p1 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p2 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p3 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p4 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p5 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p6 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p7 IN NUMBER DEFAULT NULL, -- deprecated
new_cpu_p8 IN NUMBER DEFAULT NULL, -- deprecated
new_active_sess_pool_p1 IN NUMBER DEFAULT NULL,
new_queueing_p1 IN NUMBER DEFAULT NULL,
new_parallel_degree_limit_p1 IN NUMBER DEFAULT NULL,
new_switch_group IN VARCHAR2 DEFAULT NULL,
new_switch_time IN NUMBER DEFAULT NULL,
new_switch_estimate IN BOOLEAN DEFAULT FALSE,
new_max_est_exec_time IN NUMBER DEFAULT NULL,
new_undo_pool IN NUMBER DEFAULT NULL,
new_max_idle_time IN NUMBER DEFAULT NULL,
new_max_idle_blocker_time IN NUMBER DEFAULT NULL,
switch_time_in_call IN NUMBER DEFAULT NULL, -- deprecated
new_mgmt_p1 IN NUMBER DEFAULT NULL,
new_mgmt_p2 IN NUMBER DEFAULT NULL,
new_mgmt_p3 IN NUMBER DEFAULT NULL,
new_mgmt_p4 IN NUMBER DEFAULT NULL,
new_mgmt_p5 IN NUMBER DEFAULT NULL,
new_mgmt_p6 IN NUMBER DEFAULT NULL,
new_mgmt_p7 IN NUMBER DEFAULT NULL,
new_mgmt_p8 IN NUMBER DEFAULT NULL,
new_switch_io_megabytes IN NUMBER DEFAULT NULL,
new_switch_io_reqs IN NUMBER DEFAULT NULL,
new_switch_for_call IN BOOLEAN DEFAULT NULL,
new_max_utilization_limit IN NUMBER DEFAULT NULL,
new_parallel_target_percentage IN NUMBER DEFAULT NULL,
new_parallel_queue_timeout IN NUMBER DEFAULT NULL,
new_parallel_server_limit IN NUMBER DEFAULT NULL,
new_utilization_limit IN NUMBER DEFAULT NULL,
new_switch_io_logical IN NUMBER DEFAULT NULL,
new_switch_elapsed_time IN NUMBER DEFAULT NULL,
new_shares IN NUMBER DEFAULT NULL,
new_parallel_stmt_critical IN VARCHAR2 DEFAULT NULL,
new_session_pga_limit IN NUMBER DEFAULT NULL,
new_pq_timeout_action IN NUMBER DEFAULT NULL);
9.5.3、删除资源计划指令
DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2);
9.6.1、动态切换资源组
begin
dbms_resource_manager.switch_consumer_group_for_sess(
session_id => '123',
session_serial => '234',
consumer_group =>'low_group');
end;
/
十、文章参考及链接
10.1、官方文档
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html#GUID-9876C289-99E4-416B-AB6F-D8318642053E
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-643C6A11-7721-4D75-AE88-65CF5AFAF6A4
关于存储过程参数那块建议看12c文档，解释较为详细
10.2、MOS文章参考
Primary Note: Overview of Oracle Resource Manager and DBMS_RESOURCE_MANAGER (Doc ID 1484302.1)
Database Resource Manager samples (Doc ID 106948.1)
Primary Note for Oracle Database Resource Manager (Doc ID 1339769.1)
Scripts and Tips for Monitoring CPU Resource Manager (Doc ID 1338988.1)
10.3、总结
配置这功玩意，11g操作还是使用OEM，12C建议使用EMCC。图形界面直观省事。注意检查不同版本之间存储过程参数之间的变化。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
